Originally posted by Blairwarlock
Does anyone have any advice or input with regards to automating a nightly DB dump of MySQL on a Win2K machine? I would guess you would use the Scheduler program, but anyone have a script that would do this? Preferrable one that would go and purge old DB dumps that are certain age (ie, 3 days)
My concern is how do you automate turning off the forums gracefully so that nobody writes to the DB anymore before the DB dump, then turn the forums on again?
We run the Rage3D Forums (http://www.rage3d.com/board) on Win2k/IIS. The following batch file is run each nite in the wee hours with scheduler to backup the forums.
move /Y <drive>:\Backups\<database>-today.dump <drive>:\Backups\yesterday\<database>-yesterday.dump
mysqldump -c -e -l -q --flush-logs -u<user> -p<password> <database> > <drive>:\backups\<database>-today.dump
First line moves the previous days backup to another directory (overwriting the file without prompting you) so we always have two days worth of forum backups available at any given time. Second line does the current days forum dump.
It may not be the ideal way to back up the DB, but it has been running without any problems for almost a year, and while I've not actually tested a backup in the last bunch of months, the couple of tests i've done in the past have always been solid.
Our DB dump files are more than 1.1GB now, so it takes 3-4mins even on the fast DB drive setup in our server, and the forum is functionally inaccessable during that time.
Hope that helps.