Detailed introduction to solving the problem of mysql shutting down immediately after startup (caused by damage to the ibdata1 file)-Mysql Tutorial-php.cn

The following editor will bring you a perfect solutionmysqlimmediately after startup Closing problem (caused by damage to the ibdata1 file). The editor thinks it is quite good, so I will share it with you now and give it a reference. Let’s follow the editor and take a look at it.

It’s on a server in the computer room. Mysql has been running for a while, and a very strange phenomenon suddenly occurred:cannot be restored after restarting! The correct situation is: mysql is shut down immediately after starting. ##View the mysql error log as follows:

160920 22:41:41 mysqld_safe Starting mysqld daemon with databases from /home/MysqlData/ 2016-09-20 22:41:41 0 [Note] /Data/app/mysql5.6.25/bin/mysqld (mysqld 5.6.25-log) starting as process 32372 ... 2016-09-20 22:41:42 32372 [Note] Plugin 'FEDERATED' is disabled. 2016-09-20 22:41:42 32372 [Warning] option 'innodb-write-io-threads': unsigned value 1000 adjusted to 64 2016-09-20 22:41:42 32372 [Warning] option 'innodb-read-io-threads': unsigned value 1000 adjusted to 64 2016-09-20 22:41:42 32372 [Note] InnoDB: Using atomics to ref count buffer pool pages 2016-09-20 22:41:42 32372 [Note] InnoDB: The InnoDB memory heap is disabled 2016-09-20 22:41:42 32372 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2016-09-20 22:41:42 32372 [Note] InnoDB: Memory barrier is not used 2016-09-20 22:41:42 32372 [Note] InnoDB: Compressed tables use zlib 1.2.3 2016-09-20 22:41:42 32372 [Note] InnoDB: Using CPU crc32 instructions 2016-09-20 22:41:42 32372 [Note] InnoDB: Initializing buffer pool, size = 1.0G 2016-09-20 22:41:42 32372 [Note] InnoDB: Completed initialization of buffer pool 2016-09-20 22:41:42 32372 [Note] InnoDB: Highest supported file format is Barracuda. 2016-09-20 22:41:42 32372 [Note] InnoDB: Log scan progressed past the checkpoint lsn 20293587957 2016-09-20 22:41:42 32372 [Note] InnoDB: Database was not shutdown normally! 2016-09-20 22:41:42 32372 [Note] InnoDB: Starting crash recovery. 2016-09-20 22:41:42 32372 [Note] InnoDB: Reading tablespace information from the .ibd files... 2016-09-20 22:41:42 32372 [Note] InnoDB: Restoring possible half-written data pages 2016-09-20 22:41:42 32372 [Note] InnoDB: from the doublewrite buffer...

Copy after login

InnoDB: Doing recovery: scanned up to log sequence number 20293596130

2016-09-20 22:41:42 32372 [Note] InnoDB: Starting an apply batch of log rec ord s to the database... InnoDB: Progress in percent: 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 136254, file name mysql-bin.00 001 3 2016-09-20 22:41:43 32372 [Note] InnoDB: 128 rollback segment(s) are active. 2016-09-20 22:41:43 32372 [Note] InnoDB: Waiting for purge to start 2016-09-20 22:41:43 7f77a9edd700 InnoDB: Assertion failure in thread 140151928772352 in file trx0purge.cc line 699

Copy after login

InnoDB: Failing assertion: purge_sys->iter.trx_no <= purge_sys->rseg->last_trx_no
##

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. 02:41:43 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.

Copy after login

After analyzing the log, it was found that the database could not be restarted The reason is that the ibdata1 file is damaged and cannot be restored normally after restarting

##Solution:

##Need to skip the recovery step
, modify the my.cnf file, add in [mysqld] in my.cnf:

innodb_force_recovery = 6innodb_purge_threads = 1

Explanation:
##innodb_force_recovery can be set to 1-6, The big number includes the influence of all the previous numbers

#The corresponding meaning of the specific number:

1--- --(SRVFORCEIGNORECORRUPT): Ignore the detected corrupt page.
2-----(SRVFORCENORECORRUPT): Prevent the main thread from running. If the main thread needs to perform a full purge operation, it will cause a crash.

3- ----(SRVFORCENOUNDORXUNDO): Do not perform transaction rollback operation.

4-----(SRVFORCENOIBUFMERGE): Do not perform merge operation of insert buffer.5-----(SRVFORCENOUNDOLOGSCAN): No. Looking at the redo log, the InnoDB storage engine treats uncommitted transactions as committed.6-----(SRVFORCENOLOG_REDO): Do not perform roll forward operation.
Start mysql again and it will be ok~

If it still cannot be started, you need to delete the ibdata1, ib_logfile* and other files in the data directory datafile.

Export the MySQL database after startup and restore it again.

The above is the detailed content of Detailed introduction to solving the problem of mysql shutting down immediately after startup (caused by damage to the ibdata1 file). For more information, please follow other related articles on the PHP Chinese website!