Mysql Database InnoDB Startup failure cannot be restarted solution _mysql

Introduction to the problem

Computer in use during the crash, restart found that MySQL did not start successfully, view error log found to be innodb problems caused MySQL startup failed.

Error log

$ mysql.server start starting MySQL. error!

The server quit without updating PID file (/usr/local/var/mysql/fdipzonedemacbook-air.local.pid). 22:08:37 Mysqld_safe starting mysqld daemon with databases From/usr/local/var/mysql 2016-04-23 22:08:38 0 [Warning] times Tamp with implicit DEFAULT value is deprecated.
Please use--explicit_defaults_for_timestamp server option (the documentation for more details).
2016-04-23 22:08:38 0 [note]/usr/local/cellar/mysql/5.6.24/bin/mysqld (mysqld 5.6.24) starting as Process 3604 ... 2016-04-23 22:08:38 3604 [Warning] Setting lower_case_table_names=2 file System because case
Insensitive 2016-04-23 22:08:38 3604 [note] Plugin ' federated ' is disabled. 2016-04-23 22:08:38 3604 [note] innodb:using Atomics to ref count buffer pool pages 2016-04-23 22:08:38 3604 [note] Innod 
B:the InnoDB memory heap are disabled 2016-04-23 22:08:38 3604 [note] innodb:mutexes and rw_locks use GCC atomic builtins 2016-04-23 22:08:38 3604 [notE] innodb:memory barrier is isn't used 2016-04-23 22:08:38 3604 [note] innodb:compressed tables use zlib 1.2.3 2016-04-23 22:08:38 3604 [note] innodb:using CPU CRC32 instructions 2016-04-23 22:08:38 3604 [note] innodb:initializing buffer pool , size = 128.0M 2016-04-23 22:08:38 3604 [note] innodb:completed initialization of buffer pool 2016-04-23 22:08:38 3604 [
NOTE] Innodb:highest supported the file format is barracuda. 2016-04-23 22:08:38 3604 [note] Innodb:log scan progressed past the checkpoint LSN 68929933440 2016-04-23 22:08:38 3604 [
Note] Innodb:database is not shutdown normally!
2016-04-23 22:08:38 3604 [note] innodb:starting crash recovery.
2016-04-23 22:08:38 3604 [note] innodb:reading tablespace information from the ... ibd files ... 2016-04-23 22:08:38 3604 [ERROR] innodb:checksum mismatch in tablespace./TEST_USER/USER_RECOMMEND_CODE#P#PMAX.IBD ( Table Test_user/user_recommend_code#p#pmax) 2016-04-23 22:08:38 3604 [note] innodb:page size:1024 Pages to analyze:64 201 6-04-23 22:08:38 3604 [note] innodb:page size:1024, Possible space_id count:0 2016-04-23 22:08:38 3604 [note] innodb:page size:2048 Pages to analyze:48 2016-04-23 22:08:38 3604 [note] innodb:page si
ze:2048, Possible space_id count:0 2016-04-23 22:08:38 3604 [note] innodb:page size:4096 Pages to analyze:24 2016-04-23 22:08:38 3604 [note] innodb:page si
ze:4096, Possible space_id count:0 2016-04-23 22:08:38 3604 [note] innodb:page size:8192 Pages to Analyze:12 2016-04-23 22:08:38 3604 [note] innodb:page si
ze:8192, Possible space_id count:0 2016-04-23 22:08:38 3604 [note] Innodb:page size:16384 the Pages to Analyze:6 2016-04-23 22:08:38 the 3604 [] innodb:valid: space:2947354 page_no:3 page_size:16384 2016-04-23 22:08:38 3604 [note] innodb:page size:16384, Possible space_id count: 1 2016-04-23 22:08:38 3604 [note] innodb:space_id:2947354, number of pages MATCHED:1/1 (16384) 2016-04-23 22:08:38 3604 [Note] Innodb:chosen space:2947354 2016-04-23 22:08:38 3604 [note] Innodb:restoring Page 0 of tablespace 2947354 2016-04-23 22:08:38 3604 [Warning] Innodb:doublewrite does not have page_no=0 of space:294
7354 2016-04-23 22:08:38 7fff79b9e300 innodb:operating system error number 2 in a file operation.
Innodb:the error means the system cannot find the path specified. Innodb:if you are installing InnoDB, remember so you must create innodb:directories yourself InnoDB the not create T
Hem. InnoDB:Error:could not open single-table tablespace file./test_user/user_recommend_code#p#pmax.ibd Innodb:we does not co Ntinue The crash recovery, because the table may become innodb:corrupt if we cannot apply to the log records in the InnoDB l
OG to it. Innodb:to Fix the problem and start mysqld:innodb:1) If There is a permission problem in the file and mysqld cannot Inn
Odb:open the file, you should modify the permissions.  INNODB:2) If The table is not needed, or I can restore it from a backup, Innodb:then can remove the. ibd file, and InnoDB'll do a normal InnoDB: Crash recovery and ignore that table. Innodb:3) If the file system or the disk is broken, and your cannot remove innodb:the. ibd file, you can set Innodb_force _recovery > 0 in my.cnf innodb:and force InnoDB to continue crash-here.

Solving method

1. If the data is unimportant or has already been backed up, just restore the MySQL boot

Enter the MySQL directory, generally:/usr/local/var/mysql/

Remove ib_logfile*

Deleteibdata*

Delete all database physical directories (for example, the database is test_db, execute rm -rf test_db )

Restart MySQL

Re-establish the database or use Backup overlay

2. If the data is important and not backed up

You can use innodb_force_recovery parameters to allow MYSQLD to skip recovery steps, start mysqld, export data, and then rebuild the database.

innodb_force_recovery Can be set to 1-6, the large number contains all the previous digits affect

1, (srv_force_ignore_corrupt): Ignore the check to the corrupt page.

2, (Srv_force_no_background): block the main thread of the operation, such as the main thread needs to perform a full purge operation, will lead to crash.

3, (Srv_force_no_trx_undo): Do not perform transaction rollback operation.

4, (Srv_force_no_ibuf_merge): Do not perform the insert buffer merge operation.

5, (Srv_force_no_undo_log_scan): Do not check the value of the log, InnoDB storage engine will be uncommitted transactions as committed.

6, (Srv_force_no_log_redo): Do not perform roll forward operation.

Add in My.cnf (Windows is My.ini)

Innodb_force_recovery = 6 
innodb_purge_thread = 0

Restart MySQL

You can only perform select,create,drop operations, but you cannot perform insert,update,delete operations

Performs a logical export, completes innodb_force_recovery=0 , innodb_purge_threads=1 then rebuilds the database, and finally imports the exported data back into the

Summarize

The above is the entire content of this article, I hope to learn or use MySQL when you have some help, if there is doubt you can message exchange, thank you for the cloud Habitat Community support.