Board logo

标题: [数据库] MySQL修改datadir后启动服务发生1067错误的解决办法 [打印本页]

作者: atux    时间: 2014-12-11 11:03     标题: MySQL修改datadir后启动服务发生1067错误的解决办法

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


原因:安装MySQL后在创建MySQL服务时使用的是NETWORK SERVICE的用户,当你改变datadir之后,新的路径NETWORK SERVICE没有读写权限。
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)
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    时间: 2015-10-13 10:42

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.


  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.

欢迎光临 花江边社区—论坛 ( Powered by Discuz! 7.2