[PHP] mysqldump : exporter une database MySQL
mercredi 5 octobre 2011
Mysqldump est un petit utilitaire qui permet d'exporter une base de donnée MySQL vers un fichier texte, pour la sauvegarde ou le transfert entre deux serveurs. Cet utilitaire, bien pratique, n'est pas toujours disponibles sur les hébergement mutualisés car il se lance en ligne de commande.
Cet article traite donc de la manière d'exporter une base de donnée MySQL en PHP.
On appelle "exportation" le fait de formatter dans un fichier (appelé dump) toutes les informations nécessaires pour créer la table et la remplir.
Le code suivant permet de réaliser un export d'une base de donnée dans un fichier SQL.
La partie Config permet de configurer les informations de connexions à la base (nom d'utilisateur,
mot de passe et adresse du serveur) ainsi que le nom de la base de donnée ($dbname
).
<?php // Init error_reporting(E_ALL); header('Content-type: text/plain'); // Config $dbname = 'wordpress'; mysql_connect('localhost', 'root', ''); mysql_select_db($dbname); // Open dump file $dumpfile = $dbname.'_'.date('Y-m-d_H-i').'.sql'; $fp = fopen($dumpfile, 'w'); if (!is_resource($fp)) { exit('Backup failed: unable to open dump file'); } // Header $out = '-- SQL Dump -- -- Generation: '.date('r').' -- MySQL version: '.mysql_get_server_info().' -- PHP version: '.phpversion().' SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `'.$dbname.'` --'; // Write fwrite($fp, $out); $out = ''; // Fetch tables $tables = mysql_query("SHOW TABLE STATUS"); $c = 0; while ($table = mysql_fetch_assoc($tables)) { $tableName = $table['Name']; $tmp = mysql_query("SHOW CREATE TABLE `$tableName`"); // Create table $create = mysql_fetch_assoc($tmp); $out .= "\n\n--\n-- Table structure: `$tableName`\n--\n\n".$create['Create Table'].' ;'; // Clean mysql_free_result($tmp); unset($tmp); // Write fwrite($fp, $out); $out = ''; // Rows $tmp = mysql_query("SHOW COLUMNS FROM `$tableName`"); $rows = array(); while ($row = mysql_fetch_assoc($tmp)) { $rows[] = $row['Field']; } // Clean mysql_free_result($tmp); unset($tmp, $row); // Get data $tmp = mysql_query("SELECT * FROM `$tableName`"); $count = mysql_num_rows($tmp); if ($count > 0) { $out .= "\n\n--\n-- Table data: `$tableName`\n--"; $out .= "\nINSERT INTO `$tableName` (`".implode('`, `', $rows)."`) VALUES "; $i = 1; // Fetch data while ($entry = mysql_fetch_assoc($tmp)) { // Create values $out .= "\n("; $tmp2 = array(); foreach ($rows as $row) { $tmp2[] = "'" . mysql_real_escape_string($entry[$row]) . "'"; } $out .= implode(', ', $tmp2); $out .= $i++ === $count ? ');' : '),'; // Save fwrite($fp, $out); $out = ''; } // Clean mysql_free_result($tmp); unset($tmp, $tmp2, $i, $count, $entry); } // Operations counter $c++; } // Close dump file fclose($fp); echo "Finished! Backup $c tables to `$dumpfile` (".filesize($dumpfile)." o)."; ?>
Comme vous pouvez le constater, le script décharge régulièrement la mémoire vive en enregistrant les informations au fur et à mesure dans le fichier de dump. Ce processus est obligatoire pour exporter de grandes bases de données.
Notez aussi que l'export d'une importante BDD peut prendre beaucoup de temps. Si vous le pouvez, pensez à élever le temps d'exécution maximum de votre script en début de fichier :
set_time_limit(0)
En annexe, retrouvez le code source du fichier mysqldump.php dans une archive ZIP :
Commentaires
C'est du bon boulot.
Mais je préfére utiliser mysqldump. Mais si vous n'avez pas accés à phpmyadmin ou à la console, vous pouvez utiliser cette classe.
Merci, ça peut aider.
Bonjour
C'est pour MySQL quelle version ?
merci
Ce code fonctionne pour MySQL version 4.1.1 ou plus.
En détail, il utilise les commandes suivantes :
- SHOW COLUMNS (v3.23)
- SHOW CREATE TABLE (v3.23.20)
- SHOW TABLE STATUS (v3.23)
- NO_AUTO_VALUE_ON_ZERO (v4.1.1)
Il suffit de supprimer la ligne 26 :
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
Et le code devrait être valide pour de la version 3.23 et plus.
ça marche bien merci beaucoup ça va me faire gagner beaucoup de temps!
Merci pour le script, une petite chose je suis obligé de créer le fichier sql avec un chmod 777 sinon j'ai l'erreur exit('Backup failed: unable to open dump file'); Mais à part ca il fonctionne super bien et je peux restaurer sans problème. Encore Merci
Salut
Excellent !
En changeant les f write,open,close en gz, on a une sauvegarde compressée.
Bye
Script modifié :
- fichier gz compressé
- limite de 100 enregistrements à la fois, pour les gros block de base
- lien de sauvegarde à la fin, à supprimer si sécurité insuffisante !
- avec ces modifs,la plupart des bases passent par phpmyadmin, si vos bases sont plus grosses, passez soit par mysql directement (instruction "source"), soit vous décompressez le fichier et le découpez.
- les commentaires avec -- empêchent l'importation, supprimé donc dans le script, utiliser # à la place si besoin ?
<?php
set_time_limit(0);
// Init
error_reporting(E_ALL);
//header('Content-type: text/plain');
// Config
$dbname = 'base';
mysql_connect('serveur', 'login', 'passwd');
mysql_select_db($dbname);
// Open dump file
$dumpfile = $dbname.'_'.date('Y-m-d_H-i').'.sql.gz';
$fp = gzopen($dumpfile, 'w');
if (!is_resource($fp)) {
exit('Backup failed: unable to open dump file');
}
// Header
$out = '
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
';
// Write
gzwrite($fp, $out);
$out = '';
// Fetch tables
$tables = mysql_query("SHOW TABLE STATUS");
$c = 0;
while ($table = mysql_fetch_assoc($tables)) {
$tableName = $table['Name'];
$tmp = mysql_query("SHOW CREATE TABLE `$tableName`");
// Create table
$create = mysql_fetch_assoc($tmp);
$out .= "\n\n".$create['Create Table'].' ;';
// Clean
mysql_free_result($tmp);
unset($tmp);
// Write
gzwrite($fp, $out);
$out = '';
// Rows
$tmp = mysql_query("SHOW COLUMNS FROM `$tableName`");
$rows = array();
while ($row = mysql_fetch_assoc($tmp)) {
$rows[] = $row['Field'];
}
// Clean
mysql_free_result($tmp);
unset($tmp, $row);
// Get data
$tmp = mysql_query("SELECT * FROM `$tableName`");
$count = mysql_num_rows($tmp);
if ($count > 0) {
$out .= "\nINSERT INTO `$tableName` (`".implode('`, `', $rows)."`) VALUES ";
$i = 1;
$limit = 1;
// Fetch data
while ($entry = mysql_fetch_assoc($tmp)) {
// Create values
$out .= "\n(";
$tmp2 = array();
foreach ($rows as $row) {
$tmp2[] = "'" . mysql_real_escape_string($entry[$row]) . "'";
}
$out .= implode(', ', $tmp2);
$out .= $i++ === $count ? ');' : ')';
if ($limit > 100) {
$out .= ";\nINSERT INTO `$tableName` (`".implode('`, `', $rows)."`) VALUES ";
$limit = 1;
}
else {
$out .= $i === $count+1 ? '' : ',';
$limit++;
}
// Save
gzwrite($fp, $out);
$out = '';
}
// Clean
mysql_free_result($tmp);
unset($tmp, $tmp2, $i, $count, $entry);
}
// Operations counter
$c++;
}
// Close dump file
gzclose($fp);
echo "Fini! Backup $c tables vers `$dumpfile` (".filesize($dumpfile)." o).";
?>
<!doctype html public "-//IETF//DTD HTML 2.0//EN">
<html>
<?php
echo "<a href=$dumpfile>Télécharger</a>";
?>
</html>
super boulot, fonctionne directement sur un php 5.6 avec un serveur hébergé en mutualisé L'export phpmyadmin est très similaire au résultat obtenu.
je cherche à adapter pour avoir un fichier par table, ca devrait etre faisable
bravo