Mysql – Migrating 300 GB thesql database from one server to another cleanly with minimum downtime

master-master-replicationMySQLmysqldumpreplicationrestore

I have a 300 GB mysql database which i want to migrate to another server in order to setup Master-Master replication between the two and my main aim is to achieve this with least possible downtime.

My database has only one table around 30GB where inserts are happening round the clock. All the other tables are history tables(static).

1) What will be the best way to go forward in this case?

  • Taking mysqldump of the whole database and transferring the dump to
    other server and importing it to the new server will do the job i want to achieve but will take a lot of
    downtime (maybe more than 14hrs) which is not permissible.

  • Or can i take individual table dumps for the static tables without stopping mysql and import it to new server ,and after all this is over, take downtime for the single active table so that no new inserts will happen and both my databases will be in sync? First of all is it possible to do such a thing ? and if possible what issues it might present while setting up a master-master replication?

  • Or is there any other method to do this with very less downtime?

Best Answer

This solution can be done with mysqldump but with a bit of a risk

For the sake of this example, suppose you have the following:

  • The database name is mydb
  • The source DB server's private IP is 10.20.30.40
  • The target DB server's private IP is 10.20.30.50
  • User is root on both source and target DB Servers
  • Password is whatever on both source and target DB Servers
  • Binary Logs on both servers are named mysql-bin

Here are your steps

STEP 01 : Create Replication User

On the Live Master, run the following

CREATE USER repluser@'%' IDENTIFIED BY 'replpass';
GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO repluser@'%';

STEP 02 : Set Up Replication

On the target DB Server, run the following:

If you have GTID enabled on source and target servers, do this:

CHANGE MASTER TO
master_host='10.20.30.40',
master_port=3306,
master_user='repluser',
master_password='replpass',
master_auto_position=1;

If you do not have GTID enabled on source and target servers, do this:

CHANGE MASTER TO
master_host='10.20.30.40',
master_port=3306,
master_user='repluser',
master_password='replpass',
master_log_file='mysql-bin.000001',
master_log_pos=4;

STEP 03 : Create a script to perform live mysqldump and load

Create a shell script called live_dump_and_load.sh

Put the following lines in it

MYSQL_USER=root
MYSQL_PASS=whatever
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DB_TO_DUMP=mydb
MYSQLDUMP_OPTIONS="--routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single_transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} -B ${DB_TO_DUMP}"

date > live_dump_and_load.runlog
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} | mysql -h10.20.30.50 ${MYSQL_CONN}
date >> live_dump_and_load.runlog

STEP 04 : Run the shell script

chmod +x live_dump_and_load.sh
nohup ./live_dump_and_load.sh &

STEP 05 : Monitor the run log

watch cat live_dump_and_load.runlog

STEP 06 : Start Replication

When the dump is loaded onto the target server, go to the target server and run

START SLAVE;

GIVE IT A TRY !!!

Related Question