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.
Step 1:
Log into MySQL and lock the databases from being edited by typing the following:
FLUSH TABLES WITH READ LOCK
Step 2:
In your favorite shell copy the databases like so:
cp -R /var/lib/mysql /mnt/mounted
Step 3:
On your new system copy the databases into the correct location:
cp -R /mnt/mounted/mysql /var/lib
Step 4:
Fix the permissions on the databases
chmod -R 700 /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
Step 5:
Reboot MySQL server
service mysql restart
or
/etc/init.d/mysql restart
Step 6:
Unlock the databases by typing this into MySQL
UNLOCK TABLES;
All your databases and previous permissions should now be copied accross and working!