Backup recommendations

[JezEling]

Hi all,

I'm really new to the MYSQL world and have a quick question about backup routines. My MySQL is running on Windows 2000 and currently I use Veritas Backup Exec for backing up my Windows 2000 and Linux servers. It seems Backup Exec is causing some problems with one of my MySQL databases as when I arrive at work in the morning the application which uses this database has crashed and one table must be repaired.

My first port of call was to remove the Data folder from the backup routine but this now leaves a gaping whole is my back and restore capabilities. Can any advise as to a method I can use to backup my MySQL data, i've tried MySQLDUMP but it seems to take for ever to run (This particular database is about 900MB).

Many Thanks

Jez

 

[TonyGroves]

Since MySQL uses memory caching to avoid unnecessary disc access, simple file backup is not reliable. If you want to avoid dumping the tables to separate files, then your backup script would need to lock the tables, flush them to disc, back them up, then unlock them.

 

[JezEling]

Thanks for the response, do you happen know where I can get more information in creating such a script?

 

[KarveR]

if you use the --quick option with mysqldump, it doesn't try and fit the whole dump into memory before writing it to disk, this should improve your dump time considerably.

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[TonyGroves]

You could use an ordinary batch script. The locking and flushing commands are just SQL statements (explained in the manual), which can be executed via the command-line by using a command such as "mysql -u root -ppassword <sqlstatements.sql" or "mysql -u root -ppassword -e "sqlstatement

 

[ericbrunson]

I use a combination of tactics. First, I use replication to mirror data in real time to another machine (several other machines, actually). Then, every 4 hours as part of my rsnapshot backups I do a "mysqldump --all-databases > db-dump.sql". I'll have to check out "--quick" option KarveR mentions.