Appendices:MariaDB

Work in Progress

Yum installation

Repo activation and installation via dnf.
Example for installing MariaDB 10.11 on Rocky Linux 9.

List the available MariaDB versions :

dnf module list mariadb

Rocky Linux 9 - AppStream
Name                                   Stream                                  Profiles                                                  Summary
mariadb                                10.5 [d]                                client, galera, server [d]                                MariaDB Module

Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled

If you have to force the version 10.11 in repo list :

cd /tmp
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
chmod +x mariadb_repo_setup
./mariadb_repo_setup
vi /etc/yum.repos.d/mariadb.repo

Edit the file as follow :

[mariadb-main]
name = MariaDB Server
baseurl = https://downloads.mariadb.com/MariaDB/mariadb-10.11/yum/rhel/9/x86_64/
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
gpgcheck = 1
enabled = 1
...
…

List the available MariaDB versions :

dnf list mariadb

Available Packages
MariaDB.src                                           10.11.7-1.el9                                                         mariadb-main
mariadb.x86_64                                        3:10.5.22-1.el9_2                                                     appstream

Installation from repo :

dnf install -y MariaDB-server MariaDB-client MariaDB-common MariaDB-shared MariaDB-backup

Post-installation changes

Start the service:

[root@vmsqwarebox tmp]# systemctl start mariadb.service

Change of MySQL user ‘root’ password :

[root@vmsqwarebox tmp]# mariadb-secure-installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

...

Adding again the unix user 'mysql' to the dba group:

[root@vmsqwarebox mysql]# id mysql
uid=1004(mysql) gid=989(mysql) groups=989(mysql)
[root@vmsqwarebox mysql]# usermod -a -G dba mysql
[root@vmsqwarebox mysql]# id mysql
uid=1004(mysql) gid=989(mysql) groups=989(mysql),1000(dba)
[root@vmsqwarebox mysql]# usermod -g dba mysql
[root@vmsqwarebox mysql]# id mysql
uid=1004(mysql) gid=1000(dba) groups=1000(dba),989(mysql)

Déplacement de l'instance dans /data :

systemctl stop mariadb.service

cd /var/lib/
mv mysql /data/mysql/.
mv /data/mysql/mysql /data/mysql/MYS_DBA_PRD
ln -s /data/mysql/MYS_DBA_PRD mysql

## Pour les binlog
mkdir -p /data/mysql/MYS_DBA_PRD-binlog
chown mysql:dba /data/mysql/MYS_DBA_PRD-binlog
## Pour les tables temporaires
mkdir -p /backups/mysql/MYS_DBA_PRD-tmpdir
chown mysql:dba /backups/mysql/MYS_DBA_PRD-tmpdir

Modif des limites pour mysql (+ dans le service) :

vi /etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535

vi /usr/lib/systemd/system/mariadb.service
# Number of files limit. previously [mysqld_safe] open-files-limit
LimitNOFILE=65535

Quelques paramètres conseillés :

[root@vmsqwarebox tmp]# vi /etc/my.cnf.d/server.cnf
[mariadb]
datadir=/data/mysql/MYS_DBA_PRD
log_error=MYS_DBA_PRD.err
max_connections = 100
extra_max_connections = 3
skip_name_resolve = 1
innodb_buffer_pool_size = 1024M
read_buffer_size = 3M
key_buffer_size = 32M
max_allowed_packet = 32M
table_definition_cache = 1000
sort_buffer_size = 32M
join_buffer_size = 64M
#innodb_thread_concurrency = 16 Deprecated: MariaDB 10.5.5 Removed: MariaDB 10.6.0
slow_query_log = 1
slow_query_log_file=MYS_DBA_PRD-slow.log
long_query_time = 2.0
server_id                = 10
binlog_cache_size        = 100M
max_binlog_size          = 250M
# log_bin                  = /data/mysql/MYS_DBA_PRD-binlog/MYSQL_BIN_PROD.log
# log_bin_index            = /data/mysql/MYS_DBA_PRD-binlog/MYSQL_BIN_INDEX_PROD.log
# binlog_format            = MIXED
log_slave_updates        = 1
sync_binlog              = 1
expire_logs_days         = 1
tmp_table_size = 64M
innodb_file_per_table = 1
tmpdir = /backups/mysql/MYS_DBA_PRD-tmpdir

Rechargement de la conf et redémarrage de MariaDB :

systemctl daemon-reload
systemctl start mariadb.service
systemctl enable mariadb.service

Modification du plugin de connexion et mot de passe pour root (si nécessaire)

root@vmsqwarebox:/root # mysql -uroot -p<rootpass>
MariaDB [(none)]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> select user, plugin, host, password from mysql.user;
+-------------+-----------------------+-----------+----------+
| User        | plugin                | Host      | Password |
+-------------+-----------------------+-----------+----------+
| mariadb.sys | mysql_native_password | localhost |          |
| root        | mysql_native_password | localhost | invalid  |
| mysql       | mysql_native_password | localhost | invalid  |
| PUBLIC      |                       |           |          |
+-------------+-----------------------+-----------+----------+
4 rows in set (0.002 sec)

MariaDB [mysql]> ALTER USER 'root'@'localhost'IDENTIFIED VIA mysql_native_password;

MariaDB [mysql]> SET PASSWORD FOR'root'@'localhost'=PASSWORD('<rootpass>');

MariaDB [mysql]> FLUSH PRIVILEGES;

MariaDB [mysql]> select user, plugin, host, password from mysql.user;
+-------------+-----------------------+-----------+----------+
| User        | plugin                | Host      | Password |
+-------------+-----------------------+-----------+----------+
| mariadb.sys | mysql_native_password | localhost |          |
| root        | mysql_native_password | localhost | *D482A5176DD5C584F3E963D9BC9C84520B4C2455|
| mysql       | mysql_native_password | localhost | invalid  |
| PUBLIC      |                       |           |          |
+-------------+-----------------------+-----------+----------+
4 rows in set (0.002 sec)

MariaDB [mysql]> exit