At Statvoo Analytics we found ourselves in the position where we needed to move our master MySQL database without ANY downtime and for anyone who’s tried to do this, you will know how hard this can be if it is not done exactly right.
Below I will run through the steps to get the job done efficiently and with no downtime (unless you mess it up that is).
First, you need to configure the master’s /etc/mysql/my.cnf and add the following lines in the [mysqld] section:
server-id = 1
binlog-format = mixed
log-bin = mysql-bin
datadir = /var/lib/mysql
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
Now you will need to restart the master mysql server and create a replication user that the slave server will use to connect with. (Make sure to choose a strong password (max of 32 chars))
CREATE USER >@>
GRANT REPLICATION SLAVE ON *.* TO >@> IDENTIFIED BY '>'
Now you will want to create a backup file with the binlog position. (Don’t worry about what that means if you’re unsure, this follow the instructions as below)
At this point your server’s performance may be impacted(a little bit), but no table locking will occur. This is because binlog actually writes to the filesystem as well, so the IOPS will just be a bit more than usual, but nothing to worry about really..
mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/sqldump.sql
You will need to find out some super interesting information(not really) that will help you later on. Take note of the values of MASTER_LOG_FILE and MASTER_LOG_POS (A pen and paper is quite handy right about now)
head sqdump.sql -n80 | grep "MASTER_LOG_POS"
Due to you not wanted to be affected by any downtime probably means you have a fair amount of traffic and this database is pretty big(or you’re paranoid about losing any potential traffic, have you tried Statvoo Analytics 😉 ), that means it will take a while to transfer the sqldump file, so why not gzip it!?
gzip ~/sqldump.sql
The time has come to transfer the sqldump gzipped file over to the slave server.
There are a few ways you can do this, but I like to use scp (Secure copy)
scp sqldump.sql.gz root@:/tmp
And yes I did just use root user to copy the file!
While this is all happening(will probably take quite a while as you’re capped to the connects on your MySQL servers) you can go ahead and edit the /etc/mysql/my.cnf file on the slave server, be sure to add the following lines.
server-id = 101
binlog-format = mixed
log_bin = mysql-bin
relay-log = mysql-relay-bin
log-slave-updates = 1
read-only = 1
Restart the MySQL slave and import the sqldump file.
cd /tmp
gunzip ~/sqldump.sql.gz
mysql -u root -p
Log into the mysql CLI on the slave server and run the following commands to get replication on the go.
CHANGE MASTER TO MASTER_HOST='>',MASTER_USER='>',MASTER_PASSWORD='>', MASTER_LOG_FILE='>', MASTER_LOG_POS=>;
START SLAVE;
It’s always good to check and see what the progress of the slave is
SHOW SLAVE STATUS G
If everything went well and you’re feeling proud of yourself, do make sure to confirm that Last_Error is blank and Slave_IO_State says something like “Waiting for master to send event”.
It’s always healthy to stare at Seconds_Behind_Master for a while to find out how far behind things are.
If you were a copy-paste ninja with completing the above(and I had no typos, and you didn’t forget the password or that thing I told you to write down), the slave will catch up pretty quickly.
Once you are sure the slave has caught up you simply point your application sql connection string to the new server, make sure to have a permitted user/pass and you’re away.
Gracefully reload the mysql server and you’re done! (You don’t really have to do this..)
Note:
If for some silly reason you changed some data on the slave, that means replication won’t go so well. To fix things you can use the following command.
STOP SLAVE;SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
Note2:
I found it healthy to keep the previous master around (turned off) for a few hours/day for my own sanity in case I found any problems later on where I needed to quickly switch the application back or export some missing data. (Which I didn’t, but it did make me feel safer.)