Avec les tables MyISAM, il est possible de sauvegarder directement les fichiers MYD et MYI lorsque le serveur MySQL tourne, en supposant bien sûr que vous avez verrouillé les tables concernées avec la commande LOCK TABLES ou qu'il n'y a aucune requête en cours. Cependant, il n'en est pas de même avec le moteur InnoDB. En effet, à la différence de MyISAM, il y a des threads qui continue à s'exécuter après que les modifications aient été faites et qui modifient les fichiers de données. Seules les journaux de transactions (iblogfiles) sont réellement écrits à chaque fin de transaction (je considère que vous ne jouez pas avec le paramètre
innodb_flush_log_at_trx_commit). De plus, il y a un fichier spécial, nommé par défaut ibdata1, qui contient le dictionnaire de données et toutes les tables (et index) InnoDB ayant été créées avec le paramètre
innodb_file_per_table désactivé).
C'est la raison pour laquelle il n'est pas possible de copier un fichier innodb d'un serveur à un autre. Il est par contre tout a fait possible de sauvegarder un fichier innodb et de le restaurer plus tard sur
le même serveur ou sur un serveur hébergeant une sauvegarde physique, sans avoir à restaurer la base dans son ensemble.
Il faut savoir qu'InnoDB associe un identifiant à chaque fichier innoDB nommé tablespace (oui cela n'a rien à voir avec les tablespaces d'Oracle). Cet id est stocké dans le catalogue InnoDB ainsi que dans le fichier concerné. C'est ce qui empêche de restaurer le fichier sur un autre serveur sur lequel le même tablespace n'existe pas ou a un identifiant différent, ou sur le même serveur sur lequel on aurait fait un TRUNCATE de la table, et donc généré implicitement un nouvel identifiant !
La méthode à suivre est la suivante, détaillée dans la document MySQL :
mysql> USE test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> CREATE TABLE t1(id int);
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT @i:=0;
+-------+
| @id:=0 |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec
mysql> INSERT INTO t1 SELECT @id:=@id+1 FROM information_schema.TABLES;
Query OK, 198 rows affected (0.03 sec)
Records: 198 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT @id:=@id+1 FROM information_schema.TABLES;
Query OK, 198 rows affected (0.00 sec)
Records: 198 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t1 SELECT @id:=@id+1 FROM information_schema.TABLES;
Query OK, 198 rows affected (0.00 sec)
Records: 198 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 594 |
+----------+
1 row in set (0.00 sec)
mysql> SHOW ENGINE INNODB STATUS\G ... Main thread process no. 5109, id 140547993949952, state: waiting for server activity
Number of rows inserted 594, updated 0, deleted 0, read 1193
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 12.91 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
1 row in set (0.00 sec)
Une fois que le statut du moteur InnoDB est bien à "waiting for server activity", On sauvegarde le fichier InnoDB
cp -p /var/lib/mysql/test/t1.ibd /tmp/
La sauvegarde effectuée, on simule la perte de données
mysql> DELETE FROM t1 WHERE id>400;
Query OK, 194 rows affected (0.04 sec)
mysql> SELECT MAX(id) FROM t1;
+---------+
| MAX(id) |
+---------+
| 400 |
+---------+
1 row in set (0.00 sec)
- On tente maintenant de restaurer nos données en prévenant MySQL
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
- On restaure le fichier à sa place d'origine
root@lizzie:~# cp -p /tmp/t1.ibd /var/lib/mysql/test/t1.ibd
- On avertit MySQL qu'il peut utiliser à présent le fichier
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT MAX(id) FROM t1;
+---------+
| MAX(id) |
+---------+
| 594 |
+---------+
1 row in set (0.02 sec)
Et voilà !
Par contre, comme je vous l'ai dit ça ne fonctionne plus quand l'id du tablespace a été modifié dans le catalogue :
mysql> TRUNCATE TABLE t1;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
root@lizzie:~# cp -p /tmp/t1.ibd /var/lib/mysql/test/t1.ibd
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
ERROR 1030 (HY000): Got error -1 from storage engine
Vous trouverez un message plus explicite dans le fichier d'erreur :
grep -A 10 "InnoDB: Error" /var/lib/mysql/lizzie.err
111012 18:09:31 InnoDB: Error: tablespace id and flags in file './test/t1.ibd' are 209768 and 0, but in the InnoDB
InnoDB: data dictionary they are 209769 and 0.
InnoDB: Have you moved InnoDB .ibd files around without using the
InnoDB: commands DISCARD TABLESPACE and IMPORT TABLESPACE?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.
111012 18:09:31 InnoDB: cannot find or open in the database directory the .ibd file of
InnoDB: table `test`.`t1`
InnoDB: in ALTER TABLE ... IMPORT TABLESPACE
Articles
