1

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?

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

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

  • Thanks a lot for this. Yes, we have a mixture of InnoDB and MyISAM, although we may be able to switch completely to InnoDB when upgrading schema. I'll have a read through the links. – Graham May 14 '14 at 7:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.