This will be a first in a two part series on backing up hosted websites to a Ubuntu computer.  In today's article I'll document how to backup the MySQL databases to your web server, so we can grab them later.

Variables

I will use the following variables in my code snippets.  These are to be replaced with your specific information.

mysql_server - This is the server that your MySQL database is hosted, sometimes this is just localhost
mysql_username - This is the user name that connects to the MySQL server
mysql_password - This is the password for the MySQL user
mysql_database - This is the specific MySQL database, some sites may have more than one
webserver - This is the server that your website is hosted on
webserver_username - This is the user name required to connect to the web server
webserver_password - This is the password for the web user
local_username - This is the local computer user name

Creating the MySQL Backup Script

The websites I host run on a LAMP server, a Linux server, running Apache, MySQL, and PHP.  As a security feature, my host does not allow just any server to connect to the MySQL server, only the web server.  So, the first step is to ssh into your web server.

{codecitation}ssh webserver_username@webserver{/codecitation}

This will put us in the home folder of the user account of the web server, \home\webserver_username.  If we do an 'ls' command, we will see our web site folder and a few others.

So, let's create our MySQL backup script.

{codecitation}pico mysql_backup.sh{/codecitation}

This will start the pico text editor editing a file called mysql_backup.sh and paste the following code:

{codecitation}#!/bin/bash
cd /home/webserver_username/
mkdir mysql
suffix=$(date +%y%m%d)
mysqldump --opt --user=mysql_username --password=mysql_password --host=mysql_host mysql_database > mysql/mysql_database.$suffix.sql
tar -cf archives/mysql_backup.$suffix.tar mysql/*
rm -r mysql/{/codecitation}

If you have more than one database to backup, then repeat line 5 for each database to get a separate backup of that database to restore.  Then, we do a ctrl+o to save the file and a ctrl+x to exit pico.  This code will create a mysql folder inside of the web server user's home folder, and create a backup of the MySQL database with a date appended to it in the mysql folder.  It will then create a compressed archive of the mysql folder inside of an archives folder, also with the date.  Then, finally, delete the mysql folder it created.

To make the script work, it needs two things, an archives folder to put the compressed files in, and to make the script executable.  So, run the following commands on the web server.

{codecitation}mkdir archives{/codecitation}
{codecitation}chmod +x mysql_backup.sh{/codecitation}

To test this, we can run the script manually by entering in

{codecitation}./mysql_backup.sh{/codecitation}

and confirming the files created by looking in the archives folder

{codecitation}ls archives{/codecitation}

If the code does not work, double check your spelling on the user accounts, passwords and server names.  Sometimes copying line 5 and pasting it as a command will tell you exactly where you messed up.

Scheduling the Backup

Now that we know the code works, it's time to schedule the backup.  This is going to vary greatly across web hosting providers.  However, with DreamHost, it's as easy as logging in to https://panel.dreamhost.com going to the "Cron Jobs" under the "Goodies" section.  Then click on "Add a New Cron Job"

Select the appropriate user, title it "Weekly MySQL Backups", put in your e-mail if you want notifications, and give it the command {codecitation}/home/webserver_username/mysql_backup.sh{/codecitation}, have it run weekly, and click "Add".

Your MySQL databases will now be automatically backup up weekly.  You may want to create a calendar appointment to remind yourself to go and clean out the old backups quarterly to keep your website size down to a reasonable level.