tail-30/data/mysql/localhost.localdomain.err (log file path depends on the situation),
----------------------------------------- 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 pages 2017-08-15 11:36:46 5497 [Note] InnoDB: The InnoDB memory heap is disabled 2017-08-15 11:36:46 5497 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2017-08-15 11:36:46 5497 [Note] InnoDB: Memory barrier is not used 2017-08-15 11:36:46 5497 [Note] InnoDB: Compressed tables use zlib 1.2.3 2017-08-15 11:36:46 5497 [Note] InnoDB: Using CPU crc32 instructions 2017-08-15 11:36:46 5497 [Note] InnoDB: Initializing buffer pool, size = 16.0M 2017-08-15 11:36:46 5497 [Note] InnoDB: Completed initialization of buffer pool InnoDB: Database page corruption on disk or a failed InnoDB: 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(A lot of similar code is omitted. InnoDB: End of page dump 2017-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 0 254222157, 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) 0 InnoDB: Page may be a transaction system page InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 5. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has 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. You 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. 2017-08-15 11:36:46 7f11c48e1720 InnoDB: Assertion failure in thread 139714288817952 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 be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html InnoDB: 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 binary or 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 help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=262144 max_used_connections=0 max_threads=1000 thread_count=0 connection_count=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 798063 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 /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 http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html to view the mandatory recovery method. Under the [mysqld] field found in MySQL configuration file my.cnf, add innodb_force_recovery=1:
[mysqld] innodb_force_recovery = 1
If innodb_force_recovery = 1 does not work, try two to six numbers
Then restart mysql and restart successfully. Then use mysqldump or pma to export data and perform repair operations. After the repair is completed, the parameter is commented out and the default value is restored to 0.
Configuration file parameters: 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 (i.e. checking data pages / purge undo / insert buffer merge / rolling back & forward) are performed when recovery is required, and mysql may not be started and error logs are recorded when effective recovery operations are not performed.
innodb_force_recovery can be set to 1-6, with large numbers containing the effects of all the previous numbers. When the parameter value is greater than 0, the table can be selected, created, drop operations, but insert,update or delete operations are not allowed.
1(SRV_FORCE_IGNORE_CORRUPT): Ignore the corrupt page checked.
2(SRV_FORCE_NO_BACKGROUND): Blocking the operation of the main thread, such as the need for the main thread to perform a full purge operation, results in crash.
3(SRV_FORCE_NO_TRX_UNDO): Transaction rollback is not performed.
4(SRV_FORCE_NO_IBUF_MERGE): The merge operation of insertion buffer is not performed.
5(SRV_FORCE_NO_UNDO_LOG_SCAN): Without looking at the redo log, the InnoDB storage engine treats uncommitted transactions as committed.
6(SRV_FORCE_NO_LOG_REDO): No rollforward operation is performed.
III. Analytical Scheme
Reference for general repair methods:
The first method
Create a new form:
Creatable demo_bak is the same as the original table structure except that INNODB is changed to MYISAM.
Import data in
insert into demo_bak select * from demo;
Delete the original table:
drop table demo;
After annotating 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 method is to export the table using mysqldump and then import it back into the InnoDB table. The results of these two methods are the same.
Backup export (including structure and data):
mysqldump -uroot -p123 test > test.sql
Reduction 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 basically useless in InnoDB databases.
The third method
1. Configure my.cnf
Configure innodb_force_recovery = 1 or 2-6 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 the databases of other servers.
Note: The data here must be backed up successfully. Then delete the data in the original database.
3. Delete ib_logfile 0, ib_logfile 1, ibdata1
Back up the ib_logfile 0, ib_logfile 1 and ibdata1 files in the MySQL data directory, and then delete the three files
4. Configure my.cnf
Remove or configure innodb_force_recovery = 0 to restart MySQL service by deleting or configuring the line configuration of innodb_force_recovery = 1 or 2-6 digits in my.cnf
5. Importing data into MySQL database
Mysql-uroot-p123 test < test.sql; or use Navicat to import the backup data into the database.
In this way, we should pay attention to the following problems:
1. The three files of ib_logfile 0, ib_logfile 1 and ibdata1 must be backed up and deleted first.
2. Make sure that the original data has been exported successfully.
3. When the data in the original database is deleted after the data export is successful, if the prompt cannot be deleted, you can enter the MySQL data directory on the command line and manually delete the relevant database folder or the data table files under the database folder, provided that the data must be exported or backed up successfully.