Comment sauvegarder les bases de données MySQL sur un VPS Ubuntu

Statut : Obsolète

Cet article couvre une version d'Ubuntu qui n'est plus prise en charge. Si vous gérez actuellement un serveur exécutant Ubuntu 12.04, nous vous recommandons vivement de mettre à niveau ou de migrer vers une version d'Ubuntu prise en charge :

• Mettez à niveau vers Ubuntu 14.04.
• Mettre à niveau d'Ubuntu 14.04 vers Ubuntu 16.04
• Migrer les données du serveur vers une version prise en charge

Raison : Ubuntu 12.04 a atteint sa fin de vie (EOL) le 28 avril 2017 et ne reçoit plus de correctifs de sécurité ni de mises à jour. Ce guide n'est plus maintenu.

Voir plutôt :
Ce guide peut encore être utile comme référence, mais il peut ne pas fonctionner sur d'autres versions d'Ubuntu. Si disponible, nous recommandons fortement d'utiliser un guide rédigé pour la version d'Ubuntu que vous utilisez. Vous pouvez utiliser la fonction de recherche en haut de la page pour trouver une version plus récente.

Qu'est-ce que MySQL ?

MySQL est une solution de gestion de base de données populaire qui utilise le langage de requête SQL pour accéder aux données et les manipuler. Il peut être facilement utilisé pour gérer les données des sites web ou des applications.

Les sauvegardes sont importantes avec tout type de données, et cela est particulièrement pertinent lorsqu'il s'agit de bases de données. MySQL peut être sauvegardé de plusieurs manières différentes que nous discuterons dans cet article.

Pour ce tutoriel, nous utiliserons un VPS Ubuntu 12.04 avec MySQL 5.5 installé. La plupart des distributions modernes et des versions récentes de MySQL devraient fonctionner de manière similaire.

Comment sauvegarder une base de données MySQL avec mysqldump

L'une des méthodes les plus courantes pour effectuer une sauvegarde avec MySQL est d'utiliser une commande appelée « mysqldump ».

Faire une sauvegarde

Il y a un article sur la façon d'exporter des bases de données en utilisant mysqldump ici. La syntaxe de base de la commande est :

mysqldump -u username -p database_to_backup > backup_name.sql

Restaurer

Pour restaurer une sauvegarde de base de données créée avec mysqldump, il suffit de rediriger le fichier vers MySQL à nouveau.

Nous devons créer une base de données vide pour héberger les données importées. Tout d'abord, connectez-vous à MySQL en tapant :

mysql -u username -p

Créez une nouvelle base de données qui contiendra toutes les données de la sauvegarde et quittez ensuite l'invite MySQL :

CREATE DATABASE database_name;
exit

Ensuite, nous pouvons rediriger le fichier de vidage vers notre base de données nouvellement créée en exécutant la commande suivante :

mysql -u username -p database_name < backup_name.sql

Vos informations devraient maintenant être restaurées dans la base de données que vous avez créée.

Comment sauvegarder une table MySQL dans un fichier texte

Vous pouvez enregistrer les données d'une table directement dans un fichier texte en utilisant la commande select dans MySQL.

La syntaxe générale pour cette opération est :

SELECT * INTO OUTFILE 'table_backup_file' FROM name_of_table;

Cette opération enregistrera les données de la table dans un fichier sur le serveur MySQL. Elle échouera s'il existe déjà un fichier portant le nom choisi.

Remarque : cette option ne sauvegarde que les données du tableau. Si la structure de votre tableau est complexe et doit être conservée, il est préférable d'utiliser une autre méthode !

Comment sauvegarder les informations MySQL en utilisant automysqlbackup

Il existe un programme utilitaire appelé « automysqlbackup » qui est disponible dans les dépôts Ubuntu.

Cet utilitaire peut être programmé pour effectuer automatiquement des sauvegardes à intervalles réguliers.

Pour installer ce programme, tapez ce qui suit dans le terminal :

sudo apt-get install automysqlbackup

Exécutez la commande en tapant :

sudo automysqlbackup

Le fichier de configuration principal pour automysqlbackup se trouve à "/etc/default/automysqlbackup". Ouvrez-le avec des privilèges administratifs :

sudo nano /etc/default/automysqlbackup

Vous pouvez voir que ce fichier, par défaut, attribue de nombreuses variables à partir du fichier MySQL situé à "/etc/mysql/debian.cnf". Cela contient les informations de connexion pour la maintenance

À partir de ce fichier, il lit l'utilisateur, le mot de passe et les bases de données qui doivent être sauvegardées.

L'emplacement par défaut des sauvegardes est "/var/lib/automysqlbackup". Recherchez dans ce répertoire pour voir la structure des sauvegardes :

ls /var/lib/automysqlbackup

daily  monthly weekly

Si nous regardons dans le répertoire quotidien, nous pouvons voir un sous-répertoire pour chaque base de données, à l'intérieur duquel se trouve un dump SQL compressé en gzip issu de l'exécution de la commande :

ls -R /var/lib/automysqlbackup/daily

.:
database_name  information_schema  performance_schema

./database_name:
database_name_2013-08-27_23h30m.Tuesday.sql.gz

./information_schema:
information_schema_2013-08-27_23h30m.Tuesday.sql.gz

./performance_schema:
performance_schema_2013-08-27_23h30m.Tuesday.sql.gz

Ubuntu installe un script cron avec ce programme qui l'exécutera tous les jours. Il organisera les fichiers dans le répertoire approprié.

Comment effectuer une sauvegarde lors de l'utilisation de la réplication

Il est possible d'utiliser la réplication MySQL pour sauvegarder les données avec les techniques ci-dessus.

La réplication est un processus de mise en miroir des données d'un serveur vers un autre serveur (maître-esclave) ou de mise en miroir des modifications apportées à l'un ou l'autre serveur vers l'autre (maître-maître).

Alors que la réplication permet la mise en miroir des données, elle rencontre des difficultés lorsque vous essayez de sauvegarder un point précis dans le temps. Cela s'explique par le fait qu'elle réplique en permanence les changements d'un système dynamique.

Pour éviter ce problème, nous pouvons soit :

• Désactiver la réplication temporairement
• Rendre la machine de sauvegarde en lecture seule temporairement

Désactivation temporaire de la réplication

Vous pouvez désactiver la réplication pour l'esclave temporairement en exécutant :

mysqladmin -u user_name -p stop-slave

Une autre option, qui ne stoppe pas complètement la réplication, mais la met en pause, pour ainsi dire, peut être réalisée en tapant :

mysql -u user_name -p -e 'STOP SLAVE SQL_THREAD;'

Après l'arrêt de la réplication, vous pouvez effectuer une sauvegarde en utilisant l'une des méthodes ci-dessus. Cela vous permet de garder la base de données MySQL principale en ligne pendant que le serveur esclave est sauvegardé.

Lorsque cela est terminé, redémarrez la réplication en tapant :

mysqladmin -u user_name -p start-slave

Rendre la machine de sauvegarde en lecture seule temporairement

Vous pouvez également assurer un ensemble de données cohérent au sein du serveur en rendant les données en lecture seule temporairement.

Vous pouvez effectuer ces étapes sur les systèmes maître ou esclave.

Tout d'abord, connectez-vous à MySQL avec suffisamment de privilèges pour manipuler les données :

mysql -u root -p 

Ensuite, nous pouvons écrire toutes les modifications mises en cache sur le disque et définir le système en lecture seule en tapant :

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

Maintenant, effectuez votre sauvegarde en utilisant mysqldump.

Une fois la sauvegarde terminée, remettez le système dans son état de fonctionnement initial en tapant :

SET GLOBAL read_only = OFF;
UNLOCK TABLES;

Une note sur les techniques qui ne sont plus recommandées

mysqlhotcopy

MySQL inclut un script Perl pour sauvegarder rapidement les bases de données appelé « mysqlhotcopy ». Cet outil peut être utilisé pour sauvegarder rapidement une base de données sur une machine locale, mais il présente des limitations qui nous font éviter de le recommander.

La raison principale pour laquelle nous ne couvrirons pas l'utilisation de mysqlhotcopy ici est qu'il ne fonctionne que pour les données stockées en utilisant les moteurs de stockage « MyISAM » et « Archive ».

La plupart des utilisateurs ne changent pas le moteur de stockage pour leurs bases de données et, à partir de MySQL 5.5, le moteur de stockage par défaut est « InnoDB ». Ce type de base de données ne peut pas être sauvegardé en utilisant mysqlhotcopy.

Une autre limitation de ce script est qu'il ne peut être exécuté que sur la même machine que celle où le stockage de la base de données est conservé. Cela empêche l'exécution de sauvegardes depuis une machine distante, ce qui peut constituer une limitation majeure dans certaines circonstances.

Copie des fichiers de table

Une autre méthode parfois suggérée consiste simplement à copier les fichiers de table dans lesquels MySQL stocke ses données.

Cette approche souffre pour l'une des mêmes raisons que « mysqlhotcopy ».

Bien qu'il soit raisonnable d'utiliser cette technique avec des moteurs de stockage qui stockent leurs données dans des fichiers, InnoDB, le nouveau moteur de stockage par défaut, ne peut pas être sauvegardé de cette manière.

Conclusion

Il existe de nombreuses méthodes différentes pour effectuer des sauvegardes dans MySQL. Toutes ont leurs avantages et leurs faiblesses, mais certaines sont beaucoup plus faciles à mettre en œuvre et plus largement utiles que d'autres.

Le schéma de sauvegarde que vous choisissez de déployer dépendra fortement de vos besoins et ressources individuels, ainsi que de votre environnement de production. Quelle que soit la méthode que vous décidez d'adopter, assurez-vous de valider vos sauvegardes et de vous entraîner à restaurer les données, afin de pouvoir être sûr que le processus fonctionne correctement.