Bienvenue > Blog sur la sauvegarde et la restauration > Comment sauvegarder une base de données MySQL ? Guide de la sauvegarde de la base de données MySQL.

Comment sauvegarder une base de données MySQL ? Guide de la sauvegarde de la base de données MySQL.

(8 votes, moyenne: 4,88 de 5)

Mis à jour 14th juillet 2021, Rob Morrison

Contents

Les différentes manières de créer une sauvegarde de la base de données MySQL
L’utilité du module de sauvegarde MySQL avec Bacula Enterprise
Binaire ou dump ?
Comment ça marche : Binaire
Comment ça marche : Dump
Modes de restauration MySQL
Conclusion

Le besoin de sauvegardes des bases de données MySQL n’est pas aussi universel que le besoin de sauvegardes régulières des utilisateurs finaux, mais lorsqu’il est nécessaire, il devient une partie particulièrement importante de l’infrastructure d’une entreprise. Le module de Bacula Enterprise pour les sauvegardes MySQL permet aux utilisateurs de réaliser des sauvegardes de bases de données MySQL efficaces – et plus important encore, des restaurations – en un temps très court. Ceci est bien sûr essentiel pour atteindre les objectifs de temps de restauration (RTO). Cet article approfondit la nature même du processus de sauvegarde qui rend Bacula Enterprise tellement meilleur que ses concurrents, y compris les différentes approches de la sauvegarde et de la restauration, et les différentes options de personnalisation.

Les différentes manières de créer une sauvegarde de la base de données MySQL

Avant de passer à la solution Bacula Enterprise, passons en revue les différentes manières, moins conventionnelles, de créer une sauvegarde de la base de données MySQL.

1. Sauvegarde automatique de la base de données MySQL sous Linux

Un certain utilitaire basé sur Linux/Unix, appelé cron, peut effectuer des sauvegardes MySQL sur ces systèmes. Il s’agit d’un utilitaire relativement simple qui démarre avec le système et lit ses instructions dans un fichier spécifique. Ce fichier de configuration est généralement stocké dans le répertoire /etc/crontab. Voici un exemple de sauvegarde quotidienne fixée à 2 h 30 du matin :

30 2 * * * root mysqldump -u root -pPASSWORD –all-databases | gzip >
/mnt/disk2/database_`data ‘ %m-%d-%Y’`.sql.gz

Les cinq premiers chiffres de la commande représentent l’heure et la date (minutes, heures, jours du mois, mois, jours de la semaine, respectivement), et le fait de mettre « * » à la place de l’un de ces chiffres signifie que n’importe quel chiffre fonctionne.

2. Utiliser PHP pour créer un fichier XML

Il est également possible de recréer votre base de données sous forme de fichier XML, à l’aide de PHP. Ce n’est pas le format le plus sûr au monde lorsqu’il s’agit de restaurer vos données, mais certaines personnes le préfèrent à tout autre. Le bout de code ci-dessous devrait vous permettre d’exporter une base de données spécifique dans un fichier au format .XML :

//connect
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);

//get all the tables
$query = ‘SHOW TABLES FROM ‘.$name;
$result = mysql_query($query,$link) or die(‘cannot show tables’);
if(mysql_num_rows($result))
{
            //prep output
            $tab = « \t »;
            $br = « \n »;
            $xml = ‘<?xml version= »1.0″ encoding= »UTF-8″?>’.$br;
            $xml.= ‘<database name= »‘.$name.' »>’.$br;

            //for every table…
            while($table = mysql_fetch_row($result))
            {
                        //prep table out
                        $xml.= $tab.'<table name= »‘.$table[0].' »>’.$br;

                        //get the rows
                        $query3 = ‘SELECT * FROM ‘.$table[0];
                        $records = mysql_query($query3,$link) or die(‘cannot select from table: ‘.$table[0]);

                        //table attributes
                        $attributes = array(‘name’,’blob’,’maxlength’,’multiple_key’,’not_null’,’numeric’,’primary_key’,’table’,’type’,’default’,’unique_key’,’unsigned’,’zerofill’);
                        $xml.= $tab.$tab.'<columns>’.$br;
                        $x = 0;
                        while($x < mysql_num_fields($records))
                        {
                                    $meta = mysql_fetch_field($records,$x);
                                    $xml.= $tab.$tab.$tab.'<column ‘;
                                    foreach($attributes as $attribute)
                                    {
                                               $xml.= $attribute.’= »‘.$meta->$attribute.' » ‘;
                                    }
                                    $xml.= ‘/>’.$br;
                                    $x++;
                        }
                        $xml.= $tab.$tab.'</columns>’.$br;

                        //stick the records
                        $xml.= $tab.$tab.'<records>’.$br;
                        while($record = mysql_fetch_assoc($records))
                        {
                                    $xml.= $tab.$tab.$tab.'<record>’.$br;
                                    foreach($record as $key=>$value)
                                    {
                                               $xml.= $tab.$tab.$tab.$tab.'<‘.$key.’>’.htmlspecialchars(stripslashes($value)).'</’.$key.’>’.$br;
                                    }
                                    $xml.= $tab.$tab.$tab.'</record>’.$br;
                        }
                        $xml.= $tab.$tab.'</records>’.$br;
                        $xml.= $tab.'</table>’.$br;
            }
            $xml.= ‘</database>’;

            //save file
            $handle = fopen($name.’-backup-‘.time().’.xml’,’w+’);
            fwrite($handle,$xml);
            fclose($handle);
}

3. Utiliser PHP comme méthode de sauvegarde en premier lieu

Une autre façon d’utiliser PHP pour vos besoins de sauvegarde est de prendre une requête de sauvegarde de base de données dans un fichier PHP et de l’utiliser. La requête dans l’exemple suivant est SELECT INTO OUTFILE.

<?php
include ‘config.php’;
include ‘opendb.php’;

$tableName = ‘mypet’;
$backupFile = ‘backup/mypet.sql’;
$query = « SELECT * INTO OUTFILE ‘$backupFile’ FROM $tableName »;
$result = mysql_query($query);

include ‘closedb.php’;
?>

Et bien sûr, vous aurez besoin d’un moyen de restaurer ce type de sauvegarde, aussi bien. Voici la requête pour cela, c’est une LOAD DATA INFILE :

<?php
include ‘config.php’;
include ‘opendb.php’;

$tableName = ‘mypet’;
$backupFile = ‘mypet.sql’;
$query = « LOAD DATA INFILE ‘backupFile’ INTO TABLE $tableName »;
$result = mysql_query($query);

include ‘closedb.php’;
?>

4. Sauvegarde par SSH

La sauvegarde via SSH est réalisable même pour une (des) base(s) de données MySQL particulièrement importante(s). Dans votre panneau Plesk, commencez par activer l’accès au shell, puis utilisez tout outil permettant d’accéder au serveur en question via SSH (PuTTY ou un outil similaire). Mais avant tout, vous devez spécifier un répertoire cible pour votre future sauvegarde :

CD wwwroot/dbbackup

Ensuite, vous utiliserez mysqldump pour exporter votre base de données dans un fichier spécifique au sein d’un serveur :

mysqldump –add-drop-table -u db_username -p db_name > mybackup.sql

5. Script Server Shell pour sauvegarder une base de données MySQL via Ubuntu

Si vous avez un serveur VPS dédié avec Ubuntu Linux à bord, vous avez une option supplémentaire. Vous pouvez utiliser un script shell spécifique pour sauvegarder toutes vos bases de données sur votre serveur FTP. Voici un exemple :

#!/bin/bash
### MySQL Server Login Info ###
MUSER= »root »
MPASS= »MYSQL-ROOT-PASSWORD »
MHOST= »localhost »
MYSQL= »$(which mysql) »
MYSQLDUMP= »$(which mysqldump) »
BAK= »/backup/mysql »
GZIP= »$(which gzip) »
### FTP SERVER Login info ###
FTPU= »FTP-SERVER-USER-NAME »
FTPP= »FTP-SERVER-PASSWORD »
FTPS= »FTP-SERVER-IP-ADDRESS »
NOW=$(date + »%d-%m-%Y »)

### See comments below ###
### [ ! -d $BAK ] && mkdir -p $BAK || /bin/rm -f $BAK/* ###
[ ! -d « $BAK » ] && mkdir -p « $BAK »

DBS= »$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse ‘show databases’) »
for db in $DBS
do
FILE=$BAK/$db.$NOW-$(date + »%T »).gz
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done

lftp -u $FTPU,$FTPP -e « mkdir /mysql/$NOW;cd /mysql/$NOW; mput /backup/mysql/*; quit » $FTPS

Après avoir enregistré ce segment en tant que fichier .sh, l’étape suivante consiste à définir le niveau de permission correct pour ce fichier spécifique :

$ chmod +x /home/your-name/mysql.backup.sh

À partir de ce moment, vous pouvez saisir l’une des deux commandes pour lancer votre processus de sauvegarde :

/home/your-name/mysql.backup.sh

Ou :

sudo /home/your-name/mysql.backup.sh

Vous pouvez également configurer ce script pour qu’il soit exécuté comme une tâche cron. Voici un exemple de script exécuté quotidiennement à minuit, il consiste en deux invites de commande différentes :

$ sudo crontab -e

Et :

@midnight /home/you/mysql.backup.sh >/dev/null 2>&1

Il est également possible que ces scripts fonctionnent avec différentes distributions UNIX ou Linux.

6. Utiliser phpMyAdmin

phpMyAdmin est un outil open source, également gratuit, qui fonctionne avec les bases de données MySQL et MariaDB. L’ordre correct des entrées est le suivant :

Ouvrez l’outil
Sélectionnez votre base de données dont vous voulez créer une sauvegarde
Choisissez l’option « export » dans la barre de navigation
Cliquez sur la ligne « afficher toutes les options possibles »
Choisissez les tables spécifiques que vous voulez voir sauvegardées
Changez le type de compression (ou laissez-le à gzipped, il est recommandé de le faire)
Lancez la sauvegarde en cliquant sur le bouton « go »

Ceci conclut notre liste des options de sauvegarde les plus populaires pour vos besoins de sauvegarde de base de données MySQL.

L’utilité du module de sauvegarde MySQL avec Bacula Enterprise

L’objectif général du module de sauvegarde MySQL de Bacula Enterprise est de rendre les processus de sauvegarde et de restauration plus faciles et plus rapides à la fois. En utilisant ce module, l’administrateur de la sauvegarde n’a pas besoin de connaître tous les tenants et aboutissants des techniques de sauvegarde MySQL et il n’est pas non plus nécessaire de savoir écrire des scripts. Le module lui-même est capable d’exécuter une variété de fonctions différentes, y compris la sauvegarde automatique des fichiers de configuration et d’autres données importantes du système. Il existe deux méthodes de sauvegarde principales : le dump et le binaire.

Binaire ou dump ?

Comme il existe deux principales techniques de sauvegarde, il est préférable de présenter une comparaison directe de certaines de leurs caractéristiques dans le tableau ci-dessous.

Capacités	Binaire	Dump
Restauration d’un seul objet	Non	Oui*
Taille de la sauvegarde	Grosse	Petite
Vitesse de sauvegarde	Rapide	Lente
Vitesse de restauration	Rapide	Très Lente
Capacité de restauration à n’importe quel moment	Oui	Oui
Prise en charge des types de sauvegarde différentielle et incrémentielle	Oui	Oui
Cohérence	Oui	Oui
Sauvegarde en ligne	Oui	Oui
Restauration de la version majeure précédente de MySQL	Non	Oui**
Restauration vers une version majeure plus récente de MySQL	Non	Oui

* L’édition manuelle d’un fichier dump est nécessaire pour effectuer une restauration d’objet unique.

** L’édition manuelle du fichier SQL peut être nécessaire pour restaurer un dump SQL vers une ancienne version de MySQL si vous utilisez des fonctionnalités qui ne sont pas disponibles sur l’ancienne version. De manière générale, la restauration vers une ancienne version de MySQL n’est pas garantie et n’est pas prise en charge.

Comment ça marche : Binaire

Le principal principe de fonctionnement du mode binaire est son interaction avec Percona XtraBackup qui lui permet d’effectuer le processus de sauvegarde sans l’arrêter au départ. Il est capable de travailler avec InnoDB, XtraDB, MyISAM et d’autres tables avec MySQL non modifié, ou avec un serveur Percona avec XtraDB.

Auparavant, il y avait deux façons principales d’effectuer une copie de base de données. La première impliquait de copier la base de données active, et cette méthode avait une chance assez élevée d’avoir toutes sortes de divergences dans la copie, ou pouvait simplement manquer tous les changements qui se produisaient dans la base de données pendant la durée de la création de la copie. La deuxième méthode implique l’arrêt complet de la base de données pour faire une copie cohérente. Mais le problème avec la seconde méthode est que, potentiellement (et généralement), chaque seconde de non-fonctionnement de la base de données d’une entreprise représente une perte de revenus, quelle que soit la taille de la période d’arrêt.

C’est précisément dans ce but que le module MySQL Percona de Bacula et l’outil Percona XtraBackup offrent une solution efficace à ce problème en permettant de créer des sauvegardes complètes ou incrémentielles de tout type de bases de données MySQL actives et en fonctionnement. Un autre avantage du mode connecté à Percona est qu’il n’a pas besoin d’utiliser de gros fichiers temporaires qui sont les doublons des données que vous sauvegardez ou restaurez.

Le processus de restauration est relativement simple, les données étant restaurées dans un emplacement temporaire, puis modifiées pour être cohérentes à l’aide de la fonction « prepare ». Avec d’autres solutions, cette fonction serait très gourmande en ressources, et ce n’est pas quelque chose que vous voudriez exécuter dans une situation de restauration urgente. Par conséquent, pour éviter d’ajouter beaucoup de temps au processus de restauration, la fonction de « préparation » de Bacula est exécutée automatiquement et à l’avance, juste après la création de la sauvegarde. Cela ajoute un peu de temps au processus de sauvegarde mais rend le processus de restauration beaucoup plus rapide. En cas d’urgence, le temps de restauration est bien plus critique que le temps de sauvegarde. Et dans une situation où la base de données en question est une base de données extrêmement importante et à fort taux de transaction, cette différence de temps peut même faire la différence entre la capacité d’une organisation à rester en activité.

La nécessité de cette combinaison du module Bacula Enterprise MySQL Percona et des outils Percona vient de l’existence problématique du MySQL traditionnel. Le principal problème de la création d’un « mysqldump » est qu’il ne s’agit pas d’une véritable sauvegarde binaire des données, l’ensemble du processus consiste à convertir la base de données entière en une grande quantité d’instructions SQL et à les traiter ensuite dans le cadre du processus de restauration. Ce processus exact, tel qu’il est, devient extrêmement lent au fur et à mesure que votre base de données est plus grande.

Les outils Percona s’avèrent utiles lorsque la situation impose une lourde charge à MySQL en général et que les performances et la stabilité sont nécessaires au bon fonctionnement du système.

Toute entreprise qui a besoin de restaurer son serveur défaillant rapidement et en toute sécurité pour protéger son activité d’une défaillance grave, voire d’un black-out, peut profiter de cette combinaison spéciale d’outils Percona avec les capacités de Bacula pour rendre le processus de restauration aussi rapide que possible dans un environnement d’entreprise.

Comment ça marche : Dump

Le mode Dump est plus ou moins l’option de sauvegarde « traditionnelle » qui reste pertinente dans certains cas. Il s’appuie sur les fichiers journaux créés au cours du processus d’existence de la base de données MySQL, et ces journaux sont utilisés par le système de récupération à un moment donné pour réaliser diverses prouesses avec vos données, comme la réplication, la protection, etc.

La méthode de sauvegarde par défaut consiste à vider chacune de vos bases de données séparément, ce qui signifie que vos données ne seront pas complètement cohérentes si vous restaurez plusieurs bases de données qui ont été sauvegardées à des périodes différentes et qui n’ont aucune cohérence entre elles. C’est un problème important si vous travaillez avec plusieurs bases de données à la fois, et la solution de Bacula est d’enregistrer les fichiers journaux générés pendant chacun des processus de sauvegarde et de les lire pour s’assurer que toutes les bases de données sont globalement cohérentes entre elles à un moment donné.

Il existe également une autre solution, plus globale, à ce problème – l’option all_databases. Elle permet à toutes les bases de données d’être vidées en une seule fois, ce qui vous assure que vos bases de données sont cohérentes et que vous pouvez effectuer plusieurs sauvegardes incrémentielles après la sauvegarde complète sans crainte d’incohérence globale.

Il y a une liste spécifique de fichiers que l’agent MySQL génère dans le catalogue Bacula pour un seul « test » de votre base de données. Vous pouvez voir ces entrées de fichiers et ce à quoi elles servent dans le tableau ci-dessous.

Nom de fichier	Dispositif	Description
global-grants.sql	global	Liste unifiée qui spécifie tous les utilisateurs, leurs mots de passe et autres données spécifiques
createdb.sql	database	Script de création de la base de données
grants.sql	database	Une liste qui spécifie chaque utilisateur qui est associé à cette base de données
data.sql	database	Les données de votre base de données en format dump
settings.txt	global	Les variables actuelles du serveur MySQL
schema.sql	database	Script sur la création du schéma de la base de données
my.cnf	global	Données de configuration du serveur pour MySQL

Modes de restauration MySQL

L’agent MySQL de Bacula est capable de fournir plusieurs modes de restauration différents, tels que :

Restauration d’une seule base de données
Restauration d’un dump ou d’un journal binaire
Restauration ponctuelle
Restauration des utilisateurs et des rôles

Comme mentionné, Bacula Enterprise utilise les outils Percona avec son agent MySQL pour restaurer toute information sur une sauvegarde en mode binaire.

Conclusion

Les nouvelles technologies sont constamment développées dans tous les environnements informatiques. C’est pourquoi la plupart des entreprises bénéficient d’une solution de sauvegarde qui innove régulièrement, et s’assure qu’elle peut couvrir toutes sortes de technologies dans un département informatique ou un centre de données – que ces technologies soient nouvelles ou anciennes. Le résultat de l’utilisation d’une solution de sauvegarde moderne et innovante est généralement un RTO et un RPO plus courts, une maintenance réduite, des coûts moindres et une sécurité beaucoup plus élevée. Bacula Enterprise est un bon exemple d’un tel fournisseur de solutions de sauvegarde.

À propos de l’auteur

Rob Morrison est le directeur marketing de Bacula Systems. Il a commencé sa carrière dans le marketing informatique chez Silicon Graphics en Suisse, où il a obtenu de bons résultats dans divers rôles de gestion du marketing pendant près de 10 ans. Au cours des 10 années suivantes, Rob a également occupé divers postes de gestion du marketing chez JBoss, Red Hat et Pentaho, assurant la croissance des parts de marché de ces sociétés bien connues. Il est diplômé de l'université de Plymouth, titulaire d'un diplôme spécialisé en médias et communications numériques, et a suivi un programme d'études à l'étranger.

Vous pouvez également être intéressé par :

logiciel sauvegarde windows

sauvegarde vmware

proteger ransomware