Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm wondering how can I backup mysql daily and backup binary log hourly?

I don't know how to write a script to do this(or use etc/crontab?).

share|improve this question
Start by reading the manpage for crontab, it has examples. – Anonymous May 11 '10 at 8:06

I typically add a gzip in there to save space:

mysqldump --add-drop-tables dbname | gzip > dbname.sql.gz

The add-drop-tables will automatically drop tables when you restore, otherwise you'd get errors that the tables already exist when restoring.

To restore: zcat dbname.sql.gz | mysql dbname

share|improve this answer

Have a look at automysqlbackup which takes daily, weekly and monthly backups of your MySQL databases using mysqldump. It also rotates the daily and weekly backups.

I cannot see a reason to take hourly backups of binary log. Better store it on redundant disks if you are worried about head crashes.

share|improve this answer

Have you looked into mysqldump? It's part of the distribution.

Run crontab -e (as root or as the user that owns mysql)

Enter the following:

0 0 * * * mysqldump databasename > nameofdumpfile

where databasename is the database to dump and nameofdumpfile should be the whole hierarchy to your desired dumpfile.

The above will dump at midnight daily but you'll want to add qualifiers to suit you.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.