Tuesday, 27 November 2012

Mysql Hot Backups using Percona's Xtrabackup and innobackupex script

Percona's Xtrabackup is one of the hot tools used in the industry for taking mysql backups of live systems. Facebook is using Xtrabackup for their mysql backup needs so you can understand the importance of Xtrabackup.

But we are not directly using Xtrabackup for live hot backups. We are using innobackupex wrapper for taking the backups as Xtrabackup tool can only take the backup of innodb. What if we are also using myisam engine? But by using innobackupex wrapper, we can not take the backup of innodb, but also whole different type of mysql engine like myisam,archieve etc. Innobackupex is a wrapper script which will call Xtrabackup program and will take care of the different type of databases.

The advantage of using innobackupex/Xtrabackup is that while taking backup there won't be any db lock. Here I'm not going describe the full process of taking backup using Xtrabackup/innobackupex as it is already well documented in the Percona's site and even made user friendly by other bloggers. Here I'll refer you to check the awesome tutorial by Vitto in his blog.

I was already using Percona's Xtrabackup/innobackupex script for the hot backups as my company was using it. But I found the blog and steps given are useful for reference than the Percona's site.

Also I liked the option to stream the gzipped backup file to another server directly using the netcat. This is really cool. Once the backup is completed, you will get the desired copy in tar.gz format in remote server where we can extract it and prepare it for restoring.

Yes, we need to prepare it using the command --apply-logs before restoring. Then restoring is a matter two simple commands which will give correct permission for the file and start mysql server. So the total process involves 3 steps.

1. Take backup using innobackupex script and compress and transfer it to another server.

2. Extract the backup using tar -xvif (-i is needed as we took the backup using innobackupex) and prepare it for restoring using --apply-logs command

3. Correct the permissions of the prepared backup files using chown and chmod commands and start the mysql server.

Check the whole tutorial step by step in Vitto's blog: http://vitobotta.com/painless-hot-backups-mysql-live-databases-percona-xtrabackup/

No comments:

Post a Comment