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.
