Howto MySQL : installation et utilisation courante.

Installation
Configuration
- Configuration de la mémoire (InnoDB)
datadir / tmpdir
Logs
Utilisation courante (TL;DR)
Utilisation courante
- Créer
- Lister
- Supprimer
- Renommer
- Insérer
- Vues
Administration
Logs
Sauvegarde
- Restauration
Monitoring
binlogs
Multiples instances MySQL
Plugins
- MariaDB Audit Plugin
Activer la connexion via SSL/TLS à la base de données
Chiffrement de la base de données Transparent Data Encryption (TDE)
- Mise en place du chiffrement avec File Key Management Encryption
Optimisation avancée
Réplication MySQL
Mariabackup / Percona XtraBackup
FAQ et erreurs courantes

Documentation MariaDB : https://mariadb.com/kb/en/library/documentation/
Documentation MySQL 5.7 : http://dev.mysql.com/doc/refman/5.7/en/
Rôle Ansible : https://forge.evolix.org/projects/ansible-roles/repository/show/mysql
Statut de cette page : prod / bullseye

MySQL est une base de données très populaire au sein des infrastructures web. Nous utilisons au choix la version libre de MySQL distribuée par Oracle, et MariaDB un fork créé en 2009 par le créateur initial de MySQL.

Installation

Depuis Debian 9, nous installons MariaDB qui est distribué par Debian à place de MySQL. Exemple en Debian 12 :

# apt install mariadb-server mariadb-client libconfig-inifiles-perl

$ mysql --version
mysql Ver 15.1 Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

Note : Sous Debian 8, nous installons la version libre de MySQL distribuée par Oracle :

# apt install mysql-server

$ mysql --version
mysql  Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (x86_64) using readline 6.3

ou MariaDB :

# apt install mariadb-server-10.0

$ mysql --version
mysql  Ver 15.1 Distrib 10.0.28-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

L’installation sous Debian 12 ne permet plus a l’utilisateur SQL root de se connecter avec un mot de passe, mais seulement via la socket

On mets donc en place le .my.cnf suivant dans /root/ :

[client]
user = root
socket = /run/mysqld/mysqld.sock

On peut créer un utilisateur root comme ceci :

mysql> CREATE USER root@localhost IDENTIFIED VIA unix_socket;

Si dans SQL_MODE la valeur NO_AUTO_CREATE_USER n’est pas activé, on peut créé l’utilisateur root comme ceci :

mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED VIA unix_socket WITH GRANT OPTION;

Depuis Debian 11, il n’y a plus d’utilisateur SQL debian-sys-main, le fichier /etc/mysql/debian.cnf est toujours créé mais est obsolète et ne doit plus être utilisé.

Sous Debian 8, avec MariaDB 10.0, l’utilisateur debian-sys-maint n’a pas le privilège GRANT. Il est donc impossible de créer d’autres utilisateurs en étant connecté avec ce compte.

Bibliothèque client

Certains logiciels tiers nécessitent la “bibliothèque client”.

Sur Debian 8, c’est le paquet libmysqlclient-dev.

A partir de Debian 9 (et Debian 8 avec backports), valable aussi en Debian 10/11/12, c’est le meta-paquet default-libmysqlclient-dev qui permet de facilement installer la paquet adapté à votre base de données.

MySQL 5.7 sur Debian 9

https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/

Pour installer MySQL 5.7 distribuée par Oracle sous Debian 9, on ajoute le dépôt repo.mysql.com :

# echo "deb http://repo.mysql.com/apt/debian stretch mysql-5.7" > /etc/apt/sources.list.d/mysql57.list
# wget 'http://keys.gnupg.net/pks/lookup?op=get&search=0x8C718D3B5072E1F5' -O /etc/apt/trusted.gpg.d/mysql57.asc

Note: la clé GPG peut aussi être récupérée via https://dev.mysql.com/doc/refman/en/checking-gpg-signature.html

On peut ensuite installer les paquets :

# apt install mysql-server mysql-client

Note : il faudra alors installer mytop en récupérant le package de Debian 8

MySQL 8.0 sur Debian 9

Pour installer MySQL 8.0 distribué par Oracle sous Debian 9, on ajoute le dépôt repo.mysql.com :

# echo "deb http://repo.mysql.com/apt/debian stretch mysql-8.0" > /etc/apt/sources.list.d/mysql80.list

Malheureusement, la clé du dépôt est expirée, il faut forcer apt update avec --allow-unauthenticated :

# apt-get update --allow-unauthenticated

On peut ensuite mettre-à-jour MySQL :

# apt install mysql-server

Enfin, désactiver le dépôt pour ne pas rencontrer des erreurs aux prochains updates :

mv /etc/apt/sources.list.d/mysql80.list /etc/apt/sources.list.d/mysql80.list~

MySQL 8.0 sur Debian 11/12

Pour installer MySQL 8.0 distribué par Oracle sous Debian 11, on utilise le paquet mysql-apt-config. Celui-ci va s’occuper de la configuration apt (dépôt & clé GPG)

# wget https://repo.mysql.com/apt/debian/pool/mysql-apt-config/m/mysql-apt-config/mysql-apt-config_0.8.24-1_all.deb
# apt install ./mysql-apt-config_0.8.24-1_all.deb

# apt update
# apt install mysql-server

$ mysql --version
mysql  Ver 8.0.35 for Linux on x86_64 (MySQL Community Server - GPL)

Configuration

Le fichier de configuration principal est /etc/mysql/my.cnf qui inclue notamment les fichiers .cnf présents dans les sous-répertoires conf.d/ et mariadb.conf.d/.

Note : attention, si vous avez une configuration MySQL/MariaDB issue d’une Debian 8 (suite upgrade par exemple), le sous-répertoire mariadb.conf.d/ ne sera pas pris en compte.

Le fichier /etc/mysql/mariadb.conf.d/z-evolinux-defaults.cnf contient nos optimisations basiques :

[mysqld]

###### Connexions
# Maximum de connexions concurrentes (défaut = 100)... provoque un "Too many connections"
max_connections = 250
# Maximum de connexions en attente en cas de max_connections atteint (défaut = 50)
back_log = 100
# Maximum d'erreurs avant de blacklister un hote
max_connect_errors = 10
# Loguer les requetes trop longues
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10

###### Tailles
# Taille réservée au buffer des index MyIsam
# A ajuster selon les résultats utilisateurs
key_buffer_size = 512M
# Taille max des paquets envoyés/reçus … provoque un "Packet too large"
max_allowed_packet = 64M
# Taille de la mémoire réservée pour un thread
thread_stack = 192K
# A mettre le nombre de threads CPU alloues pour MySQL
thread_cache_size = 1
# Taille maximum des tables de type MEMORY
max_heap_table_size = 64M

###### Cache
# max_connections x nombre max de tables dans une jointure (défaut = 64)
table_open_cache       = 4096
table_definition_cache = 4096
# Taille max des requêtes cachées (défaut = 1M)
query_cache_limit       = 8M
# Taille réservée pour le cache (défaut = 0)
query_cache_size        = 256M
# Type de requêtes à cacher (défaut = 1 : tout peut être caché)
query_cache_type        = 1
# Cache tables
max_heap_table_size     = 128M
tmp_table_size          = 128M

###### InnoDB
# Si InnoDB n'est pas utilisé... le désactiver
#skip-innodb
# En général, il est plus optimum d'avoir un fichier par table
innodb_file_per_table
# Taille mémoire allouée pour le cache des datas et index
# A ajuster en fonction de sa RAM (si serveur dédié à MySQL, on peut aller jusqu'à 70%)
innodb_buffer_pool_size = 512M
# Nombre maximum de threads système concurrents
innodb_thread_concurrency = 16
# Ajuste la valeur des logs InnoDB
# (attention, il faut ensuite stopper MySQL et effacer les fichiers ib_logfile*)
#innodb_log_file_size = 128M
#innodb_log_files_in_group = 2

###### Misc
# Charset utf8 par défaut
character-set-server=utf8
collation-server=utf8_general_ci
# Patch MySQL 5.5.53
secure-file-priv = ""

Note sur le charset et collation : MariaDB 10.1 avec Debian 9 est par défaut en utf8mb4 + collation utf8mb4_general_ci.

A partir de Debian 12, avec MariaDB 10.11, les valeurs character-set-server=utf8 et collation-server=utf8_general_ci sont un alias vers utf8mb3

On peux modifié cela, en modifiant la variable old_mode, par une valeur vide, car par défaut old_mode à pour valeur UTF8_IS_UTF8MB3 Donc si UTF8_IS_UTF8MB3 est actif, utf8 est un alias vers utf8mb3, s’il n’est pas activé, utf8 est un alias vers utf8mb4. cf. https://mariadb.com/kb/en/old-mode/#utf8_is_utf8mb3

Le fichier /etc/mysql/mariadb.conf.d/zzz-evolinux-custom.cnf contient nos éventuelles optimisations spécifiques.

Nous désactivons également une protection mise en place par l’unité systemd. Cela permet d’utiliser la partition /home ou /srv pour des sauvegardes ou autres opérations :

# cat /etc/systemd/system/mariadb.service.d/evolinux.conf

[Service]
ProtectHome=false

Par défaut, MySQL écoute en réseau sur 127.0.0.1 (port TCP/3306) et sur la socket Unix /var/run/mysqld/mysqld.sock Pour activer les connexions réseau à distance, il faut ajouter la configuration suivante dans zzz-evolinux-custom.cnf :

[mysqld]
bind-address = 0.0.0.0

Note : depuis MariaDB 10.11, il est possible de mettre plusieurs adresses IP séparées par des virgules :

[mysqld]
bind-address=127.0.0.1,172.17.0.1

Puis, mettre les permissions :

# chmod o+r /etc/mysql/mariadb.conf.d/zzz-evolinux-custom.cnf

Pour plus d’informations sur l’optimisation avancée de MySQL, consultez le guide /HowtoMySQL/Optimize.

Note : Sous Debian 8, nous mettons notre configuration dans /etc/mysql/conf.d/evolinux.cnf

Configuration de la mémoire (InnoDB)

Selon les ressources de la machine, il faut optimiser davantage les options (par défaut, la configuration est adaptée pour une machine avec très peu de mémoire vive !).

On conseille au minimum d’ajuster thread_cache_size et innodb_buffer_pool_size :

[mysqld]
# Nombre de threads CPU alloués pour MySQL
thread_cache_size = 2
# Mémoire allouée pour le cache InnoDB (si serveur dédié à MySQL, on peut aller jusqu'à 70% de la RAM)
innodb_buffer_pool_size = 2G

Attention : MySQL consomme plus de mémoire que ce qui est indiqué dans la variable innodb_buffer_pool_size. Celle-ci n’est que la taille totale du tampon mémoire utilisable par les instances des « workers » de InnoDB.

Si MySQL utilise trop de mémoire, le système va swapper, et on peut observer une importante baisse des performances. Pour vérifier que MySQL ne swappe pas, il faut que les valeurs si et so de la commande suivante soient égales à zéro :

# vmstat
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0 976884 3301840 200564 1540540    0    0     6    53    0    0  2  1 96  1  0

A partir de MariaDB 10.2 (Debian 10 - Buster), il est possible d’ajuster dynamiquement (= sans redémarrer MySQL) la variable innodb_buffer_pool_size. Elle doit être un multiple de innodb_buffer_pool_chunk_size x innodb_buffer_pool_instances (par défaut 128M x 8), en octets (1 Go = 1073741824 octets).

# Afficher  la valeur de innodb_buffer_pool_size :
mysql > SELECT @@innodb_buffer_pool_size;

# Redimensionner dynamiquement innodb_buffer_pool_size :
mysql > SET GLOBAL innodb_buffer_pool_size=...;  # en octets

# Afficher l'avancement du redimensionnement dynamique :
mysql > SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------+
| Variable_name                    | Value                                              |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at  ...             |
+----------------------------------+----------------------------------------------------+

Il faut ensuite penser à ajuster aussi la valeur de innodb_buffer_pool_size dans /etc/mysql/mariadb.conf.d/zzz-evolinux-custom.cnf comme indiqué ci-dessus.

Voici la commande pour calculer la mémoire théorique qui sera utilisée (théorique car ça ne prend pas en compte les fuites mémoire) :

MariaDB [(none)]> SELECT ( @@key_buffer_size + @@query_cache_size + @@tmp_table_size + @@innodb_buffer_pool_size 
+ @@innodb_log_buffer_size + @@max_connections * ( @@read_buffer_size + @@read_rnd_buffer_size + @@sort_buffer_size
+ @@join_buffer_size + @@binlog_cache_size + @@thread_stack ) ) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

Pour aller plus loin : https://mariadb.com/kb/en/mariadb-memory-allocation/

datadir / tmpdir

Par défaut, le datadir (le répertoire où sont stockées les données brutes) est /var/lib/mysql/.

Pour diverses raisons il peut être intéressant de le déplacer (partition ou disque dédié etc.).

Pour des raisons de compatibilité, on conseille de conserver un lien symbolique :

# systemctl stop mysql
# mv /var/lib/mysql /srv/mysql-datadir
# ln -s /srv/mysql-datadir /var/lib/mysql
# mv /var/log/mysql /srv/mysql-log
# ln -s /srv/mysql-log /var/log/mysql
# systemctl start mysql
# systemctl status mysql

Pour certaines opérations lourdes, MySQL a besoin d’un tmpdir (répertoire où il va écrire des tables temporaires). Par défaut il utilise /tmp mais vu qu’il est parfois nécessaire d’avoir plusieurs Go de libre, on pourra utiliser un répertoire différents :

[mysqld]
tmpdir = /srv/mysql-tmpdir

Logs

Sous Debian, les journaux de MySQL (démarrage, arrêt, erreurs, informations) sont envoyés via syslog. Par défaut, ils seront donc visibles dans /var/log/syslog.

Le répertoire /var/log/mysql/ contient les binlogs (trace de toutes les requêtes INSERT/UPDATE/DELETE exécutées).

Utilisation courante (TL;DR)

Se connecter :

mysql [--defaults-group-suffix=1]

Attention :

Les noms contenant des traits d’union ou un wildcard % doivent être mis entre des backquotes.

Variables de configuration (dynamique)

Voir la valeur d’une variable de configuration :

> SHOW VARIABLES LIKE '<VAR>';

On peut aussi utiliser un wildcard % :

> SHOW VARIABLES LIKE '%<KEYWORD>%';

Afficher plusieurs variables :

> SHOW VARIABLES WHERE Variable_name = '<VAR1>' OR Variable_name = '<VAR2>';

Changer dynamiquement la valeur d’une variable de configuration :

> SET GLOBAL <VAR>=<VALUE>;

Bases de données

Lister les bases :

> SHOW DATABASES;

Ajouter une base :

> CREATE DATABASE <DB_NAME>;

Note : si on veut créer un utilisateur et une table du même nom, on préférera utiliser la commande shell # mysqladmin create <NAME>.

Supprimer une base :

> DROP DATABASE <DB_NAME>;

Utiliser (ou entrer dans) une base :

> USE <DB_NAME>;

Lister les tables de la base :

> SHOW TABLES;

Utilisateurs

Attention :

Les permissions et mots de passe ne sont pas spécifiques à un utilisateur, mais à un couple utilisateur/hôte.
Par défaut, utiliser l’hôte localhost. Pour autoriser l’utilisateur à se connecter à partir de n’importe où, utiliser le wildcard %.

Lister les utilisateurs :

> SELECT host, user, priv FROM mysql.global_priv [WHERE db=<BD_NAME> ORDER BY user];

Lister les privilèges d’un utilisateur/hôte :

> SHOW GRANTS FOR <USER>@<HOST>;

Créer un utilisateur/hôte :

CREATE USER <USER>@<HOST> IDENTIFIED BY '<PASSWORD>';

Note : si on veut créer un utilisateur et une base du même nom, on préférera la commande shell mysqladmin create <NAME>.

Pour autoriser un utilisateur existant à se connecter à partir d’un autre hôte (ou de %), réutiliser le hash de son mot de passe :

> SELECT host, user, password FROM mysql.user WHERE user LIKE '<USER>';
> CREATE USER <USER>@<HOST2> IDENTIFIED BY PASSWORD '<HASH>';

Donner tous les privilèges sur une base à un utilisateur/hôte :

> GRANT ALL PRIVILEGES ON <DB_NAME>.* TO <USER>@<HOST>;

Changer le mot de passe d’un utilisateur :

> SET PASSWORD FOR <USER>@<HOST> = PASSWORD('<PASSWORD>');

Révoquer les accès d’un utilisateur/hôte à une base (attention un utilisateur peut avoir des permissions avec plusieurs hôtes !) :

> REVOKE ALL PRIVILEGES ON <DB_NAME>.* FROM <USER>@<HOST>;

Ajouter uniquement le privilège SELECT sur toute une base pour un utilisateur :

> GRANT SELECT ON <DB_NAME>.* TO <USER>@<HOST>;

Révoquer le privilège SELECT sur toute une base pour un utilisateur :

> REVOKE SELECT ON <DB_NAME>.* FROM <USER>@<HOST>;

Supprimer un couple utilisateur/hôte :

> DROP USER <USER>@<HOST>;

Utilisation courante

Créer

Créer une nouvelle base de données nommée foo :

mysql> CREATE DATABASE foo;

Créer une table nommée bar avec différents champs :

mysql> CREATE TABLE bar (id INT not null AUTO_INCREMENT, prenom VARCHAR
(50) not null , nom VARCHAR (50) not null , ne_le DATE not null ,
ville VARCHAR (90), enfants INT, PRIMARY KEY (id));

Ajouter un champ à une table :

mysql> ALTER TABLE bar ADD another VARCHAR(100) DEFAULT NULL;

Ajouter un champ à une table en précisant sa place :

mysql> ALTER TABLE bar ADD another VARCHAR(100) DEFAULT NULL AFTER prenom;

Lister

Voir les bases de données créées :

mysql> SHOW DATABASES;

Lister les utilisateurs :

mysql> select Host,user from mysql.global_priv;

Lister les utilisateurs d’une base :

select User,Host from mysql.db where Db="DATABASE" order by User;

Connaître les privilèges d’un utilisateur :

mysql> show grants for USER@'HOST';

Utiliser la base de données foo :

mysql> USE foo

Voir les tables créées :

mysql> SHOW TABLES;

Décrire une table :

mysql> DESC bar;

Pour voir la définition d’une table :

mysql> SHOW CREATE TABLE bar;

Sélectionner tous les champs d’une table :

mysql> SELECT * FROM bar;

Compter les lignes d’un table :

mysql> SELECT COUNT(*) from bar;

Lancer des commandes bash depuis l’invite de commande :

mysql> \! ls -l
-rw-r----- 1 user user  208774 Jan 11 14:31 dump_base.sql

Lister les droits pour tous les accès MySQL créés :

# mysql -e "select concat('\'',User,'\'@\'',Host,'\'') as '' from mysql.global_priv" | sort | \
( while read user; do [ -z "$user" ] && continue; echo "-- $user :"; mysql -e "show grants for $user"; done )

-- 'accesbase'@'localhost' :
Grants for accesbase@localhost
GRANT USAGE ON *.* TO 'accesbase'@'localhost' IDENTIFIED BY PASSWORD '*XXXX'
GRANT ALL PRIVILEGES ON `base`.* TO 'accesbase'@'localhost'

# mysql -e "select * from information_schema.user_privileges;"

Supprimer

Supprimer un champ à une table :

mysql> ALTER TABLE bar DROP another;

Effacer des données d’une table :

mysql> DELETE FROM bar WHERE nom='waddle';

Effacer TOUTES les données d’une table :

mysql> DELETE FROM bar;

Supprimer une table :

mysql> DROP TABLE bar;

Supprimer une base de données :

mysql> DROP DATABASE foo;  # Mettre le nom de la base entre backquotes s'il contient un trait d'union : `foo-bar`

Supprimer les différents privilèges pour un utilisateur mysql :

mysql> REVOKE ALL PRIVILEGES ON base.* FROM 'user'@'localhost';

Lister les bases de données dont l’utilisateur à le droit d’accès:

SELECT * FROM mysql.db WHERE User="nvmpubli";

Supprimer un utilisateur (supprime aussi les entrées dans mysql.db):

mysql> DROP USER 'user'@'localhost';

Renommer

Base

Créer une base vide
Renommer toutes les tables vers cette dernière (utilisation script ci-dessous)

BASE_FROM=db1; BASE_TO=db2
for table in $(mysql -e "use $BASE_FROM; show tables\G;" | grep -v '^\*\*\*' | cut -d':' -f2 | sed 's/^ //'); do echo $table; mysql -e "RENAME TABLE ${BASE_FROM}.${table} TO ${BASE_TO}.${table};"; done

Appliquer les bons droits, aux utilisateurs sur la bdd.

Table

Renommer un champ :

mysql> ALTER TABLE bar CHANGE COLUMN another anotherone TEXT;

Changer le type d’un champ :

mysql> ALTER TABLE bar CHANGE another another enum('foo',bar');

Insérer

Insertion de données dans une table :

mysql> INSERT INTO bar VALUES (1,'jp','papin','2005-06-12','Marseille',2);
INSERT INTO test (id,prenom,nom,ne_le) VALUES (2,'c','waddle','2004-06-17');

Insérer des données depuis un fichier CSV (attention le fichier doit pouvoir être lu par mysql) :

mysql> LOAD DATA INFILE '/tmp/mysql/foo.csv' INTO TABLE bar CHARACTER SET utf8 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' ;

Vues

Lister les vues de la base foo :

mysql> SHOW FULL TABLES IN foo WHERE TABLE_TYPE LIKE 'VIEW';

Créer une vue baz à partir du contenu de la table bar :

mysql> CREATE VIEW baz AS SELECT * FROM bar;

Voir la vue :

mysql> SHOW CREATE VIEW `baz`;

Supprimer une vue baz :

mysql> DROP VIEW `baz`;

Lister toutes les vues d’une instance :

mysql> SELECT CONCAT_WS('.', TABLE_SCHEMA, TABLE_NAME) from INFORMATION_SCHEMA.VIEWS;

Administration

Créer une base de données et un utilisateur associé

On crée une base de données et un utilisateur associé :

# mysqladmin create $db_name
# mysql
mysql> GRANT ALL PRIVILEGES ON $db_name.* TO '$user'@'localhost' IDENTIFIED BY '$password';

Cette opération revient à faire les requêtes suivantes qui seront stocké dans les tables mysql.global_priv et mysql.db :

mysql> SET PASSWORD FOR 'jdoe'@localhost = PASSWORD('mypassword');
mysql> INSERT INTO mysql.db VALUES ('localhost','foo','jdoe','Y','Y','Y','Y','Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql> FLUSH PRIVILEGES;

Si l’utilisateur existe déjà, on peut récupérer le hash de son mot de passe et lui créer l’accès de la manière suivante :

mysql> SHOW GRANTS FOR '$user'@'host';
mysql> GRANT ALL PRIVILEGES ON $db_name.* TO '$user'@'localhost' IDENTIFIED BY PASSWORD '$hash';

À savoir :

Pour migrer de Debian 6 à 7 : - 3 colonnes ont été ajoutées dans mysql.user : Create_tablespace_priv, plugin et authentication_string (pour migrer il faut ajouter ‘N’ en 32ème position + ’’ et NULL à la fin) - 2 colonnes ont été ajoutées dans mysql.db : Event_priv et Trigger_priv (pour migrer il faut ajouter ‘Y’ et ‘Y’ à la fin)

Pour migrer de Debian 10/11 à 12 (MariaDB 10.3 ou 10.5 vers 10.11) : - La table mysql.user est devenu une VIEW, les informations sont maintenant stokées dans la table mysql.global_priv - Si on veux faire un dump global d’une instance MariaDB 10.3 ou 10.5 vers MariaDB 10.11, il faut faire un dump en excluant la table mysql.user :

# mysqldump --defaults-extra-file=/etc/mysql/debian.cnf -P 3306 \
      --skip-opt --add-drop-table  --create-options --disable-keys --extended-insert --quick --set-charset --all-databases --force --events --hex-blob --ignore-table=mysql.user | gzip --best > /home/backup/mysql.bak.gz

Il faut ensuite, dumper les utiilisateurs a part, soit avec l’outil pt-show-grants disponible dans le paquet percona-toolkit comme ceci :

# pt-show-grants > /home/backup/MySQLUserGrants.sql

Si le paquet percona-toolkit n’est pas disponible, on peux le faire comme ceci :

# mysql --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql --skip-column-names -A | sed 's/$/;/g' > /home/backup/MySQLUserGrants.sql

Créer des utilisateurs et gérer leurs droits

On pourra ainsi régler finement les droits d’un utilisateur en connaissant la signification de chaque colonne :

mysql> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host                   | char(60)                          | NO   | PRI |         |       |
| User                   | char(16)                          | NO   | PRI |         |       |
| Password               | char(41)                          | NO   |     |         |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N       |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N       |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N       |       |
| File_priv              | enum('N','Y')                     | NO   |     | N       |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N       |       |
| References_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N       |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N       |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N       |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N       |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N       |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N       |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N       |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N       |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N       |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |         |       |
| ssl_cipher             | blob                              | NO   |     | NULL    |       |
| x509_issuer            | blob                              | NO   |     | NULL    |       |
| x509_subject           | blob                              | NO   |     | NULL    |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
| plugin                 | char(64)                          | YES  |     |         |       |
| authentication_string  | text                              | YES  |     | NULL    |       |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)

Par exemple, pour permettre à un utilisateur (ici debian-sys-maint) de faire des SHOW VIEW :

mysql> UPDATE user SET Show_view_priv='Y' WHERE User='debian-sys-maint';
mysql> FLUSH PRIVILEGES;

…que l’on peut aussi faire via :

mysql> GRANT SHOW VIEW on *.* to `debian-sys-maint`@localhost;

Pour créer un utilisateur sans droit particulier, par exemple pour du monitoring :

mysql> create user nrpe@localhost identified by 'PASSWORD';

On peut aussi gérer des droits sur les tables :

mysql> GRANT Select,Insert,Update ON foo.bar TO 'jdoe'@localhost;

Pour révoquer des droits sur une table, on utilisera REVOKE :

mysql> REVOKE ALL PRIVILEGES ON foo.bar FROM 'jdoe'@localhost;

/!\ Un droit est particulier : pour utiliser LOAD DATA INFILE ou SELECT INTO OUTFILE, il faut avoir le droit FILE … mais il est global (et dangereux) ! On le positionnera ainsi :

mysql> GRANT FILE ON *.* TO 'jdoe'@localhost;

Si l’on veux autoriser l’accès a une base depuis un utilisateur MySQL, depuis l’extérieur :

/!\ Il faut s’assurer que MySQL écoute bien sur l’ip accessible, LAN ou WAN (bind-address = dans la configuration MySQL)

Depuis une IP particulière :

mysql> GRANT ALL PRIVILEGES ON foo.* TO 'jdoe'@'<IP_ADDRESS>' IDENTIFIED BY '<PASSWORD>';

Depuis un sous-réseau, le plus sûr est d’utiliser l’operateur wildcard %, on peut aussi utiliser IP/NETMASK mais il faut tester car il y a des limitations selon la version ( cf MXS-1827 ( il vaut mieux utiliser un mask en .0 ou .255 et une addresse en .0 ) :

mysql> GRANT ALL PRIVILEGES ON foo.* TO 'jdoe'@'172.16.0.%' IDENTIFIED BY '<PASSWORD>';
mysql> GRANT ALL PRIVILEGES ON foo.* TO 'jdoe'@'172.16.0.0/255.255.255.0' IDENTIFIED BY '<PASSWORD>';

Attention , dans mariadb, la notation CIDR ( 172.16.0.0/24 par exemple ) ne fonctionne pas ( cf MDEV-25515

Depuis toutes les IPs :

mysql> GRANT ALL PRIVILEGES ON foo.* TO 'jdoe'@'%' IDENTIFIED BY '<PASSWORD>';

Si l’on ne connait pas le mot de passe, on peut utiliser le hash du mot de passe de l’utilisateur mysql comme ceci

mysql> GRANT ALL PRIVILEGES ON foo.* TO 'jdoe'@'%' IDENTIFIED BY PASSWORD '*E355A1AB8251C0B7E02ED8483696B2F3954C05CC';

Pour créer un nouvel administrateur qui puisse également gérer les droits :

GRANT ALL PRIVILEGES ON *.* TO '<ADMIN>'@'localhost' IDENTIFIED BY '<PASSWORD>' WITH GRANT OPTION;

Liste toutes les permissions utilisateurs

Déplacé sur la page de Percona Toolkit

Vérifications et réparations

Pour vérifier et réparer toutes les tables (une sorte de fsck pour les tables), on lancera :

# mysqlcheck --auto-repair --check --all-databases

On peut aussi réparer qu’une base en particulier :

# mysqlcheck --auto-repair --check foo

Note : ceci peut être à faire en cas d’arrêt inopiné du service.

Pour réparer une seule table :

mysql> CHECK TABLE foo.bar;
mysql> REPAIR TABLE foo.bar;

Dans le cas des tables MyISAM, si le REPAIR échoue, une réparation est aussi possible via myisamchk… à faire avec le service MySQL arrêté :

# myisamchk -r -q /var/lib/mysql/foo/bar.MYD

En cas d’échec (segfault par exemple), on peut tenter :

# myisamchk --safe-recover -v -f --key_buffer_size=512M --sort_buffer_size=512M --read_buffer_size=4M --write_buffer_size=4M /var/lib/mysql/BASE/TABLE.MYD

OPTIMIZE

Il est conseillé de lancer régulièrement la commande OPTIMIZE TABLE sur ses tables. Cela va réaliser une sorte de défragmentation des tables (*.idb), notamment sur les indexes. C’est particulièrement recommandé sur les tables qui subissent beaucoup de changement, notamment des modifications/suppressions de lignes.

Voici comment on lance l’opération sur une table :

mysql> OPTIMIZE TABLE foo.bar;

On peut également lancer cela sur l’ensemble des tables d’une base (mais cela peut être très long) :

# mysqlcheck --optimize --all-databases

Note : lors d’un OPTIMIZE TABLE, la table est lockée.

Suivant le moteur utilisé pour une table, les opérations vont être différentes. Notamment pour le moteur InnoDB vous aurez un message du type Table does not support optimize, doing recreate + analyze instead et une table temporaire sera complètement recréée et remplacera l’ancienne (attention à l’espace disque !).

Par défaut les OPTIMIZE sont répliqué sur les autres serveurs, pour éviter cette réplication, il y a l’option NO_WRITE_TO_BINLOG :

mysql> OPTIMIZE NO_WRITE_TO_BINLOG TABLE foo.bar

mysqlcheck --optimize --all-databases --skip-write-binlog

routines MySQL

mysql> select * from INFORMATION_SCHEMA.routines;

Changement mot de passe utilisateur

mysql> SET PASSWORD FOR 'jdoe'@'localhost' = PASSWORD('my_password');
MariaDB [(none)]> GRANT USAGE ON *.* TO 'jdoe'@'%' IDENTIFIED BY "passwd";

Ou encore plus simple en se connectant avec le compte utilisateur puis lancer les commandes suivantes sans oublier de modifier le fichier de configuration :

$ mysql
> set password = password("mon-mot-de-passe-en-clair");
$ vim ~/my.cnf

Changer variables globales d’environnement

On peut changer à chaud certaines variables globales d’environnement :

mysql> SET GLOBAL max_connect_errors=50;

mysql> SET @@max_connect_errors=50;

Voici les changements utiles :

mysql> set global max_connections = 350;

Note : on prendra garde à modifier aussi en dur dans la configuration si le changement doit être persistent.

Lister les variables de configuration

Pour voir la liste de toutes les variables :

mysql> show variables;

Pour ne voir qu’un sous-ensemble de variables :

mysql> show variables like 'read_only';
mysql> show variables like '%thread%';

Pour dumper la configuration MySQL dans un fichier (à adapter s’il y a plusieurs instances) :

mysql -A -e"SHOW GLOBAL VARIABLES;" > mysql_settings

Lecture seule

Si on veut qu’une instance de MySQL soit démarrée en lecture seule, on peut ajouter la variable read_only = 1 dans la section [mysqld]. Seules les requêtes faites par des utilisateurs ayant le privilège SUPER seront alors exécutées.

C’est par exemple utile dans une situation où une instance “slave” doit pouvoir être utilisée en lecture seule, tout en utilisant les mêmes comptes utilisateurs qui ont accès en écriture sur le “master”. La réplication en elle-même n’est pas impactée.

Tailles de bases et de tables

Pour afficher la taille d’une base (remplacer <DB_NAME>) :

mysql> SELECT table_schema "DB Name", 
     Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
WHERE table_schema = '<DB_NAME>';

Pour lister la taille de chaque base :

mysql> SELECT table_schema "DB Name", 
     Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 
FROM information_schema.tables 
GROUP BY table_schema
ORDER BY 2 DESC;

Si on préfère avoir le détail entre taille de données et d’index :

mysql> SELECT table_schema "DB Name", 
       Round(Sum(data_length) / 1024 / 1024, 1) "Data (in MB)",
       Round(Sum(index_length) / 1024 / 1024, 1) "Index (in MB)",
       Round(Sum(data_length + index_length) / 1024 / 1024, 1) "Total size in MB" 
FROM information_schema.tables 
GROUP BY table_schema;

Si on préfère avoir le détail entre taille de données et d’index & le total :

mysql> SELECT table_schema "DB Name", 
       Round(Sum(data_length) / 1024 / 1024, 1) "Data (in MB)",
       Round(Sum(index_length) / 1024 / 1024, 1) "Index (in MB)",
       Round(Sum(data_length + index_length) / 1024 / 1024, 1) "Total size in MB" 
FROM information_schema.tables 
GROUP BY table_schema WITH ROLLUP;

Pour lister la taille de toutes les tables d’une base (remplacer <DB_NAME>) :

mysql> SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
WHERE table_schema = '<DB_NAME>'
ORDER BY (data_length + index_length) DESC;

Pour lister la taille de toutes les tables de toutes les bases :

mysql> SELECT 
     table_schema as `Database`, 
     table_name AS `Table`, 
     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES 
ORDER BY (data_length + index_length) DESC;

Lister les Indexes de toutes les tables

Pour lister tous les Indexes de toutes les tables et de toutes les bases :

select index_schema, index_name, group_concat(column_name order by seq_in_index) as index_columns, index_type, case non_unique when 1 then 'Not Unique' else 'Unique' end as is_unique, table_name from information_schema.statistics where table_schema not in ('information_schema', 'mysql', 'performance_schema', 'sys') group by index_schema, index_name, index_type, non_unique, table_name order by index_schema, index_name;

En version plus “lisible” pour mettre dans un script ou autre :

select index_schema,
       index_name,
       group_concat(column_name order by seq_in_index) as index_columns,
       index_type,
       case non_unique
            when 1 then 'Not Unique'
            else 'Unique'
            end as is_unique,
        table_name
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql',
                           'performance_schema', 'sys')
group by index_schema,
         index_name,
         index_type,
         non_unique,
         table_name
order by index_schema,
         index_name;

Lister la taille des tables, des indexs, et l’espace libre (vide) de chaques tables :

SELECT ENGINE, TABLE_NAME,Round( DATA_LENGTH/1024/1024) as data_length , round(INDEX_LENGTH/1024/1024) as index_length, round(DATA_FREE/ 1024/1024) as data_free from information_schema.tables  where  DATA_FREE > 0;

Lister toutes les tables en MyISAM ou en InnoDB.

Ça peut être utile, par exemple, pour donner plus ou moins de mémoire au variable key_buffer_size pour MyISAM et innodb_buffer_pool_size pour InnoDB

Lister toutes les tables en MyISAM :

SELECT table_schema AS database_name,
       table_name
FROM information_schema.tables TAB
WHERE engine = 'MyISAM'
      AND table_type = 'BASE TABLE'
      AND table_schema not in ('information_schema', 'sys',
                               'performance_schema','mysql')
ORDER BY table_schema,
         table_name;

Lister toutes les tables en InnoDB :

SELECT table_schema AS database_name,
       table_name
FROM information_schema.tables TAB
WHERE engine = 'InnoDB'
      AND table_type = 'BASE TABLE'
      AND table_schema not in ('information_schema', 'sys',
                               'performance_schema','mysql')
ORDER BY table_schema,
         table_name;

Si on veux juste ciblé une BDD en particulier, on peut rajouter ce AND avant le ORDER BY :

AND table_schema = 'foo'

Logs

Log de toutes les requêtes

On peut activer à chaud le logging de l’ensemble des requêtes :

mysql> SET GLOBAL GENERAL_LOG=ON;

Toutes les requêtes seront immédiatemment écrites dans le fichier DATADIR/HOSTNAME.log.

Pour désactiver à chaud :

mysql> SET GLOBAL GENERAL_LOG=OFF;

Note : évidemment, sur un serveur de production il faut éviter d’activer cela plus de quelques secondes car cela impacte fortement la performance et cela va rapidement remplir l’espace disque.

Log des requêtes lentes (slow queries)

Pour débugger les applications lentes, c’est une fonctionnalité intéressante de trouver quelle requête est longue. Pour cela on peut spécifier par exemple que l’on veut avoir toutes les requêtes qui durent plus de 5 secondes :

[mysqld]
slow_query_log = 1
long_query_time = 5
slow_query_log_file = /var/log/mysql/mysql-slow.log

Ou à chaud :

mysql> SET GLOBAL slow_query_log=ON;
mysql> SET GLOBAL long_query_time=5;
mysql> SET GLOBAL slow_query_log_file= "/var/log/mysql/mysql-slow.log";

Il est également possible de remonter les requêtes n’utilisant pas d’index (qu’importe le temps qu’elle prennent pour s’exécuter), avec le paramètre booléen log_queries_not_using_indexes mais nous déconseillons de le faire car cela ajoute de nombreuses requêtes inintéressantes en général.

long_query_time=0

Une astuce pour analyser l’ensemble des requêtes et de positionner long_query_time=0 mais il faut évidemment faire très attention car la taille du fichier de logs peut grossier très vite. C’est intéressant à faire pendant quelques secondes, ou exceptionnellement pendant quelques heures pour une analyse avec pt-query-digest par exemple.

mysqldumpslow

Pour avoir une meilleure lecture des slow queries, on peut utiliser la commande mysqldumpslow :

# mysqldumpslow /var/log/mysql/mysql-slow.log

pt-query-digest

On peut aussi utilise la commande pt-query-digest disponible dans le paquet percona-toolkit afin d’avoir une analyse plus poussée des slow queries. Plus d’information sur la page de Percona Toolkit.

Log des dead lock

Note : Seulement possible depuis MySQL 5.6 ou MariaDB 10.

[mysqld]
innodb_print_all_deadlocks = on

À chaud :

mysql> SET GLOBAL innodb_print_all_deadlocks=on;

Sauvegarde

On documente ici l’utilisation de mysqldump. On notera que depuis Debian 11, mysqldump est désormais un lien symbolique vers mariadb-dump.

Et attention, en raison d’une correction de faille de sécurité en mai 2024, mysqldump/mariadb-dump en Debian 9/10/11 génère un dump non compatible avec les clients MySQL non corrigés… notamment Debian 12 ! Plus d’informations sur HowtoMySQL/Troubleshooting#erreur-lors-dun-restauration-dun-dump-error-at-line-1-unknown-command–.

Pour sauvegarder une base de données dans un seul fichier (sans et avec compression) :

$ mysqldump --hex-blob foo > foo.sql
$ mysqldump --hex-blob foo | gzip > foo.sql.gz

Note : l’option --hex-blob est importante pour ne pas risquer de perdre certains caractères dans les colonnes de type BINARY/BLOB/BIT

Il est aussi possible de sauvegarder une seule table avec mysqldump. Exemple avec la table bar de la base foo : $ mysqldump --hex-blob foo bar

Pour sauvegarder une base de données au format tab-separated data files, avec - pour chaque table - un fichier .sql contenant la structure de la table (CREATE TABLE) et un fichier .txt contenant les données brutes (réinjectable avec LOAD DATA INFILE) :

# mkdir /tmp/foo && chown mysql:mysql /tmp/foo
$ mysqldump -T foo > /tmp/foo

Note : le répertoire de destination doit exister et mysqld doit avoir les droits d’écrire dedans.

On peut utiliser les options --fields-xxx pour obtenir des fichiers au format CSV :

$ mysqldump --hex-blob --fields-enclosed-by='\"' --fields-terminated-by=',' -T /tmp/foo foo

Pour sauvegarder toutes les bases (exemples en mode classique et en mode -T) :

$ mysqldump --opt --all-databases --events --hex-blob > all.sql
$ for db in $(mysql -P3308 -e "SHOW DATABASES" | egrep -v "^(Database|information_schema|performance_schema)"); do \
  mkdir /backupmysql/$db && chown mysql:mysql /backupmysql/$db && \
  mysqldump --events --hex-blob -T /backupmysql/$db $db; done

Pour sauvegarder uniquement certaines tables :

$ mysqldump --hex-blob foo TABLE0 [TABLE1…] > foo_tables.sql

Pour presque faire un --exclude (qui manque cruellement à mysqldump):

$ mysql -B -N -e 'show databases' | \
   perl -ne 'print unless /\b(?:phpmyadmin|mysql|information_schema)\b/' | \
   xargs echo mysqldump --hex-blob -B

Et pour sauvegarder des tables correspondant à un motif (préfixe le plus souvent) :

$ mysqldump --hex-blob foo $(mysql foo -B --column-names=False -e "show tables like 'exemple_%'") > foo_motif.sql

Pour exclure les VIEWS d’une base :

DB=mydb

TBLIST=$(mysql --skip-column-names -e "SHOW FULL TABLES IN \`${DB}\` WHERE \`TABLE_TYPE\` != 'VIEW';" | awk '{print $1}')

mysqldump --single-transaction ${DB} ${TBLIST} > /home/backup/${DB}_tables_without_views.sql

Pour exclure des tables d’un mysqldump :

$ mysqldump [OPTIONS] --ignore-table=DB.TABLE1 --ignore-table=DB.TABLE2

Pour exclure seulement les données de tables mais en garder la structure (à partir de MariaDB 10.1) :

$ mysqldump [OPTIONS] --ignore-table-data=DB.TABLE1 --ignore-table-data=DB.TABLE2

Pour dumper avec une condition particulière :

$ mysqldump -t foo bar --hex-blob --where="id='66666666'"

Ce qui permet de réinjecter des données résultantes d’un SELECT * FROM foo.bar WHERE id='66666666'.

Il est évidement possible de faire toutes ces opérations sur une instance en précisant son port avec l’option --port (valable pour mysqldump et mysql).

Pour obtenir une liste des utilisateurs mysql, on peut utiliser cette fonction (glanée sur serverfault) :

mygrants()
{
  mysql -B -N -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR *, user, _'@'_, host, *;'
    ) AS query FROM mysql.user" | \
  mysql | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

Pour avoir un dump avec un seul insert par ligne, pratique pour restaurer partiellement les tables mysql.global_priv et mysql.db par exemple :

$ mysqldump --skip-extended-insert --events --hex-blob mysql > mysql.sql

Pour sauvegarder une grosse base de donnée en InnoDB et NDB, on peut ajouter l’argument --single-transaction et la combiner avec --skip-lock-tables qui permet d’effectuer le dump dans une transaction et ainsi ne pas verrouiller les tables.

Voir plus d’infomation ici sur ces deux options

Pour sauvegarder uniquement la structure (pour toutes les bases d’un coup)

$ mysqldump --no-data --all-databases > schema.sql

Idem pour une seule base en particulier

$ mysqldump --no-data --databases DATABASE > DATABASE.schema.sql

Il existe aussi une autre manière de sauvegarder une instance MariaBD avec Mariabackup voir /HowtoMySQL/mariabackup.

Restauration

Pour restaurer une base de données (sans et avec compression) :

$ mysqladmin create foo
$ mysql -o foo < foo.sql
$ gunzip < foo.sql.gz | mysql -o foo

Pour restaurer uniquement une base inclus dans un dump complet de MySQL :

$ mysql -o foo < all.sql

Mais si venant d’un dump complet voir aussi : Restaurer une base depuis un dump complet

Pour restaurer une table de type “tab-separated data files” (mysqldump -T) dans une base foo (exemples par défaut et avec des options --fields-xxx) :

$ mysqlimport --default-character-set=utf8 foo /tmp/foo/bar.txt
$ mysqlimport --default-character-set=utf8 --fields-enclosed-by='\"' --fields-terminated-by=',' foo /tmp/foo/bar.txt

Pour restaurer une base entière avec un dump de type “tab-separated data files” (exemples par défaut et avec des options --fields-xxx) :

db=test1

for file in *.sql; do
   mysql $db <$file
done

grep CHARSET= *txt

for file in *.txt; do
   tablename=$(basename $file .txt)
   echo "LOAD DATA INFILE '$PWD/$file' INTO TABLE \`$tablename\`" CHARACTER SET utf8 | mysql $db
   #echo "LOAD DATA INFILE '$PWD/$file' INTO TABLE \`$tablename\`" CHARACTER SET utf8 FIELDS TERMINATED BY ',' ENCLOSED BY '"' | mysql $db
done

Note 1 : Attention, l’utilisateur MySQL doit avoir le droit de lecture sur les fichiers .txt Se positionner dans un répertoire où mysql a les droits (mysqltmp - /home/mysqltmp par ex).

Note 2 : Si vous n’avez pas toutes vos tables en utf8 (par exemple du CHARSET=LATIN1), ce n’est pas bien… et vous devrez pour la peine adapter le script (en détectant le charset utilisé avec « file » si nécessaire)

Note 3 : Si erreur 150 : «Can’t create table» voir du côté des foreign keys :
$ mysql -e 'SHOW ENGINE INNODB STATUS\G;' | grep LATEST\ FOREIGN -A3
et ignorer les erreurs pour pouvoir recréer les tables :
$ mysql -e "set GLOBAL foreign_key_checks=OFF;"

Si cela concerne plusieurs bases réparties dans différents sous-répertoires :

cd /home/mysqldump
for dir in *
do
  echo "=======base $dir========="
  db=$dir
  mysql -e "create database ${dir};"
  ls $dir/*.sql || continue
  for file in $dir/*.sql
  do
    mysql $db <$file
  done
  grep CHARSET= $dir/*txt
  ls $dir/*.txt || continue
  for file in $dir/*.txt
  do
    tablename=$(basename $file .txt)
    echo "LOAD DATA INFILE '$PWD/$file' INTO TABLE $tablename" CHARACTER SET utf8 | mysql $db
  done
done

On peut également restaurer un dump foo.sql (ou tout script au format SQL) de façon interactive via la commande source :

mysql> source foo.sql

Note 1 : il est nécessaire que MySQL ait les droits de lecture sur le fichier foo.sql

Note 2 : les sorties des requêtes sont renvoyées sur la sortie standard (au contraire de la restauration avec mysql < foo.sql)

Pour extraire une table précise d’un dump complet pour ensuite la restaurer

Exclure des tables de la restauration

Si vous disposez d’un dump complet dont vous souhaitez exclure des tables lors de la restauration, vous pouvez utilisez sed pour retirer les lignes correspondantes (pour autant que les INSERT soient en mode mono-ligne) :

# sed '/INSERT INTO `\(table1\|table2\)`/d' dump-full.sql > dump-light.sql

Attention que ça ne supprime pas les instructions DROP TABLE et CREATE TABLE qui sont en général en multi-lignes donc plus difficiles à supprimer.

Monitoring

Pour surveiller un service MySQL en production, on pourra faire :

# mysqladmin status
# mysqladmin extended-status
# mysqladmin processlist

mytop

Pour avoir une version conviviale et dynamique des process en cours, on utilisera l’outil mytop.

# apt install mariadb-client-10.1 libconfig-inifiles-perl libterm-readkey-perl

Note : Pour Debian 7 et 8, c’était dans un package indépendant :
# aptitude install mytop

On édite le fichier /root/.mytop ainsi :

user = debian-sys-maint
pass = PASSWORD
db = mysql

Reste plus qu’à lancer la commande mytop -s1 (pour un rafraichissement toutes les secondes) : on appréciera les raccourcis p (mettre en pause l’affichage), o (pour voir en 1er les requêtes les plus longues / inverse l’ordre de tri sur le temps des requêtes), k (pour killer une requête… par exemple celle qui bloque toutes les autres), i (pour masquer les requêtes en status sleep) et ? (pour voir les autres raccourcis possibles).

L’outil mytop se sert principalement de la requête SHOW PROCESSLIST que l’on pourra bien sûr lancer manuellement. Tout comme KILL :

mysql> SHOW PROCESSLIST;
mysql> SHOW FULL PROCESSLIST;
mysql> KILL <id_requête>;

Pour lister les requêtes qui durent plus de 30 secondes pour pouvoir les tuer facilement :

# mysql -e 'select group_concat(concat("kill ",ID) separator ";") from information_schema.processlist where TIME>=30;'

Puis exécuter le résultat avec la commande mysql, exemple :

# mysql -e 'kill 1854;kill 1853;kill 1852;kill 1851;kill 1850;kill 1848'

Pour surveiller le moteur InnoDB, on utilisera la commande suivante :

mysql> SHOW ENGINE INNODB STATUS;

Pour lister tous les utilisateurs connectés, groupés par host (copié de blog.shlomoid.com) :

mysql> SELECT SUBSTRING_INDEX(host, ':', 1) AS host_short, GROUP_CONCAT(DISTINCT USER) AS users, COUNT(*)
       FROM   information_schema.processlist
       GROUP  BY host_short
       ORDER  BY COUNT(*), host_short;

Installation de mytop sur Debian9 avec MySQL 5.7 (Oracle)

Sur debian9 mytop fait partie du paquet mariadb-client, et n’est plus disponible en stand alone, cela pose un problème si on installe MySQL depuis les dépôts d’oracle.

Il faut prendre le paquet mytop de jessie et l’installer sur stretch :

# wget http://ftp.de.debian.org/debian/pool/main/m/mytop/mytop_1.9.1-2_all.deb
# dpkg -i mytop_1.9.1-2_all.deb

Il se peut qu’il y est des dépendances manquante dans ce cas il faut les installer :

# apt install libdbi-perl libdbd-mysql-perl libconfig-inifiles-perl
# apt --fix-broken install

log2mail

Afin d’être alerté en cas de souci, il est conseillé d’ajouter la configuration suivante au logiciel log2mail :

file = /var/log/syslog
pattern = "is marked as crashed and should be repaired"
mailto = monitoring@example.com
template = /etc/log2mail/template.mysql

file = /var/log/syslog
pattern = "init function returned error"
mailto = monitoring@example.com
template = /etc/log2mail/template.mysql

file = /var/log/syslog
pattern = "try to repair it"
mailto = monitoring@example.com
template = /etc/log2mail/template.mysql

file = /var/log/syslog
pattern = "InnoDB: Fatal error"
mailto = monitoring@example.com
template = /etc/log2mail/template.mysql

file = /var/log/syslog
pattern = "as a STORAGE ENGINE failed"
mailto = monitoring@example.com
template = /etc/log2mail/template.mysql

Le fichier /etc/log2mail/template.mysql contenant :

From: %f
To: %t
Subject: MySQL problem

Hello!

We have matched your pattern "%m" in "%F" %n times:

%l

Yours,
log2mail.

Note : il faut ajouter l’utilisateur log2mail dans le groupe adm.

binlogs

Par défaut, MySQL stocke chaque requête en écriture dans des fichiers appelés binlogs.

Configuration

Par défaut les binlogs sont conservés sur une durée de 10 jours, avec des fichiers n’excédant pas 100 Mo :

[mysqld]
log_bin          = /var/log/mysql/mysql-bin.log
expire_logs_days  = 10
max_binlog_size   = 100M
#binlog_do_db     = include_database_name
#binlog_ignore_db = include_database_name
binlog_format     = mixed

Note : depuis Debian 9, il faut explicitement préciser la directive log_bin pour activer les binlogs.

On peux modifier la valeur de la variable expire_logs_days à chaud avec la commande suivante :

SET GLOBAL expire_logs_days=5;

Il ne faut pas oublié également de mettre à jour la valeur dans le fichier de configuration.

Format

https://mariadb.com/kb/en/binary-log-formats/

On peut choisir 3 types de format pour les binlogs :

statement : En mode statement, les intructions (statements) sont enregistrées dans le binlog exactement comme elles ont été executées. Les tables temporaires créés sur le primaire, sont également créés sur le réplica.

Note : Ce mode est uniquement recommandé lorsque l’on doit conserver les binlogs aussi petit que possible, que les tables primaires et réplicas ont des données identiques (y compris l’utilisation des mêmes moteurs de stockage pour toutes les tables) et que toutes les fonctions utilisées sont déterministes (répétables avec les mêmes arguments). Les instructions et les tables utilisant des horodatages ou l’auto_increment peuvent être utilisées en toute sécurité avec les binlogs en mode statement.

row : Dans ce mode la journalisation est basée sur les lignes (row), les instructions (statements) DML ne sont pas loggées, les DML sont les instructions de manipulation de données (INSERT, UPDATE, DELETE), chaque insertion, mise à jour, ou suppression de chaque ligne efféctué par les intructions, sont enregistrés séparément dans les binlog. Les instructions DDL (CREATE, DROP, ALTER) sont elles toujours enregistrés dans les binlogs. Le row format utilise plus de stockage que les autres mode de binlogs, mais elle est considéré comme la plus sûre à utilisé. En pratique le format mixed devrais être tout aussi sûr.

Note : si l’on souhaite pouvoir voir la requête originale qui a été enregistrée, on peux activé l’annotations des lignes avec la commande mariadb-binlog --binlog-annotate-row-events

mixed : Format de binlog par défaut, dans ce mode, le serveur utilise majoritairement le mode statement, sauf lorsque le serveur détermine qu’une instruction n’est pas sûre, alors il passe en mode row. Voici la liste des instructions déclaré non sûre par MariaDB

Avantages et inconvénients :

Le mode statement est utile pour conserver en clair toutes les requêtes. Il permet aussi de meilleures performances quand des UPDATE contiennent des clauses WHERE qui modifient de nombreuses lignes. Pour de la réplication, il peut être non fiable car le résultat d’un UPDATE peut donner des résultats différents sur un serveur SLAVE. Cela peut aussi poser des soucis avec les transactions InnoDB.

Le mode row a l’inconvénient de rendre illisibles toutes les requêtes. Dans certains cas particuliers (UPDATE contiennent des clauses WHERE qui modifient de nombreuses lignes), il peut être moins performant. Il a l’avantage d’être plus fiable pour de la réplication.

Le mode mixed est un bon compromis pour de la réplication : il permet de voir la plupart des requêtes en clair, mais évite le problème de fiabilité en passant en mode row quand c’est nécessaire.

Les cas suivants permettent de définir l’usage des différents mode suivants :

Si vous exécutez des instructions uniques qui mettent à jour de nombreuses lignes, les binlogs en mode statement sera plus efficace que le mode row.
Si vous exécutez de nombreuses instructions qui n’affectent aucune ligne, les binlogs en mode row sera plus efficace que le mode statement.
Si vous exécutez des instructions qui prennent beaucoup de temps à se terminer, mais qui, en fin de compte, insèrent, mettent à jour ou suppriment uniquement quelques lignes dans la table, les binlogs en mode row sera plus efficace que le mode statement.

Informations

On peut savoir le dernier binlog écrit :

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.020505 | 17736280 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

On peut lister l’ensemble des binlogs vus par MySQL :

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.020437 |  18697341 |
| mysql-bin.020438 | 104858013 |
| mysql-bin.020439 | 104858274 |
| mysql-bin.020440 | 104863158 |
[…]

Et les visualiser d’un point de vue filesystem :

# ls -l /var/log/mysql/

-rw-rw---- 1 mysql adm  18697341 Nov 29 00:01 mysql-bin.020437
-rw-rw---- 1 mysql adm 104858013 Nov 29 09:41 mysql-bin.020438
-rw-rw---- 1 mysql adm 104858274 Nov 29 13:10 mysql-bin.020439
-rw-rw---- 1 mysql adm 104863158 Nov 29 17:28 mysql-bin.020440
[…]
-rw-rw---- 1 mysql adm  17863542 Dec  9 01:24 mysql-bin.020505
-rw-rw---- 1 mysql adm      2208 Dec  9 00:01 mysql-bin.index

Suppression

Si une réplication est en place, il faut vérifier sur le slave quel est le dernier binlog qu’il a récupéré/traité. Pour cela on fait SHOW SLAVE STATUS\G, puis on note la ligne Relay_Master_Log_File:. Elle indique le dernier binlog récupéré/traité. On peut donc supprimer sur le master, tout ceux d’avant. Par précaution, on peut en garder 10.

Exemple : Relay_Master_Log_File: mysql-bin.009628 → sur le master BINARY LOGS TO 'mysql-bin.009618';

Note : On peut s’assurer qu’on a récupéré la valeur de Relay_Master_Log_File sur le bon serveur en la comparant avec les plus récents fichiers mysql-bin.00NNNN présents dans le répertoire /var/log/mysql que l’on souhaite nettoyer.

Pour supprimer les binlogs antérieurs à mysql-bin.00NNNN :

mysql> PURGE BINARY LOGS TO 'mysql-bin.00NNNN';

ou par rapport à une date :

mysql> PURGE BINARY LOGS BEFORE "2011-12-07 00:00:00";

Si cela vient à se reproduire régulièrement, on peut baisser le nombre de jour durant lesquels les binlogs sont gardés avec la directive expire_logs_days.

On peut automatiser la tâche avec l’outil mysqlbinlogpurge dispo via mysql-utils. Il faudra aussi mysql-connector-python.

Exemple :

mysqlbinlogpurge --master=mysqladmin:PASSWORD@192.0.2.1:3306 --slaves=mysqladmin:PASSWORD@192.0.2.2:3306 --dry-run

Note : Il est nécessaire que le slave s’annonce avec –report-host et –report-port. Note : Il est nécessaire de ne pas mettre localhost ni 127.0.0.1 pour –master, mais l’adresse IP principale.

[mysqld]
report-host = 192.0.2.1
report-port = 3306

Désactivation

Ce procédure est à faire à froid (besoin d’un restart), on ajoutera l’option suivante dans la configuration :

[mysqld]
disable-log-bin

Puis on videra les binlogs manuellement depuis mysql :

RESET MASTER;

Lecture

On pourra lire en ligne de commande le contenu d’un binlog via la commande :

# mysqlbinlog /var/log/mysql/mysql-bin.001789 | less

Note* : si vous obtenez une erreur mysqlbinlog: unknown variable 'default-character-set=utf8' c’est que la directive default-character-set a été placée dans la configuration MySQL (/etc/mysql ou .my.cnf) dans la mauvaise section : [client] au lieu de [mysql] (ou [mysqldump]). On peut aussi lancer la commande avec l’option --no-defaults.

Replay

/!\ CES MANIPULATIONS SONT DANGEREUSES POUR VOS DONNÉES, BIEN SAVOIR CE QUE L’ON FAIT !

On pourra ainsi injecter le contenu d’un binlog dans une base… tout simplement avec une commande du type :

# mysqlbinlog /var/log/mysql/mysql-bin.001789 | mysql -P3307

À noter que si une partie des données étaient déjà présentes (cas d’un binlog corrompu lors d’incident lors d’une réplication), on pourra procéder ainsi :

# mysqlbinlog /var/log/mysql/mysql-bin.001789 > mysql-bin.001789.txt
# sed -i 's/INSERT INTO/INSERT IGNORE INTO/gi' mysql-bin.001789.txt
# cat mysql-bin.001789.txt | mysql -P3307

On peut aussi injecter les binlogs, sur une intervale de date et heure précise, en précisant le début et la fin comme ceci :

# mysqlbinlog --database foo --start-datetime='2018-12-27 16:44:57' --stop-datetime='2018-12-27 16:58:00' binlog.*|mysql -u user -p password

SET sql_log_bin = 0

On peut effectuer des requêtes SQL qui ne seront pas écrites dans le binlog. Pour cela on positionne la variable sql_log_bin à 0 et les requêtes interactives suivantes ne seront pas prises en compte dans le binlog (bien sûr, si l’on quitte le CLI MySQL, cela ne sera plus valable) :

mysql> SET sql_log_bin = 0;

Note : cela nécessite le droit MySQL SUPER

Multiples instances MySQL

Il est possible de faire fonctionner plusieurs instances de MySQL sur un serveur où chacune à ses propres données, sa propre configuration et son utilisateur dédié.

On préfère utiliser le port 3307 pour la première instance afin de ne pas confondre une configuration avec et sans instance.

Installation

Pour éviter l’héritage de paramètres issue de l’instance principale, il est nécessaire de commenter cette ligne dans le /etc/mysql/mariadb.conf.d/50-server.cnf :

#user = mysql

Ainsi que rajouter ces lignes dans /etc/mysql/mariadb.conf.d/50-multi.cnf :

[mysqld_multi]
user = mysqladmin

Désactiver l’instance par défaut (qui écoute sur le port 3306) si elle n’est pas nécessaire :

systemctl disable --now mariadb

Créer un utilisateur dédié pour l’instance :

instance=1
useradd mysqld${instance}

Créer les dossiers qui vont accueillir le datadir et les données temporaire avec les bons droits :

mkdir -vp /srv/mysqld_instances/mysqld${instance} /home/mysqld${instance}-tmp
touch /var/log/mysqld${instance}.log
chmod 755 /srv /srv/mysqld_instances
chown -R mysqld${instance}:mysqld${instance} /srv/mysqld_instances/mysqld${instance} /home/mysqld${instance}-tmp /var/log/mysqld${instance}.log

Ajouter ces lignes dans /etc/mysql/mariadb.conf.d/zzz_mysqld${instance}.cnf :

echo "[mysqld${instance}]
user = mysqld${instance}
port = $((3306+${instance}))
tmpdir = /home/mysqld${instance}-tmp/
socket = /var/run/mysqld${instance}/mysqld${instance}.sock
pid-file = /var/run/mysqld${instance}/mysqld${instance}.pid
datadir = /srv/mysqld_instances/mysqld${instance}
log_error = /var/log/mysqld${instance}.log" \
> /etc/mysql/mariadb.conf.d/zzz_mysqld${instance}.cnf

L’ajout de ces directives permet de surcharger les valeurs issue de l’instance principale :

skip-slave-start
log_bin = 0
slow_query_log = 0

Ajuster les permissions du fichier de configuration :

chmod 644 /etc/mysql/mariadb.conf.d/zzz_mysqld${instance}.cnf

Créer manuellement le datadir :

mariadb-install-db --user=mysqld${instance} --datadir=/srv/mysqld_instances/mysqld${instance} --defaults-file=/etc/mysql/mariadb.conf.d/zzz_mysqld${instance}.cnf --skip-test-db
chmod 700 /srv/mysqld_instances/mysqld${instance}

Noter les information de sortie de la commande mariadb-install-db pour pouvoir se connecter à l’instance.

Note 1 : La commande mariadb-install-db remplace l’ancienne commande mysql_install_db

Note 2 : à partir de MariaDB 10.11 (Debian 12) il faut spécifier l’utilisateur, même en cas d’instance unique.

Mettre en place la rotation des logs dans /etc/logrotate.d/zzz-mysql-server en ajustant <INSTANCE> et PORT :

/var/log/mysqld<INSTANCE>.log {  # mettre aussi les slow logs si activés
    daily
    rotate 7
    missingok
    create 640 mysqld<INSTANCE> mysqld<INSTANCE>
    compress
    sharedscripts
    postrotate
          test -x /usr/bin/mysqladmin || exit 0
          mysqladmin --defaults-group-suffix=<INSTANCE> flush-error-log flush-engine-log flush-general-log flush-slow-log
    endscript
}

Valider la configuration :

# logrotate --verbose --debug --force /etc/logrotate.d/zzz-mysql-server

Démarrer une instance avec systemd

On créé une unité systemd multi-instances pour mysql dans /etc/systemd/system/mysqld@.service

[Unit]
Description=MySQL Multi Server for instance %i
After=syslog.target
After=network.target

[Service]
PIDFile=/var/run/mysqld%i/mysqld%i.pid
User=mysqld%i
Group=mysqld%i
Type=forking
ExecStart=/usr/bin/mysqld_multi start %i
ExecStop=/usr/bin/mysqld_multi stop %i
Restart=always
PrivateTmp=true
RuntimeDirectory=mysqld%i

[Install]
WantedBy=multi-user.target

On recharge le daemon systemd pour que l’unité soit prise en compte et on lance la nouvelle instance dont le “1” est une sorte d’alias de mysqld1 :

# systemctl daemon-reload
# systemctl start mysqld@1

On s’assure qu’elle fonctionne bien :

# systemctl status mysqld@1

● mysqld@1.service - MySQL Multi Server for instance 1
   Loaded: loaded (/etc/systemd/system/mysqld@.service; disabled; vendor preset: enabled)
   Active: active (running) since Tue 2020-08-18 15:49:27 CEST; 6min ago
  Process: 16403 ExecStart=/usr/bin/mysqld_multi start 1 (code=exited, status=0/SUCCESS)
 Main PID: 16408 (mysqld)
    Tasks: 30 (limit: 4915)
   Memory: 70.7M
   CGroup: /system.slice/system-mysqld.slice/mysqld@1.service
           └─16408 /usr/sbin/mysqld --defaults-group-suffix=1 --user=mysqld1 --port=3307 --tmpdir=/localhome/emorino/mysqld1-tmp --socket=/var/run/mysqld1/mysq

août 18 15:49:27 huit systemd[1]: Starting MySQL Multi Server for instance 1...
août 18 15:49:27 huit mysqld_multi[16403]: [67B blob data]
août 18 15:49:27 huit mysqld_multi[16403]: Starting MariaDB servers
août 18 15:49:27 huit mysqld_multi[16403]: 2020-08-18 15:49:27 0 [Note] /usr/sbin/mysqld (mysqld 10.3.23-MariaDB-0+deb10u1) starting as process 16408 ...
août 18 15:49:27 huit systemd[1]: Started MySQL Multi Server for instance 1.

Pour stopper une instance, on évite la commande « mysqld_multi stop 1 » qui n’est pas assez fiable et peut laisser l’instance dans un état incorrect, difficile à récupérer.

On préfère passer la commande « shutdown » en interne qui devrait envoyer un signal SIGTERM (kill -15) au process mysqld :

# mysqladmin -P3307 shutdown

Avec l’unité systemd on peut stopper l’unité systemd en faisant :

# systemctl stop mysqld@1

Connexion aux instances et création du fichier ~/.my.cnf

Lors de la création de plusieurs instances MySQL, on préférera donner à chacune un mot de passe différents à l’utilisateur mysqladmin

On sécurise la nouvelle instance en définissant un mot de passe au compte mysqladmin (adapter en fonction de la sortie de la commande mariadb-install-db précédemment) :

# mysql -P3307 --socket=/var/run/mysqld1/mysqld1.sock -u root -p

On supprime l’utilisateur root pour le remplacer par l’utilisateur mysqladmin dont les requêtes sont identiques à une installation mono-instances.

Pour utiliser le multi client, on configurera le fichier ~/.my.cnf comme ceci :

[client1]
host = 127.0.0.1
port = 3307
user = mysqladmin
password = foo

[client2]
host = 127.0.0.1
port = 3308
user = mysqladmin
password = bar

Cette configuration permettra de se connecter à l’instance mysql avec l’option --defaults-group-suffix=$NUM par exemple :

mysql --defaults-group-suffix=1

Attention : Si on utilise cela, il faut modifier vos scripts de mysqldump ou de création de base en conséquence.

Nettoyage

Si le mysqld principal n’est pas utilisé, on désactivera l’unité systemd comme ceci :

# systemctl stop mysql.service
# systemctl disable mysql.service

Pour gérer les instances créées, on utilisera l’unité systemd mysqld@.service :

# systemctl status mysqld@1
# systemctl stop mysqld@1
# systemctl start mysqld@1
# systemctl enable mysqld@1

Administration

Pour voir le statut de l’instance n°1, logiquement nommée mysqld1 (GNR=1) et tournant sur le port 3307 :

# mysqld_multi report 1
Reporting MySQL servers
MySQL server from group: mysqld1 is running

Pour l’arrêter/redémarrer, même principe (attention, mysqld_multi est peu verbeux) :

# ps auwx | grep 3307
# mysqladmin -P 3307 shutdown
# ps auwx | grep 3307
# mysqld_multi start 1
# ps auwx | grep 3307

Si on a configuré plusieurs instance dans le fichier .my.cnf, on peut choisir l’instance sur laquelle on veux se connecter en utilisant mysql –defaults-group-suffix=1 par exemple.

Plugins

MariaDB Audit Plugin

Documentation : https://mariadb.com/kb/en/mariadb-audit-plugin/

Ce plugin est fourni par MariaDB. Il permet d’avoir un log d’audit des connexions et opérations réalisées au niveau de MySQL/MariaDB.

Pour l’installer, le mieux est de le faire via le fichier de configuration (par exemple /etc/mysql/mariadb.conf.d/zzz-evolinux-custom.cnf), dans la section adaptée :

[mariadb]
[…]
plugin_load_add = server_audit

On peut aussi le faire dynamiquement, mais cela ne sera pas persistent au prochain redémarrage :

mariadb> INSTALL SONAME 'server_audit';

Par défaut le plugin est inactif, on peut l’activer avec la variable globale server_audit_logging (dans la config ou bien dynamiquement) :

[mariadb]
plugin_load_add = server_audit
server_audit_logging = ON

Par défaut il écrit ses logs dans un fichier DATA_DIR/server_audit.log, mais il est conseillé de le placer avec les autres logs :

[mariadb]
plugin_load_add = server_audit
server_audit_file_path = /var/log/mysql/server_audit.log
server_audit_logging = ON

Il est possible de filtrer le type d’événements avec la variable server_audit_events et de filtrer les utilisateurs avec les variables server_audit_incl_users et server_audit_excl_users. La documentation indique toutes les valeurs possibles.

La rotation des logs est automatique, mais personnalisable.

Activer la connexion via SSL/TLS à la base de données

Principe de fonctionnement

On génère une CA (clé privée et certificat) pour pouvoir signé une certificat serveur et client.

On ajoute le certificat serveur dans la configuration du serveur MySQL.

Le certificat client sert a se connecter au instances MySQL en SSL, soit avec PhpMyAdmin soit avec mysql-client, ou autres.

On par du principe qu’on l’on créer les clés / certificats dans /etc/mysql/ssl

Création de la CA

On créé la CA comme ceci :

# openssl genrsa 4096 > mysql-ca-key.pem
# openssl req -new -x509 -nodes -days 365000 -key mysql-ca-key.pem -out mysql-ca-cert.pem

Création du certificat SSL serveur

On créé le certificat serveur comme ceci, ne général on mets comme Common Name le hostname du serveur :

# openssl req -newkey rsa:2048 -days 365 -nodes -keyout mysql-server-key.pem -out mysql-server-req.pem
# openssl rsa -in mysql-server-key.pem -out mysql-server-key.pem
# openssl x509 -req -in mysql-server-req.pem -days 365 -CA mysql-ca-cert.pem -CAkey mysql-ca-key.pem -set_serial 01 -out mysql-server-cert.pem

Création du certificat SSL client

On créé le certificat client comme ceci, le Common Name est identique à celui du serveur :

# openssl req -newkey rsa:2048 -days 365 -nodes -keyout mysql-client-key.pem -out mysql-client-req.pem
# openssl rsa -in mysql-client-key.pem -out mysql-client-key.pem
# openssl x509 -req -in mysql-client-req.pem -days 365 -CA mysql-ca-cert.pem -CAkey mysql-ca-key.pem -set_serial 01 -out mysql-client-cert.pem

On vérifie la correspondance des certificats entre le certificat serveur et client comme ceci :

# openssl verify -CAfile mysql-ca-cert.pem mysql-server-cert.pem mysql-client-cert.pem
mysql-server-cert.pem: OK
mysql-client-cert.pem: OK

Installation du certificat SSL serveur sur une instance MySQL

Il faut possitionner les bons droits sur les certificats comme ceci :

# chown -Rv mysql:root /etc/mysql/ssl/

Puis on mets cette configuration dans /etc/mysql/mariadb.conf.d/zzz-evolinux-custom.cnf

# SSL
ssl = on
ssl-ca = /etc/mysql/ssl/mysql-ca-cert.pem
ssl-cert = /etc/mysql/ssl/mysql-server-cert.pem
ssl-key = /etc/mysql/ssl/mysql-server-key.pem
ssl-cipher = AES256-SHA

Un redémarrage de mysql / mariadb est necessaire :

# systemctl restart mysql.service

On peut vérifier que c’est bien pris en compte sur l’instance :

mysql> show variables like '%ssl%';
+--------------------+--------------------------------------+
| Variable_name      | Value                                |
+--------------------+--------------------------------------+
| have_openssl       | YES                                  |
| have_ssl           | YES                                  |
| mysqlx_ssl_ca      |                                      |
| mysqlx_ssl_capath  |                                      |
| mysqlx_ssl_cert    |                                      |
| mysqlx_ssl_cipher  |                                      |
| mysqlx_ssl_crl     |                                      |
| mysqlx_ssl_crlpath |                                      |
| mysqlx_ssl_key     |                                      |
| ssl_ca             | /etc/mysql/ssl/mysql-ca-cert.pem     |
| ssl_capath         |                                      |
| ssl_cert           | /etc/mysql/ssl/mysql-server-cert.pem |
| ssl_cipher         | AES256-SHA                           |
| ssl_crl            |                                      |
| ssl_crlpath        |                                      |
| ssl_fips_mode      | OFF                                  |
| ssl_key            | /etc/mysql/ssl/mysql-server-key.pem  |
+--------------------+--------------------------------------+
17 rows in set (0.00 sec)

Chiffrement de la base de données Transparent Data Encryption (TDE)

On peut chiffrer les bases et les tables avec la méthode Transparent Data Encryption (TDE), cela ne nécessite aucun changement au niveau applicatif pour se connecter à la base de donnée, d’où le terme « Transparent », il existe plusieurs plugins pour faire cela :

File Key Management Encryption
- Le fichier de clé est stocké dans MariaDB.
- La bonne pratique est de la stocké dans un montage séparé.
AWS Key Management Encryption Plugin
- La clé est créé et stocké sur AWS, c’est un bon choix si on a une base de données managé chez AWS.

Mise en place du chiffrement avec File Key Management Encryption

Dans une premier temps on doit créer un dossier où le fichier de clé sera stocké, puis on genère la clé :

$ mkdir -p /etc/mysql/encryption
$ echo "1;"$(openssl rand -hex 32) > /etc/mysql/encryption/keyfile
$ openssl rand -hex 128 > /etc/mysql/encryption/keyfile.key
$ openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql/encryption/keyfile.key -in /etc/mysql/encryption/keyfile -out /etc/mysql/encryption/keyfile.enc

Puis on supprime la clé originale non chiffré :

rm -f /etc/mysql/encryption/keyfile

et on donne les bon droits au dossier / fichiers, on présume ici que l’instance tourne avec l’utilisateur mysql :

$ chown -R mysql:mysql /etc/mysql
$ chmod -R 500 /etc/mysql
$ ls -lRt /etc/mysql
/etc/mysql:
total 0
dr-x------. 2 mysql mysql 44 Mar 22 17:45 encryption

/etc/mysql/encryption:
total 8
-r-x------. 1 mysql mysql  96 Mar 22 17:43 keyfile.enc
-r-x------. 1 mysql mysql 257 Mar 22 17:43 keyfile.key

Puis on rajoute ceci dans le fichier de configuration, soit dans la section [mariadb], soit dans la section de notre instance, par exemple, [mysqld1] :

plugin_load_add = file_key_management
file_key_management_filename = /etc/mysql/encryption/keyfile.enc
file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
file_key_management_encryption_algorithm = AES_CBC

innodb_encrypt_tables = FORCE
innodb_encrypt_log = ON
innodb_encrypt_temporary_tables = ON

encrypt_tmp_disk_tables = ON
encrypt_tmp_files = ON
encrypt_binlog = ON
aria_encrypt_tables = ON

innodb_encryption_threads = 4
innodb_encryption_rotation_iops = 2000

Dans la première section de la configuration :
- plugin_load_add : on charge le plugin file_key_management
- file_key_management_* : on indique le chemin des clés
- file_key_management_encryption_algorithm : on defini l’algorithme de chiffrement, soit AES_CBC si MariaDB utilise le binaire yaSSL ou wolfSSL, soit AES_CTR mais ce dernier est disponible seulement si MariaDB utilise le binaire OpenSSL
Dans la seconde section de la configuration :
- innodb_encrypt_tables : on force le chiffrement des bases / tables existantes.
- innodb_encrypt_log : on chiffre les redo logs
- innodb_encrypt_temporary_tables : on chiffre les tables temporaires.
Autres paramètres intérréssants :
- encrypt_binlog : on chiffre les logs binaires.
- encrypt_tmp_files : on chiffre les fichiers temporaires.

Une fois la configuration en place, on redémarre MariaDB :

# systemctl restart mariadb

Puis on peut vérifier le chiffrement des bases / tables avec cette requête :

MariaDB [none]> SELECT CASE WHEN INSTR(NAME, '/') = 0 
                   THEN '01-SYSTEM TABLESPACES'
                   ELSE CONCAT('02-', SUBSTR(NAME, 1, INSTR(NAME, '/')-1)) END 
                     AS "Schema Name",
         SUM(CASE WHEN ENCRYPTION_SCHEME > 0 THEN 1 ELSE 0 END) "Tables Encrypted",
         SUM(CASE WHEN ENCRYPTION_SCHEME = 0 THEN 1 ELSE 0 END) "Tables Not Encrypted"
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
GROUP BY CASE WHEN INSTR(NAME, '/') = 0 
                   THEN '01-SYSTEM TABLESPACES'
                   ELSE CONCAT('02-', SUBSTR(NAME, 1, INSTR(NAME, '/')-1)) END
ORDER BY 1;
;


+-----------------------+------------------+----------------------+
| Schema Name           | Tables Encrypted | Tables Not Encrypted |
+-----------------------+------------------+----------------------+
| 01-SYSTEM TABLESPACES |                1 |                    0 |
| 02-mysql              |                4 |                    0 |
+-----------------------+------------------+----------------------+
2 rows in set (0.002 sec)

Si on tente de lire un fichier .idb d’une table, voici ce qu’on obtient :

# cd /var/lib/mysql/mysql/
# strings transaction_registry.ibd | head -20
mPQ&
P.S|y
"h_S2$
m,uQ
I0$Y
AhZV
,tp"
Jb4\
TT v
))ok
hByc
-	aG
GQQM
nLUZ$
Jb9q
)72B
`dDF'
f=Fb
4 PR
b?;(

Optimisation avancée

Voir /HowtoMySQL/Optimize.

Réplication MySQL

Voir /HowtoMySQL/Replication.

Mariabackup / Percona XtraBackup

Voir /HowtoMySQL/mariabackup.

FAQ et erreurs courantes

Voir /HowtoMySQL/Troubleshooting.