Simon Davies Digital ltd

Backing up MySQL Databases

Posted by & filed under Bash, MySQL.

As a web developer you learn that there is nothing more important than keeping regular backups of your databases. I’ve created a little bash script to help with Backing up MySQL databases for this I’ll use mysqldump. Mysqldump is a nifty little utility that lets you dump the contents of a database to a file, so I use this to create a little bash script that will backup the database at regular intervals, and I’ll even chuck in deleting backups older than 30 days so you don’t over run with files.

First lets state the obvious security warning here, this script will contain details of your database so make sure you set the correct permissions, I use 700 but this may change depending on your hosting provider. Also don’t place the backup folder in your public_html folder otherwise people will be able to see the contents of your database and again lets make sure the .sql have the correct permissions otherwise over users on your server will be able to access them.

I create a folder in my home folder called _backup and inside here I will have a directory for my backups mysql and a bash script called backup.sh. First I’ll create a few variables to contain the database credentials.

#!/bin/bash
# Database credentials
 user=""
 password=""
 host=""
 db_name=""

The line at the top is the shebang and is used to select the interpreter, in my case this is bash. Next I’ll declare a few other variables, 1 for which directory I want to use for the backup files. The other is for the current date as I also like to include the date in the file name.

# Other options
 backup_path="/path/to/your/home/_backup/mysql"
 date=$(date +"%d-%b-%Y")

Now we need to create the actual backup files, before doing so I run umask to set the default permissions for the backup file. This will give me 600 which will allow only my user to read and write to the file. Then we run the mysqldump utility with are variables from above. I create the file name using the database name and current date, so it’s easy to see from the file name which backup is which.

# Set default file permissions
 umask 177
# Dump database into SQL file
 mysqldump --user=$user --password=$password --host=$host $db_name > $backup_path/$db_name-$date.sql

The last step is to delete files older than 30 days, for this we will use the find utility. First we pass the path to the directory that contains the .sql files and use a wildcard to select all. Then we need to set 2 options, the first is mtime which we use to select files that are older than a given amount of days. The next option exec allows us pass the files that are found into another utility, which I will use rm to delete the files. The last few characters are to end the line.

# Delete files older than 30 days
 find $backup_path/* -mtime +30 -exec rm {} \;

And that’s it, all you need to do now is setup a cron job to run whenever you want to backup your database. I run this everyday at midnight but you can run it hourly, weekly or however else you want. The whole backup.sh is below.

#!/bin/bash
# Database credentials
 user=""
 password=""
 host=""
 db_name=""
# Other options
 backup_path="/path/to/your/home/_backup/mysql"
 date=$(date +"%d-%b-%Y")
# Set default file permissions
 umask 177
# Dump database into SQL file
 mysqldump --user=$user --password=$password --host=$host $db_name > $backup_path/$db_name-$date.sql
# Delete files older than 30 days
 find $backup_path/* -mtime +30 -exec rm {} \;

This is creating a local backup of your database files, and to be extra safe you should be backing up these files on another server in a different location as well. If you want help setting this up let me know if comments below.

  • Indispus

    Thanks!

  • Rajat Sethi

    how to backup each database in a separate file in a folder named as the present date?

  • Peter Furesz

    Just be aware to set root / directory as backup path, because the last find command will delete every file from your entire hard drive 🙂
    I suggest to use like this:

    find $backup_path/* -name *.sql -mtime +30 -exec rm {} ;

    • John

      I wish I saw this comment 5 hours ago. ugh!

  • seanrall

    How do you run this in a Cron Job? Im a noob so please be nice.

    • simonda86

      Hi Sean,

      Enter “crontab -e” in your command line, to edit your crontab. Then enter a new line in this file for the job. If you want to run this every day at midnight for example you would enter add the following.

      00 00 * * * /path/to/your/file

      I would put this script in the bin folder of my home directory so my path would be ~/bin/backup.sh

      I hope this helps.

      Simon

      • seanrall

        That is what I needed. Thanks!

  • Paul DiCecco

    The rm command is not working for me. I changed it to +5 and noticed it not working so I then tried +05 and still not deleting anything.

    # Delete files older than 30 days

    find $backup_path/* -mtime +05 -exec rm {} ;

    • Paul DiCecco

      The response from my hosting support:

      Unfortunately, the issue you are having would be
      considered coding-related and it is outside the scope of support. However, you
      should be able to use the -delete flag for find instead of having to -exec, and
      I noticed that you are using a asterisk without escaping. You may need to
      contact someone familiar with bash scripting to assist you if you are still
      having issues with your script.

      • simonda86

        Hi Paul,

        Changing the number of days to “+5” is correct, so there must be something else wrong here. Try running “find ./* -mtime +5” from the command line in the backup directory. This should show you a list of all files older than 5 days.

        • Paul DiCecco

          Yes, that worked correctly but the command

          find $backup_path/*.sql -mtime +5 -exec rm -f {} ;

          in my script doesn’t delete the one file that is over five days old.

          • Paul DiCecco

            ok it looks like it may be working but setting it to +5 actually keeps 6 days..

  • Hanna

    Hi,
    So I am completely new at this, so not even sure if this applies to what I am trying to do…
    But can I use this to run a cron job in cPanel? Currently I am backing up my databases using the following command:

    nice -n 19 mysqldump -u myusername –password=mypassword mydatabase -c | nice -n 19 gzip -9 > ~/backups/mydatabasename/myfilename-$(date ‘+%Y%m%d’).sql.gz

    can I add the find bit on to this to delete older backup files?

    sorry if this is a stupid question.

    • simonda86

      Hi Hanna,

      No question is a stupid one. 🙂

      Yes you can use the find command to delete the older backup files. Something along the lines of “find ~/backups/mydatabasename/* -mtime +30 -exec rm {} ;” would delete files that are older than 30 days.

      I hope that thelps!

  • Travis Hershberger

    Great little script. Even us old-hats need a refresher, we only set this stuff up once each time we setup a database/database server.

    I added 4 lines in mine to compress the backup file.

    #Compress the backup file
    7z a $backup_path/$db_name-$date.7z $backup_path/*.sql
    #Remove the original sql file
    rm -I $backup_path/*.sql

    Handy if you expect the database to be quite large, or just save a little drive space. I used 7z, but you could use any compression program you like (zip, gzip, etc)

    • Old comment, I know….but You should probably include some error checking. Otherwise if the compression fails, you have no backup.

      Something like (pseudocode):

      if backup successful: rm
      else: log an error somewhere (and notably, do not remove the sql file)

  • robPearce

    Thanks Simon – saved me some head scratching!

  • Brunobliss

    simple and awesome! I’ve added an array of databases and looped through each to preform a backup

    db=(database1 database2)

    for i in “${db[@]}”
    do
    mysqldump –user=$user –password=$password –host=$host $i > $backup_path/$i_name-$date.sql
    done

  • This is awesome! Worked like a charm!

  • Pingback: Cómo automatizar los Backup de MySQL con un script y Crontab - CDUser()

  • Awesome! Thanks for sharing this 🙂

  • cytopia

    You can also have a look at this script, which will take care about password security, automated encryption and compression and integrates well into Nagios:

    http://mysqldump-secure.org

  • great script

  • Luiz Barros

    Not work for me

    octal number out of range 11 :umask 117
    /home/admin/backup.sh line 13: /home/admin/web/default.domain/public_html/bk_my
    No such file or diretory

    find ‘/home/admin/web/default.domain/public_html/bk_mysqlr*’ : No such file or diretory

    Help – ME

  • Dharak

    I want to take backup of one MySQL Server to Another Server. How can it do it using scripting?

  • huzanspenta

    Hi,

    Thanks but it shows permission denied. Please support.

    • simonda86

      Hi,

      This is a MySQL issue, please check your connection details (username, password, host).

      Thanks,

      Simon

      • huzanspenta

        Thanks Simon. Is there anyway I can add time to the file name? like date and time

  • Nick Vlădiceanu

    awesome, thanks !!

  • nipon

    How to backup to a server with ssh access securely ?

    • simonda86

      Hi Nipon, I would recommend running the backup script on the database server and then use scp to copy the SQL files over to a remote server.

      • nipon

        do you have example script ?

        • simonda86

          Sorry I don’t have an example but it should be relatively simple to write.

    • Vural Acar

      @nipon you can simply use (linux only)
      >ssh {address-to-server} -l {username}
      >@Password: {enter your password}
      >crontab -e
      vim editor will be opened and press “i” to be able to insert.
      enter following line to run the backup.sh every 12 hours. (you can change, how you like)
      0 */12 * * * /path/to/your/site/backup.sh >/dev/null 2>&1

      this crontab line will run every 12hours..

      if you don’t know how to check the current path of your directory
      > pwd

      good luck 🙂

  • Haider Ali

    Variables should be in single quotes, other wise it can make escape issue like dollar sign in password will produce access denied.

    Script appreciated.

  • gsc 89

    Its better to use password=”

    The single quotes wont cause issues when using passwords which contain characters that would need escaping I spent literally 2 hours trying to figure this out.