Moving MySQL InnoDB data files

Standard

If you ever need to move around MySQL data files for InnoDB tables or getting the error mentioned below then follow the instructions in this post.

Note that this post might be helpful only if you have a backup of complete data files directory from your source MySQL installation. Otherwise please refer to the links at the bottom of this post.

Take backup of your data files (source)

Copy your data files from /usr/local/mysql/data/ on Mac or /var/lib/mysql/ on Ubuntu/Linux to a safe location, e.g. inside your home directory. Location of data files can be different so refer to your MySQL configuration file (my.cnf) for correct path.

mkdir ~/db-files/

On Mac

On Ubuntu/Linux

Do not forget to copy the period (.) at the end of commands above.

Restore files (destination)

Stop the MySQL server on target system, if running.

On the new/target installation, replacing the target data directory contents with source data directory may not work, specifically if you have InnoDB tables.

Take a backup of data files on the destination and keep them safe somewhere in case anything goes wrong. Assuming that target data directory location is /usr/local/newmysql/data/

Extract the compressed archive of source data files.

From the extracted contents, manually copy the database folders under destination data files directory. Do not copy/overwrite any files or folders inside destination data directory, for example sys and mysql folders. For example, if one of the database name is mydb and destination data directory path is /usr/local/newmysql/data/ then execute the following command.

cp -R mydb /usr/local/newmysql/data/

This needs to be done for all your databases one by one. Any files or folders which pre-exist in the target data directory must not be overwritten. If you do then there is a fair probability that you are doomed.

Once you have copied all your databases manually (without overwriting any existing folder or file) to the target data directory, copy/replace ibdata1 file from your source directory to target data directory

Finally, ensure that ownership and permissions of folders and files inside destination data directory are correct. Data directory itself and it’s content must be owned by mysql user, _mysql  in Mac and mysql in Ubuntu/Linux.

On Mac

On Ubuntu/Linux

I am not 100% sure but permissions should be as mentioned below. These permissions work on my Mac correctly.

755 (rwxr-xr-x) for mysql top data directory

700 (rwx——) for each database directory

660 (rw-rw—-) for database files inside database directory

Lastly, restart the MySQL server and verify if everything is as expected.

Summary

The most important part is the one related to ibdata1 file. This file stores lot of information and data about your InnoDB tables along with an internal index to all the InnoDB tables. If you don’t replace the ibdata1 file related to your original/source data files then that index would be lost and MySQL would fail to understand the schema and data too would be lost despite having ibd and frm files in place.

Here are links to some useful discussions/posts related to the topic for reference.

What is the ibdata1 file in my /var/lib/mysql directory?

How to restore table stored in a .frm and a .ibd file?

Recovering an InnoDB table from only an .ibd file.

InnoDB Corruption Repair Guide

Leave a Reply