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.
