En marge de mon article sur les backups à froid avec RMAN, je vous propose un petit point sur la sauvegarde à froid avec MySQL.
En effet, la sauvegarde à froid n’est plus forcement la plus utilisée de nos jours mais elle s’avère parfois la solution la plus simple à mettre en oeuvre.
Il faut simplement avoir toutes les clés en main pour assurer une restauration sans stress.

La sauvegarde à froid, qu’est-ce que c’est ?

Quand je parle de sauvegarde à froid, je parle évidemment de sauvegarde des fichiers physiques de vos bases de données.
Une sauvegarde à froid consiste donc à sauvegarder tous les fichiers vitaux des bases de données alors que l’instance MySQL est arrêtée ou que les écritures sont gelées.

Les fichiers à sauvegarder dépendent du ou des moteurs de stockage utilisés :

• MyISAM : sauvegarder tous les fichiers .FRM, .MYI et .MYD
• InnoDB : sauvegarder tous les fichiers .FRM, les fichiers de données (ibdata), les fichiers des tables .ibd (si vous êtes en innodb_file_per_table) et les fichiers journaux (iblogfile)

Avec MyISAM, si vous avez utilisé les clauses DATA DIRECTORY ou INDEX DIRECTORY dans les instructions de création de vos tables, vous vous retrouvez avec des liens symboliques dans le répertoire datadir.
Attention, donc, à ne pas sauvegarder les liens à la place des fichiers de données !

La sauvegarde à froid peut également être mise en oeuvre sur un esclave de réplication afin de ne pas perturber votre production.

Les avantages et inconvénients

Rapidement, quelques avantages et inconvénients de la sauvegarde à froid :
(qui peuvent s’appliquer pour certains à la sauvegarde physique à chaud)

• (+) Restauration plus rapide qu’avec un backup via mysqldump
• (+) Adaptée aux grosses volumétries
• (+) Restauration simplifiée
• (-) Pas d’outil fourni par MySQL
• (-) L’arrêt de l’instance signifie la perte de tous les caches de base de données
• (-) Moins portable qu’un dump
• (-) Prise en charge des logs binaires plus complexe

La méthode, avec ou sans logs binaires

La méthode dépend effectivement de l’utilisation ou non des logs binaires dans le processus de sauvegarde.
En effet, si vous souhaitez utiliser les logs binaires comme sauvegarde incrémentale, il sera nécessaire de le prendre en compte dans votre méthode de sauvegarde à froid.

MySQL ne fourni pas d’outil permettant de réaliser une sauvegarde à froid, il faudra donc se débrouiller avec les moyens du bord.

Méthode 1 : Sans prise en compte des logs binaires

• Arrêter l’instance MySQL et vérifier qu’elle s’est arrêtée sans erreur dans le log d’erreur
• Copier tous les fichiers de données mentionnés plus haut (MyISAM et InnoDB) vers le répertoire de sauvegarde
• Copier les fichiers de journaux InnoDB
• Copier également le fichier de configuration MySQL (my.cnf), ça ne peut pas faire de mail
• N’oubliez pas les fichiers .frm de vos tables
• Relancer l’instance MySQL

Méthode 2 : Prise en compte des logs binaires

• Dans une première session, figer les écritures via la commande suivante : flush tables with read lock;
• Dans cette même session, récupérer les informations du log binaire en cours : show master status;
• Sauvegarder ces informations, elles seront indispensables pour savoir à partir de quel log binaire et de quelle position partir en cas de restauration
• A ce stade, attention à ne pas se déconnecter de cette première session car cela aurait pour effet d’annuler le verrouillage posé à la première étape
• Dans une seconde session, arrêter l’instance MySQL : /etc/init.d/mysql.server stop
• Vérifier qu’elle s’est arrêtée sans erreur dans le log d’erreur
• Copier tous les fichiers de données mentionnés plus haut (MyISAM et InnoDB) vers le répertoire de sauvegarde
• Copier les fichiers de journaux InnoDB
• Copier également le fichier de configuration MySQL (my.cnf), ça ne peut pas faire de mail
• N’oubliez pas les fichiers .frm de vos tables
• Relancer l’instance MySQL

Notez qu’avec cette méthode, vous pouvez également copier les fichiers sans arrêter MySQL puisque les écritures sont figées à la première étape.
Simplement, la plupart des fichiers seront ouverts, ça peut poser des problèmes sur certaines plateformes ou avec certains outils de sauvegarde de fichiers.

Concernant la perte des caches lors de l’arrêt de MySQL, sachez que le moteur xtraDB permet la sauvegarde/restauration du cache de données : http://www.mysqlplus.fr/2010/03/xtradb-sauvegarde-votre-cache-et-ca-marche/

N’hésitez pas à apporter votre propre expérience dans les commentaires.

Aujourd’hui, au hasard d’une consultation de log d’erreur, je suis tombé sur l’erreur suivante :

• [Warning] option ‘innodb-autoextend-increment’: unsigned value 100000000 adjusted to 1000

C’est un piège dans lequel je tombe assez souvent avec MySQL, toutes les variables permettant de définir une taille (cache, taille de fichier, tables temporaires…) ne s’expriment pas avec le même type de valeur !

Pourquoi faire simple lorsqu’on peut faire compliqué !

C’est le cas ici avec la variable innodb_autoextend_increment qui s’exprime directement en MB (de 8 à 1000), il n’est donc pas nécessaire de préciser MB après la valeur sous peine de tomber sur l’erreur précédemment indiquée.

J’en profite donc pour faire un post rapide sur cette variable finalement peut utilisée et donc peut connue.

Cette variable permet de définir le pas d’auto-extension du fichier de données associé au tablespace partagé (ibdata), en MB donc.
Attention, ce paramètre ne s’applique que pour ce fichier, si vous utilisez une configuration de type “fichier par table”, l’extension des fichiers de tables se fera sans tenir compte de ce paramètre.

Pour faire simple, si votre configuration de fichiers de données InnoDB est celle-ci :

• innodb_data_file_path = ibdata1:500M:autoextend:max:2000M
• innodb_file_per_table = 1
• innodb_autoextend_increment = 50

Une fois les 500 premiers mégas de votre fichier partagé (ibdata) remplis, une extension de 50M sera ajoutée au fichier et ainsi de suite…
Il est à noter qu’il n’y a pas de commande permettant de réduire la taille de ce fichier.

Pour les fichiers des tables, ils évolueront par pas de 4M maximum sans se soucier de la valeur du paramètre innodb_autoextend_increment.

J’ai pour habitude de changer la valeur par défaut de ce paramètre qui est de 8M, surtout quand je sais que mon fichier va évoluer fréquemment, cela évite une allocation disque trop fréquente.
N’oubliez pas que ce fichier contient également les segments d’annulation qui peuvent faire grossir rapidement ce fichier en cas de forte activité transactionnelle.

Si vous souhaitez réagir, ce sera avec plaisir et ça se passe dans les commentaires.
Est-ce dans vos habitudes de modifier la valeur de ce paramètre ?