backing up MySQL dbase
 

OK i have tried a few different ways to back up all of mysql databses

First was a script run from cron it owrked fine for a while then i ran into a table corruption problem. Basically i gather that somone was making an update via one of our websites, and the dump was happening at the same time.

So itried the webmin backup and the same thing has happened.

So the big question is how to doit?

Z

It seems as though mysqldump has an option to lock your tables for read on all tables. Might be what your looking for.

Hum yeah maybe...
 

Yeah that seems right, alhtough i am trying the mysqlhotcopy now seems ALOT faster anyway although i am unsure how to recover using the files that mysqlhotcopy creates, any insight onthis?

Z

No, sorry!
The once I have seen have dumped it to sql files. Then you could just run them as a SQL batch job.

Hum ok
 

Well ok i guess it should be easy since it made backups of everything, yeah i could also go back to using the mysqldump with the lock command, although it is REALLY slow since my dbase os around 100 mg the hotcopy takes about 9 secs and is done!!

Z

Use the -opt command which is the same as specifying
--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

The -quick command really speeds up mysqldump.


Heres a snippet of the shell script i'm using:

/usr/local/mysql/bin/mysqldump --flush-logs --opt -u SQLUSER -pSQLPWD dbname > /dir1/dir2/dir3/backup.sql