MySQL ibdata 文件中的日志序列号不匹配

Log sequence number in ibdata files does not match

提问by theking2

Everytime when I start my Mysql database I see this in the error_log:

每次启动 Mysql 数据库时，我都会在 error_log 中看到：

131015 12:07:06 [Note] Plugin 'FEDERATED' is disabled. 131015 12:07:06 InnoDB: The InnoDB memory heap is disabled 131015 12:07:06 InnoDB: Mutexes and rw_locks use Windows interlocked functions 131015 12:07:06 InnoDB: Compressed tables use zlib 1.2.3 131015 12:07:06 InnoDB: Initializing buffer pool, size = 16.0M 131015 12:07:06 InnoDB: Completed initialization of buffer pool 131015 12:07:06 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 131015 12:07:06 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 131015 12:07:07 InnoDB: Waiting for the background threads to start 131015 12:07:08 InnoDB: 5.5.32 started; log sequence number 1595695 131015 12:07:08 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306 131015 12:07:08 [Note] - '0.0.0.0' resolves to '0.0.0.0'; 131015 12:07:08 [Note] Server socket created on IP: '0.0.0.0'.

131015 12:07:06 [注意] 插件“联邦”被禁用。131015 12:07:06 InnoDB：InnoDB 内存堆被禁用 131015 12:07:06 InnoDB：互斥锁和 rw_locks 使用 Windows 互锁函数 131015 12:07:06 InnoDB：压缩表使用 zlib:13203:1320。 InnoDB：正在初始化缓冲池，大小 = 16.0M 131015 12:07:06 InnoDB：已完成缓冲池初始化 131015 12:07:06 InnoDB：支持的最高文件格式为 Barracuda。InnoDB：ibdata 文件中的日志序列号不匹配 InnoDB：ib_logfiles 中的日志序列号！131015 12:07:06 InnoDB：数据库没有正常关闭！InnoDB：启动崩溃恢复。InnoDB：从 .ibd 文件中读取表空间信息...... InnoDB：从双写 InnoDB 恢复可能的半写入数据页：缓冲区...... 131015 12:07：07 InnoDB：等待后台线程启动 131015 12:07:08 InnoDB：5.5.32 启动；日志序列号 1595695 131015 12:07:08 [注意] 服务器主机名（绑定地址）：'0.0.0.0'；端口：3306 131015 12:07:08 [注意] - '0.0.0.0' 解析为 '0.0.0.0'；131015 12:07:08 [注意] 在 IP 上创建的服务器套接字：'0.0.0.0'。

I've tried mysqlcheck -u root -p --repair -A in order to repair the database. This reports that all tables are a-ok.

我试过 mysqlcheck -u root -p --repair -A 来修复数据库。这报告所有表都正常。

I've also tried setting innodb_force_recovery to 4

我也试过将 innodb_force_recovery 设置为 4

I've tried SET GLOBAL innodb_fast_shutdown = 1; and shutdown the DB.

我试过 SET GLOBAL innodb_fast_shutdown = 1; 并关闭数据库。

None of these make the errors go away.

这些都不会使错误消失。

How do I repair the InnoDB tables in my database?

如何修复数据库中的 InnoDB 表？

回答by akuzminsky

LSN (log sequence number) may not match if MySQL crashed. But if the crash recovery process finished you should be fine. If any transaction modifies data in a page, new LSN will be written to the page header.

如果 MySQL 崩溃，LSN（日志序列号）可能不匹配。但是如果崩溃恢复过程完成了，你应该没问题。如果任何事务修改了页面中的数据，则新的 LSN 将写入页面标题。

I would investigate why MySQL crashes every time you restart it. To my knowledge /etc/init.d/mysql stop may take long time, so if you restart the server OS may just kill mysqld. In that case you should manually stop MySQL before reboot the whole server.

我会调查为什么每次重启 MySQL 都会崩溃。据我所知 /etc/init.d/mysql stop 可能需要很长时间，所以如果你重新启动服务器操作系统可能会杀死 mysqld。在这种情况下，您应该在重新启动整个服务器之前手动停止 MySQL。

回答by zero-day

I had the same issue.

我遇到过同样的问题。

In my case, I was running MySQL on windows via XAMPP and using the XAMPP controls to start/stop the server.

就我而言，我通过 XAMPP 在 Windows 上运行 MySQL，并使用 XAMPP 控件启动/停止服务器。

It appears the XAMPP shutdown control was killing the MySQL server, rather than requesting a shutdown.

看起来 XAMPP 关闭控件正在杀死 MySQL 服务器，而不是请求关闭。

Solution

解决方案

1. (optional) In my.ini set innodb_fast_shutdown=0to configure mysqld to perform a full showdown
2. Create a .bat file, with contents mysqladmin --user=[user] --password=[password] shutdownreplacing [user]and [password]with your root user details

1. (可选) 在 my.ini 设置innodb_fast_shutdown=0中配置 mysqld 以执行完整摊牌
2. 创建一个 .bat 文件，mysqladmin --user=[user] --password=[password] shutdown替换内容[user]并[password]使用您的 root 用户详细信息

When you wish to shutdown the MySQL server, just run the .bat file. The shutdown is detected by XAMPP control panel so you can restart using the XAMPP control.

当您希望关闭 MySQL 服务器时，只需运行 .bat 文件。XAMPP 控制面板检测到关闭，因此您可以使用 XAMPP 控件重新启动。

If you now take a look at your logs, you'll see the errors have gone away

如果你现在看看你的日志，你会看到错误已经消失了

Or better yet, just install mysql as a windows service.

或者更好的是，只需将 mysql 安装为 Windows 服务。

References:

参考：

1. innodb_fast_shutdownhttp://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_fast_shutdown
2. mysqladmin shutdown- http://dev.mysql.com/doc/refman/5.7/en/server-shutdown.html

1. innodb_fast_shutdownhttp://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_fast_shutdown
2. mysqladmin shutdown- http://dev.mysql.com/doc/refman/5.7/en/server-shutdown.html

回答by Hessy SharpSabre

I resolve my problem by removing some files in "Xampp\mysql\data":

我通过删除以下文件解决了我的问题"Xampp\mysql\data"：

• ib_logfile0
• ib_logfile1
• ibdata1

• ib_logfile0
• ib_logfile1
• ibdata1

** Be Sure to Backup your DBs by :

** 请务必通过以下方式备份您的数据库：

[mysqld]
innodb_force_recovery = 2

(This command will help you to start Mysql in Read-only mode.)

（此命令将帮助您以只读模式启动 Mysql。）