Tuesday 14 June 2011

mydumper, now with continuous backup mode!

It has happened to the best of us.  Our MySQL servers are running nicely when the tea-lady in the data centre trips over a wire, knocks a cup hot of java down the UPS batteries and fries every server in the data centre.  OK, so that doesn't happen very often, but there have been cases where data centre failures have physically damaged servers.

So, if this happens with your MySQL server, what do you do?  You can setup new servers in a new data centre and hope you have an up-to-date backup.  But unless you had remote slaves these backups probably won't be as current as they could be.  When you get to the point where you have many servers keeping slaves for every one just for backups can be difficult to maintain.  Many DBAs have different backup strategies to cover this potential problem.

This is where the new mydumper 0.5.0 comes in.  It has a new 'daemon' mode which lets it run in the background.  When in daemon mode it will take multi-threaded consistent snapshots on a regular basis (60 minutes by default) whilst also pretending to be a MySQL slave and continuously retrieving a local copy of binary logs.  This can, in-theory, give an up-to-the-second backup of your server.  Right up until the point where you need to fire the tea-lady.  As an example you can use it in this mode as follows to make snapshots every 2 hours and constantly retrieve the binary logs:
mydumper --daemon --snapshot-interval=120 --logfile=mydumper.log --user=mydumper_user
--password=mydumper_password --host=mysql_server

A maximum of 2 snapshots are retained, one for the previous snapshot and one for the current one in case a failure happens during the current snapshot.  A symbolic link inside the dump directory will always point to the last good snapshot.  Each snapshot contains the binary log positions from that snapshot so at any given time the snapshot can be loaded in using myloader (a multi-threaded restoration tool bundled with mydumper) and then the log applied from that point using mysqlbinlog.  In later versions I plan to have myloader handle the binary logs too.

Mydumper 0.5.0 is currently considered in an 'alpha' stage (0.2.3 is the latest stable version but this will only take single snapshots).  This means there are probably bugs I haven't spotted yet and there is still a little bit of work to do (such as better myloader support for the daemon mode snapshots).  You can download it from Launchpad here.