Once MySQL hangs for no reason, using innodb_force_recovery repair

Recently encountered a strange problem, in everyone in the NAP, suddenly the phone rang up, I do not wake up the other people picked up the phone to see the monitoring information, I went, incredibly is the database down, this is a long running database server, when I log in the server, try to restart MySQL , but the newspaper (starting MySQL ..... error! The server quit without updating PID file (/usr/local/mysql/data/bigdata_zt_py_92.pid).) error, then go to the error log and other troubleshooting methods, In the troubleshooting period suddenly to monitor the alarm, prompted XXX host has just been restarted, I tried to ping the host results ping, I was on the spot, the server on the end of their own restart, and after a continuous restart several times. Finally contact the computer room personnel, help connect the display to see what situation.

After a toss-up, the machine finally got up, we began to troubleshoot. Viewing the error log discovery

Innodb:end of page Dump

2018-05-23 21:10:08 7f6786710700 innodb:uncompressed page, stored checksum in field1 2222046951, calculated checksums for Field1:crc32 2624418990, InnoDB 12552

80539, none 3735928559, stored checksum in Field2 1914065653, calculated checksums for Field2:crc32 2624418990, InnoDB 30 45085343, none 3735928559, page LSN 555

2748030571, low 4 bytes of LSN in page end 2748030571, page number (if stored to page already) 84692, Space ID (if created With >= MySQL-4.1.1 and stored alread

Y) 2618

Innodb:page may a index Page where index ID is 8005

Innodb:database page corruption on disk or a failed

Innodb:file read of page 84692.

Innodb:you May has to recover from a backup.

Innodb:it is also possible that your operating

Innodb:system have 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. can use CHECK

Innodb:table to scan your TABLE for corruption.

Innodb:see also http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

Innodb:about forcing recovery.

Innodb:ending processing because of a corrupt database page.

2018-05-23 21:10:08 7f6786710700 innodb:assertion failure in thread 140082613913344 in file buf0buf.cc line 4201

Innodb: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, even

Innodb:immediately after the mysqld startup, there may

Innodb:corruption in the InnoDB tablespace. Refer to

Innodb:http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html

Innodb:about forcing recovery.

13:10:08 Utc-mysqld got signal 6;

This could is because a bug. It is also possible the this binary

Or one of the libraries it was linked against IS corrupt, improperly built,

Or misconfigured. This error can also is caused by malfunctioning hardware.

We'll try our best to scrape up some info, that'll hopefully help

Diagnose the problem, but since we have already crashed,

Something is definitely wrong and this may fail.

key_buffer_size=8388608

read_buffer_size=131072

Max_used_connections=0

max_threads=1024

Thread_count=0

Connection_count=0

It is possible this mysqld could use

Key_buffer_size + (read_buffer_size + sort_buffer_size) *max_threads = 415416 K bytes of memory

Hope 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

63/usr/local/mysql/bin/mysqld (MY_PRINT_STACKTRACE+0X2C) [0x8f339c]

/usr/local/mysql/bin/mysqld (handle_fatal_signal+0x364) [0x66e3e4]

/lib64/libpthread.so.0 (+0XF5E0) [0X7F6B9C5B45E0]

/lib64/libc.so.6 (GSIGNAL+0X37) [0X7F6B9B3BA1F7]

/lib64/libc.so.6 (abort+0x148) [0x7f6b9b3bb8e8]

/USR/LOCAL/MYSQL/BIN/MYSQLD[0XA9C5C5]

/USR/LOCAL/MYSQL/BIN/MYSQLD[0XADECD6]

/USR/LOCAL/MYSQL/BIN/MYSQLD[0XA400C8]

/lib64/libpthread.so.0 (+0X7E25) [0x7f6b9c5ace25]

/lib64/libc.so.6 (CLONE+0X6D) [0x7f6b9b47d34d]

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains

Information that should help you find out what's causing the crash.

180523 21:10:09 mysqld_safe mysqld from PID File/usr/local/mysql/data/bigdata_zt_py_92.pid ended

180523 21:44:59 Mysqld_safe starting mysqld daemon with databases From/usr/local/mysql/data

2018-05-23 21:44:59 0 [Warning] TIMESTAMP with implicit the DEFAULT value is deprecated. --explicit_defaults_for_timestamp server option (see documentation for more details).

The above can be seen that the information is the rollback of information error, and then to check the information found that the binary files may be corrupted.

Later decided to use forced InnoDB recovery,

Here's how to use the parse:

[Mysqld]

Innodb_force_recovery = 1

Warning

You can start InnoDB and dump the table only if the innodb_force_recovery is set to a value greater than 0 in an emergency situation. Before you do this, make sure that you have a backup copy of the database in case you need to rebuild it. Values 4 and above can permanently destroy the data file. Only the independent physical copy of the database has successfully tested the settings to use the Innodb_force_recovery settings of 4 and above in the production server instance. When forcing InnoDB recovery, you should always start with innodb_force_recovery=1 and increase the value only when needed.

Innodb_force_recovery defaults to 0 (normal startup without forced recovery). Non-0 values allowed for Innodb_force_recovery are 1 to 6. Larger values include features with smaller values. For example, a value of 3 includes all the values 1 and 2 of the functionality.

If you can dump your table with innodb_force_recovery of 3 or lower, then you are more secure and only some of the data on the corrupted personal page will be lost. A value of 4 or greater is considered dangerous because the data file can be permanently corrupted. A value of 6 is considered critical, and the database page is left in a stale state, which in turn may bring more damage to b-trees and other database structures.

As a security measure, InnoDB blocks insert,update or delete operations at Innodb_force_recovery greater than 0 o'clock. For MySQL5.6.15, setting Innodb_force_recovery to 4 or higher causes InnoDB to be in read-only mode.

1 (srv_force_ignore_corrupt)

Let it run even if the server detects that the damaged page is still running. Attempts to make select* from tbl_name skip corrupt index records and pages, which helps to dump tables.

2 (Srv_force_no_background)

Prevents the main thread and any cleanup threads from running. If a crash occurs during a purge operation, the recovery value will block it.

3 (Srv_force_no_trx_undo)

Do not run a transaction rollback after a crash recovery.

4 (Srv_force_no_ibuf_merge)

Prevents the Insert Buffer merge operation. If they can cause crashes, do not do this. Table statistics are not calculated. This value can permanently damage the data file. With this value, the prepare sign deletes and rebuilds all secondary indexes. In MySQL5.6.15, set InnoDB to read-only.

5 (Srv_force_no_undo_log_scan)

Do not view undo log when starting the database: InnoDB will be committed even if the unfinished transaction is also submitted. This value can permanently damage the data file. In MySQL5.6.15, set InnoDB to read-only.

6 (Srv_force_no_log_redo)

Do not roll forward the redo log by resuming. This value can permanently damage the data file. Database pages are left in a stale state, which in turn may bring more damage to b-trees and other database structures. In MySQL5.6.15, set InnoDB to read-only.

You can dump them from the select in the table. The value of Innodb_force_recovery is 3 or lower, and you can drop or create tables. In MySQL 5.6.27, DROP table is also supported by a Innodb_force_recovery value greater than 3.

If you know a given table in rollback causes a crash, you can delete it. If you encounter a runaway rollback or alter TABLE that caused the failure of a large-scale import, you can kill the MYSQLD process and set the Innodb_force_recovery to 3 so that the database starts without rolling back, and then the drop causes the table to roll out of control.

If corruption in the table data prevents you from dumping the contents of the entire table, the query with the ORDER BY primary_key DESC clause can dump the portion of the table after the damaged part.

If a high innodb_force_recovery value needs to start InnoDB, there may be a corrupted data structure that could cause complex queries (queries that contain where,order by or other clauses) to fail. In this case, you may only be able to run the basic select* from T query.

Then start the following database:

[[email protected] ~]#/etc/init.d/mysql start

Start the database to go into the database show slave status\g; see from the library, and then put the/etc/my.cnf file Innodb_force_recovery = 1 Note Restart the database is no problem.

Later troubleshooting could be a failure of the server hardware, which could cause the database to be stopped or the binaries to be broken.

And in the/ETC/MY.CNF configuration file, set the

Innodb_flush_log_at_trx_commit = 2 # The main library is 1 (when the IO overload is changed to 2), and the library is 2;

If set to 1 o'clock IO performance will be poor, so this host can only be set to 2.

Once MySQL hangs for no reason, using innodb_force_recovery repair