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

Change user:

# su - backup

Start our server

$ /usr/bin/mysqld --datadir=/opt/backup_mysql/2012-06-12_19-22-54/ \

                  --socket=/opt/backup_mysql/backup_mysql.sock \

                  --skip-networking

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;