In this article, we will go over how to backup your database if your host does not allow remote MySQL connections. Please note that you will need SSH access.
Step 1: SSH into your web server
First we will need to SSH into our web server. Open up a terminal program (Terminal/iTerm if you are on Mac, Command Line if you are on Windows) and type in the following substituting your information in:
ssh <ssh_username>@<webserver_hostname>
You will then be asked to enter your password. Enter it and your terminal should look like the following:
Step 2: Make sure you can connect to your MySQL server
Now that we are SSH'd into the web server. Let's make sure that we can connect to the MySQL server and perform a test mysqldump. Run the following command, substituting your information in.
mysqldump -h <database_hostname> -u<database_username> -p'<database_password>' <database_name> > test.sql
Note depending on how large your database is, this may take some time. Once you are able to interact with the shell again and you see no error output, we can check to make sure the dump completed successfully. You can use any unix based text editor or tool such as vim to look at it.
less test.sql
If it is successful, we can see our mysqldump contents:
You can then remove the test file by running
rm test.sql
Step 3: Create mysqldump shell script
Now that we know we can connect to our database and run mysqldump correctly, we need to write a shell script that will do this for us. First, we will need to make a directory for the mysqldumps to go into. This can be accomplished using the 'mkdir' command:
mkdir mysqldumps
Set up an .htaccess file so the backups cannot be access from HTTP.
echo "Deny from all" > mysqldumps/.htaccess
Now that there is a directory for the backups to be stored so we can create the shell script. Using an editor such as nano, open a new shell script as shown below:
nano mysqldump.sh
Once the file is open, we can add the necessary scripts. If you would like to delete backups that are older than seven days in order to save space, you can add the following line:
find <path_to_mysqldumps_directory> -name '<expression_matching_your_mysqldump_files>' -mtime +7 -delete
To add the mysqldump command, we will be using a very similar command to the one we used earlier to test. Be sure to place your specific information in the command:
mysqldump -h <db_hostname> -u<db_username> -p'<db_password>' <db_name> > <path_to_your_mysqldumps_directory>/<file_name>-$(date +%Y-%m-%d).sql
So for example, here is a command filled with data:
mysqldump -h myhost.com -umyusername -p'mypassword' mydbname > ~/mysqldumps/wordpress-mysqldump-$(date +%Y-%m-%d).sql
As you can see, we included some date formatting so that you can see what date the backup was from. If you have multiple databases, you can add as many mysqldump commands as you would like. Just be sure to change the filename for each one. Also, take note of how since my backups are named wordpress-mysqldump-date, I used '*-mysqldump-*' for the expression in the find command.
This is what your final shell script might look like:
#Shell script that gets a mysqldump from the remote databases and writes them to the mysqldumps folder
#deletes the dump files that are older than 7 days
find /path/to/backups/mysqldumps/ -name '*-mysqldump-*' -mtime +7 -delete
#mysqldump command for the database
mysqldump -h <hostname> -u<user_name> -p'<password>' <database_name> > /path/to/backups/mysqldumps/wordpress-mysqldump-$(date +%Y-%m-%d).sql
Once you are happy, save and exit from the script. If you are using vim, you can follow the below command:
press ESC to get out of insert mode, then :wq to save and exit
Next set executable permission on the script by running chmod u+x mysqldump.sh.
To test that the script works, call the following command and then check in the directory to make sure that the backups are there.
./mysqldump.sh
Go into your mysqldumps directory and look for your backup to make sure that the script worked:
Make sure that when you open the file, you see the mysqldump data.
Step 4: Automate the backup
In order to automate the backup, we will have to use a cron job. To access your server's cron jobs, use the following command:
crontab -e
Now we can add a cron job that calls the script automatically for us. Here is a chart taken from the Wikipedia article on cron jobs that details the syntax.
# * * * * * command to execute # ┬ ┬ ┬ ┬ ┬ # │ │ │ │ │ # │ │ │ │ │ # │ │ │ │ └───── day of week (0 - 6) (0 to 6 are Sunday to Saturday, or use names) # │ │ │ └────────── month (1 - 12) # │ │ └─────────────── day of month (1 - 31) # │ └──────────────────── hour (0 - 23) # └───────────────────────── min (0 - 59)
For example, if I wanted to run the backup script every day at 2am, I would use the following command:
0 2 * * * path/to/backups/mysqldump.sh
Once you have written your cron job for the interval that you see fit, be sure to save the file and you are done. Now your cron job will run at the specified time and run your backup script. Note that the cronjob will normally run on the timezone set on the box. You can check this with cat /etc/timezone.
Frequently Asked Questions
What if my database credentials change?
If your database credentials change, you will need to update your shell script to reflect the changes.
Can I add another database to backup?
Yes, you can add another database by adding another mysqldump command to your current shell script. Be sure to use your new database's credentials as well as modify the file that it writes to so that your script won't write to the same file twice.
My mysqldump file is not complete. Why is that?
If your mysqldump file is not complete, it is highly likely that it ran into an error while executing. To test this, run the mysqldump command in your shell outside of the script and see if any errors are returned. Also, it is possible that the mysqldump took longer to complete than the server allows processes to run for.