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 ***************************
> 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;