dimanche , 24 septembre 2017

MySQL backup

Nous allons voir aujourd’hui comment sauvegarder une ou plusieurs bases de données MySQL de manière automatisée avec un jeu de sauvegardes tournantes compressés au format GZip et ceci sur un serveur mutualisé (cette méthode fonctionne aussi sur un serveur dédié). Cette méthode ne requiert aucun accès root ou administrateur à votre serveur. Il vous suffit simplement d’avoir les droits en lecture/écriture sur la base de données et dans l’espace de stockage où vous allez enregistrer vos fichiers !

Cet article se décompose en 2 sections :

  1. L’utilisation du script backup.php
    télécharger
  2. Fonctionnement et explication du code source.

1) UTILISATION DU SCRIPT BACKUP.PHP

Pour utiliser le script, il vous suffit d’ouvrir le fichier avec votre éditeur de code préféré et de vous rendre à la fin de la classe BackupMySQL présente dans le fichier backup.php.

La classe BackupMySQL fonctionne de la manière suivante : une instance de la classe déclenchera automatiquement une sauvegarde ! C’est aussi simple que cela…

Exemple :

new BackupMySQL(array(
	'username' => 'root',
	'passwd' => 'root',
	'dbname' => 'ma_base'
	));

Explication : La classe va tenter de créer une sauvegarde entière de la base de données ma_base avec le serveur MySQL localhost ayant pour identifiant root et mot de passe root.

Vous constatez que l’instance de la classe new BackupMySQL n’est allouée à aucune variable. Ceci est tout à fait normal car la classe effectue une seule action : la sauvegarde, puis la classe se détruit d’elle-même.

La ligne new BackupMySQL n’utilise qu’un seul argument array() tableau. Ce tableau vous permet d’inscrire les différentes options dont vous avez besoin pour personnaliser votre sauvegarde. Toutes les clés du tableau sont optionnelles en théorie. En pratique, certaines sont nécessaires comme l’identifiant et mot de passe de la base de données MySQL. Voici le détail complet de ce tableau magique avec ses valeurs par défaut :

$default = array(
		'host' => ini_get('mysqli.default_host'),
		'username' => ini_get('mysqli.default_user'),
		'passwd' => ini_get('mysqli.default_pw'),
		'dbname' => '',
		'port' => ini_get('mysqli.default_port'),
		'socket' => ini_get('mysqli.default_socket'),
		'dossier' => './',
		'nbr_fichiers' => 5,
		'nom_fichier' => 'backup'
		);
  • La clé host vous permet de définir l’hôte du serveur MySQL,
  • La clé username vous permet de définir l’identifiant de connexion au serveur MySQL,
  • La clé passwd vous permet de définir le mot de passe de connexion au serveur MySQL,
  • La clé dbname vous permet de définir le nom de la base à sauvegarder,
  • La clé port vous permet de définir le port de connexion au serveur MySQL,
  • La clé socket vous permet de définir le socket à utiliser pour le serveur MySQL,
  • La clé dossier vous permet de définir le dossier où se trouveront les sauvegardes de votre base. L’emplacement du dossier se fait selon l’emplacement du fichier PHP qui appellera votre script de sauvegarde,
  • La clé nbr_fichiers vous permet de définir le nombre de sauvegardes à conserver,
  • La clé nom_fichier vous permet de définir le préfix du nom du fichier pour la sauvegarde (Exemple : backup20130130-153012.sql.gz)

Une fois votre script PHP réglé avec les valeurs ci-dessus, il vous suffit simplement de lancer le script et la sauvegarde se fait toute seule !

Les anciennes sauvegardes seront effacées automatiquement pour ne pas saturer votre espace disque.

En ce qui concerne la restauration, elle pourra être effectuée avec n’importe quel outil d’administration MySQL tel que phpMyAdmin.

Vous souhaitez effectuer plusieurs sauvegardes ? Rien de plus simple. Il vous suffit de faire autant d’instance de classe que nécessaire. Je vous déconseille de placer toutes vos sauvegardes dans le même fichier PHP, car les scripts PHP ne peuvent pas s’exécuter indéfiniment sur le serveur (cf. max_execution_time dans le fichier php.ini). Un fichier PHP par sauvegarde me semble plus sage car ce script est plutôt gourmand en ressources serveur 😉

Comment planifier vos sauvegardes de manières automatisées ? Certains hébergeurs proposent les tâches planifiées (cron, crontab) dans leur hébergement. Il vous suffit de spécifier l’emplacement du script PHP, la date et l’heure de l’exécution, et le tour est joué.

Si votre hébergeur ne propose pas ce type de prestation, Google vous proposera quelques adresses avec la recherche suivante : crontab gratuit.

2) FONCTIONNEMENT ET EXPLICATION DU CODE SOURCE

Prérequis : Avant de commencer à modifier ce script tête baissée, je vous invite à réviser les concepts de la POO Programmation Orientée Objet. http://www.php.net/manual/fr/language.oop5.php

Si vous avez déjà ouvert le script PHP (et je présume que c’est le cas), vous avez pu constater que la classe BackupMySQL est étendue de mysqli, ceci veut dire que toutes les méthodes disponibles dans mysqli sont à votre disposition dans BackupMySQL sans instancier mysqli et pour cause, elle hérite de mysqli.

Le constructeur de la classe public function __construct reprend le principe d’écriture des paramètres avec un tableau array() et non avec des arguments à la suite. Ceci permet une certaine souplesse dans l’écriture des paramètres : vous êtes libre de l’ordre des paramètres et tous les paramètres sont en option (ce dernier point peut aussi être un problème, à vous d’affiner les contrôles si vous souhaitez éviter les plantages).

Les valeurs par défaut, vues dans la première partie, proviennent de la documentation officielle PHP du constructeur mysqli.

mysqli est donc réellement lancé (si je puis m’exprimer ainsi) avec la ligne parent::__construct( … );

Ci-dessous, vous avez les divers contrôles d’usage :

– La connexion au serveur fonctionne –t-elle ?

– Le dossier spécifié existe-il ?

– Un fichier peut-il être écrit ?

Et si tout va bien, la sauvegarde démarre…

La méthode protected function message() vous permet de suivre l’évolution de votre script. Si vous ne souhaitez plus voir les opérations par soucis de confidentialité, il vous suffit de commenter le echo.

La méthode protected function insert_clean() a pour but de protéger les caractères spéciaux dans votre fichier de sauvegarde et surtout ne pas avoir de bugs lors de la restauration du fichier avec les apostrophes et autres retour-charriots.

La méthode protected function sauvegarder() comme son nom l’indique, déclenche LA sauvegarde et l’écriture du fichier GZip sur l’espace disque.

Comment procède-t-elle ?

  1. On demande à MySQL de retourner le nom des tables présentes dans la base avec la requête SHOW TABLE STATUS,
  2. Nous voici dans une boucle while qui tournera pour chaque table,
  3. On inscrit dans le fichier sauvegarde un DROP TABLE IF EXISTS pour supprimer les anciennes tables,
  4. La requête SHOW CREATE TABLE nous retourne une requête toute faite pour la création de la table en cours,
  5. On inscrit les données par INSER INTO que tout le monde connaît, je présume,
  6. On écrit les données avec gzwrite() et le fichier se clôt avec gzclose(),
  7. Votre sauvegarde est terminée !

La dernière méthode protected function purger_fichiers() a pour but de ? de ?… Oui ! De purger les vieux fichiers !

Comment procède-t-elle ?

  1. On parcourt le dossier avec la classe Directory par l’intermédiaire de la fonction dir(),
  2. La boucle while permet de lister les dossiers et fichiers présents, nous allons uniquement sélectionner les fichiers (et non les dossiers) se terminant par l’extension .gz avec l’expression régulière preg_match(‘/\.gz$/i’, $fichier)
  3. Une fois la boucle terminée, la variables $fichiers contient tous les fichiers de sauvegarde .gz, la date de la sauvegarde étant présente dans le nom du ficher, il suffit d’inverser l’ordre du nom des fichiers avec rsort() et le script n’aura plus qu’à supprimer les anciens fichiers dans la boucle for avec unlink().
  4. Et voilà, vos anciens fichiers ont été supprimés !

Cette classe étant livrée en l’état, libre à vous de la modifier, de l’améliorer comme bon vous semble. N’oubliez pas qu’elle hérite de mysqli, donc, faites attention de ne pas écraser une méthode ou une variable présente dans la classe mysqli.

http://www.php.net/manual/fr/book.mysqli.php

[Total : 11    Moyenne : 4.4/5]

A propos jeanluc

45 Commentaires

  1. Génial tout simplement ! 🙂

  2. Merci Jean luc pour ce partage et ton script 🙂
    Très facile d’utilisation et fonctionne impeccablement …

  3. Super! Génial! Merci

  4. Merci pour ce script très utile !

    Néanmoins, il ne gère pas la création des clés étrangères APRES la création de toutes les tables lors de la restauration d’où une erreur lors de la restauration du backup.

    Un petit complément de ce coté là et ça serait parfait !

  5. D’abord un grand merci merci pour ce boulot !

    Question : Comment gérer l’encodage des données ?

    J’utilise UTF-8 et si j’ouvre le backup (même en forçant l’ouverture en UTF-8) les caractères accentués ne sont pas correctement encodés.

    Merci d’avance pour toute solution ou piste.

  6. Bonjour,
    Super ce script, je ne trouvais que des solutions avec la commande mysql_dump qui ne m’arrangeait pas du tout dans mon cas.
    Cependant, serait il possible de rendre l’archive téléchargeable? J’aimerais l’avoir sur le serveur mais également dans un dossier chez moi si possible…

    Merci pour votre superbe boulot!

    • Salut, si la base de donnée n’est pas trop volumineuse, peut être pourrait tu l’envoyer par Email avec la fonction php qui va bien ?

      J’ai justement réfléchi à cette problématique, et je pense que je vais faire de la sorte.

      Ps, j’ai également un problème avec la restauration de la sauvegarde à cause des clé étrangère, ma base est eb Innodb

  7. Je m’auto répond, le problème d’erreur à la restauration de la base et du a l’ordre de création des tables qui n’est pas respecté. je vais voir comment faire pour corriger ce problème.

  8. Grand merci pour ce script très propre et très efficace !

  9. J’ai un petit problème quand j’extrais mon gz, le fichier sql n’a qu’une seule ligne comment faire ? (P.S je suis en local)

  10. Pardon du dérangement je m’etais planté, merci super script et rare en mysqli 😉 !

  11. un grand merci pour ce travaille tt simplement bien fait! thanksssss!

  12. Sympa et bien utile, merci pour ce script.
    Pour les bases en UTF8 je rajoute:
    $val= utf8_encode($val);
    après la ligne 148 (avant la ligne $sql .= ‘\ ». $this->insert_clean($val) . ‘\ »;)
    Par contre si le champ est un BLOB il faudrait le détecter pour ne pas l’encoder car le blob serait corrompu.

  13. Merci pour ce script, je l’ai tester sous symfony et il fonctionne. Génial !.

  14. Merci pour le script, ça roule nickel et sans prise de tête.

  15. Pour ma part, la sauvegarde fonctionne mais il y a quand même ce message d’erreur ci-dessous :/
    Fatal error: Call to protected method BackupMySQL::sauvegarder() from context
    Quelqu’un pourrait t-il m’aider à résoudre ce problème?
    Merci d’avance!

  16. Valentin DELBEKE

    Script très performant et très utile qui m’a économisé pas mal d’heures de casse-tête.
    Cependant, en l’exploitant dans l’état, j’ai pas mal de difficulté à l’exploiter dans un script de restauration.
    A moins que ce soit de mon côté.

  17. que dire ?…… simple, concret, fonctionnel ^^
    Un script comme on aimerait en voir plus souvent !
    Merci Jean-Luc

  18. Super Jean-Luc, ca marche nickel pour moi.
    Un grand merci à toi pour ce partage

  19. Pour le problème d’encodage UTF8 lors de l’importation, on peut simplement ouvrir le fichier sql dans notepad++ (par exemple) et convertir l’encodage de ANSI vers Utf8 et le tour est joué.

    Le mieux serait quand même de trouver le moyen que le fichier sql soit encodé d’office en Utf8….

  20. J’ai le même problème que Nico : ma base est encodée en UTF-8 et les caractères accentués ne sont pas correctement encodés.

    J’ai essayé la solution proposée par plusdespam d’ajouter $val= utf8_encode($val), mais ça ne fonctionne pas.

    La seule solution qui fonctionne est de décompresser la base, de convertir le fichier obtenu en UTF-8 sans BOM puis d’importer le fichier obtenu. Ce n’est pas viable pour une base volumineuse.

    Quelqu’un sait comment résoudre le problème ?

    • Finalement j’ai trouvé tout seul la réponse à ma question : il faut ajouter

      $this->set_charset(« utf8 »);

      en ligne 70, juste après l’appel au constructeur @parent::__construct qui connecte à la bd.

      Bravo pour cet excellent script !

      • Merci bien pour ce script assez bien écrit et très simple à comprendre et à utiliser.
        J’ai toutefois rajouté `$this->set_charset(« utf8″);` à la ligne 70 pour régler le problème d’encodage, merci pour la solution rapide.

  21. Salut à tous,

    Après  » while($obj_table = $result_tables->fetch_object()) {  » j’ai essayé de modifier le script pour qu’il sauvegarde une table après l’autre, et comme je débute en php traditionnel, je ne comprends pas grand chose à la POO.

    Ensuite j’essayerai de faire une autre modif, ne pas sauvegarder la table si la dernière sauvegarde de celle-ci à moins de 24h.

    Vos explications et votre coup de pouce me seraient très utiles

  22. tout simplement génial ce script
    chez moi fonctionne en UTF-8 …
    J’ai les bons accents

  23. du beau travail, merci.
    lorsqu’on sauve une base moyennement volumineuses, le temps de téléchargement par phpmyadmin dépasse les 30s.
    ceci est dû au fait que le fichier sauve généré répète les « INSERT INTO table VALUES (‘….’, ‘….’, ……);
    sont répétés autant de fois que de lignes dans les tables.

    générer un sauve sql du genre « INSERT INTO table VALUES (‘…’, ‘…’…), (‘…’, ‘…’…), (‘…’, ‘…’…)…..(‘…’, ‘…’…);
    va réduire considérablement les temps d’exécution du fichier au chargement.

    merci.

  24. Merci pour cet excellent article. Il m’a été bien utile!
    J’aime ce style simple et efficace

  25. Pour régler le problème des accents et autres caractères spéciaux, une solution élégante consiste à ajouter au début du fichier de backup les lignes suivantes en début :
    SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
    SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
    SET @OLD_CHARACTER_SET_CONNECTION=@@CHARACTER_SET_CONNECTION;
    SET @@CHARACTER_SET_CLIENT=’latin1′;
    SET @@CHARACTER_SET_RESULTS=’latin1′;
    SET @@CHARACTER_SET_CONNECTION=’latin1′;
    avec ‘latin1’ dépendant de la configuration de votre base de donnée et/ou connexion.

    Pour cela, j’ai ajouté dans le script les lignes suivantes :
    // Prise en compte des types de caractères
    fwrite($file, »SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;\nSET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;\nSET @OLD_CHARACTER_SET_CONNECTION=@@CHARACTER_SET_CONNECTION;\n »);
    $charSet = $v_bdiLink->query(‘SELECT @@CHARACTER_SET_CLIENT,@@CHARACTER_SET_RESULTS,@@CHARACTER_SET_CONNECTION’)->fetch_array();
    // print_r($charSet);
    fwrite($file, »SET @@CHARACTER_SET_CLIENT='{$charSet[0]}’;\nSET @@CHARACTER_SET_RESULTS='{$charSet[1]}’;\nSET @@CHARACTER_SET_CONNECTION='{$charSet[2]}’;\n »);

    Pour bien finir les choses, il faut restaurer les valeurs des CHARACTER_SET_… en ayant à la fin du fichier backup :
    SET @@CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
    SET @@CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
    SET @@CHARACTER_SET_CONNECTION=@OLD_CHARACTER_SET_CONNECTION;

    que j’ai obtenu en ajoutant dans le script :
    fwrite($file, »SET @@CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;\nSET @@CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;\nSET @@CHARACTER_SET_CONNECTION=@OLD_CHARACTER_SET_CONNECTION;\n »);

  26. Script Top
    Toujours utilisé en 2016

    pour utiliser ce script via crontab notamment des serveurs mutualisé ou hébergé (Paramétrage cron via interface Web) je le lance via un bath sh
    en indiquant un PATH pour que le script trouve le dossier de sauvegarde .
    Sinon le script backup.php génère une erreur de dossier.

    • Bonjour
      ça m’interesse d’avoir le détail car je pense que c’est mon souci. si je lance depuis mon http c »est OK mais en mode cron message d’erreur

      2016-02-02 23:10:03] ## OVH ## START – 2016-02-02 23:10:03.254809 executing: /usr/local/php5.6/bin/php /homez.2049/snlunionkv/backup.php
      [2016-02-02 23:10:03] Could not open input file: /homez.2049/snlunionkv/backup.php

  27. excellent: merci pour les nombreuses heures que tu m’as fait gagner !
    c’est du plug and play 😉
    du travail pour tout comprendre, mais ainsi je peux avancer.
    je te fais un gros poutou.

  28. … pensez aussi à initialiser l’objet en utf8 pour les tables en utf_8
    Clair et limpide, du bon boulot!

  29. Bonjour,

    Le script fonctionne parfaitement en local avec wamp, mais lorsque je le passe en ligne j’ai cette erreur : Erreur de connexion (2002) Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2).
    Avez vous une idée ?

  30. Merciii infiniment pour le script!
    je suis debutante avc le PHP et jaimerai envoyer le backup par email … Pouviez me dire que dois ecrire dans $mail->addAttachment( »); pour envoyer le fichier sauvgardé

  31. Il y a effectivement un problème pour les clés étrangères.
    Il faut d’abord que le script crée les tables sans les clés étrangères.
    Import des données
    Et à la fin mette en place les clés étrangères.
    Sinon, on se retrouve avec des tables qui déclarent des clés étrangères sur des tables qui sont créées plus tard dans le script, ce qui cause un arrêt prématuré de l’import.
    Si les tables sont dans l’ordre alphabétique, on n’a effectivement aucun problème.
    Exemple de base pouvant poser problème :
    DROP TABLE IF EXISTS `api_session`;
    CREATE TABLE `api_session` (
    `user_id` mediumint(9) unsigned NOT NULL,
    `logdate` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
    `sessid` varchar(40) NOT NULL DEFAULT  »,
    KEY `API_SESSION_USER` (`user_id`),
    KEY `API_SESSION_SESSID` (`sessid`),
    CONSTRAINT `FK_API_SESSION_USER` FOREIGN KEY (`user_id`) REFERENCES `api_user` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’Api Sessions’;

    DROP TABLE IF EXISTS `api_user`;
    CREATE TABLE `api_user` (
    `user_id` mediumint(9) unsigned NOT NULL AUTO_INCREMENT,
    `firstname` varchar(32) NOT NULL DEFAULT  »,
    `lastname` varchar(32) NOT NULL DEFAULT  »,
    `email` varchar(128) NOT NULL DEFAULT  »,
    `username` varchar(40) NOT NULL DEFAULT  »,
    `api_key` varchar(40) NOT NULL DEFAULT  »,
    `created` datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
    `modified` datetime DEFAULT NULL,
    `lognum` smallint(5) unsigned NOT NULL DEFAULT ‘0’,
    `reload_acl_flag` tinyint(1) NOT NULL DEFAULT ‘0’,
    `is_active` tinyint(1) NOT NULL DEFAULT ‘1’,
    PRIMARY KEY (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=’Api Users’;

    Les tables dans le script sont renvoyées dans l’ordre alphabétique, or la première table api_session dépend de la seconde : erreur de l’import

  32. Salut,

    Juste pour te dire que ton partage… C’EST JUSTE UNE TUERIE !!!!!!

    Un GRAND merci 😉

  33. Salut!
    Merci pour le script mais j’ai un petit problème de clés primaires lors de l’importation de la base. Y a -t- il une solution à ça?
    Merci!

  34. Bonjour,
    Le lien pour télécharger le script n’existe plus. Est-il possible d’avoir une nouvelle URL svp?
    Merci d’avance

  35. Salut, C’est parfait.
    Merci

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *