Migrating data from and to MySQL
Yoan Blanc — Mon, 28 Feb 2011 — MySQL, Extract, Transform, Load
In the current project I’m working on, we are currently in the process of migrating the old data to their new model, and it’s way more complex that it first seemed. Mostly because the old database is a mess but that not the point here. I want to expose the way we choose to do this, a way that is efficient. It must be a better way, so speak your mind.
At the end this operation will happen once, but during the current development we might be running them a lot while adding new data sets to it.
Let’s dive, on how we import the data into the new database:
LOAD DATA INFILE
"/tmp/data.dat"
INTO TABLE
table_name;
That’s very fast, and can be faster if you allow more space for the InnoDB logs.
Now, where does that tmp/data.dat file come from what does it look like. This file has been generated by another request ran on the old database.
SELECT
id, name, some_value
FROM
old_table
INTO OUTFILE
"/tmp/data.dat";
This is a trivial one, recreating a new table with three columns. By default the output format is a tab separated file where NULL has been replaced by \N.
If your old data base — like it’s our case — is still in Latin 1 or 15 (in Europe), using iconv on the .dat file can convert it to, for example, utf-8.
iconv \
-f iso-8859-15 \
-t utf-8 \
/tmp/data.dat > /tmp/data_utf8.dat
mv /tmp/data{_utf8,}.dat
We got tricked by some cp-1252 characters mixed with the Latin 15 ones, beware of those.
And to finish this, two tipps if your old data are too messed up.
You are using text fields in the old data but will use a dedicated table now one so you should use a temporary table for this kind of mapping, i.e. country.
You cannot get all the data at once and need to compute stuff for some rows. In our case moving the pictures to a dedicated table and linking them back afterwards. So we load the data that need to be updated into a temporary table and then perform on update SQL statement to update them all at once, they work like natural joins.
Mon projet actuel consistant à migrer des données presque historiques peu structurées, chaotiques et pleines de surprises vers un modèle plus carré aux contraintes d’intégrités respectées. Pour y arriver, et après divers essais, voici comment nous nous y prenons. Le contexte est simple, une base de donnée MySQL en MyISAM et latin 15 (iso-8859-15) est morphée en une autre devant inclure la première mais permettre plus utilisant InnoDB (pour les contraintes d’intégrité notamment) et UTF-8 (unicode partout, tout le temps).
Dans les solutions explorées, il y a la manière dont fonctionne la commande mysqldump qui produit des INSERT. C’est bien, dès qu’il s’agit d’effectuer des opérations un peu plus complexe sur les données à sélectionner ça en devient plus difficile et il y a des limites quant au nombre d’insertions pouvant être effectué à la fois. Pas assez simple.
Une autre approche aurait été de se reposer sur un outil de ETL (Extract Transform Load), très utilisé pour tout ce qui est BI (business intelligence), mais à vue de nez ça ne serait pas assez rapide.
Car même si l’opération, au final, ne sera exécutée qu’une seule fois, au moment de la migration vers le nouveau schéma, en développement il nous faut le faire régulièrement pour nous assurer que les données récentes passent toujours et que le nouveau schéma en cours de migration le fasse.
Et dans une optique purement MySQL, assumant que l’ancienne et la nouvelle base sont indépendantes, l’option a été de se servir des facultés d’export et d’import de MySQL vers le système de fichier avec les commandes :
SELECT …
FROM
nom de la table
INTO OUTFILE
nom du fichier;
et
LOAD DATA INFILE
nom du fichier
INTO
nom de la table;
Le fichier intermédiaire (les données sont séparées par des tabulations et retours-ligne par défaut) peut être converti en unicode à l’aide de la commande iconv et à coups de tr, il est même possible de se débarasser des quelques caractères cp1252 restant.
Et pour tous les cas qui sortent d’un simple SELECT / LOAD, les tables temporaires sont d’une très grande utilité pour effectuer des tables de liaisons (données textuelles vers identifiant, i.e. pays, monnaie, …) ou des importations en plusieurs étapes à l’aide d’un UPDATE sur toute une table (via une très vilaine jointure naturelle, si si).
Au final, je me demande si nous n’aurions pas dû effectuer ce travail là sur la version live du site et amorcer des migrations partielles des données plutôt que de vouloir tout basculer d’un coup. L’avenir le dira.
Si vous avez mieux que cette méthode assez brute, c’est avez grand plaisir que j’en prendrai connaissance.
