Transfer MySQL database to another server with minimal downtime through replication

There are cases when you need to transfer MySQL data from one server to another powerful server. Here are some of the common methods:

1. Backup/restore MySQL database
   Backup data from the source server and restore on the destination server. This is the safest way to transfer but it will consume some time depending on the amount of data you have. Usually, it's taking much longer for restoring than backing up. Especially, if we are using InnoDB database engine.
2. Replication MySQL server
   Personally, I prefer this method as the service downtime is minimal. If you don't know MySQL Replication, please read it.

Furthermore, this document will only focus on transferring MySQL data to another server using MySQL Replication.

Objectives:

• Transfer MySQL data from source server to destination server.

Requirements:

• Can perform MySQL Replication.

Assumptions:
Assume that you have bin-log enabled on the source (master) server and configurations are configured correctly on both servers. Otherwise, please read my MySQL Replication post.

Procedures:

1. Disable write on MySQL source server. Temporarily disable write queries on the source server in order for us to do a backup with consistence data. All write queries are stored on log file. Write operation will be resumed once write is enabled.
   

   mysql> FLUSH TABLES WITH READ LOCK;

2. Get the binary log position
   

   mysql> SHOW MASTER STATUS;

   If we see something like this, then we are on the right track:
   

   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | mysql-bin.000001 |      106 |              |                  |
   +------------------+----------+--------------+------------------+
   1 row in set (0.00 sec)

   Please take note of the position (106)! This is the log position where we start backing up the data.
   
3. Backup the databases on source server using mysqldump
   

   mysqldump --routines --triggers --single-transaction --quick -uroot -p --all-databases > all-dbs.sql

4. Enable write on MySQL source server. This allowed data to be written to MySQL database again.
   

   mysql> UNLOCK TABLES;

5. Transfer the database backup file to MySQL destination server.
6. Restore the database on the destination server
   

   mysql -uroot -ppassword mysql < /path/to/all-dbs.sql
   Note: -ppassword: no space between -p & password

7. Set the binary log position
   

   mysql> CHANGE MASTER TO
   MASTER_HOST='192.168.0.1',
   MASTER_USER='slave_user',
   MASTER_PASSWORD='password',
   MASTER_LOG_FILE='mysql-bin.001',
   MASTER_LOG_POS=106;

8. Start replication
   

   mysql> SLAVE START;

9. Wait until destination (slave) server catch up (synced) with source (master) server.
   
   1. Stop MySQL service on destination server:
      

      Debian based:  service mysql restart
      Red Hat based: /etc/init.d/mysqld restart

   2. All database access must now point to the destination server. For minimal downtime, we should configure port-forwarding using iptables on the source server. This mean all incoming traffic from MySQL port (3306) should be forwarded to the destination MySQL server. Setup iptables port-forwarding is beyond the scope of this document.
   3. Now we have to make sure all servers accessing the source MySQL server have to point destination MySQL server. After this is done, we can disable port-forwarding on the source server.

By now, the source server should be able to power off. This method is by far the quickest way I could think of.