Setup mysql replication without downtime
Basically we have a large MySQL database and we're looking to do replication to a slave (master slave setup). Do you guys have any step-by-step guide on how to do this with MINIMAL downtime on the database? Backing up the database and transferring the backup to the slave server (even through a private network) takes like 40 minutes or so. Quite a long downtime.
asked Jan 9, 2011 at 11:57
I assume you use InnoDB as a storage engine. If so, you need to turn on bin-logging. If it's not on now, you need to restart MySQL after modifying my.cnf. It is the only downtime, after which you can take dump of the database with binlog position without blocking the database:
Based on this backup, restore data on the slave. After this, you can follow any MySQL replication tutorial and let slave catch up/run together with the master.
answered Jan 9, 2011 at 21:35
pQdpQd 29.6k5 gold badges64 silver badges106 bronze badges 3 to start a replication you need a consistent copy of your database on the slave are your tables myisam or innodb backed? to find out issue
and look at the 'Engine' column if all the tables are innodb than you can do zero downtime setup using mysqldump --single-transaction and importing that into the slave if the tables are myisam there will have to be downtime as you have to lock the tables for writing while they are being copied. mysqlhotcopy is the tool to help you with that. alternatively, if you are using LVM (logical volume manager), you can stop the database, take a LVM snapshot in a few seconds and start the db again. Then you can make a consistent copy from the snapshot. answered Jan 9, 2011 at 20:56
In order to setup the slave you will need to take a data snapshot and record the binary log position. Backing up the database should not cause downtime although it will cause reduced performance, but your already backing up the database anyway right? You may find the backup needs to lock some tables and this may cause lockups on your application, really need to look at how it is implemented for that. Can you snapshot the disk? That would reduce downtime considerably if you can do a hotbackup. answered Jan 9, 2011 at 12:13
Recently I had to add a new MySQL slave server to an existing master -> slave setup, without causing any downtime. Having done that only a couple of times in the past and never having played the central role in the process, I wanted to make sure I was getting everything right, so I did a lot of research but couldn’t find all the best (or what I consider the best) bits and pieces in one single place, so I am documenting everything here for future reference
and hopefully they will also help others. I’ll start by mentioning something that saved me a ton of time: don’t use Lots of articles around the web mention that you should create a dump of your existing slave using A better way is to just copy the MySQL data directory directly to the new slave and ajust a few things. This is actually the method recommended in the MySQL manual, although it is lacking in details. I stopped the existing slave before copying the data directory over to the new slave
to make sure I was getting all the data but I have also seen it mentioned in an article that you can copy the data with An important thing to note about this method (copying data directory directly vs. dumping+copying+importing) is that MySQL versions on the existing slave and the new slave should be the same, otherwise you will probably get into trouble. A difference in the revision number (e.g. 5.5.10 and 5.5.22) should not be a problem, but I would check before using different minor versions (e.g. 5.5 and 5.4). Major version
differences (e.g. 5.x and 4.x) are definitely a no go. Jacob Holmes noted in a comment that in newer MySQL versions (the manual says it’s from 5.6 onward) there’s a The solution is to not copy the file or delete it in the new slave, since MySQL will recreate it as needed. This process was done on CentOS 6.4 (not my personal favorite but I wasn’t the one who installed the OS) but you could easily apply it on other
distributions as well. If you’re looking into this matter, you probably know what to do, like replace MySQL was Percona’s MySQL 5.5 but this should apply to the regular MySQL 5.5. Also, for long running commands, I suggest you use screen or byobu, so you can detatch your session, close your SSH connection, shutdown your computer, go
drink a cup of tea or get some sleep. Just don’t start the Also, when you see a Right, let’s get down to business. This first step is optional, but it can potentially save you time when copying the data over to the new slave. If you have a big dataset, though, the time it will take to optimize your database(s) will probably negate the quicker transfer time. On your existing slave: Stop the new slave, so we don’t mess up the data we’re going to copy: On the existing slave, add the
following line to /etc/my.cnf, under the [mysqld] section:
Then restart MySQL:
Make sure the line “Seconds_Behind_Master:” shows “0” (zero). If it doesn’t, wait until it does (assuming there’s no error).
The lines “Slave_IO_Running:” and “Slave_SQL_Running:” should both say “No”.
You may also need to execute
You’ll see something like this:
Don’t forget to replace the file name and the start position with the values you got on the previous step.
You’ll see something like this (with a few blank lines that I deleted for brevity):
Don’t forget to replace the log file name and the start position with the values you got on the previous step.
The lines “Slave_IO_Running:” and “Slave_SQL_Running:” should both say “Yes”. The line “Seconds_Behind_Master:” may have a non 0 value but that’s fine. It will go down to zero as the new slave catches up with the master.”
And you’re done! Now you just have to wait for new slave to catch up with the master and you will have a brand new MySQL slave on your setup! I am by no means an expert on MySQL and replication is a complex topic, so any suggestions you may have to improve on this are very welcome. Any questions you may have about this process are also welcome and I’ll try to help out as much as I can. |