Annexes:MariaDB

Installation par yum

Activation des repo et installation par dnf.
Exemple pour l’installation d’une MariaDB 10.11 sur Rocky Linux 9.
(Version 10.5 sur CentOS 7 : Installation MariaDB CentOS 7).
(Version 10.6 sur Rocky 8 : Installation MariaDB Rocky 8).

Lister les versions de MariaDB dispo :

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

Forcer la version 10.11 dans la liste du repo :

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

Modifier les lignes pour obtenir ceci :

[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
...
…

Lister les versions de MariaDB dispo :

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 par le repo :

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

Modifications post-installation

Démarrage :

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

Changement du mot de passe du user MySQL ‘root’ :

[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.

...

Remise du user unix ‘mysql’ dans le groupe dba :

[root@vmsqwarebox mysql]# id mysql
uid=1004(mysql) gid=989(mysql) groups=989(mysql)
[root@vmsqwarebox mysql]# usermod -g dba mysql
[root@vmsqwarebox mysql]# usermod -a -G mysql 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