Skip to content

Moving a MySQL Database without downtime

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
Code language: Bash (bash)

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

Code language: SQL (Structured Query Language) (sql)

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
Code language: Bash (bash)

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"
Code language: Bash (bash)

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
Code language: Bash (bash)

The time has come to transfer the sqldump gzipped file over to the slave server.

See also  How to Create a MySQL Table in Python

There are a few ways you can do this, but I like to use scp (Secure copy)

scp sqldump.sql.gz [email protected]:/tmp
Code language: Bash (bash)

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
Code language: Bash (bash)

Restart the MySQL slave and import the sqldump file.

cd /tmp gunzip ~/sqldump.sql.gz mysql -u root -p
Code language: Bash (bash)

Log into the mysql CLI on the slave server and run the following commands to get replication on the go.

Code language: SQL (Structured Query Language) (sql)

It’s always good to check and see what the progress of the slave is

Code language: SQL (Structured Query Language) (sql)

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.

See also  How many stairs will Suzuki climb in 20 years? in Java

Gracefully reload the mysql server and you’re done! (You don’t really have to do this..)

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.

Code language: SQL (Structured Query Language) (sql)

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.)

Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x