Comment exporter une table MySql
Du point de vue technique, le dump de la table est un fichier texte contenant l'instruction Sql servant à créer la structure de la table puis une succession d'INSERT pour ajouter les données.Outils en ligne de commande
MySql: exporter directement avec le moteur Mysql
mysql --host host --user user --password pass nom_base_de_donnees > fichier_dump.sql
Mysqldump: paquet à installer en plus de MySql
#installer le paquet sous Linux
sudo apt-get install mysqldump
#Dumper la table
mysqldump -u root -p nom_base_de_donnees> fichier_dump.sql
sudo apt-get install mysqldump
#Dumper la table
mysqldump -u root -p nom_base_de_donnees> fichier_dump.sql
Outils avec interface Graphique
phpMyAdmin: outil d'administration de MySql
Dans l'onglet 'Exporter', sélectionner une ou plusieurs tables puis lancer l'export.
Exporter une base de donnée avec PHP
Etant sur un serveur mutualisé, les outils en ligne de commande ne sont pas accessibles. Le script PHP permet de réaliser soi-même les opérations de dump avec possibilités d'automatiser le process de sauvegarde.La class MySqlDump exporte une ou plusieurs tables au format sql et compresse les fichiers directement dans une archive zip.
<?php
/**
* MySqlDump Exporter une base de donnee MySql avec Pdo
*
* @author Fobec 06/14
* @see http://www.fobec.com/php5/1148/exporter-une-base-donnee-mysql-avec-pdo.html
*/
class MySqlDump {
private $DB_CONF =
array('host' => '', 'name' => '', 'user' => '',
'password' => '', 'filter' => '*'
);
private $compressToZip = false;
private $colInfo = array();
private $MAX_SQLFILE_SIZE = 1000; //in Mo
public function asZipArchive($host, $dbname, $user, $password, $filter = '*') {
$this->DB_CONF = array('host' => $host,
'name' => $dbname,
'user' => $user,
'password' => $password,
'filter' => $filter);
$this->compressToZip = true;
$fziparchive = tempnam(sys_get_temp_dir(), $this->DB_CONF['name']);
$files_dumpsql = $this->backupDB($this->DB_CONF, $fziparchive);
if ($this->isZipValid($fziparchive, $files_dumpsql) == FALSE) {
echo "ZipArchive seems to be corrupt";
} else {
header("Content-Type: application/zip");
header("Content-Transfer-Encoding: Binary");
header("Content-Length: " . filesize($fziparchive));
header('Content-Disposition: attachment; filename="' . $this->DB_CONF['name'] . '.zip' . '"');
header("Content-Transfer-Encoding: binary");
header('Expires: 0');
header('Pragma: no-cache');
/* ob_clean();
flush(); */
readfile($fziparchive);
exit(0);
}
}
/**
* Fixer la taille maximale du fichier d'export Sql
* @param type $maxsize taille en Mo
*/
public function setMaxFileSize($maxsize) {
$this->MAX_SQLFILE_SIZE = $maxsize;
}
/**
* Lancer l'export d'une base de donnée
* @param type $db_conf
* @param type $fziparchive
* @return type
*/
private function backupDB($db_conf, $fziparchive = '') {
set_time_limit(60);
$file_dumptable = array();
try {
$pdo = new PDO('mysql:host=' . $db_conf['host'] . ';dbname=' . $db_conf['name'], $db_conf['user'], $db_conf['password']);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//List table from DBname
$stmt = $pdo->prepare('SHOW TABLES');
$stmt->execute();
/** Table filter * */
$filter_table = explode(';', $db_conf['filter']);
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
if (in_array($row[0], $filter_table) || $db_conf['filter'] == '*') {
$tables[] = $row[0];
}
}
$stmt->closeCursor();
$pdo = null;
/* Dump each Table **/
foreach ($tables as $tablename) {
$ntemp = $this->backupTable($db_conf, $tablename, $fziparchive);
foreach ($ntemp as $sqlfile) {
$file_dumptable[] = $sqlfile;
}
}//end loop table
return $file_dumptable;
} catch (PDOException $err) {
$msg = __METHOD__ . ' - ' . $err->getMessage();
echo $msg;
$this->pdo = NULL;
}
}
/**
* Export Table to SQL File, files can be split
* @param type $db_conf
* @param type $tablename
* @param type $fziparchive
* @return string
*/
private function backupTable($db_conf, $tablename, $fziparchive = '') {
$pdo = new PDO('mysql:host=' . $db_conf['host'] . ';dbname=' . $db_conf['name'], $db_conf['user'], $db_conf['password']);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//Avoid General error: 2008 MySQL client ran out of memory
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$sql_files = array();
//Open new SQL File
$tmpfile = tempnam(sys_get_temp_dir(), $tablename);
$filelabel = $tablename . '.sql';
$sql_files[] = array('file' => $tmpfile, 'name' => $filelabel);
$fhandle = fopen($tmpfile, 'w');
//Create Table SQL
$stmt = $pdo->prepare('SHOW CREATE TABLE ' . $tablename);
$stmt->execute();
$rs = $stmt->fetch(PDO::FETCH_NUM);
$buf = '-- MySqlDump v.09 ' . "n";
$buf.='-- http://www.fobec.com/php5/1148/exporter-une-base-donnee-mysql-avec-pdo.html' . "nn";
$buf.='-- server: ' . $db_conf['host'] . "n";
$buf.='-- date: ' . date('d/m/Y H:i:s') . "n";
$buf.='-- db: ' . $db_conf['name'] . "nn";
$buf.='-- create table: ' . $tablename . "nn";
$buf.= $rs[1] . ';' . "nn";
fwrite($fhandle, $buf);
$stmt->closeCursor();
//colmuns type
$stmt = $pdo->prepare('SHOW COLUMNS FROM ' . $tablename);
$stmt->execute();
$this->colInfo = $stmt->fetchAll(PDO::FETCH_NUM);
$col_mapping = $this->parseColInfo($this->colInfo);
$stmt->closeCursor();
//Select All
$stmt = $pdo->prepare('SELECT * FROM ' . $tablename);
$stmt->execute();
$line = '';
$buf = '';
$filecount = 2;
while ($row = $stmt->fetch(PDO::FETCH_NUM)) {
$buf = "INSERT INTO " . $tablename . " VALUES (";
$len = count($row);
//Buffer value add slash if dif. than int
for ($i = 0; $i < $len; $i++) {
if ($i != ($len - 1)) {
$buf.=$this->setValue($row[$i], $col_mapping[$i]) . ',';
} else {
$buf.=$this->setValue($row[$i], $col_mapping[$i]);
}
}
$buf = $this->removeLineBreak($buf);
$line.=$buf . ");n";
$buf = '';
//Write SQL commands to file
fwrite($fhandle, $line);
//Start new file if max size reach
if (ftell($fhandle) > (1024 * 1024 * $this->MAX_SQLFILE_SIZE)) {
fclose($fhandle);
$this->addTozipFile($fziparchive, $tmpfile, $filelabel);
$sql_files[] = array('file' => $tmpfile, 'name' => $filelabel);
$tmpfile = tempnam(sys_get_temp_dir(), $tablename . '_' . $filecount);
$filelabel = $tablename . '_' . $filecount . '.sql';
$filecount++;
$fhandle = fopen($tmpfile, 'w');
}
$line = '';
}
fclose($fhandle);
$this->addTozipFile($fziparchive, $tmpfile, $filelabel);
$stmt->closeCursor();
$pdo = null;
return $sql_files;
}
/**
* Compress Sql File in ZipArchive
* @param type $fzip
* @param type $file_path
* @param type $file_name
*/
private function addTozipFile($fzip, $file_path, $file_name) {
if ($this->compressToZip == true) {
$zip = new ZipArchive();
$zip->open($fzip, ZIPARCHIVE::CREATE);
$zip->addFile($file_path, $file_name);
$zip->close();
}
}
/**
* Check if all sql files have been added
* @param type $zipfile
* @param type $files_sql
* @return boolean
*/
private function isZipValid($zipfile, $files_sql) {
$zip = new ZipArchive;
$res = $zip->open($zipfile);
if ($res === TRUE) {
foreach ($files_sql as $zfile) {
$n = $zip->statName($zfile['name']);
if ($n == FALSE || $n['size'] < 10) {
$zip->close();
return FALSE;
}
}
$zip->close();
} else {
return FALSE;
}
return TRUE;
}
/**
* Add Slashes if Value is String
* @param type $val
* @param type $isInt
* @return string
*/
private function setValue($val, $isInt) {
if ($isInt) {
return $val;
} else {
if (!empty($val)) {
$sval = addslashes($val);
return "'" . $sval . "'";
} else {
return "''";
}
}
}
/**
* Remove Line break
* @param type $line
* @return type
*/
private function removeLineBreak($line) {
$nsearch = array("rn", "n", "r");
$nreplace = array('rn', 'n', 'r');
$sline = str_replace($nsearch, $nreplace, $line);
return $sline;
}
/**
* Build Field array with field type
* @param type $rs
* @return boolean
*/
private function parseColInfo($rs) {
$len = count($rs);
$map = array();
for ($i = 0; $i < $len; $i++) {
if (substr($rs[$i][1], 0, 3) == 'int' || $rs[$i][1] == 'float') { //'Type'
$map[] = true;
} else {
$map[] = false;
}
}
return $map;
}
}
?>
Comment faire un dump d'une table
La class MySqlDump se pilote rapidement dans un script php, il suffit d'autoriser la connexion au serveur Mysql, tout reste des opérations de dump est automatique.<?php
require $_SERVER['DOCUMENT_ROOT'] . '/' . 'MySqlDump.php';
$mySqlDump = new MySqlDump();
$mySqlDump->setMaxFileSize(2);
$mySqlDump->asZipArchive("adresse_serveur", "base_de_donnee", "user", "password", 'table1;table2');
?>
filtre: par défaut le caractère * indique que toutes les tables sont à exporter. La définition d'une sélection de table est possible en séparant le nom des tables avec ;
setMaxFileSize: la taille des fichiers d'export peut être limitée par exemple à 2 Mo pour faciliter l'import via phpMyAdmin. Attention, lors de l'import toujours commence par le 1er fichier car il contient la structure de la table
export: les données sont retournées au format zip directement dans le navigateur internet. La class peut être modifiée pour enregistrer les données dans un répertoire donnée ou envoyé par email.
Sur un hébergement mutualisé de type 1and1, une erreur 500 peut survenir lors de l?exécution du script de dump. En cherchant la raison, il semble que l?hébergeur limite la durée d?exécution du script dans les pack mutualisés, donc ça se sert à rien d'augmenter le timeout avec set_time_limit() par exemple.
Il serait intéressant d'implémenter une fonction resume ou d'export partielle à la class. Justement pour contourner la limitation des timeout lors des export de tables, la sauvegarde pourrait être reprise à l'ancienne position.
