Mysql – How to restore database with ib_log0, ib_log1 and ibdata1 files in MySQL

MySQLmysql-workbench

We have a Mysql5.5 running on Windows server 2008 from past 4 years.

Recently we have moved ib* files to new locations because of space constraint in c drive.

When we started the Mysql it created all 3 ib* files in the default location. We could able to see the databases and tables through sql workbench. But when selecting data it says <schema>.<table> doesn't exists. So we copied back all 3 ib* files back to default location. But the service is not getting started. Getting error code 1067.

enter image description here

And My Error Log is as below.

160603 12:23:33 [Note] Plugin 'FEDERATED' is disabled.
160603 12:23:34 InnoDB: The InnoDB memory heap is disabled
160603 12:23:34 InnoDB: Mutexes and rw_locks use Windows interlocked functions
160603 12:23:34 InnoDB: Compressed tables use zlib 1.2.3
160603 12:23:34 InnoDB: Initializing buffer pool, size = 753.0M
160603 12:23:34 InnoDB: Completed initialization of buffer pool
160603 12:23:34  InnoDB: Operating system error number 5 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory. It may also be you have created a subdirectory
InnoDB: of the same name as a data file.
InnoDB: File name .\ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

Running service as default LocalSystem.

And checked administrator login has full rights on data folder.

Can anyone suggest how to get back my tables with data using the old ib* files which I moved to new location.

Thanks in advance.

Best Answer

The important part of the error log shows your instance is not able to open() the ibdata1 file (for reading and writing).

First to check in such situation are user rights. In Windows another possibility is the read-only file attribute which can block writes too.

Related Question