Just downloaded, built and test a tool for Hot Backup of MySQL InnoDB databases: xtrabackup from Percona.
You can try the apt/rpm repository too.
You get two backup tools, that are quite easy to use:
- xtrabackup – a C program that merges redo logs and InnoDB files to create Hot Backups;
- innobackupex – a perl wrapper around xtrabackup.
To backup your databases, prepare a mysql-owned /opt/backup_mysql/
#useradd backup -m -d /opt/backup_mysql;
Then run just run
# innobackupex –user=root –password=password /opt/backup_mysql/
Once backup is finished (it took about 17 seconds for a 300Mb DB entirely fitting memory), you have a timestamped directory in /opt/backup_mysql/. You can use it as a new datadir to test your backup simply starting a new mysql instance.
UPDATE: latest innobackupex releases require you to APPLY redo logs to the datafiles. This is done in two steps
Apply redo logs:
# innobackupex --apply-log /opt/backup_mysql/2012-06-12_19-22-54/
Check innobackupex output
120612 19:22:57 innobackupex: completed OK!
REMEMBER: ALWAYS CHECK THAT YOUR BACKUP IS CONSISTENT! Unless you are sure to be able to recover the data your backup is not done 😉
To improve isolation you could even start this backup instance with another user, using eg. –user=backup. So, check backup permissions:
# chown -R backup.backup ~backup
# su - backup
Start our server
$ /usr/bin/mysqld --datadir=/opt/backup_mysql/2012-06-12_19-22-54/ \
This new instance won’t listen on any port (–skip-networking) but only on your backup_mysql.sock, so that it won’t interfere with existing instances.
Now we can check if our backup is fine, running mysql and issuing some queries:
# mysql --socket=/opt/backup_mysql/backup_mysql.sock -uroot -p > show databases \G *************************** 1. row *************************** Database: employees > use employees; > show table status \G ... a lot of table informations ... mysql> select count(salary) from salaries; +---------------+ | count(salary) | +---------------+ | 2844047 | +---------------+ 1 row in set (4.55 sec) mysql> select sum(salary) as HR_Budget from salaries; +--------------+ | HR_Budget | +--------------+ | 181480757419 | +--------------+ 1 row in set (0.83 sec) > select sum(salary) as HR_Budget from salaries;