Migrating 300 GB mysql database from one server to another cleanly with minimum downtime 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?
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 &gt; live_dump_and_load.runlog
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} | mysql -h10.20.30.50 ${MYSQL_CONN}
date &gt;&gt; live_dump_and_load.runlog


STEP 04 : Run the shell script

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


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 !!!