Shulou(Shulou.com)06/01 Report--

The following content mainly brings you innodb problems leading to mysql startup failure to explain how to deal with the knowledge here, slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

The computer crashed during use. After rebooting, it was found that mysql did not start successfully. Looking at the error log, it was found that the problem with innodb led to the failure of mysql startup.

Error log $mysql.server startStarting 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/mysql2016-04-23 22:08:38 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use-- explicit_defaults_for_timestamp server option (see 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 because file system for / usr/local/var/mysql/ is case insensitive2016-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 pages2016-04-23 22:08:38 3604 [Note] InnoDB: The InnoDB memory heap is disabled2016-04-23 22:08:38 3604 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins2016-04-23 22:08:38 3604 [Note] InnoDB: Memory barrier is not used2016-04-23 22:08:38 3604 [Note] InnoDB: Compressed tables use zlib 1.2.32016 -04-23 22:08:38 3604 [Note] InnoDB: Using CPU crc32 instructions2016-04-23 22:08:38 3604 [Note] InnoDB: Initializing buffer pool Size = 128.0M2016-04-23 22:08:38 3604 [Note] InnoDB: Completed initialization of buffer pool2016-04-23 22:08:38 3604 [Note] InnoDB: Highest supported file format is Barracuda.2016-04-23 22:08:38 3604 [Note] InnoDB: Log scan progressed past the checkpoint lsn 689299334402016-04-23 22:08:38 3604 [Note] InnoDB: Database was not shutdown normally2016-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:642016-04-23 22:08:38 3604 [Note] InnoDB: Page size:1024 Possible space_id count:02016-04-23 22:08:38 3604 [Note] InnoDB: Page size:2048 Pages to analyze:482016-04-23 22:08:38 3604 [Note] InnoDB: Page size:2048, Possible space_id count:02016-04-23 22:08:38 3604 [Note] InnoDB: Page size:4096 Pages to analyze:242016-04-23 22:08:38 3604 [Note] InnoDB: Page size:4096 Possible space_id count:02016-04-23 22:08:38 3604 [Note] InnoDB: Page size:8192 Pages to analyze:122016-04-23 22:08:38 3604 [Note] InnoDB: Page size:8192, Possible space_id count:02016-04-23 22:08:38 3604 [Note] InnoDB: Page size:16384 Pages to analyze:62016-04-23 22:08:38 3604 [Note] InnoDB: VALID: space:2947354 page_no:3 page_size:163842016-04-23 22:08:38 3604 [Note] InnoDB: Page size:16384 Possible space_id count:12016-04-23 22:08:38 3604 [Note] InnoDB: space_id:2947354 Number of pages matched: 1InnoDB 1 (16384) 2016-04-23 22:08:38 3604 [Note] InnoDB: Chosen space:29473542016-04-23 22:08:38 3604 [Note] InnoDB: Restoring page 0 of tablespace 29473542016-04-23 22:08:38 3604 [Warning] InnoDB: Doublewrite does not have page_no=0 of space:29473542016-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 that you must createInnoDB: directories yourself InnoDB does not create them.InnoDB: Error: could not open single-table tablespace file. / test_user/user_recommend_code#P#pmax.ibdInnoDB: We do not continue the crash recovery, because the table may becomeInnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.InnoDB: To fix the problem and start mysqld:InnoDB: 1) If there is a permission problem in the file and mysqld cannotInnoDB: open the file, you should modify the permissions.InnoDB: 2) If the table is not needed, or you can restore it from a backup InnoDB: then you can remove the .ibd file, and InnoDB will do a normalInnoDB: crash recovery and ignore that table.InnoDB: 3) If the file system or the disk is broken, and you cannot removeInnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnfInnoDB: and force InnoDB to continue crash recovery here. Solution method

1. If the data is not important or there is already a backup, you only need to restore the mysql startup

Enter the mysql directory, which is usually / usr/local/var/mysql/

Delete ib_logfile*

Delete ibdata*

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

Restart mysql

Re-establish the database or overwrite with backup

two。 If the data is important and not backed up

You can use the innodb_force_recovery parameter to make mysqld skip the recovery step, start mysqld, export the data, and then rebuild the database.

Innodb_force_recovery can be set to 1-6, and large numbers include the effects of all previous numbers

(SRV_FORCE_IGNORE_CORRUPT): ignore checked corrupt pages.

(SRV_FORCE_NO_BACKGROUND): blocking the running of the main thread, if the main thread needs to perform a full purge operation, will result in crash.

(SRV_FORCE_NO_TRX_UNDO): no transaction rollback operation is performed.

(SRV_FORCE_NO_IBUF_MERGE): merge operations that insert buffers are not performed.

(SRV_FORCE_NO_UNDO_LOG_SCAN): without viewing the redo log, the InnoDB storage engine treats uncommitted transactions as committed.

(SRV_FORCE_NO_LOG_REDO): roll forward is not performed.

Add to my.cnf (windows is my.ini)

Innodb_force_recovery = 6

Innodb_purge_thread = 0

Restart mysql

Only select,create,drop operations can be performed at this time, but not insert,update,delete operations

Perform a logical export, innodb_force_recovery=0,innodb_purge_threads=1 after completion, then rebuild the database, and finally re-import the exported data

This article explains how to deal with the failure of mysql innodb startup and cannot be restarted. Please pay attention to more related content.

Related recommendations:

Explain how php can get the specified date.

Detailed explanation of PHP to generate unique RequestID class

How to view the capacity size of database tables through MySQL

The above is about the mysql innodb startup failure can not restart the details of how to deal with, please pay attention to other related articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.