Suite à un crash serveur, la base de données MySQL ne démarrais plus avec le message d'erreur suivant : Your database may be corrupt or you may have copied the InnoDB. Nous allons voir dans cet article comment corriger cette erreur.
Problème rencontré
1) Redémarrage du service mysqld impossible :
[root@SFEYLP06 ~]# systemctl restart mysqld
Job for mariadb.service failed because a fatal signal was delivered to the control process. See "systemctl status mariadb.service" and "journalctl -xe" for details.
2) Affichage des derniers logs pour diagnostiquer le problème :
[root@SFEYLP06 ~]# tail -f /var/log/messages
Sep 12 01:05:22 SFEYLP06 mysqld: 2019-09-12 01:05:22 7ff7b3bfd700 InnoDB: Error: page 791 log sequence number 13945130376
Sep 12 01:05:22 SFEYLP06 mysqld: InnoDB: is in the future! Current system log sequence number 13945106720.
Sep 12 01:05:22 SFEYLP06 mysqld: InnoDB: Your database may be corrupt or you may have copied the InnoDB
Sep 12 01:05:22 SFEYLP06 mysqld: InnoDB: tablespace but not the InnoDB log files. See
Sep 12 01:05:22 SFEYLP06 mysqld: InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
Sep 12 01:05:22 SFEYLP06 mysqld: InnoDB: for more information.
Solution
Pour corriger le problème , il suffit de déplacer les fichiers se nommant ib_logfileX et de redémarrer le service mysqld.
1) Se connecter sur le serveur en ayant les autorisations nécessaires.
2) Afficher le contenu du répertoire stockant les données de la BDD.
[root@SFEYLP06 ~]# ls /var/lib/mysql/
aria_log.00000001 aria_log_control centreon centreon_storage ddl_log.log ibdata1 ib_logfile0 ib_logfile1 multi-master.info mysql performance_schema SFEYLP06.pid tc.log test
3) Déplacer les fichiers ib_logfileX dans /tmp
[root@SFEYLP06 ~]# cd /var/lib/mysql/ [root@SFEYLP06 mysql]# mv ib_logfile* /tmp/
4) Redémarrer le service mysqld :
[root@SFEYLP06 mysql]# systemctl restart mariadb.service
5) Finalement, utiliser la commande suivante pour afficher l'état du service mysqld :
[root@SFEYLP06 mysql]# systemctl status mariadb.service ● mariadb.service - MariaDB 10.1.38 database server Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled) Drop-In: /etc/systemd/system/mariadb.service.d └─centreon.conf, limits.conf, migrated-from-my.cnf-settings.conf Active: active (running) since jeu. 2019-09-12 01:08:06 CEST; 1s ago Docs: man:mysqld(8) https://mariadb.com/kb/en/library/systemd/ Process: 4020 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 3953 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS) Process: 3951 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Main PID: 3992 (mysqld) Status: "Taking your SQL requests now..." CGroup: /system.slice/mariadb.service └─3992 /usr/sbin/mysqld sept. 12 01:08:06 SFEYLP06 systemd[1]: Stopped MariaDB 10.1.38 database server. sept. 12 01:08:06 SFEYLP06 systemd[1]: Starting MariaDB 10.1.38 database server... sept. 12 01:08:06 SFEYLP06 systemd[1]: Started MariaDB 10.1.38 database server.