This tutorial assumes you have some experience with Linux administration although I try to hand-hold as much as possible. If you are on a shared hosting solution and don’t have root access, you can easily modify the shell script we will create later to target your user’s home directory on the server.
You also need to know the address of your SQL server. In my case, I am working on a VPS so the SQL server is local (so I just use localhost) but you might need to use a URL or IP where your SQL server is located.
Okay… digging in. Here is the gist’ of it…
1. Create a new user for your MySQL database that will be just for backups
2. Create some folders on your server to store your backups locally
3. Create a script to actually create the database backups, compress them, clean up backups older than 30 days
4. Test the script
5. Setup the script to run as a cron job.
Here we go!
1. New MySQL database user – from the command line
Login to MySQL
mysql -h <address of your mysql server> -u <name of your mysql root user> -p
#enter your password at the prompt that appears
Some actual examples of this might look like:
mysql -h localhost -u root -p #this is for a local install of mysql and logging in as root
mysql -h 18.104.22.168 -u root -p #this is an example with a made-up public IP address
mysql -h external-db.s12345.gridserver.com -u -p #this is an example of what logging into a Media Temple shared hosting grid-server MySQL instance looks like
Once you are in, you will see a prompt that looks like this “mysql>”. You can now enter commands…
show databases; #this command should give you a list of databases on the mysql server, note the name of the database for your website
create user backup; #this creates a new user named "backup"
grant all on <db_name>.* to 'backup'@'localhost' identified by '<db_password>'; #this gives that user full admin rights on the database and sets a password
exit #this closes the mysql prompt
Okay, because I know how hard it can be to interpret syntax here is an example of that last command for a made up database called “contosodb”
grant all on contosodb.* to 'backup'@'localhost' identified by 'contpAss12word';
Make sure you get all of the single ‘quote’ marks correct and end each command with a semi-colon.
You can put whatever password you want for that user on that database. Make sure you take note of it. Now we have our backup user all setup lets move on to creating the folder structure for backups.
2. Setting up the Backup Folder Structure
I am using a folder in the root of my server called “/backup” – If you are on shared hosting your folder structure will probably need to look a little different. Like /home/username/backup. You need to figure it out before you proceed. I recommend whenever you create a script to think through when it makes sense to use absolute paths vs relative paths. In this case, I use absolute paths because I like having the versatility of putting my script file wherever I want on my server. If you are lost regarding relative/absolute that is another (short) discussion for another day. You can just try to follow my example.
Okay, so I am working from root and have created the following file structure. I will use this site as an example because I actually host multiple sites on my server. I keep a separate folder of backups for each site. So a file structure for contoso.com might look like this:
Here is how you can create this structure, you can do this from a non-elevated prompt so it gets your permissions correct for your regular user:
If you did this as root, and want access to your files from another non-elevated user account, you can change the group ownership of the folders but I won’t go into that now.
3. Create the script.
Okay, this is where I have done the heavy lifting but you will need to make it unique to your environment. Wherever you see the following !some description! you need to fill in your information (i.e. wipe out everything including the exclamation points and put your info in). So copy and paste this into a text editor, modify it, then we can copy and paste it into a new file on the server.
##BACKUP !sitename! database
mysqldump -h !IP or HOSTNAME of SQL SERVER! --opt -u backup --password=!backup user password! !database name! > /backup/database/!sitefoldername!/!sitename!-backup-$DATE.sql
zip /backup/database/!sitefoldername!/!sitename!-backup-$DATE.zip /backup/database/!sitefoldername!/!sitename!-backup-$DATE.sql
find /backup/database/!sitefoldername!/* -type f -mtime +30 -delete
I know syntax can be a pain in the rear so here is a sample example with all the variables filled in. This is for the contoso.com example site and database above:
##BACKUP CONTOSO.COM DATABASE
mysqldump -h localhost --opt -u backup --password=contpAss12word contosodb > /backup/database/contoso/contoso-backup-$DATE.sql
zip /backup/database/contoso/contoso-backup-$DATE.zip /backup/database/contoso/contoso-backup-$DATE.sql
find /backup/database/contoso/* -type f -mtime +30 -delete
Once you get all of your stuff filled in and looking correct, on your server do the following to create a new file and then cut and paste this info in to the file:
cd /backup ##or wherever the backup directory is that you went with
vim db-backup.sh #this will create a new blank file and take you into the VIM editor to edit it
#you will need to hit the "i" key to go into insert mode. then you can just copy the text from your text file into your shell (if you are using SSH which I assume you are...) and then save and close vim.
One other note about my method… I use zip to compress my files as it compresses pretty well and works with windows. This isn’t built-in to most ubuntu installs and you might need to install it with:
sudo apt-get install zip
Okay, so you should now have a new file called db-backup.sh
We need to make this file executable and then we can test…
chmod +x db-backup.sh
We are now ready to move on with testing our script.
4. Test your script
If your script executes successfully, you should get something like the following output:
Thu Apr 17 09:24:35 EDT 2014
adding: backup/database/contoso/contoso-backup-20140417.sql (deflated 88%)
You should also now be able to sftp into your server and download the zip archive, open it up, and see your database backup inside. You can use this for database restores in the even your site gets royally jacked up.
Now, we want this script to run automatically daily. So assuming all has gone well thus far, we are going to setup a cron job to run this script every day.
5. Setup your script to run as a cron job and output to a log file
Cron is a simple yet powerful task scheduling tool that is part of most linux builds. Cron involves a file for each user and it can execute things as that user. I recommend setting this up as the root user on your box if you can, however you can set it up using a regular user as long as all of your permissions on your folders and script file are correct. I am setting up the script to run daily.
Login and elevate your prompt to root if you need to (skip this if you don’t):
open up cron for the logged in user (root if you elevated) and select your editor.
##if this is your first time using crontab command, it will present you with options for which editor to use for editing the cron file. My preference is usually VIM but you can use whatever you are comfortable with
Now that you are in your editor, go to the bottom and create a new line with the following:
01 03 * * * /backup/db-backup.sh >> /backup/databasebackup.log
You may need to tweak your line in cron if you are not having much luck getting your script to run. You can adjust the first two numbers to set the minute and hour the script runs to only a minute or two away from the current time of day for easy testing. This is a good discussion regarding some of the idiosyncrasies of getting cron to run scripts: http://askubuntu.com/questions/264607/bash-script-not-executing-from-crontab and here http://askubuntu.com/questions/23009/reasons-why-crontab-does-not-work
If you want to learn how to further customize cron, you can reference the following: https://help.ubuntu.com/community/CronHowto
The above cron command will run the backup script we created at 3:01 AM every day, of every week, of every month. It will also create (the first time) and then append the output of the script to that databasebackup.log file so there is a record of the backups that occur every day.
You will need to change your pathing above if you are aren’t working from root.
Assuming everything went okay you should be golden for database backups. I will eventually create a follow-up article on backing up your webfolder as well at least once a month.