MySQL Database Server Migration

I am going to show you a quick way to move your databases to a new server in this scenario this is for a database server upgrade with as little downtime as possible with complete new hardware.

The first thing to do is login to your existing mysql server and make sure you have your root login details correct and that you can login on the new machine so you root user will need to have an entry for localhost or the new servers IP in the mysql users tabel.

mysql> SELECT user,host FROM mysql.user;
+------------------+-------------------------------+
| user             | host                          |
+------------------+-------------------------------+
| root             | 127.0.0.1                     |
| root             | ::1                           |
| root             | localhost                     |
+------------------+-------------------------------+

If you want to be able to access your old server from the new server then you will need to add a new root user with the appropriate permissions and host IP (don’t use % for root user).

The next step is to set the old server to read only, dump the databases and transfer the sql file to the new server ready for import, one thing you can do is dump the database from the new server rather than transferring it after a dump, you may also want to try this before setting to read only mode as a test to make sure that all databases will import.

In mysql set all databases to read only

FLUSH TABLES WITH READ LOCK;
SET GLOBAL read_only = ON;

Now you need to dump the databases ready to be transferred

mysqldump -p --routines --triggers --flush-privileges --lock-all-tables --all-databases > databases.sql

Once you have dumped the databases to an sql file you can now transfer the file o your new database server you can do this with scp or some other method

scp database.sql root@{NEW_DB_SERVER_IP}:/root/

once you have transferred the file login to your new database server and import the database.

mysql -u root -p < databases.sql

once complete login with your old servers login details if you have set the mysql password the same then this will be fine if you setup the new server with a new password then you will find that the password has been updated (this caught me out 1st time).

You can check you databases and uses by logging in and checking mysql users table and checking on the databases query

mysql> SELECT user,host FROM mysql.user;
+------------------+-------------------------------+
| user             | host                          |
+------------------+-------------------------------+
| wordpress_db     | %                             |
| stats            | %                             |
| bob1             | %                             |
| root             | 127.0.0.1                     |
| root             | ::1                           |
| root             | localhost                     |
+------------------+-------------------------------+
mysql> show databases;
+-----------------------------+
| Database                    |
+-----------------------------+
| information_schema          |
| bob_test                    |
| webstats                    |
| wordpress                   |
+-----------------------------+

Now this should be all done you just need to point your applications at the new database server, change your DNS or what ever is appropriate.

One thing to note if you are moving from Windows to Linux you may need to add lower_case_table_names=1 in your my.cnf file under the mysqld section

[mysqld]
lower_case_table_names=1

To unlock the databases on the old server you can run the following but this should not be required if you are decommissioning the old server, also by leaving it locked any sites that are still trying to use it after the move wont be able to write any new data which means you should not need to re copy any databases.

SET GLOBAL read_only = OFF;
UNLOCK TABLES;