Shulou(Shulou.com)05/31 Report--

This article mainly explains "innodb_force_recovery parameter analysis in mysql". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "innodb_force_recovery parameter analysis in mysql".

I. description of the problem

A mysql running online today crashed.

View the error log as follows:

-- 161108 11:36:45 mysqld_safe Starting mysqld daemon with databases from / usr/local/mysql/var 2017-08-15 11:36:46 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-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 may be removed in future releases Together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.2017-08-15 11:36:46 5497 [Note] InnoDB: Using atomics to ref count buffer pool pages2017-08-15 11:36:46 5497 [Note] InnoDB: The InnoDB memory heap is disabled2017-08-15 11:36:46 5497 [Note] InnoDB: Mutexes and rw_locks use GCC 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 page corruption on disk or a failedInnoDB: file read of page 5.InnoDB: You may have to recover from a backup.2017-08-15 11:36:46 7f11c48e1720 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384 Hex 7478d078000000050000000000000000000000000f271f4d000700000000000000000000000000000000001b4000000000000000000200f20000000000000006000000000000002d000000000000002e000000000000002f0000000000000030000000000 (omitting 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 checksums for field2: crc32 993334256, innodb 1606613742, none 3735928559, page LSN 0254222157, low 4 bytes of LSN at page end 254221236, page number (if stored to page already) 5 Space id (if created with > = MySQL-4.1.1 and stored already) 0InnoDB: Page may be a transaction system pageInnoDB: Database page corruption on disk or a failedInnoDB: file read of page 5.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.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 in 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, evenInnoDB: immediately after the mysqld startup There may 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 be because you hit a bug. It is also possible that this binaryor one of the libraries it was linked against is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.We will try our best to scrape up some info that will hopefully helpdiagnose the problem, but since we have already crashed, 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 that 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: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack_bottom = 0 thread_stack 0x40000 / usr/local/mysql/bin/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 contains information that should help you find out what is causing the crash. 161108 11:36:46 mysqld_safe mysqld from pid file / usr/local/mysql/var/VM_241_49_centos.pid ended-II. Problem analysis

You can see from the log that there is something wrong with the innodb engine. The log prompts you to go to http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html to see how to force recovery. In the configuration file my.cnf of mysql, under the "mysqld" field, add innodb_force_recovery=1:

[mysqld] innodb_force_recovery = 1

If innodb_force_recovery = 1 does not work, you can try a few numbers of 2Murray 6.

Then restart mysql and restart 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.

Parameter of 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 are performed when recovery is needed (that is, check data page / purge undo/insert buffer merge/rolling back&forward). When a valid recovery operation cannot be performed, mysql may not be able to start and log errors

Innodb_force_recovery can be set to 1-6, and large numbers contain the effects of all previous numbers. When the setting parameter value is greater than 0, you can select,create,drop the table, but operations such as insert,update or delete are not allowed.

1 (SRV_FORCE_IGNORE_CORRUPT): ignore checked corrupt pages.

2 (SRV_FORCE_NO_BACKGROUND): blocking the running of the main thread, if the main thread needs to perform full purge operations, will result in crash.

3 (SRV_FORCE_NO_TRX_UNDO): no transaction rollback operation is performed.

4 (SRV_FORCE_NO_IBUF_MERGE): merge operations that insert buffers are not performed.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN): without viewing the redo log, the InnoDB storage engine treats uncommitted transactions as committed.

6 (SRV_FORCE_NO_LOG_REDO): roll forward is not performed.

III. Analytical scheme

General repair methods refer to:

The first method

Create a new table:

Create table demo_bak # is the same as the original table structure, except that it changes INNODB to MYISAM.

Import the data

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

Another way is to use mysqldump to export the table and then import it back into the InnoDB table. The results of the 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

Export the SQL script. Or use Navicat to import all databases / tables into databases on other servers.

Note: the data here must be backed up successfully. Then delete the data in the original database.

3. Delete ib_logfile0, ib_logfile1, ibdata1

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

4. Configure my.cnf

Delete or configure the line with innodb_force_recovery = 1 or 2murmur6 in my.cnf to innodb_force_recovery = 0, and restart the MySQL service.

5. Import data into MySQL database

Mysql-uroot-p123 test < test.sql; or import the backed-up data into the database using Navicat.

Problems that should be paid attention to in this method:

1. Ib_logfile0, ib_logfile1 and ibdata1 must be backed up before deletion.

2. Make sure that the original data is exported successfully.

3. When the data is exported successfully, when you delete the data in the original database, if the prompt cannot be deleted, you can enter the MySQL data directory on the command line and manually delete the folder of the relevant database or the data table file under the database folder, as long as the data must be exported or backed up successfully.

Thank you for your reading, the above is the content of "innodb_force_recovery parameter analysis in mysql". After the study of this article, I believe you have a deeper understanding of the problem of innodb_force_recovery parameter analysis in mysql, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.