[PHP] mysqldump : exporter une database MySQL

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

1. Commentaire de Daniel le jeudi 10 novembre 2011

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.

2. Commentaire de Nardaud le samedi 12 novembre 2011

Bonjour
C'est pour MySQL quelle version ?
merci

3. Commentaire de Ted le dimanche 13 novembre 2011

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)

4. Commentaire de Ted le dimanche 13 novembre 2011

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.

5. Commentaire de nielberg shumi le samedi 19 novembre 2011

ça marche bien merci beaucoup ça va me faire gagner beaucoup de temps!

6. Commentaire de terrygt le dimanche 25 mars 2012

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

7. Commentaire de caspual le jeudi 11 octobre 2012

Salut

Excellent !
En changeant les f write,open,close en gz, on a une sauvegarde compressée.

Bye

8. Commentaire de caspual le vendredi 12 octobre 2012

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>

9. Commentaire de geh le dimanche 1 janvier 2017

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


About the Author

Ted Marklor est un web designer, un web developer et un génie de la nature. Transcendant le web depuis bientôt 15 ans, Ted est une source d’inspiration et de conseil pour toute une génération de jeunes programmeurs. Le Web 2.0, c’est lui. Dans la vie, il aime aussi faire des avions en papier, s’inventer des pseudonymes et une vie de winner, et surtout parler de lui à la troisième personne. Ça se fait en ce moment sur les blogs…


Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.