Annexes:MariaDB

Installation par yum

Activation des repo et installation par yum.
Exemple pour l’installation d’une MariaDB 10.5 sur CentOS 7.

Activation du repo officiel MariaDB :

#Activate official repo
yum install -y wget
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
chmod +x mariadb_repo_setup
./mariadb_repo_setup

[root@vmsqwarebox ~]# cat /etc/yum.repos.d/mariadb.repo
[mariadb-main]
name = MariaDB Server
baseurl = https://downloads.mariadb.com/MariaDB/mariadb-10.5/yum/rhel/$releasever/$basearch
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
gpgcheck = 1
enabled = 1
...
...

Installation par le repo :

yum install -y MariaDB-server MariaDB-client MariaDB-common MariaDB-compat 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]# mysql_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 -Gdba,mysql mysql
[root@vmsqwarebox mysql]# id mysql
uid=1004(mysql) gid=989(mysql) groups=989(mysql),1000(dba)

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]# cat /etc/my.cnf.d/server.cnf
[mysqld]
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

Patch de RPM

Téléchargez les RPM client, devel, shared, shared-compat et server pour votre OS et mettez-les dans /tmp.
Exemple pour patcher d’une 5.6.27 à une 5.6.43 :

[root@vmsqwarebox home]# cd /tmp

## Stop instance
/etc/init.d/mysql stop

[root@vmsqwarebox ~]# rpm -Uvh MySQL-client-5.6.43-1.el6.x86_64.rpm MySQL-shared-5.6.43-1.el6.x86_64.rpm MySQL-devel-5.6.43-1.el6.x86_64.rpm MySQL-server-5.6.43-1.el6.x86_64.rpm MySQL-shared-compat-5.6.43-1.el6.x86_64.rpm
warning: MySQL-client-5.6.43-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
Preparing...                ########################################### [100%]
   1:MySQL-shared-compat    ########################################### [ 20%]
Giving mysqld 5 seconds to exit nicely
   2:MySQL-server           ########################################### [ 40%]
   3:MySQL-devel            ########################################### [ 60%]
   4:MySQL-shared           ########################################### [ 80%]
   5:MySQL-client           ########################################### [100%]

## Start instance
/etc/init.d/mysql start