Back up all of your mysql databases nightly

June 10, 2013 — 3 Comments

Have you ever wanted to make your own MySQL database backups?  You can use this script I wrote to make a backup of all databases, then save each one in a dated backup directory.  It will keep doing this and keep 10 days worth of backups.  I’d suggest you use rsync or something to automatically back them up off the server each night/morning also.

Put the following into something like /usr/local/bin/mysql_backup.sh and since it has MySQL’s root password in it, make sure that you chmod 700 to it so no one else can read it.

Then just call it from cron by creating a root cron entry:

BTW, a simpler way to grab all of them is to use the –all-databases flag in the mysqldump command.. but it doesn’t make nice separate files for you.  It will make one large file.

Let me know if this helped you out!

3 responses to Back up all of your mysql databases nightly

  1. Easy peasy! This worked exactly as described. It’s really generous of you to share your code like this. I just wanted you to know that it is much appreciated. Have a goodun!

  2. Thank you for sharing your code. It is always important backup your databases at least every night so this code and guide will be very useful. I am really liking this blog so far, it has been really useful to me.

  3. Hi,
    thank you for your script. I’ve got an error on execution.
    Could you please look over…

    /usr/local/bin/mysql_backup.sh: Zeile 16: gt: Kommando nicht gefunden(command not found).
    gzip: compressed data not written to a terminal. Use -f to force compression.
    For help, type: gzip -h
    mysqldump: Got errno 32 on write

    Thank you

Leave a Reply