Fastest way to set up MySQL replication from backup

Question

Here's my scenario:

• I have a single database on network A that needs to be transferred to network B.
• on network B, I need a master-slave setup with 2/3 slaves.
• I need to run some significant updates / deletions to the database before it can be put into production again This will probably take a few hours.
• Most importantly, I need to minimize downtime.

My current plan is to:

• turn off MySQL
• backup the MySQL datadir
• run my scripts to update the database to the new schema
• rsync the entire datadir to all master/slave servers on network B

At this point I have exact copies of the data on all servers. From what I've read, I should then be able to extract the binlog status and start replication.

Does this sound like a reasonable plan, or am I missing something obvious that would a) rule this out or b) make this easier / faster?

Answer 1

For the Sake of this Post

• IP of Master is 10.20.30.100
• IP of Slave1 is 10.20.30.110
• IP of Slave2 is 10.20.30.120
• IP of Slave3 is 10.20.30.130

ALL InnoDB

If all your tables are InnoDB, you can do this:

STEP 01 : If you do not have binary logging enabled, you need to enable it. Here is how you can tell:

Login to MySQL and run this

mysql> SHOW MASTER STATUS;

If you get something like this:

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000266 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

then, it is enabled. If you get Empty Set, then it is not enabled.

To enable it, add this to your my.cnf

[mysqld]
log-bin=mysql-bin

and restart mysql

STEP 02 : Create a Replication User on the Master

Run this

GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.*
TO replicator@'%' IDENTIFIED BY 'replicationpass';

STEP 03 : Test the Replication User

On each of the Slaves, run this in Linux

MASTER_IP=10.20.30.100
mysql -h${MASTER_IP} -ureplicator -preplicationpass -ANe"SHOW VARIABLES LIKE 'hostname'"

Make sure it echos the hostname of the Master from all Slaves.

STEP 04 : Clear all binary logs on the Master

Simply run

mysql> RESET MASTER;

To clear away all binary logs and start with a fresh one

STEP 05 : Dump all your data to a text file

From

MASTER_IP=10.20.30.100
echo "CHANGE MASTER TO master_host='${MASTER_IP}'," > LoadSlaveData.sql
echo "master_post=3306,master_user='replicator'," >> LoadSlaveData.sql
echo "master_password='replicationpass'," >> LoadSlaveData.sql
echo "master_log_file='bogus'," >> LoadSlaveData.sql
echo "master_log_pos=2;" >> LoadSlaveData.sql
DUMP_OPTIONS="-single-transaction --master_data=1 --routines --triggers"
mysqldump --uroot -p ${DUMP_OPTIONS} --all-databases >> LoadSlaveData.sql
echo "START SLAVE;" >> LoadSlaveData.sql

Line 28 will have the binary log and position for replication because of --master_data=1.

STEP 06 : Load the SQL file into each Slave

Login to Each Slave and Load that File

mysql -h10.20.30.110 -uroot -p < LoadSlaveData.sql
mysql -h10.20.30.120 -uroot -p < LoadSlaveData.sql
mysql -h10.20.30.130 -uroot -p < LoadSlaveData.sql

THAT'S IT. NO DOWNTIME

ALL MYISAM or SOME InnoDB / SOME MyISAM

What you proposed in your question is more of a fit for databases with any MyISAM tables

I have recommended the same technique over the years

• Nov 09, 2012 : How can I reinitialize some MyISAM tables without restarting MySQL?
• Jul 24, 2012 : How to copy a MySQL database which is a mixture of InnoDB and MyISAM to the same server?
• Mar 15, 2012 : Why is mysqlhotcopy slower compared to rsync?
• Jun 17, 2011 : What is the difference between XtraBackup and rsync?

I even mentioned this in StackOverflow : MySql - create replication with minimal downtime