Simon Davies Digital ltd

Backing up MySQL Databases

Posted by Simon Davies & 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.

Contact

Want to hear more about Simon Davies Digital and what we can do for your business or simply want to say hello, then get in touch.

+44 7846 400933
[email protected]