MySQL Replication for the New Year

Posted Mon Jan 02 @ 01:09:17 PM PDT 2012

After launching Bingo Baker, I decided to consolidate my web applications onto a single server, well, a VPS from Linode. One of my goals was to figure out a way to do database backups without punishing the server. My old way of doing backups was pretty bad.

I would do a mysqldump with the --lock-tables option set to false, so inserts and updates could still happen on my MyISAM tables. The dump would also be saved to the same machine being backed up, which makes the disk do double duty (read from the database on disk, then write it back to the disk). After the SQL dump was generated, I would transfer it to another machine.

The main problems with that backup scheme are:

  1. You get an inconsistent backup because the tables can change while the backup is occurring
  2. Your CPU, disk and memory work hard to generate the backup, and your website visitors suffer from the slow down
  3. You use up a lot of bandwidth transferring a gzipped database dump to another machine

On my new machine, I did the smarter thing, and setup a MySQL master-slave pair (with the master being the new server, and the slave being an old computer I had laying around in my house). It was incredibly easy to setup (change a few settings in /etc/mysql/my.cnf and you're good to go). You can learn how to do replication on MySQL here.

The cool part about replication is that I can stop the slave, do a mysqldump, and start it back up, to get a consistent snapshot of the master. During the backup, the master can continue to do write queries. When the slave comes back online, it can catch up to the master. I also don't have to hammer the master with any heavy IO work.

Another benefit, is that I can execute long running read queries for reports on the slave, and I don't have to tie up the master.

I wish I had done this years ago.

<< Home