Doesn't always work with InnoDB tables
I've found the backup in the article, mysqldump, is fast and works well. However, when restoring InnoDB tables, it can be problematic. I've found that the backup creates the restore script with the tables in alphabetical order which will seldom work when foreign key constraints are involved. The restore script drops the table, rebuilds the table, and then loads the table with your backed up data. With constraints, you cannot drop a table that is involved in a foreign key constraint as the parent until the child is dropped or the constraint is dropped. A similar situation exists with the Create Table command: If you're trying to build a child table with a foreign key constraint before the parent table is build, the restore will fail.
To make the restore work, I've had to move the Drop Table commands to the top of the script, re-order the Drops to ensure children are dropped before parents, and re-order the Create Table commands so as to build parents before children. Obviously, this will take time that you don't always have when in a restore situation.
I would like to think that there is a better way somewhere out there on the market but I've not yet found it.
