Detailed introduction to solving the problem of mysql shutting down immediately after startup (caused by damage to the ibdata1 file)

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

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

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.

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!