更改innodb_log_file_size的问题

I haven't done much tweaking in the past so this might be relatively easy however I am running into issues. This is what I do:

我过去没有做太多的调整,所以这可能相对容易,但我遇到了问题。这就是我做的:

Stop MySQL
Edit my.cnf (changing innodb_log_file_size)

编辑my.cnf(更改innodb_log_file_size)

Remove ib_logfile0/1
Start MySQL

Starts fine however all InnoDB tables have the .frm file is invalid error, the status shows InnoDB engine is disabled so I obviously go back, remove the change and everything works again.

开始没问题,但所有InnoDB表都有.frm文件无效错误,状态显示InnoDB引擎被禁用,所以我显然回去,删除更改,一切都恢复正常。

I was able to change every other variable I've tried but I can't seem to find out why InnoDB fails to start even after removing the log files. Am I missing something?

我能够改变我尝试过的所有其他变量,但我似乎无法找出为什么即使在删除日志文件后InnoDB也无法启动。我错过了什么吗?

Thanks.

Edit: Pasting of the log below - looks like it still seems to find the log file even though they are not there?

编辑:粘贴下面的日志 - 看起来它似乎仍然找到日志文件,即使它们不在那里?

Shutdown:

090813 10:00:14  InnoDB: Starting shutdown...
090813 10:00:17  InnoDB: Shutdown completed; log sequence number 0 739268981
090813 10:00:17 [Note] /usr/sbin/mysqld: Shutdown complete

Startup after making the changes:

进行更改后启动:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!
090813 11:00:18 [Warning] 'user' entry 'root@XXXXX.com' ignored in --skip-name-resolve mode.
090813 11:00:18 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.81-community-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Edition (GPL)
090813 11:00:19 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './XXXX/User.frm'
090813 11:00:19 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './XXXX/User.frm'
090813 11:00:19 [ERROR] /usr/sbin/mysqld: Incorrect information in file: './XXXX/User.frm'

Its just a spam of the same error until I correct it

它只是一个相同错误的垃圾邮件,直到我纠正它

When it did start after it recreated the log files so it must be looking in the same spot I am.

当它在重新创建日志文件之后启动它所以它必须在同一个地方寻找我。

5 个解决方案

#1

First, I must point that before any chage in InnoDB data files, one should read 13.2.5. Adding, Removing, or Resizing InnoDB Data and Log Files.

首先,我必须指出,在InnoDB数据文件的任何变形之前,应该读取13.2.5。添加,删除或调整InnoDB数据和日志文件的大小。

The steps you pointed are almost correct. It´s recommended to backup before this types of changes. Lets see what happened to you:

你指出的步骤几乎是正确的。建议在此类更改之前进行备份。让我们看看你发生了什么:

The error messages

错误消息

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 268435456 bytes!

are caused by changing the size of innodb_log_file_size and dont deleting the old files. Probably you forgot to delete de ib_logfile0/1 the first time you ran mysqld after the changing. The message

是由更改innodb_log_file_size的大小而不删除旧文件引起的。可能你忘了在更改后第一次运行mysqld时删除de ib_logfile0 / 1。消息

090813 11:00:18 [Note] /usr/sbin/mysqld: ready for connections.

shows that you resolved this problem (by removing ib_logfiles). But, on removing them you create the other problem. Data corrupt for XXXX\User.frm or some problem with InnoDB Engine.

表明您已解决此问题(通过删除ib_logfiles)。但是,在删除它们时,您会产生另一个问题。 XXXX \ User.frm数据损坏或InnoDB引擎出现问题。

To be sure that InnoDB is enabled, run this command on a mysql prompt:

要确保启用InnoDB,请在mysql提示符上运行此命令:

show variables like "%inno%";

In the result list must have "have_innodb = YES". Sometimes when InnoDB can not start (value is NO or DISABLED) the Incorrect information in file message appears.
- If this is the problem, you have changed something else that prevents InnoDB Engine to start properly (datadir´s permissions or tmpdir´s permissions, other innodb variables (you didnt show the complete log.), etc.). Review the changes or put the .cnf files (before and after).
- If the InnoDB Engine is YES then you have to repair the table. (If it´s this case, add a comment and I´ll add the information here. I´m too lazy to do it now.)

在结果列表中必须有“have_innodb = YES”。有时,当InnoDB无法启动(值为NO或DISABLED)时,将显示文件消息中的错误信息。 - 如果这是问题,你已经改变了阻止InnoDB引擎正常启动的其他东西(datadir的权限或tmpdir的权限,其他innodb变量(你没有显示完整的日志。)等)。查看更改或放置.cnf文件(之前和之后)。 - 如果InnoDB引擎为YES,则必须修复该表。 (如果是这种情况,请添加注释,我会在这里添加信息。我现在太懒了。)

#2

The first line in the error suggests you haven't deleted the ib_logfile0. Can you confirm you're renaming / deleting the right one?

错误中的第一行表明您尚未删除ib_logfile0。你能确认你正在重命名/删除正确的吗?

#3

Things to check:

要检查的事项:

Is there enough space on the device to create the log file of that size? This behavior may be coming from a full device.

设备上是否有足够的空间来创建该大小的日志文件?此行为可能来自完整设备。

Are the log files being created with the wrong settings?

是否使用错误的设置创建了日志文件?

Your path to the logfile doesn't show as absolute. Just for grins, see if there are any other locations on your drive that contain an ib_logfile0. Might be a silly bug.

您的日志文件路径不会显示为绝对路径。只是为了咧嘴笑,看看你的驱动器上是否有任何其他位置包含ib_logfile0。可能是一个愚蠢的错误。

#4

Have you tried using an SQL Client to edit the innodb_log_file_size field?

您是否尝试使用SQL客户端编辑innodb_log_file_size字段?

Personally I like SQuirreL

我个人喜欢SQuirreL

#5

I suspect that you removed the wrong log files. look around for other ib_logfile* files. maybe you are using innodb_log_group_home_dir ?

我怀疑你删除了错误的日志文件。环顾四周寻找其他ib_logfile *文件。也许你正在使用innodb_log_group_home_dir?

if so, make sure that you removed the ib_logfiles from there and not the datadir.

如果是这样,请确保从那里删除了ib_logfiles而不是datadir。

#1