We have built an alternative save restore process. First we take all of the
.frm files and build
"select * into outfile '/path/tablename.txt' from tablename"
statements. We also dump the structure only and put it in the same
directory. This runs much faster than myysqldump and every table is in its
own file. The real advantage for us is that for many user errors we can
quickly load the few tables into a 2nd database on the server and then
reconstruct. On our 2 processer Pentium server a 650,000 row table takes 7
seconds to save or load. We have a mixture of MyISAM and INNODB tables and
this process works for both. The primary reason we went to it was we were
using mysqldump and encounterd a case where the output file grew to > 2GB.
Restores worked fine until one day we had to do a restore and for some
reason the file was corupted about half way through. The tables we were
trying to get back happened to be at the end of the file and we could not
find a tool to bypass the corupted data.
Our database is ~ 3 GB with data and indexes and the backup takes a few
minutes to run in total.
I would be iterested if anyone on the list sees any issues with this as a
backup/restore methodology assuming we still do lock tables and use the
binary log. This approach does take some additional admin effort if we add
or drop tables.