InnoDB: No valid checkpoint found.

InnoDB: No valid checkpoint found.

原创 MySQL 作者：ywxj_001 时间：2018-12-29 16:22:02  1745  1

MySQL启动报错：

SSOT01:/usr/local/mysql/data>service mysqld restart

MySQL server PID file could not be found!                                                                          failed

Starting MySQL.The server quit without updating PID file (/usr/local/mysql/data/SSOT01.pid).                       failed

查看err日志：

SSOT01:/usr/local/mysql/data>tail -100 SSOT01.err

181229 13:32:12 InnoDB: Mutexes and rw_locks use GCC atomic builtins

181229 13:32:12 InnoDB: Compressed tables use zlib 1.2.3

181229 13:32:12 InnoDB: Using Linux native AIO

181229 13:32:12 InnoDB: Initializing buffer pool, size = 1.0G

181229 13:32:12 InnoDB: Completed initialization of buffer pool

181229 13:32:12 InnoDB: highest supported file format is Barracuda.

InnoDB: No valid checkpoint found.

InnoDB: If this error appears when you are creating an InnoDB database,

InnoDB: the problem may be that during an earlier attempt you managed

InnoDB: to create the InnoDB data files, but log file creation failed.

InnoDB: If that is the case, please refer to

InnoDB: http://dev.mysql.com/doc/refman/5.5/en/error-creating-innodb.html

181229 13:32:12 [ERROR] Plugin 'InnoDB' init function returned error.

181229 13:32:12 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

日志的错误信息都看了，方法都试了还是不行。

最后的问题关键点在这里：

InnoDB: No valid checkpoint found.

将innodb的ibdata*和ib_logfile*都删除掉之后，重启MYSQL，会发现原来的innodb表都丢失了，原因是因为innodb的数据是存储在ibdata*文件里的，

如果保留ibdata*但将ib_logfile*都删除掉的话，mysql还是启动不了，会报错no valid checkpoint found,这是因为innodb是支持事务的，启动之后，MYSQL需要一些前滚和回滚的操作，

以保证事务的一致性，但ib_logfile*被删除之后，无法保障事务的一致性，最终结果导致MYSQL无法启动。

一句话总结：ib_logfile*文件就是Oracle里的redolog和archivelog文件。

解决办法：删除innodb的ibdata*和ib_logfile*，然后重启mysql服务。

不再报错：

181229 14:55:35 [Note] Plugin 'FEDERATED' is disabled.

181229 14:55:35 InnoDB: The InnoDB memory heap is disabled

181229 14:55:35 InnoDB: Mutexes and rw_locks use GCC atomic builtins

181229 14:55:35 InnoDB: Compressed tables use zlib 1.2.3

181229 14:55:35 InnoDB: Using Linux native AIO

181229 14:55:35 InnoDB: Initializing buffer pool, size = 5.0G

181229 14:55:36 InnoDB: Completed initialization of buffer pool

181229 14:55:36 InnoDB: highest supported file format is Barracuda.

181229 14:55:36  InnoDB: Waiting for the background threads to start

181229 14:55:37 InnoDB: 1.1.8 started; log sequence number 1595675

Thread pool plugin started successfully with parameters:

thread_pool_size = 24,

thread_pool_algorithm = Low Concurrency Algorithm

thread_pool_stall_limit = 6

thread_pool_prio_kickup_timer = 1000

thread_pool_max_unused_threads = 0

启动正常：

SSOT01:/usr/local/mysql/data>service mysqld restart

MySQL server PID file could not be found!                                                                                                        failed

Starting MySQL........                                                                                                                           done

之前是删除了ibdata*或ib_logfile*，都无法启动mysql服务

而且SSOT01.pid这个文件就算手工创建也会自动被删除。

必须把ibdata*和ib_logfile*都删除才可以，再重新启动mysql服务正常，并重新自动生成了SSOT01.pid文件。

但是因为数据库是innodb_file_per_table    | OFF 共享表空间模式，所以删除ibdata*后，数据文件还需要自己手工还原。