Shell Script to Backup WordPress Database

Instructions

• 1

  Find Out the Structure & Make the Backup Script
  

  Shell Scripts can be easily executed as text wp-content/uploads and during their execution, they can run one or a set of commands. Given below is the actual script; make sure you follow along and adapt the code accordingly.

  
  

  - You have SSH access on example.org — your username is username and the password is password.

  
  

  - Your home directory is at /home/username/ and your website’s root directory is/home/username/www/example.org/, so that’s where my WordPress wp-content/uploads are.

  
  

  - MySQL database is operating on localhost. Its user id is mysqluser and password is mysqlpass. You can make database name as example_org.

  
  

  - The shell script will be stored in /home/username/scripts/ and the backups will go to/home/username/backups/. So always remember those directories exist before continuing.

  
  

  One thing to keep in mind is that you need to log on to your server through SSH. Use three simple commands that are written below to browse your scripts directory. Then create a new executable file by using these commands.

  
  

  $ cd /home/username/scripts/
  $ touch backup.script
  $ chmod +x backup.script

  
  

  Now, you can edit the contents of the backup script file by using text editors such as vim, emacs, nano etc.
• 2

  How to Define Variables and Transforms
  

  There are many variables used throughout the code so that you can easily manage the script afterwards. In addition to that you can replicate it for other websites as well. You need to have a good understanding on variables in bash for this, and make sure to write interpreter header on the start of your script file.

  
  

  #!/bin/bash
  NOW=$(date +"%Y-%m-%d-%H%M")
  FILE="example.org.$NOW.tar"
  BACKUP_DIR="/home/username/backups"
  WWW_DIR="/home/username/www/example.org/"

  
  

  Append the following code to your script;

  
  
  DB_USER="mysqluser"
  DB_PASS="mysqlpass"
  DB_NAME="example_org"
  DB_FILE="example.org.$NOW.sql"
  WWW_TRANSFORM='s,^home/username/www/example.org,www,'
  DB_TRANSFORM='s,^home/username/backups,database,'
  
  
  

  The transforms that are defined here are not necessary, but your archive structure will be clean by using these transforms. However, these are used as transform expressions during archive creation. This will help you to store WordPress directory wp-content/uploads in a directory called www.  On the other hand, the database dump will go in a folder called database. Make sure to avoid a single mistake when writing this script, as it can result in an invalid transform expression.

  
  
• 3

  File Archive Creation and Backup MySQL

  
  

  You have defined all variables in the upper part. Now you have to use these variables in expressions with tar and mysqldump commands. You need to have a good understanding of these variables as they play a vital role in your day to day server maintenance life. Add the following code to the backup script.

  
  
  
  
  tar -cvf $BACKUP_DIR/$FILE --transform $WWW_TRANSFORM $WWW_DIR
  mysqldump -u$DB_USER -p$DB_PASS $DB_NAME > $BACKUP_DIR/$DB_FILE
  
  
  

  The v flag that is used for the tar command is helpful in running in verbose mode. You can use it for debugging as well when things are not going in a right direction. The second line of the code makes an SQL dump of your database into the backups directory.

  
  
• 4

  Merging, Cleaning Up and Compressing the script

  
  

  These commands will add into the database archive file and  you do not need to deal with two separate wp-content/uploads. The structure becomes very clean when you put it into the database directory within the archive. Add the following code in your script.

  
  tar --append --file=$BACKUP_DIR/$FILE --transform $DB_TRANSFORM $BACKUP_DIR/$DB_FILE
  

  rm $BACKUP_DIR/$DB_FILE
  gzip -9 $BACKUP_DIR/$FILE

  
  

  Now you are almost done with your backup script. In case you want to test it, run it directly from the command line and then look at the backup directory. In addition to that, unarchive the result file with tar-svf so that you will become familiar on how data is stored inside the archive.
• 5

  Task Scheduling: WordPress Backups with Crontab

  
  

  Ubuntu Server is used here that usually sticks all Cron related stuff in the /etc/crontab and various other parts to /etc/cron. You need to have a very good control over when your backups will be made. Now make a new daily executable file:

  
  
  
  
  $ cd /etc/cron.daily/
  $ sudo touch example_org_backup
  $ sudo chmod +x example_org_backup
  
  
  

  Then append the below content to the script.

  
  
  
  

  #!/bin/bash

  
  

  /home/username/scripts/backup.script

  
  
  
  
  
  

  It is quite simple, and the backup script that is created before is launched on daily basis. It should be noted that default setup will run the script as root. Consequently, it will create your backup wp-content/uploads owned by root, and every one can read it. You can either solve it by configuring cron or append a chmod/chown command to the script. It will enable you to change the privileges or the owner/group of the resulted backup file.

  
  
• 6

  Backup Files Downloading
  If you are using a Mac or a Linux environment locally then you need to have all the UNIX tools. This will help you to grab the wp-content/uploads. In case you are using windows, use Google for “rsync windows”. You can also use other tool that enables you to grab wp-content/uploads from SSH protocol.
  In order to use rsync, make a new directory where you’d like to store your backup wp-content/uploads add the following:
  
  $ mkdir my_backups && cd my_backups
  $ rsync example.org:/home/username/backups/*