1) Introduction.
Dans ce didacticiel, nous allons traité un problème que l'on rencontre parfois avec la version 5.6 de MySql, suite à la destruction accidentielle ou encore la corruption du fichier ibdata1.
2) Un conseil très avisé.
Il semble que la corruption du fichier "ibdata1" vient d'une pratique désastrueuse, qui lors d'un changement de version, soit de WampServer, soit de MySql, consiste à venir bidouiller dans le répertoire "/data".
Lorsque vous migrez d'une ancienne version vers une nouvelle version, vous ne devez jamais, et j'insiste lourdement sur ce point, intervenir dans le répertoire "/data" pour récupérer vos bases de données.
Ce n'est pas ainsi que l'on fait une migration !
La bonne démarche consiste à faire une sauvegarde de toutes vos bases de données applicatives puis ensuite à les restaurer.
Et vous allez me dire, comment fait-on une sauvegarde ? Suivez les explications ci-après :
Vous vous rendez dans votre PhpMyAdmin de votre ancienne version WampServer ou MySql.
Vous séléctionnez votre base de données en cliquant dans la colonne de gauche sur le nom de celle-ci.
Ensuite, vous cliquez sur l'onglet "export" afin de faire votre sauvegarde.
Pour chaque base de données applicatives, vous recommencez cette procédure de sauvegarde.
Vous sortez de PhpMyAdmin de votre ancienne version.
Maintenant vous pouvez basculer vers votre nouvelle version WampServer ou MySql.
Vous vous rendez dans votre PhpMyAdmin de votre nouvelle version.
Puis, vous cliquez sur l'onglet "import" afin de faire la restauration de votre base de données applicative.
Vous recommencez cette dernière étape autant de fois que vous avez de bases de données applicatives.
C'est ainsi que l'on travaille pour migrer une base de données d'une ancienne version vers une nouvelle version.
On ne bidouille pas dans le coeur de MySql. Normalement, chez un hébergeur, vous n'avez pas accès au répertoire "/data" et de ce fait, la bonne pratique est celle que je viens de vous décrire.
En plus, vous pouvez rencontrer des problèmes de compatibilités entre l'ancienne et la nouvelle version de MySql si vous remplacez le répertoire "/data".
3) Les messages d'anomalies.
Une mauvaise manipulation et vous venez d'écraser, ou de détruire le fichier ibdata1 qui se trouve dans le répertoire "/mysql5.6.23/data", et vos tables InnoDb ne sont plus reconnues dans MySql.
Vous allez dans le fichier "mysql_error.log" et vous avez des messages comme ci-après :
2015-02-21 02:49:22 4476 [Warning] InnoDB: Cannot open table mysql/slave_master_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2015-02-21 02:49:22 4476 [Warning] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened. 2015-02-21 02:49:22 4476 [Warning] InnoDB: Cannot open table mysql/slave_worker_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2015-02-21 02:49:22 4476 [Warning] InnoDB: Cannot open table mysql/slave_relay_log_info from the internal data dictionary of InnoDB though the .frm file for the table exists. See http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting.html for how you can resolve the problem. 2015-02-21 02:49:22 4476 [Warning] Info table is not ready to be used. Table 'mysql.slave_relay_log_info' cannot be opened.
Les tables sont bien présentes dans le répertoires "/data/mysql" et pourtant vous avez ce messages d'anomalies. En fait, elles sont absentes dans le fichier ibdata1 qui se trouve dans le répertoire "/data".
L'anomalie n'est pas anodine car vous avez perdu tout le descriptif des tables InnoDB dans le coeur de votre SGBDR MySql, au travers du fichier ibdata1.
4) Comment résoudre ce problème ?
Rien de plus à facile, il suffit de réinstaller toutes les tables InnoDB, comme si c'était la première fois !
Mais avant de procéder ainsi, vous devez remettre à zéro le fichier ibdata1 qui est source de notre anomalie, en le détruisant.
Oui, mais voila, vous n'avez pas le descriptif de ces tables qui vous pose problème, et vous ne savez pas où les trouver.
5) Où trouver le descriptif de ces tables ?
Je pourrais vous donner la solution, mais ne sachant pas sur quelle version de MySql vous vous trouvez, ces descriptifs de tables pourraient ne plus être en phase.
D'ailleurs, la première fois que j'ai rencontré ce problème, j'ai trouvé un didacticiel qui me donnait ces descriptifs, que j'ai installé sans trop me poser des questions. Et voila qu'une de ces tables étaient encore en anomalie.
Mais avant de faire quoi que ce soit, nous devons d'abord identifier nos tables.
6) Identifier les tables InnoDB !
Ci-après, le batch qui va déclencher la commande sql :
@echo off chcp 1252 > nul set PATH=.;%WAMPSERVER%\bin\mysql\mysql5.6.23\bin\;%PATH% @echo "+---------------------------+" @echo "| Liste des Tables 'InnoDB' |" @echo "+---------------------------+" @echo. mysql mysql < innodb.sql @echo. pause exit
La requête "select" se trouve dans le fichier "innodb.sql" que voiçi :
select table_name from information_schema . tables where table_schema = 'mysql' and engine = 'InnoDB' ;
Et voici le résultat que j'obtiens dans la version 5.6.23 du MySql qui est installé dans WampServer.
+----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table_stats | | slave_master_info | | slave_relay_log_info | | slave_worker_info | +----------------------+ 5 rows in set (0.00 sec)
Nous avons cinq tables dans la base de données "mysql" qui sont de type "InnoDB".
7) Comment récupérer le descriptif de ces tables !
Il suffit de faire pour chacune de ces tables un "export". Nous nous proposons de le faire en ligne de commande, avec l'utilitaire mysqldump.
7-A) L'utilitaire mysqldump !
Voici un exemple d'utilisation de cet utilitaire pour récupérer le descriptif de nos tables.
@echo off chcp 1252 > nul set PATH=%WAMPSERVER%\bin\mysql\mysql5.6.23\bin\;%PATH% @echo "+--------------------------+" @echo "| Export DataBase 'InnoDB' |" @echo "+--------------------------+" @echo. mysqldump --no-create-db --opt --tables mysql innodb_index_stats --result-file=innodb_index_stats.sql @echo. mysqldump --no-create-db --opt --tables mysql innodb_table_stats --result-file=innodb_table_stats.sql @echo. mysqldump --no-create-db --opt --tables mysql slave_master_info --result-file=slave_master_info.sql @echo. mysqldump --no-create-db --opt --tables mysql slave_relay_log_info --result-file=slave_relay_log_info.sql @echo. mysqldump --no-create-db --opt --tables mysql slave_worker_info --result-file=slave_worker_info.sql @echo. pause exit
Je tiens à préciser que je n'ai pas mis dans ces lignes de commande, le user + password, ni l'hôte. Afin de rendre cet accès standard, j'ai placé ses informations dans mon fichier my.ini de configuration de MySql.
Pour résoudre ce problème, vous devez ajouter ce qui est en rouge, comme dans la ligne ci-après :
mysqldump --user=root --password=toor --host=localhost --no-create-db --opt --tables mysql innodb_index_stats
--result-file=innodb_index_stats.sql
7-B) Le résultat de l'exécution !
De notre tentative d'exportation du descriptif de nos tables, nous obtenons le résultat ci-après :
"+--------------------------+" "| Export DataBase 'InnoDB' |" "+--------------------------+" mysqldump: Got error: 1146: La table 'mysql.innodb_index_stats' n'existe pas when doing LOCK TABLES mysqldump: Got error: 1146: La table 'mysql.innodb_table_stats' n'existe pas when doing LOCK TABLES mysqldump: Got error: 1146: La table 'mysql.slave_master_info' n'existe pas when doing LOCK TABLES mysqldump: Got error: 1146: La table 'mysql.slave_relay_log_info' n'existe pas when doing LOCK TABLES mysqldump: Got error: 1146: La table 'mysql.slave_worker_info' n'existe pas when doing LOCK TABLES Appuyez sur une touche pour continuer...
C'est tout à fait normal, puisque ces tables n'existe pas dans le fichier ibdata1, d'où la raison de ce didacticiel !
7-C) Comment résoudre ce problème ?
Vous devez installer le composant MySql d'origine, juste pour extraire le descriptif de nos cinq tables.
Voici la démarche à suivre :
Arrêter WampServer.
Renommer le répertoire "/mysql5.6.23" en "/mysql5.6.23Bis".
Télécharger la version 5.6.23 de MySql. Vous devez savoir le faire !
Vous dézippez le fichier.
Vous copiez le fichier dans le répertoire "/Wamp/bin/mysql".
Vous le renommer en "/mysql5.6.23".
Redémarrer WampServer.
Normalement, vous avez accès à votre SGBD MySql.
J'espère pour vous que vous avez assez d'espace sur votre disque dur, car cette version MySql fait environ 1,52 Go.
Pour accéder à phpMyAdmin, vous devez le faire sans mot de passe, puisse que vous avez la version MySql d'origine.
7-D) Nouvelle tentative !
Cette fois-çi, notre tentative fonctionne et nous obtenons cinq fichiers contenant le descriptif de nos cinq table. À titre indicatif, je vous communique le résultat de ces cinq fichiers. J'ai fait un peu de nettoyage afin de conserver la partie qui nous intéresse.
Si vous avez une autre version que la version 5.6.23, je vous conseille de procéder à la récupération comme je vous l'ai indiqué.
Voici le descriptif de la table "mysql.innodb_index_stats" :
--
-- Table structure for table `innodb_index_stats`
--
DROP TABLE IF EXISTS `innodb_index_stats`;
CREATE TABLE `innodb_index_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`index_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
`stat_value` bigint(20) unsigned NOT NULL,
`sample_size` bigint(20) unsigned DEFAULT NULL,
`stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
Vous pouvez récupérer le script de la table "innodb_index_stats" en cliquant sur ce lien.
Voici le descriptif de la table "mysql.innodb_table_stats" :
--
-- Table structure for table `innodb_table_stats`
--
DROP TABLE IF EXISTS `innodb_table_stats`;
CREATE TABLE `innodb_table_stats` (
`database_name` varchar(64) COLLATE utf8_bin NOT NULL,
`table_name` varchar(64) COLLATE utf8_bin NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`n_rows` bigint(20) unsigned NOT NULL,
`clustered_index_size` bigint(20) unsigned NOT NULL,
`sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
Vous pouvez récupérer le script de la table "innodb_table_stats" en cliquant sur ce lien.
Voici le descriptif de la table "mysql.slave_master_info" :
--
-- Table structure for table `slave_master_info`
--
DROP TABLE IF EXISTS `slave_master_info`;
CREATE TABLE `slave_master_info` (
`Number_of_lines` int(10) unsigned NOT NULL COMMENT 'Number of lines in the file.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
COMMENT 'The name of the master binary log currently being read from the master.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last read event.',
`Host` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '' COMMENT 'The host name of the master.',
`User_name` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The user name used to connect to the master.',
`User_password` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The password used to connect to the master.',
`Port` int(10) unsigned NOT NULL COMMENT 'The network port used to connect to the master.',
`Connect_retry` int(10) unsigned NOT NULL
COMMENT 'The period (in seconds) that the slave will wait before trying to reconnect to the master.',
`Enabled_ssl` tinyint(1) NOT NULL COMMENT 'Indicates whether the server supports SSL connections.',
`Ssl_ca` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Authority (CA) certificate.',
`Ssl_capath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path to the Certificate Authority (CA) certificates.',
`Ssl_cert` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL certificate file.',
`Ssl_cipher` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the cipher in use for the SSL connection.',
`Ssl_key` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The name of the SSL key file.',
`Ssl_verify_server_cert` tinyint(1) NOT NULL COMMENT 'Whether to verify the server certificate.',
`Heartbeat` float NOT NULL,
`Bind` text CHARACTER SET utf8 COLLATE utf8_bin
COMMENT 'Displays which interface is employed when connecting to the MySQL server',
`Ignored_server_ids` text CHARACTER SET utf8 COLLATE utf8_bin
COMMENT 'The number of server IDs to be ignored, followed by the actual server IDs',
`Uuid` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The master server uuid.',
`Retry_count` bigint(20) unsigned NOT NULL COMMENT 'Number of reconnect attempts, to the master, before giving up.',
`Ssl_crl` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)',
`Ssl_crlpath` text CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files',
`Enabled_auto_position` tinyint(1) NOT NULL COMMENT 'Indicates whether GTIDs will be used to retrieve events from the master.',
PRIMARY KEY (`Host`,`Port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Master Information';
Vous pouvez récupérer le script de la table "slave_master_info" en cliquant sur ce lien.
Voici le descriptif de la table "mysql.slave_relay_log_info" :
--
-- Table structure for table `slave_relay_log_info`
--
DROP TABLE IF EXISTS `slave_relay_log_info`;
CREATE TABLE `slave_relay_log_info` (
`Number_of_lines` int(10) unsigned NOT NULL
COMMENT 'Number of lines in the file or rows in the table. Used to version table definitions.',
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'The name of the current relay log file.',
`Relay_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The relay log position of the last executed event.',
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
COMMENT 'The name of the master binary log file from which the events in the relay log file were read.',
`Master_log_pos` bigint(20) unsigned NOT NULL COMMENT 'The master log position of the last executed event.',
`Sql_delay` int(11) NOT NULL COMMENT 'The number of seconds that the slave must lag behind the master.',
`Number_of_workers` int(10) unsigned NOT NULL,
`Id` int(10) unsigned NOT NULL COMMENT 'Internal Id that uniquely identifies this record.',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Relay Log Information';
Vous pouvez récupérer le script de la table "slave_relay_log_info" en cliquant sur ce lien.
Voici le descriptif de la table "mysql.slave_worker_info" :
--
-- Table structure for table `slave_worker_info`
--
DROP TABLE IF EXISTS `slave_worker_info`;
CREATE TABLE `slave_worker_info` (
`Id` int(10) unsigned NOT NULL,
`Relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Relay_log_pos` bigint(20) unsigned NOT NULL,
`Master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Master_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_relay_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Checkpoint_relay_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_master_log_name` text CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
`Checkpoint_master_log_pos` bigint(20) unsigned NOT NULL,
`Checkpoint_seqno` int(10) unsigned NOT NULL,
`Checkpoint_group_size` int(10) unsigned NOT NULL,
`Checkpoint_group_bitmap` blob NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0 COMMENT='Worker Information';
Vous pouvez récupérer le script de la table "slave_worker_info" en cliquant sur ce lien.
7-E) On revient à la version précédente !
Maintenant que nous avons le descriptif de nos cinq tables, nous pouvons supprimer le répertoire "/mysql5.6.23" qui ne nous sert plus à rien.
Avant de procéder à cette destruction définitive, je vous conseille de recopier le fichier /data/ibdata1 qui pourra vous servir pour une des solutions que je vous propose.
Pour terminer, vous renommer le répertoire "/mysql5.6.23Bis" en "/mysql5.6.23" afin de revenir dans votre environnement de départ, puis vous redémarrer WampServer.
8) Comment devons-nous procéder ?
Plusieurs solutions se présentent à nous. Nous allons détailler chacune d'elle, ci-après.
8-A) Réinstaller le fichier d'origine ibdata1 !
Puisque nous avons corrompu ce fichier, il suffit de remettre la version d'origine car elle possède la configuration de nos cinq tables.
La directive qui gère le fichier ibdata1 dans le fichier my.ini est :
innodb_data_file_path = ibdata1:10M:autoextend
Nous pouvons rencontrer un problème en ce qui concerne la taille de ce fichier dans le fichier my.ini. En cliquant avec le bouton droite de la souris, puis sur propriété, on connait alors sa taille exacte.
Il suffit alors de reporter cette taille dans la ligne ci-dessus. Une taille trop importante peut causer une anomalie. À l'inverse, une taille plus petite ne produit pas d'anomalie, certainement à cause du paramètre "autoextend".
À chaque problème similaire, vous devrez réinstaller ce fichier ibdata1, sans réinstaller les cinq tables, puisqu'elles s'y trouvent déjà.
Si vous avez choisi cette solution, maintenant vous devez vous rendre au paragraphe 8-D).
8-B) Mettre ces tables en "MyIsam" !
Si vous n'utilisez pas ces fichiers alors il suffit de replacer "engine=InnoDB" par "engine=MyIsam" dans le descriptif ci-dessus.
Ainsi le problème que nous avons ne se reproduira plus. Mais il est nécessaire de les réinstaller, comme nous allons le voir dans le paragraphe suivant.
8-C) Procéder à la réinstallation de nos cinq tables !
Nous disposons du descriptif de nos cinq tables, avec ou sans la modification du paragraphe précédent.
Nnous devons respecter les étapes suivantes :
Arrêter WampServer.
Supprimer le fichier ibdata1.
Pour chacune de nos cinq tables, vous devez aller dans le répertoire "/data/mysql" et supprimer les fichiers ayant l'extensions ".ibd" et ".frm".
Redémarrer WampServer. Le fichier ibdata1 a été reconstruit, mais à vide.
Pour chaque descriptif, vous devez réinstaller la table dans le SGBDR MySql. Voici un exemple de ce qu'il faut faire :
@echo off chcp 1252 > nul set PATH=%WAMPSERVER%\bin\mysql\mysql5.6.23\bin\;%PATH% mysql mysql < Import\innodb_index_stats.sql @echo. mysql mysql < Import\innodb_table_stats.sql @echo. mysql mysql < Import\slave_master_info.sql @echo. mysql mysql < Import\slave_relay_log_info.sql @echo. mysql mysql < Import\slave_worker_info.sql @echo. pause exit
Chaque script sql est placé dans un sous-répertoire de nom "/import". Je rappelle que ce sont le descriptifs de nos cinq tables de la base de données "MySql" de type InnoDB.
À la fin de cette opération, vous devez "redémarrer les services" de WampServer.
En consultant le fichier "mysql error.log", vous ne devez plus obtenir les messages d'anomalies du début de ce didactoriel.
C'est terminé, votre problème a été résolu !
8-D) Que reste-t-il à faire ?
Si vous avez d'autres tables InnoDB, vous devez aussi les réinstaller. Je tiens à signaler la nécessité d'avoir une sauvegarde de toutes vos bases de données que vous utilisez.
Pour ce faire, le plus simple est d'utiliser l'onglet "export" dans phpMyAdmin. Après tout, vous êtes en développement, et votre jeu d'essai est rudimentaire. La réinstallation ne prendra que quelques secondes.
La procédure à suivre est assez similaire que précédemment.
Vous devez détruire les sous-répertoires de toutes vos bases de données dans le répertoire "/data".
Vous réinstallez toutes vos bases de données InnoDB avec l'utilitaire MySql.
"redémarrer les services" de WampServer.
Normalement, l'opération est terminée.
9) Conclusion !
L'idée de ce didacticiel repose sur deux opérations, à savoir :
Destruction du fichier ibdata1 afin de repartir d'un fichier vide.
Réinstallation de toutes vos tables ou bases de données InnoDB.
À l'issu de cette manipulation, vous ne devez pas rencontrer d'autres problèmes avec votre fichier ibdata1.
Un conseil avisé : ne bidouillez pas dans le répertoire "/data" si vous ne maitrisez pas encore MySql.
J'insiste sur ce points, faites des sauvegardes de vos bases de données.