Monday, July 07, 2008

Even faster online backup!


I discussed my findings with Guilhem Bichot, one of the online backup creators, and he remarks:
You could also try
export MYISAM_BACKUP_NO_INDEX=1

before starting mysqld. It should not backup index (and rebuild them at repair time). Should make a smaller backup and a longer restore.

I am not really looking for a longer restore, but let's give it a try. I restarted the database with the suggested option, and here is what I got:


backup database employees to 'emp2.bkp';
+-----------+
| backup_id |
+-----------+
| 4 |
+-----------+
1 row in set (3.18 sec)

drop schema employees;
Query OK, 10 rows affected (0.02 sec)

restore from 'emp2.bkp';
+-----------+
| backup_id |
+-----------+
| 5 |
+-----------+
1 row in set (18.33 sec)

The backup is definitely faster, and the restore is slower than the 11 seconds I got with the previous test. However, it's much faster than the standard restore!
Moreover, the backup file is barely 70 MB. That's an excellent compromise. Perhaps it won't be that fast with a several GB of data, but for now, it looks very good.

No comments:

Vote on Planet MySQL