Sharing of experience on repairing MySQL innodb exceptions

A set of MySQL libraries for testing. The previous version used was MySQL 5.1.71 in the default source of centos6. Later I wanted to try Percona server 5.7, because there was no important data in this library. Therefore, no backup was performed before the operation. After configuring the source, the installation was carried out directly. The data files are also stored in the default location. After the installation is complete, start MySQL directly and find that the startup fails and it is found that it cannot start normally.

1. Roll back and reinstall MySQL

To avoid the trouble of importing this data from other places, first make a backup of the database file of the current library (/var/lib/mysql/ location). Next, I uninstalled the Percona server 5.7 package, reinstalled the old 5.1.71 package, started the mysql service, and it prompted Unknown/unsupported table type: innodb, and could not start normally.

110509 12:04:27 InnoDB: Initializing buffer pool, size = 384.0M
110509 12:04:27 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 157286400 bytes!
110509 12:04:27 [ERROR] Plugin 'InnoDB' init function returned error.
110509 12:04:27 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
110509 12:04:27 [ERROR] Unknown/unsupported table type: innodb
110509 12:04:27 [ERROR] Aborting
110509 12:04:27 [Note] /usr/sbin/mysqld: Shutdown complete

Delete the /var/lib/mysql/ directory, restart the database service, and initialize it. It is normal. The show engines command can find that there is an innodb engine. Stop the database again, overwrite the contents of the previously backed up /var/lib/mysql/ directory with the contents of the current location, and restart. It was found that it could not be started, and the error content was the same as before.

The structure of the /var/lib/mysql directory contents is as follows:

-rw-rw---- 1 mysql mysql 10485760 February 26 18:10 ibdata1
-rw-rw---- 1 mysql mysql 5242880 February 26 18:10 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 February 26 17:20 ib_logfile1
drwx------ 2 mysql mysql 4096 February 26 17:20 mysql
drwx------ 2 mysql mysql 4096 February 26 17:24 wiki

The wiki directory is the library for test data, the ibdata1 file is the data file, the two files starting with ib are log files, and the mysql directory contains things related to the system library. Use the initialized data again and overwrite the wiki directory and ibdata1 file to the /var/lib/mysql directory. You can start and log in normally.

2. Reinstall innodb module

However, when backing up through mysqldump, it prompts "unknow table engine "Innodb". After logging in, I checked all the current engine types and found that there was indeed no innodb type:

I used the alter command to change the type of one of the tables to MyISAM, but found that the error still occurred.

Through find, we found that there is a ha_innodb_plugin.so file in the /usr/lib64/mysql/plugin/ directory. I have the impression that versions after MySQL 5 support online plug-in installation. Check and confirm below, it is indeed supported:

When loading using the following command, it was found to be unsuccessful:

install plugin innodb soname 'ha_innodb.so';

3. Backup

Add the following configuration in /etc/my.cnf:

plugin-load=innodb=ha_innodb_plugin.so
plugin_dir=/usr/lib64/mysql/plugin/
default-storage-engine=InnoDB

It was found that the startup still failed. Checking mysql-error.log, I found the following:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

Open the official page of forcing-innodb-recovery and find that you can force startup and recovery by specifying the innodb_force_recovery parameter. Add the following content to /etc/my.cnf:

innodb_force_recovery=6

The reboot was successful. There is no problem with backing up via mysqldump, and importing the backup data into other hosts also works fine and can be tested.

This is easy to do. Completely delete mysql and reinstall Percona server 5.7. After the installation, create a database, restore the data, reconnect the program, and everything will be OK.

Summarize:

Due to the characteristics of MySQL innodb data files, when problems occur and the system cannot be started normally, you can first move the two log files ./ib_logfile0 and ./ib_logfile1 and then restart. If this fails, you can use the innodb_force_recovery parameter to force recovery. In addition, the log is also very restartable. If you have any questions, read the log first.