Nous savons tous que les sauvegardes doivent être programmées périodiquement. Les sauvegardes complètes, celles qui prélèvent toutes les données des bases, peuvent être réalisées avec plusieurs outils MySQL. Par exemple, InnoDB Hot Backup fournit un utilitaire de sauvegarde en ligne et non bloquant pour les fichiers InnoDB, et mysqldump fournit un outil de sauvegarde logique. Cette section utilise mysqldump.

Supposons que nous souhaitons réaliser une sauvegarde le dimanche, à une heure du matin, lorsque la charge sur le serveur est au plus bas. La commande suivante va faire une sauvegarde de toutes nos tables InnoDB, dans toutes les bases :

shell> mysqldump --single-transaction --all-databases > backup_sunday_1_PM.sql

C'est un outil de sauvegarde en ligne, non-bloquant, qui ne perturbe pas les opérations sur les tables. Nous avons supposé plus haut que nos tables utilisent le moteur InnoDB : l'option --single-transaction utilise une lecture cohérente, et garantit la stabilité des données prélevées par mysqldump. Les modifications peuvent être faîtes par d'autres clients sur les tables InnoDB sans que la commande mysqldump ne le per¸oive. Si nous avons d'autres types de tables, nous devons aussi supposer qu'elles ne changeront pas durant la sauvegarde. Par exemple, pour une table MyISAM dans la base mysql, nous devons supposer qu'aucun administrateur ne fera de modification aux comptes MySQL durant la sauvegarde.

Le fichier .sql résultant, produit par mysqldump contient les commandes SQL INSERT qui peuvent être utilisées pour recharger les tables ultérieurement.

Les sauvegardes complètes sont nécessaires, mais elles ne sont pas toujours pratiques. Elles produisent de très grands fichiers de données, et prennent du temps à s'exécuter. Elles ne sont pas optimales, car chaque sauvegarde inclut toutes les données, même celles qui n'ont pas évolué entre deux sauvegardes. Une fois qu'une sauvegarde initiale a été faite, les sauvegardes incrémentales sont bien plus optimales : elles génèrent des fichiers plus petits, et sont plus rapides à réaliser. L'inconvénient est que cette sauvegarde ne vous permettra pas de restaurer toutes vos données à partir de la sauvegarde complète : il vous faudra utiliser les sauvegardes incrémentales pour restaurer totalement votre base.

Pour réaliser des sauvegardes incrémentales, vous devez sauver les modifications incrémentales. Le serveur MySQL doit être lancé avec l'option --log-bin pour qu'il puisse stocker ces modifications au fur et à mesure des modifications des données. Cette option active le log binaire, ce qui fait que chaque commande qui modifie les données est enregistré dans un fichier appelé le log binaire. Voyons le dossier de données de MySQL, une fois qu'il a été lancé avec l'option --log-bin. Nous y trouverons les fichiers suivants :

-rw-rw---- 1 guilhem  guilhem   1277324 Nov 10 23:59 gbichot2-bin.000001
-rw-rw---- 1 guilhem  guilhem         4 Nov 10 23:59 gbichot2-bin.000002
-rw-rw---- 1 guilhem  guilhem        79 Nov 11 11:06 gbichot2-bin.000003
-rw-rw---- 1 guilhem  guilhem       508 Nov 11 11:08 gbichot2-bin.000004
-rw-rw---- 1 guilhem  guilhem 220047446 Nov 12 16:47 gbichot2-bin.000005
-rw-rw---- 1 guilhem  guilhem    998412 Nov 14 10:08 gbichot2-bin.000006
-rw-rw---- 1 guilhem  guilhem       361 Nov 14 10:07 gbichot2-bin.index

A chaque fois que le serveur redémarre, MySQL crée un nouveau fichier de log binaires, en utilisant le numéro de séquence suivant. Lorsque le serveur fonctionne, vous pouvez aussi lui dire de clore le fichier de log, et d'en ouvrir un nouveau avec la commande SQL FLUSH LOGS ou bien avec la commande en ligne mysqladmin flush-logs. La commande mysqldump dispose aussi d'une option pour clore les fichiers de logs. Le fichier .index contient la liste de tous les fichiers de logs binaire du dossier de données. Ce fichier est utilisé durant les opérations de réplication.

Les fichiers de log binaires MySQL sont importants lors de restauration, car ils représentent des sauvegardes incrémentales. Si vous vous assurez de bien refermer les fichiers de log binaire lorsque vous réalisez une sauvegarde complète, alors les fichiers de log binaires qui ont été créés après votre sauvegarde représente les modifications incrémentales de vos données. Maintenant, modifions la commande mysqldump pour qu'elle referme les logs binaires lors de sauvegarde complète, et que le fichier de sauvegarde contienne les noms des nouveaux fichiers de logs :

shell> mysqldump --single-transaction --flush-logs --master-data=2
           --all-databases > backup_sunday_1_PM.sql

Après avoir exécuté cette commande, le dossier de données contient un nouveau fichier de log binaire, gbichot2-bin.000007. Le fichier .sql résultant contient les lignes suivantes :

-- Position to start replication or point-in-time recovery from
-- CHANGE MASTER TO MASTER_LOG_FILE='gbichot2-bin.000007',MASTER_LOG_POS=4;

Comme la commande mysqldump a fait une sauvegarde complète, ces lignes signifie deux choses :

Le lundi, à une heure du matin, nous pouvons créer une sauvegarde incrémentale en refermant les fichiers de log binaire, et en créant un nouveau fichier de log. Par exemple, la commande mysqladmin flush-logs crée un fichier gbichot2-bin.000008. Toutes les modifications qui ont eu lieu entre dimanche, 1 heure et lundi, 1 heure sont stockées dans le fichier gbichot2-bin.000007. Cette sauvegarde incrémentale est importante, et il est recommandé de la stocker dans un endroit sûr. Par exemple, copiez la sur une cassette ou un DVD, ou même sur une autre machine. Le mardi, à 1 heure, vous pouvez exécuter à nouveau la commande mysqladmin flush-logs. Toutes les opérations effectuées entre lundi, 1 heure et mardi, 1 heure sont dans le fichier gbichot2-bin.000008, qui doit être mis en sécurité.

Les logs binaires MySQL occupent de l'espace disque sur le serveur. Pour récupérer cet espace, supprimez-le de temps en temps. Pour le faire en toute sécurité, supprimez simplement les fichiers qui ne servent plus à rien, c'est à dire ceux qui sont antérieurs à la dernière sauvegarde complète :

shell> mysqldump --single-transaction --flush-logs --master-data=2
           --all-databases --delete-master-logs > backup_sunday_1_PM.sql

Note : effacer les logs binaires avec la commande mysqldump --delete-master-logs peut être dangereux, car si le serveur est un maître de réplication, les esclaves pourraient ne pas avoir traités en totalité le contenu des logs binaires.

La description de la commande PURGE MASTER LOGS explique ce qui doit être vérifié avant d'effacer un fichier de log binaire. See Section 13.6.1.1, « PURGE MASTER LOGS ».