Migrating MySQL to a Different System the Easy Way
Well this week I’ve replaced my personal home server and reinstalled my work’s development server; both these systems have a MySQL server running which contain databases (duh) that would be either too large to import/export or too complex to rebuild from scratch.
So I decided to find a way of copying the database files directly between systems and it’s actually dead easy and strangely not very well documented! So here’s the steps to do it fairly painlessly.
This guide assumes you have a root account and root database access.
Log into MySQL and lock the databases from being edited by typing the following:
FLUSH TABLES WITH READ LOCK
In your favorite shell copy the databases like so:
cp -R /var/lib/mysql /mnt/mounted
On your new system copy the databases into the correct location:
cp -R /mnt/mounted/mysql /var/lib
Fix the permissions on the databases
chmod -R 700 /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
Reboot MySQL server
service mysql restart
Unlock the databases by typing this into MySQL
All your databases and previous permissions should now be copied accross and working!