[数据库] MySQL修改datadir后启动服务发生1067错误的解决办法 mysql, 1067

atux

当前windows下最新版的mysql (5.6.22.0)在安装时没有指定安装路径和数据存放路径的选项，而是默认把程序安装在C盘Program Files下，数据也存放在C:/ProgramData下面，找到my.ini，修改datadir变量后重启服务会报1067错误。解决办法如下：

来自：http://bugs.mysql.com/bug.php?id=68584

我用中文大概总结一下：
原因：安装MySQL后在创建MySQL服务时使用的是NETWORK SERVICE的用户，当你改变datadir之后，新的路径NETWORK SERVICE没有读写权限。
解决办法：给MySQL服务的登录账户赋予可读写权限。具体操作如下：
1、在服务那里右击MySQL服务名>属性>登录>查看创建服务的账户名，一般是NETWORK SERVICE。
2、给datadir指定的目录添加NETWORK SERVICE具有所有权限。

[13 Mar 2013 7:59] david trillo
I write on the right my.ini file.

The service is created like this:
"C:\Archivos de programa\MySQL\MySQL Server 5.6\bin\mysqld" --defaults-file="C:\Documents and Settings\All Users\Datos de programa\MySQL\MySQL Server 5.6\my.ini" MySQL56

And THIS is the file iI'm changing!

Finally, i got WHY was this error happening!

When creating the service, It used a user like NTblablabla, instead of a local user.

After changing this, everything was OK!

i'm happy to find the solution. It wasn't the datadir, but how the service was created (the user)!

i hope this can be useful to other users!
[13 Mar 2013 21:28] Wichen Moormann
Thanks to david trillo. We solved the problem also.

It was not a matter of correct my.ini.
It was a matter of user rights of service and data dir.

Let me describe it for Windows Server 2008 R2, which was the last OS i used.
I am always logged in as administrator, but that is not the point.

Important is:
The installer installs the service by default for the user: "network service"
(see properties of the service MySQ56).
If we copy the data dir to a new location (e.g. C: to D:) it has by default
the security rights of the user: "local system"
(see properties of the the new data dir).
---------------------------------------------------------
These different user/rights are the reason for 1067 here.
---------------------------------------------------------

Now we have at least 2 choices:
1. change the service user to: "local system" (as david trillo did)
or
2. add to the data dir security properties the user: "network service",
with all rights.

Both solution work OK when the service is started!

On other OS the problem should be solved analogous.
I hope this is helpfull for others.
Thanks to david trillo again.

atux

如上述操作无效，请尝试如下操作

1. Open my.ini

2. In [mysqld] section, add the following line:
innodb_force_recovery = 1

3. Save the file and try starting MySQL

4. Remove that line which you just added and Save.

原因：查看data目录下的主机名.err日志，可以看到如下信息，日志中说明的解决方法3即为简单粗暴行之有效的方式

1. 2015-10-13 10:24:32 8200 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace byt_atuxe/at_post uses space ID: 22 at filepath: .\byt_atuxe\at_post.ibd. Cannot open tablespace byt_iot/tbl_migration which uses space ID: 22 at filepath: .\byt_iot\tbl_migration.ibd
   
2. InnoDB: Error: could not open single-table tablespace file .\byt_iot\tbl_migration.ibd
   
3. InnoDB: We do not continue the crash recovery, because the table may become
   
4. InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
   
5. InnoDB: To fix the problem and start mysqld:
   
6. InnoDB: 1) If there is a permission problem in the file and mysqld cannot
   
7. InnoDB: open the file, you should modify the permissions.
   
8. InnoDB: 2) If the table is not needed, or you can restore it from a backup,
   
9. InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
   
10. InnoDB: crash recovery and ignore that table.
    
11. InnoDB: 3) If the file system or the disk is broken, and you cannot remove
    
12. InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
    
13. InnoDB: and force InnoDB to continue crash recovery here.

复制代码