MySQL database migration


gearsI recently upgraded my development laptop to CentOS 7 and while I was at it built a spare laptop running the same operating system. After manually creating a dozen MySQL databases and users and then importing their data from dump files on one laptop, I wasn’t interested in going through exactly the same process again on the second laptop.

As is now the default on CentOS 7, the databases were actually MariaDB (a community-developed fork of MySQL) rather than MySQL, but that’s not relevant here. The following procedure describes how I migrated an entire MariaDB/MySQL RDBMS installation in one go without recreating any databases or users or dumping and re-importing any files. The same procedure would of course work for migrating similar data between CentOS 7 servers.

Before proceeding I should emphasise that this data migration was between two machines with identical freshly installed operating systems. In cases where the operating systems, distributions or version were different things might not go so smoothly. One concern would be that global database configuration parameters on both machines were compatible.

Original Database Server

1: Create a directory to hold the data being migrated:

# mkdir mariadbdata

2: Stop the MariaDB/MySQL server:

# systemctl stop mariadb

3: Copy contents of /var/lib/mysql to the directory just created:

# cp -r /var/lib/mysql/* mariadbdata

4: Restart the MariaDB/MySQL server again:

# systemctl start mariadb

5: Compress the data:

 # tar -czvf mariadbdata.tar.gz mariadbdata

6: Copy the compressed file to new server.


New Database Server

1: Install MariaDB but don’t start it yet.

2: Uncompress data file:

# tar -xzvf mariadbdata.tar.gz

3: Move contents of data directory to /var/lib/mysql:

# mv mariadbdata/* /var/lib/data

4: Change ownership of all files in /var/lib/mysql to mysql user:

# chown -R mysql.mysql /var/lib/mysql/*

5: Restore correct SELinux security contexts:

# restorecon -R /var/lib/mysql/

6: Enable and start MariaDB:

# systemctl enable mariadb
# systemctl start mariadb

That’s it! As I mentioned above, this went so smoothly probably because the operating systems and installed software at either end were identical.