How can I setup an automatic backup with mysql? I've looked at the options that my hosting provider offer. You can do it manually there's nothing to allow it to be done automatically.

I use crontab for regular backups on our live server - log in using ssh or (if you must) telnet, and add a line such as

mysqldump -hlocalhost -psqlpassword -uwellho wellho > /home/wellho/sqlbackup/`date +%A`.sql

to your crontab using the crontab -e command.

Notes:

a) There's a little bit more about crontab here

b) My sample provides for a weekly cycle of 7 backups so that you can revert up to 7 days.  I would suggest an extra line for a weekly backup, using a date format that won't repeat the file name ever - that way you'll get a growing archive that you can tidy up manually, very occasionally.

c) Have a look carefully at locking options to mysqldump if you want to backup a set of tables that are under active modification all the time

d) Copy a backup off your server - at least occasionally. Remember that your backups generated by the scheme above will allow you to restore if you do something stupid to your database, but they will NOT let you restore in the event of a hard disc crash if the backup was on the same drive as the database engine uses.

---