设置innodb_log_file_size会导致MySQL崩溃

So, I'd like to be able to set the max log file size to 64M, but after doing so with innodb_log_file_size=64M MySQL starts OK, but nothing seems to work properly. EDIT: and by properly I mean not at all. Setting other InnoDB variables aren't causing any problems.

所以，我希望能够将最大日志文件大小设置为64M，但在使用innodb_log_file_size = 64M之后，MySQL启动正常，但似乎没有任何工作正常。编辑：并且恰当地说我的意思是根本没有。设置其他InnoDB变量不会导致任何问题。

How should I go about troubleshooting this one?

我应该如何解决这个故障？

3 个解决方案

#1

Make sure MySQL shuts down cleanly, and delete (or move elsewhere) all ib_logfile* files from MySQL data directory (/var/lib/mysql/ usually).

确保MySQL干净地关闭，并从MySQL数据目录（/ var / lib / mysql /通常）删除（或移动到其他地方）所有ib_logfile *文件。

I've tested it and worked for me. Here's source of this hint.

我已经测试过并为我工作过。这是这个提示的来源。

InnoDB reports some errors in show table status comment field. You'll find other problems in MySQL error log (hostname.err in MySQL data directory).

InnoDB报告show table status comment字段中的一些错误。您将在MySQL错误日志中找到其他问题（MySQL数据目录中的hostname.err）。

#2

I ran into this problem too, and as per @porneL's answer, here were my specific bash steps to correct this:

我也遇到了这个问题，按照@ porneL的回答，这是我特定的bash步骤来纠正这个问题：

service mysql stop              # Stop MySQL
rm /var/lib/mysql/ib_logfile0   # Delete log file 1
rm /var/lib/mysql/ib_logfile1   # Delete log file 2
vim my.conf                     # Change innodb_log_file_size = 64M
service mysql start             # Start MySQL

I found these specific steps on the MySQL forums.

我在MySQL论坛上找到了这些具体步骤。

#3

Before changing the innodb_log_file_size, you must flush all remaining transactional data out of it. You simply set innodb_fast_shutdown to 0 or 2.

在更改innodb_log_file_size之前，必须从中清除所有剩余的事务数据。您只需将innodb_fast_shutdown设置为0或2即可。

innodb_fast_shutdown = 0 : InnoDB does a slow shutdown, a full purge and an insert buffer merge before shutting down
innodb_fast_shutdown = 0：InnoDB在关闭之前执行缓慢关闭，完全清除和插入缓冲区合并
innodb_fast_shutdown = 2 : InnoDB flushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.
innodb_fast_shutdown = 2：InnoDB刷新日志并关闭冷，好像MySQL崩溃了;没有提交的事务丢失，但崩溃恢复操作使下一次启动需要更长的时间。

In light of this, this is how you handle it

鉴于此，这就是你如何处理它

mysql -ANe"SET GLOBAL innodb_fast_shutdown = 2"
vi /etc/my.cnf                  # Change innodb_log_file_size = 64M
service mysql stop              # Stop MySQL
rm /var/lib/mysql/ib_logfile0   # Delete log file 1
rm /var/lib/mysql/ib_logfile1   # Delete log file 2
service mysql start             # Start MySQL

#1