• I am currently setting up a system which uses a MySQL database.
  • I want to create automated backups which run every evening at 10:00PM and also keep the backups for 30 days.

I know there is a way using cronjob and a bashscript which is executed. I already configured a .my.cnf for my SQL user so I won't have to paste the password into the bashscript.

All the tutorials I find do have some extras which I don't need and currently I don't feel to confident to create a script myself.

Do you have an idea or tutorial which is simple and easy?


There is not really a need for a bash script. First, work out what backup command would work for you. In principle, you could do something as easy as:

mysqldump > /path/to/mysqldump.sql 2>/path/to/mysqldump.err

This will write the contents of all the data bases to a file called mysqldump.sql. The form of this is such that, if you need to restore the data base after a disaster, all you need to do is mysql < /path/to/mysqldump.sql. Moreover, mysqldump preserves database integrity, i.e. it will produce a consistent copy of the database even if a transaction was going on at the time of the dump. If there is an error, it will be written to mysqldump.err.

You may need to investigate parameters such as -u, --login-path=local and --events. Also you may want to specify certain --databases to be backed up. For the sake of this discussion, I will not go into all that.

When the mysqldump command works to your satisfaction, you will want to have it run automatically as a cron job. To run the dump at 22:00 daily, specify this in the cron tab:

0 22 * * * mysqldump ... (etc)

Of course, this will overwrite the dumped database daily. To keep 30 copies, you will want to use logrotate. In /etc/logrotate.d, create a new file called (let's say) mysqldump. In this file enter this:

/path/to/mysqldump.sql {
  su (your username)
  rotate 30

Save the file and restart logrotate.

Logrotate will visit your mysqldump directory daily and rename the newest one to mysqldump.sql.1, the newest-but-one to mysqldump.sql.2, and so on. If you have already 30 copies, it will delete the oldest.

As for tutorials, you don't really need one. Just read man mysqldump, man crontab and man logrotate. These are all very well understood tools that will simply work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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