Migrating 300 GB mysql database from one server to another cleanly with minimum 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 !!!