Déménager une immense base de données avec phpMyAdmin

Le transfert du contenu d'une base de données d'un serveur à l'autre (ou même d'une base à l'autre) avec phpMyAdmin est une opération assez simple : il suffit généralement d'exporter le contenu de la base source sous forme d'une requête SQL pour ensuite l'importer dans la nouvelle base.

Mais les choses peuvent se corser lorsque le fichier de requête SQL produit pèse plusieurs centaines de mégaoctets. Effectivement, le serveur de destination risque de ne pas arriver à traiter d'un coup une requête de cette taille. D'autant plus qu'en tentant de télécharger en amont un tel fichier, même compressé, vous risquez d'atteindre le délai limite d'exécution du serveur avant d'avoir réussi à le télécharger.

C'est ce qui m'est arrivé récemment en essayant de déménager une base de données Piwik sur un nouveau serveur, le fichier SQL produit pesant quelques 537 Mo !

J'ai donc développé la solution suivante pour fragmenter un immense fichier SQL en plusieurs petites requêtes qui pourront être importées une à une. J'ai ensuite modifié le script pour qu'il produise des fichiers gzippés, question de minimiser la bande passante (et surtout le temps de téléchargement) au moment de l'importation.

Dans ce cas-ci, mon fichier SQL s'appelait "piwik.sql". Le script "sql-split-gz.php", placé dans le même répertoire, contient le code PHP suivant :

<?php
$source = 'piwik';
$part_threshold = 40*1024*1024;
if(!$f = fopen($source.'.sql','r')) die("No such file: $source.sql");
mkdir("$source.part");

$part = 1;
$size = 0;
#$o = fopen($fn="$source.part/$part.sql",'w+');
$o = gzopen($fn="$source.part/$part.sql.gz",'w9');

while($line = fgets($f,4096)) {
 if(substr($line,0,7)==='INSERT ') {
  if($size >= $part_threshold) {
   #fclose($o);
   gzclose($o);
   echo "$fn ($size)\r\n";
   $part++;
   $size = 0;
   #$o = fopen($fn="$source.part/$part.sql",'w+');
   $o = gzopen($fn="$source.part/$part.sql.gz",'w9');
  }
 }
 #fwrite($o,$line);
 gzwrite($o,$line);
 $size += strlen($line);
}
#fclose($o);
gzclose($o);
echo "$fn ($size)\r\n";
fclose($f);
?>

Le script créera un dossier nommé "piwik.part" et y placera les fichier SQL numérotés, avec l'extension .sql.gz. Il suffira ensuite d'importer ces fichiers un par un dans phpMyAdmin, qui interprète nativement les fichiers gzippés. Mais attention ! Il faudra les importer dans le même ordre !

Fonctionnement

Le script lit le fichier SQL source ligne par ligne. Il écrit chaque ligne dans le fichier de sortie actuel, en commençant par "1.sql.gz". Lorsqu'il rencontre une requête "INSERT", il vérifie si la quantité de données (non-compressées) déjà écrites dans le fichier de sortie dépasse la valeur de $part_treshold (ici configurée à 40 Mo, soit 40 * 1024 * 1024 octets). Le cas échéant, la variable $part est incrémentée et les données sont écrites dans le fichier de sortie suivant.

Il faut donc modifier la variable $source pour qu'elle corresponde au nom de votre fichier source (sans l'extension ".sql") et la variable $part_treshold à la taille désirée. Une valeur plus élevée donnera des fragments moins nombreux mais plus volumineux. Cependant, gardez à l'idée que phpMySQL peut imposer une limite de taille aux fichiers importés (dans mon cas elle est de 50 Mo sur mon serveur de destination).

Attention : la valeur $part_treshold n'est pas une limite de taille, mais, comme son nom l'indique, un seuil : étant testée seulement à chaque requête "INSERT", la quantité de données (non compressées) écrites dans chaque fichier de sortie dépasse nécessairement cette grandeur. Dans ce cas-ci, puisque les fichiers de sortie sont gzippés, on pourrait néanmoins se permettre une valeur plus grande (dans mon cas, le plus gros fichier de sortie produit ne pèse que 17,8 Mo), ce que j'aurais peut-être dû faire, tout compte fait, pour réduire le nombre d'importations successives nécessaires.