Home / ubuntu / Questions / 799790
Accepted
Private
Asked: 2016-07-18 08:47:45 +0800 CST

Working with cronjobs, bashscripts to automate MySQL backups

  • 772
  • 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?

bash

1 Answers

  1. Best Answer

    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
  daily
  missingok
  notifempty
}

    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.

    • 1