Backup MySQL Daily?

rrinc · - Joined: 24 Feb 2006 Posts: 725 Location: Arkansas, USA

I'm wanting to backup all of my MySQL databases daily. Can I just copy the data directory?

I can write a script to do whatever I need to do unless one of you has another suggestion.
_________________
-Blake | New Server :D
SaveTheInternet
Soy hispanohablante. Puedes contactarme por mensajes privados.

Manacit · - Joined: 06 May 2008 Posts: 8

I assume your using linux, make a crontab to run however often you want to update your databases, and use mysqldump to back the databases up, and optionally send them to another server. More info on mysqldump can be found at http://www.devshed.com/c/a/MySQL/Backing-up-and-restoring-your-MySQL-Database/

AbyssUnderground · - Joined: 31 Dec 2004 Posts: 3855

I use Norton Ghost to backup my MySQL data. It works brilliantly. I make hourly backups and keep a 5 day archive. :)

If you want to do a simple copy and paste backup, you must stop the MySQL service and all related software (eg the winmysqladmin.exe program, not Abyss etc) and then copy it or it will say file in use. Norton Ghost uses the Shadow Copy service or something built into the OS to copy without doing that.
_________________
Andy (AbyssUnderground) (previously The Inquisitor)
www.abyssunderground.co.uk

rrinc · - Joined: 24 Feb 2006 Posts: 725 Location: Arkansas, USA

Actually I am using Windows. I might consider mysqldump, but I already wrote a script to copy the data directory. My mysql isn't complaining when I copy the files (it is running). Mine is the NT daemon (mysqld-nt.exe).

If anyone wants my backup script, just ask (I wrote it using my latest fascination...Python). It also backs up the htdocs directory. It dates the folders too (htdocs_6-9-08 for example).
_________________
-Blake | New Server :D
SaveTheInternet
Soy hispanohablante. Puedes contactarme por mensajes privados.

Lawrence · - Joined: 16 Jan 2003 Posts: 207 Location: Brisbane, AU

MySQL offers a free tool that does this for you. Their MySQL Admin program allows you to set up scheduled backups from a live database. It works perfectly well in Windows, and once you tell it how often it automatically sets Windows up to trigger it.

Dead easy, and totally reliable.

http://www.mysql.com/products/tools/administrator/