Sauvegarde logique des bases de données MariaDB

Contrairement à une idée reçue et véhiculée par certains DBA, les sauvegardes logiques à chaud sous MariaDB / MySQL peuvent être consistantes à la condition que vos bases de données utilisent le moteur InnoDB ou Aria. Elles restent le moyen le plus simple de disposer d’une copie de vos données, faciles à restaurer. Pour faire un DUMP des bases de données d’une instance MariaDB, vous disposez de la commande mariadb-dump que vous pouvez exécuter aussi avec l’alias mysqldump.

Sauvegarde logique des bases de données MariaDB

Création de l’utilisateur backup

Sous l’interpréteur mariadb (mysql) de votre serveur ou à l’aide de HeidiSQL sur une station de travail exécutant Windows, vous créerez tout d’abord l’utilisateur backup à l’aide des commandes SQL suivantes :

CREATE USER 'backup'@'localhost' IDENTIFIED BY '';
GRANT EVENT, LOCK TABLES, READ_ONLY ADMIN, RELOAD, SELECT, SHOW DATABASES, SHOW VIEW ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;
GRANT USAGE ON *.* TO 'backup'@'localhost' WITH MAX_USER_CONNECTIONS 1;
FLUSH PRIVILEGES;

Les options par défaut de la commande mariadb-dump

Les options de cette commande sont fort nombreuses. Pour disposer des options par défaut de la version 10.5.8 de la commande mariadb-dump, tapez à partir de la ligne de commandes mariadb-dump –help :

all-databases                     FALSE
all-tablespaces                   FALSE
no-tablespaces                    FALSE
add-drop-database                 FALSE
add-drop-table                    TRUE
add-drop-trigger                  FALSE
add-locks                         TRUE
allow-keywords                    FALSE
apply-slave-statements            FALSE
character-sets-dir                (No default value)
comments                          TRUE
compatible                        (No default value)
compact                           FALSE
complete-insert                   FALSE
compress                          FALSE
create-options                    TRUE
databases                         FALSE
debug-check                       FALSE
debug-info                        FALSE
default-character-set             utf8mb4
delayed-insert                    FALSE
delete-master-logs                FALSE
disable-keys                      TRUE
dump-slave                        0
events                            FALSE
extended-insert                   TRUE
fields-terminated-by              (No default value)
fields-enclosed-by                (No default value)
fields-optionally-enclosed-by     (No default value)
fields-escaped-by                 (No default value)
flush-logs                        FALSE
flush-privileges                  FALSE
force                             FALSE
gtid                              FALSE
hex-blob                          FALSE
host                              (No default value)
include-master-host-port          FALSE
insert-ignore                     FALSE
lines-terminated-by               (No default value)
lock-all-tables                   FALSE
lock-tables                       TRUE
log-error                         (No default value)
log-queries                       TRUE
master-data                       0
max-allowed-packet                25165824
max-statement-time                0
net-buffer-length                 1046528
no-autocommit                     FALSE
no-create-db                      FALSE
no-create-info                    FALSE
no-data                           FALSE
no-data-med                       TRUE
order-by-primary                  FALSE
port                              0
quick                             TRUE
quote-names                       TRUE
replace                           FALSE
routines                          FALSE
set-charset                       TRUE
single-transaction                FALSE
dump-date                         TRUE
socket                            (No default value)
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-crl                           (No default value)
ssl-crlpath                       (No default value)
tls-version                       (No default value)
ssl-verify-server-cert            FALSE
system
tab                               (No default value)
triggers                          TRUE
tz-utc                            TRUE
user                              (No default value)
verbose                           FALSE
where                             (No default value)
plugin-dir                        (No default value)
default-auth                      (No default value)

Les options choisies

Pour les sauvegardes de mes bases de données MariaDB, voici les options que j’utilise :

--add-drop-database Add a DROP DATABASE before each create.
-B, --databases     Dump several databases. Note the difference in usage; in
-E, --events        Dump events.
-F, --flush-logs    Flush logs file in server before starting dump. Note that
                    if you dump many databases at once (using the option
                    --databases= or --all-databases), the logs will be
                    flushed for each database dumped. The exception is when
                    using --lock-all-tables or --master-data: in this case
                    the logs will be flushed only once, corresponding to the
                    moment all tables are locked. So if you want your dump
                    and the log flush to happen at the same exact moment you
                    should use --lock-all-tables or --master-data with
                    --flush-logs.
--flush-privileges  Emit a FLUSH PRIVILEGES statement after dumping the mysql
                    database.  This option should be used any time the dump
                    contains the mysql database and any other database that
                    depends on the data in the mysql database for proper
                    restore.
-h, --host=name     Connect to host.
--max-allowed-packet=#
                    The maximum packet length to send to or receive from
                    server.
-R, --routines      Dump stored routines (functions and procedures).
--single-transaction
                    Creates a consistent snapshot by dumping all tables in a
                    single transaction. Works ONLY for tables stored in
                    storage engines which support multiversioning (currently
                    only InnoDB does); the dump is NOT guaranteed to be
                    consistent for other storage engines. While a
                    --single-transaction dump is in process, to ensure a
                    valid dump file (correct table contents and binary log
                    position), no other connection should use the following
                    statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
                    TRUNCATE TABLE, as consistent snapshot is not isolated
                    from them. Option automatically turns off --lock-tables.
-u, --user=name     User for login if not current user.

Pour la table mysql.global_priv qui contient les utilisateurs et leurs droits associés, j’ai utilisé les options –tables  et –replace :

--replace           Use REPLACE INTO statements instead of INSERT INTO statements.
--tables            Overrides option --databases (-B).

Le script Bash de sauvegarde

J’ai réalisé un script en Bash qui permet de sauvegarder dans le dossier /home/sauve, tous les jours de la semaine, mes différentes bases de données ainsi que la table mysql.global_priv. Il n’y a aucun intérêt à sauvegarder l’intégralité de la base système mysql.

#!/bin/bash
DJ=$(date +%a|cut -d '.' -f 1)
for DB in dsfc extime dba sedf tools phplist
do
    mariadb-dump --add-drop-database -B $DB -E -F --flush-privileges -h localhost --max-allowed-packet=1073741824 -R --single-transaction -u backup |gzip > /home/sauve/$DB.$DJ.sql.gz
done
mariadb-dump -B mysql -F --flush-privileges -h localhost --max-allowed-packet=1073741824 --replace --single-transaction --tables global_priv -u backup |gzip > /home/sauve/global_priv.$DJ.sql.gz
exit 0

Il ne vous restera plus qu’à le planifier à l’aide de la commande crontab -e.

MariaDB /