Mas DB2 Notes: November 2021

Tuesday, November 30, 2021

Installing mongodb

Reference: Install MongoDB Community Edition on Red Hat or CentOS — MongoDB Manual

Configure the package management system (`yum`).

Create a /etc/yum.repos.d/mongodb-org-5.0.repo file so that you can install MongoDB directly using yum:

[mongodb-org-5.0]
name=MongoDB Repository
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/5.0/x86_64/
gpgcheck=1
enabled=1
gpgkey=https://www.mongodb.org/static/pgp/server-5.0.asc

[root@unicaapdemo yum.repos.d]# yum install -y mongodb-org

MongoDB Repository 14 kB/s | 14 kB 00:00

Dependencies resolved.

======================================================================================================================================================================

Package Architecture Version Repository Size

======================================================================================================================================================================

Installing:

mongodb-org x86_64 5.0.4-1.el8 mongodb-org-5.0 11 k

Installing dependencies:

mongodb-database-tools x86_64 100.5.1-1 mongodb-org-5.0 47 M

mongodb-mongosh x86_64 1.1.4-1.el7 mongodb-org-5.0 43 M

mongodb-org-database x86_64 5.0.4-1.el8 mongodb-org-5.0 11 k

mongodb-org-database-tools-extra x86_64 5.0.4-1.el8 mongodb-org-5.0 16 k

mongodb-org-mongos x86_64 5.0.4-1.el8 mongodb-org-5.0 19 M

mongodb-org-server x86_64 5.0.4-1.el8 mongodb-org-5.0 28 M

mongodb-org-shell x86_64 5.0.4-1.el8 mongodb-org-5.0 15 M

mongodb-org-tools x86_64 5.0.4-1.el8 mongodb-org-5.0 11 k

Transaction Summary

======================================================================================================================================================================

Install 9 Packages

Total download size: 153 M

Installed size: 559 M

Downloading Packages:

(1/9): mongodb-org-5.0.4-1.el8.x86_64.rpm 39 kB/s | 11 kB 00:00

(2/9): mongodb-org-database-5.0.4-1.el8.x86_64.rpm 39 kB/s | 11 kB 00:00

(3/9): mongodb-org-database-tools-extra-5.0.4-1.el8.x86_64.rpm 66 kB/s | 16 kB 00:00

(4/9): mongodb-org-mongos-5.0.4-1.el8.x86_64.rpm 4.7 MB/s | 19 MB 00:04

(5/9): mongodb-mongosh-1.1.4.el8.x86_64.rpm 4.0 MB/s | 43 MB 00:10

(6/9): mongodb-org-server-5.0.4-1.el8.x86_64.rpm 4.5 MB/s | 28 MB 00:06

(7/9): mongodb-org-tools-5.0.4-1.el8.x86_64.rpm 41 kB/s | 11 kB 00:00

(8/9): mongodb-org-shell-5.0.4-1.el8.x86_64.rpm 4.6 MB/s | 15 MB 00:03

(9/9): mongodb-database-tools-100.5.1.x86_64.rpm 3.3 MB/s | 47 MB 00:14

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Total 11 MB/s | 153 MB 00:14

warning: /var/cache/dnf/mongodb-org-5.0-85529b2afce6c228/packages/mongodb-database-tools-100.5.1.x86_64.rpm: Header V3 RSA/SHA1 Signature, key ID e2c63c11: NOKEY

MongoDB Repository 1.6 kB/s | 1.6 kB 00:00

Importing GPG key 0xE2C63C11:

Userid : "MongoDB 5.0 Release Signing Key <packaging@mongodb.com>"

Fingerprint: F567 9A22 2C64 7C87 527C 2F8C B00A 0BD1 E2C6 3C11

From : https://www.mongodb.org/static/pgp/server-5.0.asc

Key imported successfully

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Installing : mongodb-org-database-tools-extra-5.0.4-1.el8.x86_64 1/9

Installing : mongodb-org-shell-5.0.4-1.el8.x86_64 2/9

Running scriptlet: mongodb-org-server-5.0.4-1.el8.x86_64 3/9

Installing : mongodb-org-server-5.0.4-1.el8.x86_64 3/9

Running scriptlet: mongodb-org-server-5.0.4-1.el8.x86_64 3/9

Created symlink /etc/systemd/system/multi-user.target.wants/mongod.service → /usr/lib/systemd/system/mongod.service.

Installing : mongodb-org-mongos-5.0.4-1.el8.x86_64 4/9

Installing : mongodb-org-database-5.0.4-1.el8.x86_64 5/9

Installing : mongodb-mongosh-1.1.4-1.el7.x86_64 6/9

Running scriptlet: mongodb-database-tools-100.5.1-1.x86_64 7/9

Installing : mongodb-database-tools-100.5.1-1.x86_64 7/9

Running scriptlet: mongodb-database-tools-100.5.1-1.x86_64 7/9

Installing : mongodb-org-tools-5.0.4-1.el8.x86_64 8/9

Installing : mongodb-org-5.0.4-1.el8.x86_64 9/9

Running scriptlet: mongodb-org-5.0.4-1.el8.x86_64 9/9

Verifying : mongodb-database-tools-100.5.1-1.x86_64 1/9

Verifying : mongodb-mongosh-1.1.4-1.el7.x86_64 2/9

Verifying : mongodb-org-5.0.4-1.el8.x86_64 3/9

Verifying : mongodb-org-database-5.0.4-1.el8.x86_64 4/9

Verifying : mongodb-org-database-tools-extra-5.0.4-1.el8.x86_64 5/9

Verifying : mongodb-org-mongos-5.0.4-1.el8.x86_64 6/9

Verifying : mongodb-org-server-5.0.4-1.el8.x86_64 7/9

Verifying : mongodb-org-shell-5.0.4-1.el8.x86_64 8/9

Verifying : mongodb-org-tools-5.0.4-1.el8.x86_64 9/9

Installed products updated.

Installed:

mongodb-database-tools-100.5.1-1.x86_64 mongodb-mongosh-1.1.4-1.el7.x86_64 mongodb-org-5.0.4-1.el8.x86_64

mongodb-org-database-5.0.4-1.el8.x86_64 mongodb-org-database-tools-extra-5.0.4-1.el8.x86_64 mongodb-org-mongos-5.0.4-1.el8.x86_64

mongodb-org-server-5.0.4-1.el8.x86_64 mongodb-org-shell-5.0.4-1.el8.x86_64 mongodb-org-tools-5.0.4-1.el8.x86_64

Complete!

[root@unicaapdemo yum.repos.d]# vi /etc/mongod.conf

[root@unicaapdemo mongodb]# sudo semanage fcontext -a -t mongod_var_lib_t '/apps/mongodb/data'

[root@unicaapdemo mongodb]# sudo chcon -Rv -u system_u -t mongod_var_lib_t '/apps/mongodb/data'

changing security context of '/apps/mongodb/data'

[root@unicaapdemo mongodb]# sudo restorecon -R -v '/apps/mongodb/data'

[root@unicaapdemo mongodb]#

[root@unicaapdemo mongodb]# sudo semanage fcontext -a -t mongod_log_t '/apps/mongodb/logs'

[root@unicaapdemo mongodb]# sudo chcon -Rv -u system_u -t mongod_log_t '/apps/mongodb/logs'

changing security context of '/apps/mongodb/logs'

[root@unicaapdemo mongodb]# sudo restorecon -R -v '/apps/mongodb/logs'

[root@unicaapdemo mongodb]# ls -ld /apps/mongodb/logs

drwxr-xr-x. 2 mongod mongod 6 Dec 1 14:11 /apps/mongodb/logs

[root@unicaapdemo mongodb]# ls -ld /apps/mongodb/data

[root@unicaapdemo mongodb]# cat /etc/mongod.conf

# mongod.conf

# for documentation of all options, see:

# http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.

systemLog:

destination: file

logAppend: true

path: /apps/mongodb/logs/mongod.log

# Where and how to store data.

storage:

dbPath: /apps/mongodb/data

journal:

enabled: true

# engine:

# wiredTiger:

[root@unicaapdemo mongodb]# sudo systemctl start mongod

[root@unicaapdemo mongodb]# systemctl status mongod

● mongod.service - MongoDB Database Server

Loaded: loaded (/usr/lib/systemd/system/mongod.service; enabled; vendor preset: disabled)

Active: active (running) since Wed 2021-12-01 14:18:19 +08; 20s ago

Docs: https://docs.mongodb.org/manual

Process: 140583 ExecStart=/usr/bin/mongod $OPTIONS (code=exited, status=0/SUCCESS)

Process: 140580 ExecStartPre=/usr/bin/chmod 0755 /var/run/mongodb (code=exited, status=0/SUCCESS)

Process: 140578 ExecStartPre=/usr/bin/chown mongod:mongod /var/run/mongodb (code=exited, status=0/SUCCESS)

Process: 140576 ExecStartPre=/usr/bin/mkdir -p /var/run/mongodb (code=exited, status=0/SUCCESS)

Main PID: 140585 (mongod)

Memory: 65.4M

CGroup: /system.slice/mongod.service

└─140585 /usr/bin/mongod -f /etc/mongod.conf

Dec 01 14:18:19 unicaapdemo systemd[1]: Starting MongoDB Database Server...

Dec 01 14:18:19 unicaapdemo mongod[140583]: about to fork child process, waiting until server is ready for connections.

Dec 01 14:18:19 unicaapdemo mongod[140583]: forked process: 140585

Dec 01 14:18:19 unicaapdemo mongod[140583]: child process started successfully, parent exiting

Dec 01 14:18:19 unicaapdemo systemd[1]: Started MongoDB Database Server.

[root@unicaapdemo mongodb]# mongo

MongoDB shell version v5.0.4

connecting to: mongodb://127.0.0.1:27017/?compressors=disabled&gssapiServiceName=mongodb

Implicit session: session { "id" : UUID("7ca50204-948d-4836-8447-9db93d22df09") }

MongoDB server version: 5.0.4

================

Warning: the "mongo" shell has been superseded by "mongosh",

which delivers improved usability and compatibility.The "mongo" shell has been deprecated and will be removed in

an upcoming release.

For installation instructions, see

https://docs.mongodb.com/mongodb-shell/install/

================

Welcome to the MongoDB shell.

For interactive help, type "help".

For more comprehensive documentation, see

https://docs.mongodb.com/

Questions? Try the MongoDB Developer Community Forums

https://community.mongodb.com

---

The server generated these startup warnings when booting:

2021-12-01T14:18:19.627+08:00: Access control is not enabled for the database. Read and write access to data and configuration is unrestricted

2021-12-01T14:18:19.627+08:00: /sys/kernel/mm/transparent_hugepage/enabled is 'always'. We suggest setting it to 'never'

---

Enable MongoDB's free cloud-based monitoring service, which will then receive and display

metrics about your deployment (disk utilization, CPU, operation statistics, etc).

The monitoring data will be available on a MongoDB website with a unique URL accessible to you

and anyone you share the URL with. MongoDB may use this information to make product

improvements and to suggest MongoDB products and deployment options to you.

To enable free monitoring, run the following command: db.enableFreeMonitoring()

To permanently disable this reminder, run the following command: db.disableFreeMonitoring()

---

> show dbs

admin 0.000GB

config 0.000GB

local 0.000GB

Sunday, November 28, 2021

mariadb truncate table with foreign constraints

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE usm_configuration;

SET FOREIGN_KEY_CHECKS = 1;

OneDB pre-requisites

Need to install the following packages:

yum install libncurses*

Thursday, November 25, 2021

Migrate SQLServer Windows to Unix

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-migrate-restore-database?view=sql-server-ver15

https://www.sqlshack.com/move-sql-database-files-in-sql-server-2019-on-linux/

https://www.mssqltips.com/sqlservertip/5346/migrate-sql-server-database-from-windows-to-sql-server-on-linux-in-cloud-part-3/

[root@database-rhel84 data]# mkdir mssql-data

[root@database-rhel84 data]# ls -l

total 1260160

-rw-r--r--. 1 root root 1290392111 Nov 22 18:16 dmdb.sql.gz

drwxr-xr-x. 2 root root 6 Nov 26 06:40 mssql-data

drwxr-xr-x. 3 root root 4096 Nov 24 14:07 mssqldbbackup

drwxr-xr-x. 13 mysql mysql 4096 Nov 26 01:52 mysql-data

[root@database-rhel84 mssqldbbackup]# sqlcmd -S localhost -U sa

Password:

1> RESTORE FILELISTONLY FROM DISK = '/data/mssqldbbackup/dcc.bak'

2> go

LogicalName PhysicalName Type FileGroupName Size MaxSize FileId CreateLSN DropLSN UniqueId ReadOnlyLSN ReadWriteLSN BackupSizeInBytes SourceBlockSize FileGroupId LogGroupGUID DifferentialBaseLSN DifferentialBaseGUID IsReadOnly IsPresent TDEThumbprint SnapshotUrl

-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---- -------------------------------------------------------------------------------------------------------------------------------- -------------------- -------------------- -------------------- --------------------------- --------------------------- ------------------------------------ --------------------------- --------------------------- -------------------- --------------- ----------- ------------------------------------ --------------------------- ------------------------------------ ---------- --------- ------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

demoCombined_Data C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dcc.mdf D PRIMARY 2242314240 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 2062352384 512 1 NULL 106335000000033000037 C81F6BD0-1B95-496B-9CEE-7F2CE95D86DC 0 1 NULL NULL

demoCombined_Log C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\dcc.LDF L NULL 786432 2199023255552 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 1 NULL NULL

(2 rows affected)

root@database-rhel84 data]# sqlcmd -S localhost -U sa

Password:

1> SELECT Name FROM sys.Databases

2> go

Name

--------------------------------------------------------------------------------------------------------------------------------

master

tempdb

model

msdb

dcc

(5 rows affected)

Tuesday, November 23, 2021

Increase mariadb max connections

[root@database-rhel84 my.cnf.d]# vi server.cnf

[root@database-rhel84 my.cnf.d]# pwd

/etc/my.cnf.d

# this is only for the mysqld standalone daemon

[mysqld]

datadir=/data/mysql-data

socket=/data/mysql-data/mysql.sock

innodb-page-size=32768

character-set-server=utf8

sql_mode='NO_BACKSLASH_ESCAPES'

innodb_strict_mode=0

lower_case_table_names = 1

max_connections=500

[root@database-rhel84 my.cnf.d]# mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 3

Server version: 10.5.13-MariaDB MariaDB Server

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SET GLOBAL max_connections=500

-> ;

Query OK, 0 rows affected (0.000 sec)

You can do the following to check:

MariaDB [(none)]> show variables

Monday, November 22, 2021

Moving mariadb database

https://www.digitalocean.com/community/tutorials/how-to-import-and-export-databases-in-mysql-or-mariadb

[root@database-rhel79 dmdbmariadbbackup]# mysqldump -p dmdb > dmdb.sql

Enter password:

[root@database-rhel79 dmdbmariadbbackup]# head dmdb.sql

-- MariaDB dump 10.19 Distrib 10.5.9-MariaDB, for Linux (x86_64)

-- Host: localhost Database: dmdb

-- ------------------------------------------------------

-- Server version 10.4.21-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8mb4 */;

[root@database-rhel84 data]# mysql -p dmdb < dmdb.sql

Enter password:

[root@database-rhel84 data]#

Friday, November 19, 2021

Setting up mariaDB OCBC on redhat

1. install unixODBC - yum install unixODBC

2. install mariadb ODBC

[root@unicaapdemo bin]# yum install mariadb-connector-odbc

Last metadata expiration check: 16:31:52 ago on Fri 19 Nov 2021 10:00:09 PM +08.

Dependencies resolved.

=============================================================================================================================================================================

Package Architecture Version Repository Size

=============================================================================================================================================================================

Installing:

mariadb-connector-odbc x86_64 3.0.7-1.el8 LocalRepo_AppStream 113 k

Transaction Summary

=============================================================================================================================================================================

Install 1 Package

Total size: 113 k

Installed size: 351 k

Is this ok [y/N]: y

Downloading Packages:

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Installing : mariadb-connector-odbc-3.0.7-1.el8.x86_64 1/1

Running scriptlet: mariadb-connector-odbc-3.0.7-1.el8.x86_64 1/1

Verifying : mariadb-connector-odbc-3.0.7-1.el8.x86_64 1/1

Installed products updated.

Installed:

mariadb-connector-odbc-3.0.7-1.el8.x86_64

Complete!

3. Add entry in /etc/odbcinst.ini

[MariaDB]

Description=ODBC for MariaDB

Driver=/usr/lib64/libmaodbc.so

Driver64=/usr/lib64/libmaodbc.so

FileUsage=1

4 Add entry in /etc/odbc.ini (match odbcinst.ini)

[ucdb]

Driver=MariaDB

User=unica

Password=unica@987!

Database=ucdb

Server=192.168.1.89

Port=3306

5. Test connection:

[root@unicaapdemo bin]# isql ucdb

+---------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

+---------------------------------------+

SQL>

odbcinst -j

[root@unicaapdemo mariadb]# odbcinst -j

unixODBC 2.3.7

DRIVERS............: /etc/odbcinst.ini

SYSTEM DATA SOURCES: /etc/odbc.ini

FILE DATA SOURCES..: /etc/ODBCDataSources

USER DATA SOURCES..: /etc/odbc.ini

SQLULEN Size.......: 8

SQLLEN Size........: 8

SQLSETPOSIROW Size.: 8

Proper way to setup mariadb repo and upgrade client for 10.4 to 10.5

https://mariadb.com/kb/en/yum/#installing-mariadb-packages-with-yum

[root@unicaapdemo yum.repos.d]# curl -LsS -O https://downloads.mariadb.com/MariaDB/mariadb_repo_setup

[root@unicaapdemo yum.repos.d]# ls

local.repo mariadb.repo MariaDB.repo mariadb_repo_setup msprod.repo redhat.repo

[root@unicaapdemo yum.repos.d]# sudo bash mariadb_repo_setup --mariadb-server-version=10.5

[info] Checking for script prerequisites.

[warning] Found existing file at /etc/yum.repos.d/mariadb.repo. Moving to /etc/yum.repos.d/mariadb.repo.old_1

[info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo

[info] Adding trusted package signing keys...

/etc/pki/rpm-gpg /etc/yum.repos.d

/etc/yum.repos.d

[info] Successfully added trusted package signing keys

[info] Cleaning package cache...

0 files removed

Upgrade client from 10.4 to 10.5

[root@unicaapdemo yum.repos.d]# yum remove MariaDB-client MariaDB-shared

Dependencies resolved.

======================================================================================================================================================================

Package Architecture Version Repository Size

======================================================================================================================================================================

Removing:

MariaDB-client x86_64 10.4.21-1.el8 @@commandline 59 M

MariaDB-shared x86_64 10.4.21-1.el8 @mariadb 331 k

Removing dependent packages:

MariaDB-common x86_64 10.4.21-1.el8 @@commandline 301 k

Transaction Summary

======================================================================================================================================================================

Remove 3 Packages

Freed space: 60 M

Is this ok [y/N]: y

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Running scriptlet: MariaDB-client-10.4.21-1.el8.x86_64 1/3

Erasing : MariaDB-client-10.4.21-1.el8.x86_64 1/3

Running scriptlet: MariaDB-client-10.4.21-1.el8.x86_64 1/3

Running scriptlet: MariaDB-common-10.4.21-1.el8.x86_64 2/3

Erasing : MariaDB-common-10.4.21-1.el8.x86_64 2/3

Running scriptlet: MariaDB-common-10.4.21-1.el8.x86_64 2/3

Running scriptlet: MariaDB-shared-10.4.21-1.el8.x86_64 3/3

Erasing : MariaDB-shared-10.4.21-1.el8.x86_64 3/3

Running scriptlet: MariaDB-shared-10.4.21-1.el8.x86_64 3/3

Verifying : MariaDB-client-10.4.21-1.el8.x86_64 1/3

Verifying : MariaDB-common-10.4.21-1.el8.x86_64 2/3

Verifying : MariaDB-shared-10.4.21-1.el8.x86_64 3/3

Installed products updated.

Removed:

MariaDB-client-10.4.21-1.el8.x86_64 MariaDB-common-10.4.21-1.el8.x86_64 MariaDB-shared-10.4.21-1.el8.x86_64

Complete!

[root@unicaapdemo yum.repos.d]# yum install MariaDB-client MariaDB-shared

MariaDB 233 kB/s | 597 kB 00:02

LocalRepository_BaseOS 266 MB/s | 2.3 MB 00:00

LocalRepository_AppStream 308 MB/s | 6.8 MB 00:00

MariaDB Server 422 kB/s | 597 kB 00:01

MariaDB MaxScale 5.2 kB/s | 8.0 kB 00:01

MariaDB Tools 2.6 kB/s | 3.6 kB 00:01

packages-microsoft-com-prod 9.7 MB/s | 7.9 MB 00:00

Dependencies resolved.

======================================================================================================================================================================

Package Architecture Version Repository Size

======================================================================================================================================================================

Installing:

MariaDB-client x86_64 10.5.13-1.el8 mariadb-main 13 M

MariaDB-shared x86_64 10.5.13-1.el8 mariadb 115 k

Installing dependencies:

MariaDB-common x86_64 10.5.13-1.el8 mariadb-main 87 k

Transaction Summary

======================================================================================================================================================================

Install 3 Packages

Total download size: 14 M

Installed size: 68 M

Is this ok [y/N]: y

Downloading Packages:

(1/3): MariaDB-shared-10.5.13-1.el8.x86_64.rpm 82 kB/s | 115 kB 00:01

(2/3): MariaDB-common-10.5.13-1.el8.x86_64.rpm 51 kB/s | 87 kB 00:01

(3/3): MariaDB-client-10.5.13-1.el8.x86_64.rpm 2.9 MB/s | 13 MB 00:04

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Total 3.0 MB/s | 14 MB 00:04

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Running scriptlet: MariaDB-common-10.5.13-1.el8.x86_64 1/3

Installing : MariaDB-common-10.5.13-1.el8.x86_64 1/3

Running scriptlet: MariaDB-common-10.5.13-1.el8.x86_64 1/3

Running scriptlet: MariaDB-shared-10.5.13-1.el8.x86_64 2/3

Installing : MariaDB-shared-10.5.13-1.el8.x86_64 2/3

Running scriptlet: MariaDB-shared-10.5.13-1.el8.x86_64 2/3

Running scriptlet: MariaDB-client-10.5.13-1.el8.x86_64 3/3

Installing : MariaDB-client-10.5.13-1.el8.x86_64 3/3

Running scriptlet: MariaDB-client-10.5.13-1.el8.x86_64 3/3

Verifying : MariaDB-shared-10.5.13-1.el8.x86_64 1/3

Verifying : MariaDB-client-10.5.13-1.el8.x86_64 2/3

Verifying : MariaDB-common-10.5.13-1.el8.x86_64 3/3

Installed products updated.

Installed:

MariaDB-client-10.5.13-1.el8.x86_64 MariaDB-common-10.5.13-1.el8.x86_64 MariaDB-shared-10.5.13-1.el8.x86_64

Complete!

Thursday, November 18, 2021

Upgrading fom mariadb 10.4 to 10.5

Reference:

https://mariadb.com/kb/en/upgrading-from-mariadb-104-to-mariadb-105/

https://computingforgeeks.com/how-to-install-mariadb-server-on-centos-rhel-linux/

1. Shutdown all client applications (eg. tomcat)

2. Shutdown mariadb

[root@database-rhel84 yum.repos.d]# systemctl stop mariadb

[root@database-rhel84 yum.repos.d]# systemctl status mariadb

● mariadb.service - MariaDB 10.4.22 database server

Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)

Drop-In: /etc/systemd/system/mariadb.service.d

└─migrated-from-my.cnf-settings.conf

Active: inactive (dead)

Docs: man:mysqld(8)

https://mariadb.com/kb/en/library/systemd/

Nov 19 20:09:26 database-rhel84 mysqld[40315]: 2021-11-19 20:09:26 0 [Note] InnoDB: FTS optimize thread exiting.

Nov 19 20:09:26 database-rhel84 mysqld[40315]: 2021-11-19 20:09:26 0 [Note] InnoDB: Starting shutdown...

Nov 19 20:09:26 database-rhel84 mysqld[40315]: 2021-11-19 20:09:26 0 [Note] InnoDB: Dumping buffer pool(s) to /data/mysql-data/ib_buffer_pool

Nov 19 20:09:26 database-rhel84 mysqld[40315]: 2021-11-19 20:09:26 0 [Note] InnoDB: Instance 0, restricted to 1013 pages due to innodb_buf_pool_dump_pct=25

Nov 19 20:09:26 database-rhel84 mysqld[40315]: 2021-11-19 20:09:26 0 [Note] InnoDB: Buffer pool(s) dump completed at 211119 20:09:26

Nov 19 20:09:27 database-rhel84 mysqld[40315]: 2021-11-19 20:09:27 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"

Nov 19 20:09:27 database-rhel84 mysqld[40315]: 2021-11-19 20:09:27 0 [Note] InnoDB: Shutdown completed; log sequence number 236382536; transaction id 89052

Nov 19 20:09:27 database-rhel84 mysqld[40315]: 2021-11-19 20:09:27 0 [Note] /usr/sbin/mysqld: Shutdown complete

Nov 19 20:09:27 database-rhel84 systemd[1]: mariadb.service: Succeeded.

Nov 19 20:09:27 database-rhel84 systemd[1]: Stopped MariaDB 10.4.22 database server.

3. Add 10.5 repo

[root@database-rhel84 yum.repos.d]# sudo bash mariadb_repo_setup --mariadb-server-version=10.5

[info] Checking for script prerequisites.

[warning] Found existing file at /etc/yum.repos.d/mariadb.repo. Moving to /etc/yum.repos.d/mariadb.repo.old_1

[info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo

[info] Adding trusted package signing keys...

/etc/pki/rpm-gpg /etc/yum.repos.d

/etc/yum.repos.d

[info] Successfully added trusted package signing keys

[info] Cleaning package cache...

40 files removed

4. Uninstall old version of Mariadb

[root@database-rhel84 yum.repos.d]# sudo yum remove MariaDB-server

Dependencies resolved.

=======================================================================================================================================================================

Package Architecture Version Repository Size

=======================================================================================================================================================================

Removing:

MariaDB-server x86_64 10.4.22-1.el8 @mariadb-main 125 M

Removing unused dependencies:

galera-4 x86_64 26.4.9-1.el8 @mariadb-main 61 M

socat x86_64 1.7.3.3-2.el8 @LocalRepo_AppStream 1.1 M

Transaction Summary

=======================================================================================================================================================================

Remove 3 Packages

Freed space: 187 M

Is this ok [y/N]: y

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Running scriptlet: MariaDB-server-10.4.22-1.el8.x86_64 1/1

Running scriptlet: MariaDB-server-10.4.22-1.el8.x86_64 1/3

Erasing : MariaDB-server-10.4.22-1.el8.x86_64 1/3

warning: /etc/my.cnf.d/server.cnf saved as /etc/my.cnf.d/server.cnf.rpmsave

Running scriptlet: MariaDB-server-10.4.22-1.el8.x86_64 1/3

Running scriptlet: galera-4-26.4.9-1.el8.x86_64 2/3

Erasing : galera-4-26.4.9-1.el8.x86_64 2/3

Running scriptlet: galera-4-26.4.9-1.el8.x86_64 2/3

Erasing : socat-1.7.3.3-2.el8.x86_64 3/3

Running scriptlet: socat-1.7.3.3-2.el8.x86_64 3/3

Verifying : MariaDB-server-10.4.22-1.el8.x86_64 1/3

Verifying : galera-4-26.4.9-1.el8.x86_64 2/3

Verifying : socat-1.7.3.3-2.el8.x86_64 3/3

Installed products updated.

Removed:

MariaDB-server-10.4.22-1.el8.x86_64 galera-4-26.4.9-1.el8.x86_64 socat-1.7.3.3-2.el8.x86_64

Complete!

6. Install new version of mariadb

[root@database-rhel84 yum.repos.d]# sudo yum install MariaDB-server MariaDB-client MariaDB-backup

MariaDB Server 457 kB/s | 597 kB 00:01

MariaDB MaxScale 4.7 kB/s | 8.0 kB 00:01

MariaDB Tools 3.2 kB/s | 3.6 kB 00:01

packages-microsoft-com-prod 9.7 MB/s | 7.9 MB 00:00

packages-microsoft-com-mssql-server-2019 8.5 MB/s | 4.3 MB 00:00

LocalRepository_BaseOS 307 MB/s | 2.3 MB 00:00

LocalRepository_AppStream 328 MB/s | 6.8 MB 00:00

Package MariaDB-client-10.4.22-1.el8.x86_64 is already installed.

Package MariaDB-backup-10.4.22-1.el8.x86_64 is already installed.

Dependencies resolved.

=======================================================================================================================================================================

Package Architecture Version Repository Size

=======================================================================================================================================================================

Installing:

MariaDB-server x86_64 10.5.13-1.el8 mariadb-main 26 M

Upgrading:

MariaDB-backup x86_64 10.5.13-1.el8 mariadb-main 7.0 M

MariaDB-client x86_64 10.5.13-1.el8 mariadb-main 13 M

Installing dependencies:

galera-4 x86_64 26.4.9-1.el8 mariadb-main 13 M

socat x86_64 1.7.3.3-2.el8 LocalRepo_AppStream 302 k

Transaction Summary

=======================================================================================================================================================================

Install 3 Packages

Upgrade 2 Packages

Total size: 60 M

Total download size: 60 M

Is this ok [y/N]: y

Downloading Packages:

(1/4): MariaDB-backup-10.5.13-1.el8.x86_64.rpm 1.8 MB/s | 7.0 MB 00:03

(2/4): MariaDB-server-10.5.13-1.el8.x86_64.rpm 4.0 MB/s | 26 MB 00:06

(3/4): MariaDB-client-10.5.13-1.el8.x86_64.rpm 3.1 MB/s | 13 MB 00:04

(4/4): galera-4-26.4.9-1.el8.x86_64.rpm 992 kB/s | 13 MB 00:13

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Total 4.4 MB/s | 60 MB 00:13

Running transaction check

Transaction check succeeded.

Running transaction test

Transaction test succeeded.

Running transaction

Preparing : 1/1

Running scriptlet: MariaDB-client-10.5.13-1.el8.x86_64 1/7

Upgrading : MariaDB-client-10.5.13-1.el8.x86_64 1/7

Running scriptlet: MariaDB-client-10.5.13-1.el8.x86_64 1/7

Installing : socat-1.7.3.3-2.el8.x86_64 2/7

Running scriptlet: galera-4-26.4.9-1.el8.x86_64 3/7

Installing : galera-4-26.4.9-1.el8.x86_64 3/7

Running scriptlet: galera-4-26.4.9-1.el8.x86_64 3/7

Running scriptlet: MariaDB-server-10.5.13-1.el8.x86_64 4/7

Installing : MariaDB-server-10.5.13-1.el8.x86_64 4/7

Running scriptlet: MariaDB-server-10.5.13-1.el8.x86_64 4/7

Two all-privilege accounts were created.

One is root@localhost, it has no password, but you need to

be system 'root' user to connect. Use, for example, sudo mysql

The second is mysql@localhost, it has no password either, but

you need to be the system 'mysql' user to connect.

After connecting you can set the password, if you would need to be

able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at https://mariadb.com/kb or the

MySQL manual for more instructions.

Please report any problems at https://mariadb.org/jira

The latest information about MariaDB is available at https://mariadb.org/.

You can find additional information about the MySQL part at:

https://dev.mysql.com

Consider joining MariaDB's strong and vibrant community:

https://mariadb.org/get-involved/

Running scriptlet: MariaDB-backup-10.5.13-1.el8.x86_64 5/7

Upgrading : MariaDB-backup-10.5.13-1.el8.x86_64 5/7

Running scriptlet: MariaDB-backup-10.5.13-1.el8.x86_64 5/7

Running scriptlet: MariaDB-client-10.4.22-1.el8.x86_64 6/7

Cleanup : MariaDB-client-10.4.22-1.el8.x86_64 6/7

Running scriptlet: MariaDB-client-10.4.22-1.el8.x86_64 6/7

Running scriptlet: MariaDB-backup-10.4.22-1.el8.x86_64 7/7

Cleanup : MariaDB-backup-10.4.22-1.el8.x86_64 7/7

Running scriptlet: MariaDB-backup-10.4.22-1.el8.x86_64 7/7

Running scriptlet: MariaDB-server-10.5.13-1.el8.x86_64 7/7

Running scriptlet: MariaDB-backup-10.4.22-1.el8.x86_64 7/7

Verifying : MariaDB-server-10.5.13-1.el8.x86_64 1/7

Verifying : galera-4-26.4.9-1.el8.x86_64 2/7

Verifying : socat-1.7.3.3-2.el8.x86_64 3/7

Verifying : MariaDB-backup-10.5.13-1.el8.x86_64 4/7

Verifying : MariaDB-backup-10.4.22-1.el8.x86_64 5/7

Verifying : MariaDB-client-10.5.13-1.el8.x86_64 6/7

Verifying : MariaDB-client-10.4.22-1.el8.x86_64 7/7

Installed products updated.

Upgraded:

MariaDB-backup-10.5.13-1.el8.x86_64 MariaDB-client-10.5.13-1.el8.x86_64

Installed:

MariaDB-server-10.5.13-1.el8.x86_64 galera-4-26.4.9-1.el8.x86_64 socat-1.7.3.3-2.el8.x86_64

Complete!

7. Start mariadb ** put back any changes to server.cnf

[root@database-rhel84 yum.repos.d]# systemctl start mariadb

[root@database-rhel84 yum.repos.d]# systemctl status mariadb

● mariadb.service - MariaDB 10.5.13 database server

Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)

Drop-In: /etc/systemd/system/mariadb.service.d

└─migrated-from-my.cnf-settings.conf

Active: active (running) since Fri 2021-11-19 20:16:11 +08; 7s ago

Docs: man:mariadbd(8)

https://mariadb.com/kb/en/library/systemd/

Process: 202704 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)

Process: 202675 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl s>

Process: 202672 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)

Main PID: 202685 (mariadbd)

Status: "Taking your SQL requests now..."

Tasks: 18 (limit: 100943)

Memory: 71.1M

CGroup: /system.slice/mariadb.service

└─202685 /usr/sbin/mariadbd

Nov 19 20:16:11 database-rhel84 mariadbd[202685]: 2021-11-19 20:16:11 0 [Note] InnoDB: 10.5.13 started; log sequence number 45094; transaction id 20

Nov 19 20:16:11 database-rhel84 mariadbd[202685]: 2021-11-19 20:16:11 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool

Nov 19 20:16:11 database-rhel84 mariadbd[202685]: 2021-11-19 20:16:11 0 [Note] Plugin 'FEEDBACK' is disabled.

Nov 19 20:16:11 database-rhel84 mariadbd[202685]: 2021-11-19 20:16:11 0 [Note] InnoDB: Buffer pool(s) load completed at 211119 20:16:11

Nov 19 20:16:11 database-rhel84 mariadbd[202685]: 2021-11-19 20:16:11 0 [Note] Server socket created on IP: '::'.

Nov 19 20:16:11 database-rhel84 mariadbd[202685]: 2021-11-19 20:16:11 0 [Note] Reading of all Master_info entries succeeded

Nov 19 20:16:11 database-rhel84 mariadbd[202685]: 2021-11-19 20:16:11 0 [Note] Added new Master_info '' to hash table

Nov 19 20:16:11 database-rhel84 mariadbd[202685]: 2021-11-19 20:16:11 0 [Note] /usr/sbin/mariadbd: ready for connections.

Nov 19 20:16:11 database-rhel84 mariadbd[202685]: Version: '10.5.13-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server

Nov 19 20:16:11 database-rhel84 systemd[1]: Started MariaDB 10.5.13 database server.

8. Run mysql_upgrade - make sure your server.cnf is pointing to the right data directory or else u will get the error below.

[root@database-rhel84 my.cnf.d]# mysql_upgrade --verbose

Looking for 'mysql' as: mysql

Looking for 'mysqlcheck' as: mysqlcheck

Phase 1/7: Checking and upgrading mysql database

Processing databases

mysql

mysql.column_stats OK

mysql.columns_priv OK

mysql.db OK

mysql.event OK

mysql.func OK

mysql.global_priv OK

mysql.gtid_slave_pos

Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine

status : Operation failed

mysql.help_category OK

mysql.help_keyword OK

mysql.help_relation OK

mysql.help_topic OK

mysql.index_stats OK

mysql.innodb_index_stats

Error : Table 'mysql.innodb_index_stats' doesn't exist in engine

status : Operation failed

mysql.innodb_table_stats

Error : Table 'mysql.innodb_table_stats' doesn't exist in engine

status : Operation failed

mysql.plugin OK

mysql.proc OK

mysql.procs_priv OK

mysql.proxies_priv OK

mysql.roles_mapping OK

mysql.servers OK

mysql.table_stats OK

mysql.tables_priv OK

mysql.time_zone OK

mysql.time_zone_leap_second OK

mysql.time_zone_name OK

mysql.time_zone_transition OK

mysql.time_zone_transition_type OK

mysql.transaction_registry

Error : Table 'mysql.transaction_registry' doesn't exist in engine

status : Operation failed

Repairing tables

mysql.gtid_slave_pos

Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine

status : Operation failed

mysql.innodb_index_stats

Error : Table 'mysql.innodb_index_stats' doesn't exist in engine

status : Operation failed

mysql.innodb_table_stats

Error : Table 'mysql.innodb_table_stats' doesn't exist in engine

status : Operation failed

mysql.transaction_registry

Error : Table 'mysql.transaction_registry' doesn't exist in engine

status : Operation failed

Phase 2/7: Installing used storage engines... Skipped

Phase 3/7: Fixing views

Processing databases

information_schema

mysql

mysql.user OK

performance_schema

test

ucdb

uidb

uidb.uaci_offerstatstx_syn OK

ujdb

ujrptdb

umpdb

updb

urptdb

Phase 4/7: Running 'mysql_fix_privilege_tables'

ERROR 1932 (42S02) at line 448: Table 'mysql.innodb_index_stats' doesn't exist in engine

FATAL ERROR: Upgrade failed

Tuesday, November 30, 2021

Configure the package management system (yum).

Sunday, November 28, 2021

Thursday, November 25, 2021

Tuesday, November 23, 2021

Monday, November 22, 2021

Friday, November 19, 2021

Thursday, November 18, 2021

Configure the package management system (`yum`).