Fix crashes for MySQL

In the MySQL configuration file my.cnf, locate the [mysqld] field, add innodb_force_recovery = 1

If Innodb_force_recovery = 1 does not take effect, you can try to 2--6 several numbers
Then restart MySQL and reboot successfully. Then use mysqldump or PMA to export data, perform repair operations, and so on. After the repair is complete, comment out the parameter and restore the default value of 0.
Parameters for the configuration file: Innodb_force_recovery
Innodb_force_recovery affects the recovery status of the entire InnoDB storage engine. The default is 0, which means that all recovery operations (that is, verifying data page/purge Undo/insert buffer merge/rolling back&forward) are performed when recovery is required, and MySQL may fail to start when a valid recovery operation is not possible. and record the error log;
The innodb_force_recovery can be set to 1-6, and the large number contains the effect of all previous numbers. When you set a parameter value greater than 0, you can perform a select,create,drop operation on the table, but the insert,update or delete operation is not allowed.

Specific introduction

1 (srv_force_ignore_corrupt): Ignores the corrupt page that is checked.

2 (Srv_force_no_background): Prevents the main thread from running, as the main thread needs to perform full purge operations, which can cause crash.

3 (Srv_force_no_trx_undo): Transaction rollback operation is not performed.

4 (Srv_force_no_ibuf_merge): Do not perform insert buffer merge operation.

5 (Srv_force_no_undo_log_scan): Do not look for the log, InnoDB storage engine will treat uncommitted transactions as committed.

6 (Srv_force_no_log_redo): Do not roll forward operations.

Three, the analytic scheme

General Repair Method Reference:

The first of these methods

Create a new table:
CREATE TABLE Demo_bak #和原表结构一样, just changed InnoDB to MyISAM.
Get the data in.
INSERT INTO Demo_bak select * from demo;
Delete the original table:
drop table demo;
After commenting out the Innodb_force_recovery, restart.
Rename:
Rename table Demo_bak to demo;
Finally, change back to the storage engine:
ALTER TABLE Demo engine = InnoDB

The second method of

Another way is to export the table using mysqldump and then back to the InnoDB table. The results of these two methods are the same.
Backup export (including structure and data):
mysqldump-uroot-p123 Test > Test.sql
Restore Method 1:
Use test;
SOURCE Test.sql
Restore Method 2 (System command line):
Mysql-uroot-p123 Test < Test.sql;
Note that the CHECK Table command is essentially useless in the InnoDB database.

The third method of

1, Configuration my.cnf

Configure Innodb_force_recovery = 1 or 2--6 several numbers to restart MySQL

2. Export Data Script

mysqldump-uroot-p123 Test > Test.sql
Export the SQL script. Or, use Navicat to import all databases/tables into a database of other servers.
Note: The data here must be backed up successfully. Then delete the data from the original database.

3. Delete Ib_logfile0, Ib_logfile1, ibdata1

Back up the MySQL data directory ib_logfile0, Ib_logfile1, ibdata1 three files, and then delete the three files

4, Configuration My.cnf

MY.CNF innodb_force_recovery = 1 or 2--6 a few digits of this line configuration is removed or configured to Innodb_force_recovery = 0, restart the MySQL service

5. Import data into MySQL database

Mysql-uroot-p123 Test < Test.sql; Or, use Navicat to import the backed up data into the database.
Problems to be aware of in this way:
1, Ib_logfile0, Ib_logfile1, ibdata1 these three files must first be backed up after deletion;
2, must confirm the original data export success
3, when the data export is successful, delete the data in the original database, if the prompt can not be deleted, you can enter the MySQL data directory at the command line, manually delete the relevant database folder or database folder data table file, if the data must be exported or backed up successfully.

Fix crashes for MySQL