MariaDB (Français)

État de la traduction: Cet article est la version francophone de MariaDB. Date de la dernière traduction: 2022-10-03. Vous pouvez aider à synchroniser la traduction s'il y a eu des changements dans la version anglaise.

Related articles

• phpMyAdmin (Français)
• Adminer
• JDBC and MySQL
• Open Database Connectivity

MariaDB est un serveur de base de données fiable, performant et complet qui se veut être un remplacement de MySQL "toujours libre, rétrocompatible et prêt à l'emploi". Depuis 2013, MariaDB est l'implémentation par défaut de MySQL sur Arch Linux. [1]

Installation

MariaDB est l'implémentation par défaut de MySQL dans Arch Linux, fournie par le paquet mariadb.

Astuce:

Installez mariadb, et exécutez la commande suivante avant de démarrer le mariadb.service :

# mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

Astuce: Si vous utilisez autre chose que /var/lib/mysql pour votre répertoire de données, vous devez définir datadir=YOUR_DATADIR dans la section [mysqld] de votre /etc/my.cnf.d/server.cnf.

Maintenant, démarrez et/ou activez mariadb.service.

Note: Avant de continuer, il est recommandé d'améliorer la sécurité initiale de l'installation de MySQL.

Pour simplifier l'administration, vous pouvez installer un front-end.

Configuration

Une fois que vous avez démarré le serveur MySQL et ajouté un compte root, vous pouvez souhaiter modifier la configuration par défaut.

Pour vous connecter en tant que root sur le serveur MySQL, utilisez la commande suivante :

# mysql -u root -p

Note: Le mot de passe par défaut est vide. Appuyez sur Enter pour vous connecter.

Ajouter un utilisateur

La création d'un nouvel utilisateur se fait en deux étapes : créer l'utilisateur ; accorder des privilèges. Dans l'exemple ci-dessous, on crée l'utilisateur monty avec some_pass comme mot de passe, puis on lui accorde toutes les permissions sur la base de données mydb :

# mysql -u root -p

MariaDB> CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
MariaDB> GRANT ALL PRIVILEGES ON mydb.* TO 'monty'@'localhost';
MariaDB> FLUSH PRIVILEGES;
MariaDB> quit

Fichiers de configuration

Les options de configuration de MariaDB sont lues dans les fichiers suivants dans l'ordre donné (selon la sortie mysqld --help --verbose | tail -20) :

/etc/my.cnf /etc/my.cnf.d/ ~/.my.cnf

En fonction de l'étendue des modifications que vous souhaitez effectuer (système, utilisateur seulement...), utilisez le fichier correspondant. Consultez cette partie de la base de connaissances pour plus d'informations.

Activer l'autocomplétion

Note: L'activation de cette fonctionnalité peut rendre l'initialisation du client plus longue.

La fonction de complétion du client MySQL est désactivée par défaut. Pour l'activer dans tout le système, éditez /etc/my.cnf.d/mysql-clients.cnf, et ajoutez auto-rehash sous mysql. Notez que cela ne doit pas être indiqué dans mysqld. L'autocomplétion sera activé la prochaine fois que vous exécuterez le client MySQL.

Utiliser UTF8MB4

Attention: Avant de modifier le jeu de caractères, assurez-vous de créer d'abord une sauvegarde.

Note:

• Le paquet mariadb utilise déjà utf8mb4 comme jeu de caractères et utf8mb4_unicode_ci comme collation. Les utilisateurs qui utilisent les paramètres par défaut (pour les caractères) peuvent vouloir sauter cette section.
• UTF8MB4 est recommandé par rapport à UTF-8 car il permet une prise en charge Unicode complet [2] [3].

Ajoutez les valeurs suivantes au fichier de configuration principal situé à /etc/my.cnf.d/my.cnf :

[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4

Redémarrez mariadb.service pour appliquer les changements.

Consultez #Maintenance pour optimiser et vérifier la santé de la base de données.

Augmenter la limite de caractères

Note: La limite de caractères dépend du jeu de caractères utilisé [4] [5] [6].

Pour InnoDB, exécutez les commandes suivantes pour prendre en charge une limite de caractères plus élevée :

mysql> set global innodb_file_format = BARRACUDA;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_file_per_table = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_large_prefix = ON;
Query OK, 0 rows affected (0.00 sec)

Ajoutez les lignes suivantes dans /etc/mysql/my.cnf pour toujours utiliser une limite de caractères plus élevée :

[mysqld]
innodb_file_format = barracuda
innodb_file_per_table = 1
innodb_large_prefix = 1

Redémarrez mariadb.service pour appliquer les changements.

Lors de la création de la table, ajoutez le ROW_FORMAT comme consulté dans l'exemple :

mysql> create table if not exists products (
   ->   day date not null,
   ->   product_id int not null,
   ->   dimension1 varchar(500) not null,
   ->   dimension2 varchar(500) not null,
   ->   unique index unique_index (day, product_id, dimension1, dimension2)
   -> ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.02 sec)

Utiliser un tmpfs pour tmpdir

Le répertoire utilisé par MySQL pour stocker les fichiers temporaires est nommé tmpdir. Il est utilisé, par exemple, pour effectuer des tris importants sur disque, ainsi que pour les tables temporaires internes et explicites.

Créez le répertoire avec les permissions appropriées :

# mkdir -pv /var/lib/mysqltmp
# chown mysql:mysql /var/lib/mysqltmp

Ajoutez le montage tmpfs suivant à votre fichier /etc/fstab :

tmpfs /var/lib/mysqltmp tmpfs rw,gid=mysql,uid=mysql,size=100M,mode=0750,noatime 0 0

Ajoutez à votre fichier /etc/my.cnf.d/server.cnf sous le groupe mysqld :

tmpdir = /var/lib/mysqltmp

Stoppez mariadb.service, montez /var/lib/mysqltmp/ et démarrez mariadb.service.

Tables de fuseaux horaires

Bien que les tables de fuseaux horaires soient créées pendant l'installation, elles ne sont pas automatiquement remplies. Elles doivent être remplies si vous prévoyez d'utiliser CONVERT_TZ() dans des requêtes SQL.

Pour remplir les tables de fuseaux horaires avec tous les fuseaux horaires :

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

En option, vous pouvez remplir la table avec des fichiers de fuseaux horaires spécifiques :

$ mysql_tzinfo_to_sql fichier_de_fuseau_horaire nom_de_fuseau_horaire. | mysql -u root -p mysql

Sécurité

Améliorer la sécurité initiale

La commande mysql_secure_installation vous guidera de manière interactive à travers un certain nombre de mesures de sécurité recommandées, comme la suppression des comptes anonymes et la suppression de la base de données de test :

# mysql_secure_installation

Attention: Après avoir exécuté cette commande, veuillez noter que le port TCP 3306 sera toujours ouvert, mais refusera les connexions avec un message d'erreur. Pour empêcher MySQL d'écouter sur une interface externe, consultez les sections #Écouter uniquement sur l'adresse de bouclage et #Restreindre l'accès local uniquement via les sockets Unix.

Écouter uniquement sur l'adresse de bouclage

Par défaut, MySQL écoutera sur l'adresse 0.0.0.0, qui inclut toutes les interfaces réseau. Afin de limiter l'écoute de MySQL à l'adresse de bouclage («loopback»), ajoutez la ligne suivante dans /etc/my.cnf.d/server.cnf :

[mysqld]
bind-address = localhost

Cela permettra le lien à la fois vers 127.0.0.1 et ::1, et permettra à MariaDB de recevoir des connexions en IPv4 et en IPv6.

Restreindre l'accès local uniquement via les sockets Unix

Par défaut, MySQL est accessible à la fois via les sockets Unix et le réseau. Si MySQL n'est nécessaire que pour l'hôte local, vous pouvez améliorer la sécurité en n'écoutant pas le port TCP 3306, et en écoutant uniquement les sockets Unix à la place. Pour ce faire, ajoutez la ligne suivante dans /etc/my.cnf.d/server.cnf :

[mysqld]
skip-networking

Vous pourrez toujours vous connecter localement comme avant, mais uniquement en utilisant les sockets Unix.

Accorder l'accès à distance

Attention: Ce n'est pas considéré comme une bonne pratique et peut causer des problèmes de sécurité. Envisagez d'utiliser Secure Shell, VNC ou VPN, si vous voulez maintenir le serveur MySQL à partir d'un autre hôte à l'intérieur ou à l'extérieur de votre réseau.

Pour autoriser l'accès à distance au serveur MySQL, assurez-vous que MySQL a un accès réseau activé et est à l'écoute sur l'interface appropriée.

Accordez à n'importe quel utilisateur de MySQL un accès distant (exemple pour root) :

# mysql -u root -p

Vérifiez les utilisateurs actuels ayant un accès à distance privilégié :

SELECT User, Host FROM mysql.user WHERE Host <> 'localhost';

Maintenant, accordez l'accès à distance à votre utilisateur (ici root) :

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.%' IDENTIFIED BY 'my_optional_remote_password' WITH GRANT OPTION;

Vous pouvez remplacer le caractère générique '%' par un hôte spécifique si vous le souhaitez. Le mot de passe peut être différent du mot de passe principal de l'utilisateur.

Configurer l'accès aux répertoires personnels

Pour des raisons de sécurité, le fichier de service systemd contient ProtectHome=true, qui empêche MariaDB d'accéder aux fichiers sous les hiérarchies /home, /root et /run/user. Le datadir doit être dans un emplacement accessible et appartenir à l'utilisateur et au groupe mysql.

Vous pouvez modifier ce comportement en créant un fichier de service supplémentaire comme décrit ici.

Maintenance

Mise à jour des bases de données lors des versions majeures

Lors de la sortie d'une version majeure de mariadb (par exemple mariadb-10.1.10-1 à mariadb-10.1.18-1), il est prudent de mettre à jour les bases de données :

# mysql_upgrade -u root -p

Pour mettre à jour de 10.1.x à 10.3.x. :

• garder le daemon de base de données 10.1.x en fonctionnement
• mettez à jour le paquet
• Exécutez mysql_upgrade. (à partir de la nouvelle version du paquet) avec l'ancien daemon encore en fonctionnement. Cela produira quelques messages d'erreur ; cependant, la mise à jour réussira.
• Redémarrez le daemon, afin que le daemon 10.3.x fonctionne.

Sinon, arrêtez le (vieux) daemon, exécutez le (nouveau) daemon en mode sans échec, exécutez mysql_upgrade par rapport à celui-ci, puis démarrez le (nouveau) daemon comme décrit dans #Impossible d'exécuter mysql_upgrade car MySQL ne peut pas démarrer.

Vérification, optimisation et réparation des bases de données

mariadb est livré avec mysqlcheck qui peut être utilisé pour vérifier, réparer et optimiser les tables des bases de données à partir du shell. Consultez la page de manuel mysqlcheck pour en savoir plus. Plusieurs tâches de commande sont présentées :

Pour vérifier toutes les tables de toutes les bases de données :

$ mysqlcheck --all-databases -u root -p -c

Pour analyser toutes les tables de toutes les bases de données :

$ mysqlcheck --all-databases -u root -p -a

Pour réparer toutes les tables de toutes les bases de données :

$ mysqlcheck --all-databases -u root -p -r

Pour optimiser toutes les tables de toutes les bases de données :

$ mysqlcheck --all-databases -u root -p -o

Sauvegarde

Il existe plusieurs outils et stratégies pour sauvegarder vos bases de données.

Si vous utilisez le moteur de stockage InnoDB par défaut, une façon suggérée de sauvegarder toutes vos bases en ligne tout en prévoyant une récupération ponctuelle (également connue sous le nom de "roll-forward", lorsque vous devez restaurer une ancienne sauvegarde et rejouer les changements survenus depuis cette sauvegarde) consiste à exécuter la commande suivante :

$ mysqldump --single-transaction --flush-logs --master-data=2 --all-databases -u root -p > all_databases.sql

Cette commande vous demandera le mot de passe de l'utilisateur root de MariaDB, qui a été défini lors de la #Configuration de la base de données.

La spécification du mot de passe en ligne de commande est fortement déconseillée, car elle l'expose à la découverte par d'autres utilisateurs via l'utilisation de ps aux ou d'autres techniques. Au lieu de cela, la commande susmentionnée demandera le mot de passe de l'utilisateur spécifié, le dissimulant ainsi.

Compression

Comme les tables SQL peuvent être assez volumineuses, il est recommandé d'acheminer la sortie de la commande susmentionnée dans un utilitaire de compression comme gzip :

$ mysqldump --single-transaction --flush-logs --master-data=2 --all-databases -u root -p | gzip > all_databases.sql.gz

Décompresser la sauvegarde ainsi créée et la recharger dans le serveur s'effectue en faisant :

$ zcat all_databases.sql.gz | mysql -u root -p

Ceci recréera et repeuplera toutes les bases de données précédemment sauvegardées (consultez ce lien ou celui-ci).

Non-interactif

Si vous souhaitez configurer un script de sauvegarde non interactif à utiliser dans des tâches cron ou des timers systemd, consultez option files et cette illustration pour mysqldump.

En gros, vous devez ajouter la section suivante au fichier de configuration concerné :

[mysqldump]
user=mysqluser
password=secret

Mentionner un utilisateur ici est facultatif, mais cela vous évitera d'avoir à le mentionner sur la ligne de commande. Si vous souhaitez définir cette option pour tous les outils, y compris mysql, utilisez le groupe [client].

Script d'exemple

La base de données peut être vidée dans un fichier pour faciliter la sauvegarde. Le script shell suivant le fera pour vous, en créant un fichier db_backup.gz dans le même répertoire que le script, contenant votre vidage de base de données :

#!/bin/sh

THISDIR=$(dirname $(readlink -f "$0"))

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases \
 | gzip > $THISDIR/db_backup.gz
echo 'purge master logs before date_sub(now(), interval 7 day);' | mysql

Consultez également la page officielle mysqldump dans les manuels MySQL et MariaDB.

Holland Backup

Un logiciel basé sur python nommé Holland Backup est disponible dans AUR pour automatiser tout le travail de sauvegarde. Il prend en charge les méthodes suivantes : mysqldump direct, instantanés LVM vers des fichiers tar (mysqllvm), instantanés LVM avec mysqldump (mysqldump-lvm), et xtrabackup pour extraire les données. Le framework Holland prend en charge une multitude d'options et est hautement configurable pour répondre à presque toutes les situations de sauvegarde.

Les paquets principaux holland^AUR et holland-common^AUR fournissent le cadre de base ; l'un des sous-paquets (holland-mysqldump^AUR, holland-mysqllvm^AUR et/ou holland-xtrabackup^AUR doit être installé pour un fonctionnement complet. Des exemples de configurations pour chaque méthode se trouvent dans le répertoire /usr/share/doc/holland/examples/ et peuvent être copiés dans /etc/holland/backupsets/, ainsi qu'en utilisant la commande holland mk-config pour générer une configuration de base pour un fournisseur nommé.

Dépannage

Impossible d'exécuter mysql_upgrade car MySQL ne peut pas démarrer

Essayez d'exécuter MySQL en mode sécurisé :

# mysqld_safe --datadir=/var/lib/mysql/

Et ensuite exécutez :

# mysql_upgrade -u root -p

Réinitialiser le mot de passe root

1. Arrêtez mariadb.service.
2. Démarrez le serveur mysqld avec les fonctions de sécurité :

   # mysqld_safe --skip-grant-tables --skip-networking &

3. Connectez-vous au serveur :

   # mysql -u root

4. Changez le mot de passe root :

   MariaDB [(none)]> use mysql
   MariaDB [mysql]> flush privileges;
   MariaDB [mysql]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
   MariaDB [mysql]> exit
   

5. Tuez les processus mysqld* en cours d'exécution :

   # kill $(cat /var/lib/mysql/$HOSTNAME.pid)

6. Démarrez mariadb.service.

Vérifier et réparer toutes les tables

Vérifier et réparer automatiquement toutes les tables dans toutes les bases de données, consultez la suite :

# mysqlcheck -A --auto-repair -u root -p

Optimiser toutes les tables

Optimisez de force toutes les tables, en réparant automatiquement les erreurs de table qui peuvent survenir.

# mysqlcheck -A --auto-repair -f -o -u root -p

Erreur OS 22 lors de l'exécution sur ZFS

Si vous utilisez des bases de données MySQL sur ZFS, l'erreur InnoDB : Operating system error number 22 in a file operation peut se produire.

Une solution de contournement consiste à désactiver aio_writes dans /etc/my.cnf.d/my.cnf :

/etc/my.cnf.d/my.cnf

[mysqld]
innodb_use_native_aio = 0

Impossible de se connecter via CLI, mais phpmyadmin fonctionne bien

Cela peut se produire si vous utilisez un mot de passe long (>70-75). En ce qui concerne la version 5.5.36, pour une raison quelconque, mysql CLI ne peut pas gérer un tel nombre de caractères en mode lecture. Donc, si vous envisagez d'utiliser le mode de saisie de mot de passe recommandé :

$ mysql -u user -p

Mot de passe :

Pensez à changer le mot de passe pour un plus petit.

Note: Vous pouvez toujours vous connecter en spécifiant le mot de passe comme argument de la commande mysql.

Attention: Ce comportement est considéré comme dangereux, car votre mot de passe pourrait fuir, par exemple, dans les journaux. N'utilisez cette méthode qu'en cas d'urgence et n'oubliez pas de changer le mot de passe juste après.

$ mysql -u user -p "some-very-strong-password"

Les journaux binaires de MySQL prennent beaucoup d'espace disque

Par défaut, mysqld crée des fichiers journaux binaires dans /var/lib/mysql. Ceci est utile pour la réplication du serveur maître ou la récupération des données. Mais ces journaux binaires peuvent consommer votre espace disque. Si vous ne prévoyez pas d'utiliser les fonctionnalités de réplication ou de récupération de données, vous pouvez désactiver la journalisation binaire en commentant ces lignes dans /etc/mysql/my.cnf :

#log-bin=mysql-bin
#binlog_format=mixed

Ou vous pouvez limiter la taille du fichier journal comme ceci :

expire_logs_days = 10
max_binlog_size = 100M

Vous pouvez également purger certains journaux binaires dans /var/lib/mysql pour libérer de l'espace disque avec cette commande :

# mysql -u root -p "PASSWORD" -e "PURGE BINARY LOGS TO 'mysql-bin.0000xx' ;"

Attention: Cela peut diminuer les chances de réussite de la récupération des données lors de la réparation des tables de la base de données (c'est-à-dire en cas de corruption de la base de données).

OpenRC ne parvient pas à lancer MySQL

Pour utiliser MySQL avec OpenRC, vous devez ajouter les lignes suivantes à la section [mysqld] du fichier de configuration MySQL, situé à /etc/my.cnf.d/my.cnf.

user = mysql
basedir = /usr
datadir = /var/lib/mysql
pid-file = /run/mysqld/mysql.pid

Vous devrez maintenant être capable de démarrer MySQL en utilisant :

# rc-service mysql start

La clé spécifiée était trop longue

Consultez #Augmenter la limite de caractères.

Changement des limites d'avertissement sur max_open_files/table_open_cache

Augmentez le nombre de descripteurs de fichiers en créant un fichiers de substitution de systemd, par ex :

/etc/systemd/system/mysqld.service.d/limit_nofile.conf

[Service]
LimitNOFILE=8192

Crash de la mise à jour de 10.4 vers 10.5 : "InnoDB: Upgrade after a crash is not supported. The redo log was created with MariaDB 10.4.x"

Avant MariaDB 10.5, le redo log était inutilement divisé en plusieurs fichiers. [7]

Déplacez les anciens journaux binaires /var/lib/mysql/ib_logfile* hors du chemin, laissant ainsi MariaDB 10.5 en créer de nouveaux. Puis redémarrez mariadb.service et mettez à jour vos tables avec mysql_upgrade.

La table 'mysql.xxx' n'existe pas dans le moteur

Symptôme : Lorsque vous exécutez mysql_upgrade ou mysqlcheck, il retourne une ou plusieurs erreurs comme celles-ci :

Table 'mysql.xxx' doesn't exist in engine

Où "xxx" est généralement la table système de la base de données mysql.

Etapes à suivre pour résoudre ce problème,

1. Créez un répertoire de sauvegarde en dehors de MariaDB ${DATADIR}</nowiki>, par exemple dans $HOME/mysql_backup.
2. Copiez les fichiers incriminés de ${DATADIR}/mysql/xxx.{frm,ibd}</nowiki> dans le répertoire de sauvegarde. Le fichier xxx.ibd peut ne pas exister.
3. Supprimez les fichiers incriminés (.frm, .ibd) de ${DATADIR}/mysql/.
4. Exécutez le mysqlcheck. En cas de succès, les fichiers xxx.frm et xxx.ibd devraient être créés à nouveau.
5. Ré-exécutez mysql_upgrade si nécessaire.

Voir aussi

• Site officiel de MariaDB
• Base de connaissances MariaDB
• Scripts et connaissances pour l'optimisation des performances de MySQL (en anglais)