Mysql innodb exception repair experience and innodb experience

Mysql innodb exception repair experience and innodb experience

A set of mysql databases for testing. mysql 5.1.71 in centos6 is used by default. Later I wanted to try Percona server 5.7, because this library does not have any important data. Therefore, no backup was performed before the operation. After the source is configured, the installation is performed directly. The data files are also stored in the default location. After the installation is complete, start mysql directly. If the startup fails, the startup will fail.

I. Roll Back and reinstall mysql

To avoid the trouble of importing this data from other places, a backup (/var/lib/mysql/location) is made for the database file of the current database ). Next, uninstall the Percona server 5.7 package, reinstall the old 5.1.71 package, and start the mysql service. The following message is displayed: Unknown/unsupported table type: innodb.

110509 12:04:27 InnoDB: Initializing buffer pool, size = 384.0M110509 12:04:27 InnoDB: Completed initialization of buffer poolInnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytesInnoDB: 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: innodb110509 12:04:27 [ERROR] Aborting110509 12:04:27 [Note] /usr/sbin/mysqld: Shutdown complete

Delete the/var/lib/mysql/directory, restart the Database Service, and initialize it. show engines can find the innodb engine. Stop the database, overwrite the content in the/var/lib/mysql/directory of the previous backup, and restart the database. The error message is the same as that of the previous one.

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

-Rw ---- 1 mysql 10485760 February 26 18:10 ibdata1-rw-rw ---- 1 mysql 5242880 February 26 18:10 ib_logfile0-rw-rw ---- 1 mysql 5242880 February 26 17:20 ib_logfile1drwx ------ 2 mysql 4096 February 26 17:20 mysqldrwx ------ 2 mysql 4096 October 11, February 26 17:24 wiki

The wiki directory is the library for testing data, the ibdata1 file is the data file, the two files starting with ib are log files, and the mysql directory is related to the system library. Use the initialized data again and overwrite the wiki directory and ibdata1 file to the/var/lib/mysql directory. The data can be started or logged on normally.

Ii. innodb module reinstallation

However, the unknow table engine "Innodb" prompt is displayed when mysqldump is used for backup ". After logging on to the console, you can view all the engine types and find that the innodb type does not exist:

If you use the alter command to change the table type to MyISAM, an error is still reported.

Find and find the ha_innodb_plugin.so file in the/usr/lib64/mysql/plugin/directory. Note that Versions later than MySQL 5 support online plug-in installation. Check and confirm through the following, and indeed support:

Failed to load by running the following command:

install plugin innodb soname 'ha_innodb.so';

Iii. Backup

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

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

Failed to start. The mysql-error.log found the following:

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

Go to the forcing-innodb-recovery official page and find that the innodb_force_recovery parameter can be specified for forced start and recovery. Add the following content to/etc/my. cnf:

Innodb_force_recovery = 6

Restart successful. There is no problem with using mysqldump for backup. If you import the backup data to another host, you can test the backup data normally.

In this case, we can completely delete mysql, reinstall Percona server 5.7, create a database, restore data, and reconnect the program. Everything is OK.

Summary:

Due to the features of mysql innodb data files, you can set. /ib_logfile0 and. /ib_logfile1: Two log files are removed first and then started. If not, use the innodb_force_recovery parameter for forced recovery. In addition, the log is also restarted. If you have any problems, check the log first.