MySQL-crash-fix corrupted Innodb:innodb_force_recovery

Ext: 77199194

First, the problem description

A MySQL that runs online today crashes.
Review the error log as follows:

-----------------------------------------161108 11:36:45 Mysqld_safe starting mysqld daemon with databases from/usr/ local/mysql/var2017-08-15 11:36:46 0 [Warning] TIMESTAMP with implicit the DEFAULT value is deprecated. --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-08-15 11:36:46 5497 [ Note] Plugin ' Federated ' is disabled.2017-08-15 11:36:46 7f11c48e1720 InnoDB:Warning:Using Innodb_additional_mem_pool_ Size is DEPRECATED. This option is removed in future releases, together with the option Innodb_use_sys_malloc and with the InnoDB ' s intern Al memory allocator.2017-08-15 11:36:46 5497 [Note] innodb:using Atomics to ref count buffer pool pages2017-08-15 11:36:4 6 5497 [note] innodb:the InnoDB memory heap is disabled2017-08-15 11:36:46 5497 [Note] innodb:mutexes and rw_locks use G CC Atomic builtins2017-08-15 11:36:46 5497 [note] innodb:memory barrier is not used2017-08-15 11:36:46 5497 [note] InnoDB : Compressed tables use Zlib 1.2.32017-08-15 11:36:46 5497 [note] innodb:using CPU crc32 instructions2017-08-15 11:36:46 5497 [note] Innodb:initializing Buffer pool, size = 16.0m2017-08-15 11:36:46 5497 [Note] innodb:completed initialization of buffer poolinnodb:database p Age corruption on disk or a failedinnodb:file read of page 5.innodb:you the May has to recover from a backup.2017-08-15 11: 36:46 7f11c48e1720 Innodb:page dump in ASCII and Hex (16384 bytes): Len 16384; Hex 7478d078000000050000000000000000000000000f271f4d000700000000000000000000000000000000001b4000000000000000000200f2000000000 0000006000000000000002d000000000000002e000000000000002f0000000000000030000000000 (omit many similar codes) Innodb:end of page  dump2017-08-15 11:36:46 7f11c48e1720 innodb:uncompressed page, stored checksum in field1 1954074744, calculated checksums For Field1:crc32 993334256, InnoDB 2046145943, none 3735928559, stored checksum in Field2 1139795846, calculated Checksu Ms for FIELD2:CRC32 993334256, InnoDB 1606613742, none 3735928559, page LSN 0 254222157, low 4 bytes of LSN at page End 254221236, page number (if stored to page already) 5, Space ID (if created W ITH >= MySQL-4.1.1 and stored already) 0innodb:page may a transaction system pageinnodb:database Page corruption o n disk or a failedinnodb:file read of page 5.innodb:you May has to recover from a backup. Innodb:it is also possible that your operatinginnodb:system have corrupted its own file Cacheinnodb:and rebooting your C Omputer removes Theinnodb:error. Innodb:if The corrupt page is a index pageinnodb:you can also try to fix the corruptioninnodb:by dumping, dropping, an d reimportinginnodb:the Corrupt table. You can use the checkinnodb:table to scan your TABLE for corruption. Innodb:see also http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.htmlInnoDB:about forcing recovery. Innodb:ending processing because of a corrupt database page.2017-08-15 11:36:46 7f11c48e1720 innodb:assertion Failure i n thread 139714288817952 in file buf0buf.cc lINE 4201innodb:we intentionally generate a memory trap. Innodb:submit a detailed bug report to http://bugs.mysql.com.InnoDB:If you get repeated assertion failures or crashes, E Veninnodb:immediately after the mysqld startup, there could beinnodb:corruption in the InnoDB tablespace. Please refer toinnodb:http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.htmlinnodb:about forcing Recovery.03:36:46 Utc-mysqld got signal 6; This could is because a bug. It's also possible that's binaryor one of the libraries it was linked against are corrupt, improperly built,or misconfi Gured. This error can also is caused by malfunctioning hardware. We'll try our best to scrape up some info that'll hopefully helpdiagnose the problem, but since we have already crashe D, something is definitely wrong and this may fail.key_buffer_size=16777216read_buffer_size=262144max_used_connections =0max_threads=1000thread_count=0connection_count=0it is possible this mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size) *max_threads = 798063 K bytes of memoryhope that ' s OK; If not, decrease some variables in the equation. Thread pointer:0x0attempting BackTrace. You can use the following information to find Outwhere Mysqld died. If you see no messages after this, something wentterribly wrong...stack_bottom = 0 Thread_stack 0x40000/usr/local/mysql/bi N/mysqld (my_print_stacktrace+0x35) [0x8e64b5]/usr/local/mysql/bin/mysqld (handle_fatal_signal+0x41b) [0x652fbb]/ lib64/libpthread.so.0 (+0XF7E0) [0x7f11c44c77e0]/lib64/libc.so.6 (GSIGNAL+0X35) [0x7f11c315d625]/lib64/libc.so.6 ( abort+0x175) [0x7f11c315ee05]/usr/local/mysql/bin/mysqld[0xa585c5]/usr/local/mysql/bin/mysqld[0xa6c7b4]/usr/ Local/mysql/bin/mysqld[0xa6cbc7]/usr/local/mysql/bin/mysqld[0xa5bce2]/usr/local/mysql/bin/mysqld[0xa1e2ba]/usr /local/mysql/bin/mysqld[0xa0bf60]/usr/local/mysql/bin/mysqld[0x95a427]/usr/local/mysql/bin/mysqld (_Z24ha_ initialize_handlertonp13st_plugin_int+0x48) [0x58f788]/usr/local/mysql/bin/mysqld[0x6e4a36]/usr/local/mysql/bin/mysqld (_z11plugin_initpippci+0xb3e) [0x6e826e]/usr/local/mysql/bin/mysqld [0x582d85]/usr/local/mysql/bin/mysqld (_z11mysqld_mainippc+0x4d8) [0x587d18]/lib64/libc.so.6 (__libc_start_main+ 0XFD) [0x7f11c3149d5d]/usr/local/mysql/bin/mysqld[0x57a019]the manual page at http://dev.mysql.com/doc/mysql/en/ Crashing.html containsinformation that should help you find out what's causing the crash.161108 11:36:46 mysqld_safe mysq LD from PID File/usr/local/mysql/var/vm_241_49_centos.pid Ended------------------------------------------------------------------------------

Second, the problem analysis

It can be seen from the log that the InnoDB engine is out of the question. The log prompts to http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html to view the method of forced recovery. In the MySQL configuration file my.cnf, locate the [mysqld] field, and add the Innodb_force_recovery=1:

[mysqld]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.

• 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 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.

MySQL-crash-fix corrupted Innodb:innodb_force_recovery