Sign in
Insights

Bash script to mirror directory and database of website between servers

Ryan Sechrest

• 10 min read
Monkey holding a mirror and staring at their reflection.
Mirror, mirror in my hand, am I pretty?

When you have a site that’s an on-going project, and you’re using a development, staging, and production server configuration, time and time again you’ll have a need to mirror the  production site content with your other environments.

Previously, this either meant to manually (FTP) or semi-manually (rsync) update the files, export the database, and in the case of WordPress MU, update certain records in the database. While the entire act isn’t hard, it has a zero-fun factor.

I put some time aside to write a small bash script that could automate this for multiple sites with just two commands. The script could definitely be improved, but it’s functional and gets the job done nicely, but I’m always open to suggestions and improvements.

I’ll go through the entire script, and if you find it useful, you can adapt it to meet your needs. Any variables or data you may need to change, will be highlighted in red below the code box.

You can find the entire code in a Gist. Note that there are two versions. One for server to server, the other for server to localhost. We’ll discuss the server edition below.

Table of Contents

  1. Program Skeleton
  2. Server Environment
  3. Production/Local Server Variables
  4. Program Arguments
  5. Display Functions
  6. MySQL Functions
  7. Main Functions
  8. Download Files
  9. Export Database
  10. Upload Files
  11. Import Database
  12. Run Functions
  13. Run Synchronize
  14. Run Update
  15. Run
  16. Run Program

1. Program Skeleton

We start with the bash shebang, followed by some program meta data, which will print out on the screen when the program runs. You’ll find a sample output of this at the end of post.

#!/bin/bash

### Program meta data

# Program name
NAME='site'

# Program version
VERSION='1.0.0'

# Today's date
DATE=$(date +'%x %r %Z')

# Program header title
TITLE="Site v$VERSION on $HOSTNAME"

# Program header information
STATUS="$DATE by $USER"

# Program usage help
USAGE="Usage: $NAME [sync|update] [sitename]"

Things to edit:

  • Line 21: sitename

2. Server Environment

Next, we determine on which server the program is being executed on. This allows us to use the very same program on all servers, meaning we only have to maintain one instance of it.

### Server environment

ENV=''
case $HOSTNAME in
  DEV01.DOMAIN.COM)
    ENV='DEV'
    ;;
  STG01.DOMAIN.COM)
    ENV='STG'
    ;;
esac

Things to edit:

  • Line 5: DEV01.DOMAIN.COM
  • Line 8: STG01.DOMAIN.COM

If you type in hostname in Terminal on each of your servers, you’d replace DEV01.DOMAIN.COM and STG01.DOMAIN.COM with those values. You can add as many server environments as you have. What this will do is set a variable called ENV that we can later check to determine which set of credentials and domain to use.

3. Production/Local Server Variables

The program needs to know where the production web and database server is, and with which username the program should attempt to connect to each. You’ll be prompted for those passwords when the time comes.

The program also needs to know where your sites live on the production server, where they live on your local server, and where you want to temporarily store the files that will be downloaded from the production server.

### Production database server

# Hostname or IP address
MYSQL_PRD_HOSTNAME='hostname'

# Username
MYSQL_PRD_USERNAME='username'

### Production web server

# Hostname or IP address
SSH_PRD_HOSTNAME='hostname'

# Username
SSH_PRD_USERNAME='username'

### Local server paths

# Absolute path to local sites directory
# Ex: /var/www/domains
LOCAL_SITES_PATH='/var/www/domains'

# Absolute path to production sites directory
# Ex: /var/www/domains
PRD_SITES_PATH='/var/www/domains'

# Absolute path to local temporary sites directory
# Ex: /opt/sites
TEMP_SITES_PATH='/opt/sites'

Things to edit:

  • Line 4: hostname
  • Line 7: username
  • Line 12: hostname
  • Line 15: username
  • Line 21: /var/www/domains
  • Line 25: /var/www/domains
  • Line 29: /opt/sites

As a side note, I created a new database user that has read-only access to the production site, to ensure that nothing would be manipulated by accident.

4. Program Arguments

The program will take two arguments, which we will save as arg1 and arg2. This allows us to reference those arguments in our functions later.

### Save program arguments

arg1=$1
arg2=$2

The first argument will tell the program what to do, and the second which site to do it to.

5. Display Functions

The following functions are purely for aesthetic reasons and help keep certain visual elements uniform across the program.

## Display functions

# Display divider
function display_divider {
  echo '------------------------------------------------------------'
}

# Display new line
function display_newline {
  echo
}

# Display output start
function display_output_start {
  echo '> Starting...'
}

# Display output end
function display_output_end {
  echo '> Done.'
}

# Display program header
function display_header {
  display_newline
  display_divider
  echo " $TITLE"
  echo " $STATUS"
  echo " $ $NAME $arg1 $arg2"
  display_divider
  display_newline
}

# Display usage help
function display_usage {
  echo $USAGE
  display_newline
}

6. MySQL Functions

There is only one function here, and it will be executed when dealing with WordPress MU sites. You can see the arguments it takes below. It will essentially replace the production domain name with the domain you’re using in the environment the program is being executed on.

### MySQL functions

# Update imported database to match local environment
# $1 	mysql hostname
# $2 	mysql username
# $3 	mysql password
# $4	mydql database
# $5 	domain name
function wp_mu_db_update {
  host=$1
  user=$2
  pass=$3
  db=$4
  domain=$5

  query="mysql --host=$host --user=$user --password=$pass $db -e"

  echo "> Setting 'domain' field in 'wp_blogs' table to '$domain'..."
  $query "UPDATE wp_blogs SET domain = '$domain' WHERE blog_id = 1 AND site_id = 1";

  echo "> Setting 'site_url' field in 'wp_options' table to 'http://$domain/wordpress'..."
  $query "UPDATE wp_options SET option_value = 'http://$domain/wordpress' WHERE option_name = 'siteurl'";

  echo "> Setting 'home' field in 'wp_options' to 'http://$domain'..."
  $query "UPDATE wp_options SET option_value = 'http://$domain/' WHERE option_name = 'home'";

  echo "> Setting 'domain' field in 'wp_site' to '$domain'..."
  $query "UPDATE wp_site SET domain = '$domain' WHERE id = 1";

  echo "> Setting 'site_url' field in 'wp_sitemeta' table to 'http://$domain/'..."
  $query "UPDATE wp_sitemeta SET meta_value = 'http://$domain/' WHERE meta_key = 'siteurl' AND site_id = 1";
}

You could add more functions that perform different actions for different platforms.

7. Main Functions

Now we get into the meat and potatoes of this program. This section contains four functions:

  1. download_files – Download the files from production to a temporary directory on the local server
  2. export_database – Export the database from production to a temporary directory on the local server
  3. upload_files – Copy the files from the temporary directory on the local server to where they need to go on the local server
  4. import_database – Import the database into the local website that we want to update

As a side note, the reason we’re using a temporary directory in the first place is two-fold. One, it allows us to reset our local site easily at any point, and two, it allows you to pull those changes into another instance of that site. The beauty is that it can do all that without having to reconnect to production, i.e., less strain on production server.

7.1 Download Files

First, the program will check if the temporary directory you specified in step 3 exists. If not, it will create it.

Second, it will start synchronizing the src location (production) with the dst location (server you are on). It will delete anything that wasn’t present in the source, and skip any directories specified in ex (exclude).

# rsync
# -a, --archive			archive mode; same as -rlptgoD
# 	-r, --recursive		recurse into directories
# 	-l, --links			copy symlinks as symlinks
# 	-p, --perms			preserve permissions
# 	-t, --times			preserve times
# 	-g, --group			preserve group
# 	-o, --owner			preserve owner (super-user only)
# 	-D					same as --devices --specials
#		--devices		preserve device files (super-user only)
#		--specials		preserve special files
# -v, --verbose			increase verbosity
# --exclude=PATTERN		exclude files matching PATTERN
# --delete 				delete files that don’t exist on sender
# --delete-excluded		also delete excluded files on receiver

# Copy files from production web server to temporary directory
# $1 	path to files on production server
# $2 	path to files on local server
# $3	rsync exclude option(s)
function download_files {
  src=$1
  dst=$2
  ex=$3

  if [ ! -d "$dst" ]; then
    echo "> Creating temporary directory..."
    echo "  $dst"
    mkdir -p "$dst"
  fi

  if [ -d "$dst" ]; then
    echo "> Synchronizing production files with temporary directory..."
    echo "  Source      : $src"
    echo "  Destination : $dst"
    rsync --archive --verbose $ex --delete --delete-excluded "$src" "$dst"
  else
    echo "> Temporary directory does not exist"
    echo "  $dst"
  fi
}

7.2 Export Database

First, the program will ensure the temporary folder exists (as before), and create it if it doesn’t.

Second, it will connect to the production database and export everything, but excluding any tables specified in ignore.

# Export database from production database server to temporary directory
# $1 	database hostname
# $2 	database username
# $3	database name
# $4 	path to save mysql export
# $5 	mysqldump ignore-table option(s)
function export_database {
  host=$1
  user=$2
  db=$3
  dst=$4
  ignore=$5

  if [ ! -d "$dst" ]; then
    echo "> Creating temporary directory..."
    echo "  $dst"
    mkdir -p "$dst"
  fi

  if [ -d "$dst" ]; then
    echo "> Exporting production database to temporary directory..."
    echo "  Database    : $db"
    echo "  Destination : $dst"
    echo "  Filename    : $db.sql"
    mysqldump --verbose --host=$host --user=$user --password $db $ignore > "$dst/$db.sql"
  else
    echo "> Temporary directory does not exist"
    echo "  $dst"
  fi
}

7.3 Upload Files

The program will mirror the temporary directory with the equivalent directory of the local site.

# Copy files from temporary directory to local site
# $1 	path to temporary files on local server
# $2	path to real files on local server
function upload_files {
  src=$1
  dst=$2

  if [[ (-d "$src") && (-d "$dst") ]]; then
    echo "> Synchronizing temporary files with local site directory..."
    echo "  Source      : $src"
    echo "  Destination : $dst"
    rsync --archive --delete --delete-excluded "$src" "$dst"
  else
    echo "> Temporary or local site directory does not exist"
    echo "  Source      : $src"
    echo "  Destination : $dst"
  fi
}

7.4 Import Database

The program will replace your local database with the exported database from production. This shouldn’t be an issue, but if you have some test data in there, remember it’ll all be overwritten.

# Import production database from temporary directory to local database
# $1 	database hostname
# $2 	database username
# $3	database password
# $4 	database name
# $5 	path to mysql export
function import_database {
  host=$1
  user=$2
  pass=$3
  db=$4
  src=$5

  if [ -f "$src/$db.sql" ]; then
    echo "> Importing temporary database export into local database..."
    echo "  Filename : $db.sql"
    echo "  Source   : $src"
    echo "  Database : $db"
    mysql --host=$host --user=$user --password=$pass $db < "$src/$db.sql"
  else
    echo "> Temporary database export does not exist"
    echo "  Filename : $db.sql"
    echo "  Source   : $src"
  fi
}

8. Run Functions

Since we have two things (synchronize and update) the program can do, we need at least two functions to take care of this.

  1. run_sync – Get everything from production and store it in the temporary directory.
  2. run_update – Copy everything from the temporary directory into the local site.

This is the place where you define the specifics for each local site, like where the files need to be copied to, what the database name is, etc.

In addition, there is one other function below those two, which I call the main component, and it either calls run_sync or run_update, depending on the arguments the user provided.

8.1 Run Synchronize

The program will mirror the production files and database with the temporary directory.

# Run sync component
function run_sync {
  case $arg2 in

    sitename)
      display_output_start

      copy_from="$SSH_PRD_USERNAME@$SSH_PRD_HOSTNAME:$PRD_SITES_PATH/domain.com/www/htdocs/wp-content/uploads/"
      copy_to="$TEMP_SITES_PATH/$arg2/uploads/"
      exclude='--exclude=temp --exclude=wpcf7_uploads'
      download_files "$copy_from" "$copy_to" "$exclude"

      display_newline

      mysql_hostname=$MYSQL_PRD_HOSTNAME
      mysql_username=$MYSQL_PRD_USERNAME
      mysql_database='domain_com'
      export_to="$TEMP_SITES_PATH/$arg2"
      ignore="--ignore-table=$mysql_database.wp_w3tc_cdn_queue"
      export_database $mysql_hostname $mysql_username $mysql_database "$export_to" "$ignore"

      display_output_end
      ;;

    *)
      display_usage
      ;;

  esac
}

Things to edit:

  • Line 5: sitename
  • Line 8: domain.com/www/htdocs/wp-content/uploads
  • Line 9: uploads
  • Line 10: --exclude=temp --exclude=wpcf7_uploads
  • Line 17: domain_com
  • Line 19: --ignore-table=$mysql_database.wp_w3tc_cdn_queue

8.2 Run Update

The program will mirror the temporary directory with the equivalent local site files and database.

Note that you’ll need to update the sitename, the path to where it should be copied to, and the different database credentials for your environment.

# Run update component
function run_update {
  case $arg2 in

    sitename)
      display_output_start

      copy_from="$TEMP_SITES_PATH/$arg2/uploads/"
      copy_to="$LOCAL_SITES_PATH/domain.com/www/htdocs/wp-content/uploads/"
      upload_files "$copy_from" "$copy_to"

      mysql_username=''
      mysql_password=''
      domain_name=''
      case $ENV in
        DEV)
          mysql_username='username'
          mysql_password='password'
          domain_name='dev01.domain.com'
          ;;
        STG)
          mysql_username='username'
          mysql_password='password'
          domain_name='stg01.domain.com'
          ;;
      esac
      if [[ (-n $mysql_username) && (-n $mysql_password) && (-n $domain_name) ]]; then
        mysql_hostname='localhost'
        mysql_database='domain_com'
        import_from="$TEMP_SITES_PATH/$arg2"
        import_database $mysql_hostname $mysql_username $mysql_password $mysql_database "$import_from"
        wp_mu_db_update $mysql_hostname $mysql_username $mysql_password $mysql_database $domain_name
      else
        echo "> Unable to obtain MySQL credentials for $ENV server"
      fi

      display_output_end
      ;;

    *)
      display_usage
      ;;

  esac
}

Things to edit:

  • Line 5: sitename
  • Line 8: uploads
  • Line 9: domain.com/www/htdocs/wp-content/uploads
  • Line 17: username
  • Line 18: password
  • Line 19: dev01.domain.com
  • Line 22: username
  • Line 23: password
  • Line 24: stg01.domain.com
  • Line 28: localhost
  • Line 29: domain_com

8.3 Run

This is responsible for routing the user’s request.

# Run main component
function run {
  case $arg1 in

    sync)
      run_sync
      display_newline
      ;;

    update)
      run_update
      display_newline
      ;;

    *)
      display_usage
      ;;

  esac
}

9. Run Program

Last, but not least, this is what starts the program.

### Run Program

display_header
run
exit 0

Summary

If you save all this code in a file called site, copy it into the /usr/bin directory on the target server, give it 750 permissions (chmod 750 /usr/bin/site), then execute it:

[root@DEV01 ~]# site

------------------------------------------------------------
 Site v1.0.0 on DEV01.DOMAIN.COM
 10/22/2013 06:05:16 PM UTC by root
 $ site  
------------------------------------------------------------

Usage: site [sync|update] [sitename]

And then to synchronize:

[root@DEV01 ~]# site sync sitename

------------------------------------------------------------
 Site v1.0.0 on DEV01.DOMAIN.COM
 10/22/2013 06:07:46 PM UTC by root
 $ site sync sitename
------------------------------------------------------------

> Starting...
> Synchronizing production files with temporary directory...
  Source      : username@hostname:/var/www/domains/domain.com/www/htdocs/wp-content/uploads/
  Destination : /opt/sites/domain/uploads/
username@hostname's password: 
receiving incremental file list
[...]
sent 770 bytes  received 1484485 bytes  118820.40 bytes/sec
total size is 1267533727  speedup is 853.41

> Exporting production database to temporary directory...
  Database    : domain_com
  Destination : /opt/sites/domain
  Filename    : domain_com.sql
Enter password: 
-- Connecting to hostname...
[...]
-- Disconnecting from hostname...
> Done.

And lastly to update:

[root@DEV01 ~]# site update sitename

------------------------------------------------------------
 Site v1.0.0 on DEV01.DOMAIN.COM
 10/22/2013 06:11:30 PM UTC by root
 $ site update sitename
------------------------------------------------------------

> Starting...
> Synchronizing temporary files with local site directory...
  Source      : /opt/sites/domain/uploads/
  Destination : /var/www/domains/domain.com/www/htdocs/wp-content/uploads/
> Importing temporary database export into local database...
  Filename : domain_com.sql
  Source   : /opt/sites/domain
  Database : domain_com
> Setting 'domain' field in 'wp_blogs' table to 'dev01.domain.com'...
> Setting 'site_url' field in 'wp_options' table to 'http://dev01.domain.com/wordpress'...
> Setting 'home' field in 'wp_options' to 'http://dev01.domain.com'...
> Setting 'domain' field in 'wp_site' to 'dev01.domain.com'...
> Setting 'site_url' field in 'wp_sitemeta' table to 'http://dev01.domain.com/'...
> Done.

Hopefully this is useful to someone else. If you have any questions or comments, especially improvement suggestions, please leave them below.

Featured image by Andre Mouton.

Comments (2)

Previously posted in WordPress and transferred to Ghost.

mahender
January 22, 2014 at 5:30 am

Hi,

We have site for library.We will keep on updating the site.We are planing for mirror site for it.
I have understood with the help of rsync we can update the /var/www folder of mirror site.
How mysql database of mirror site will be in sync with production server.Could you please tell me how it can be achieved.

Thanks in advance,
Raja

Ryan Sechrest
January 22, 2014 at 8:55 am

The key commands are rsync (to synchronize files), mysqldump to export a database to a file, and mysql used to import a file into another database. With those three commands, all showcased in the above code, you can mirror files and databases between servers, or more specifically, keep one server (primary) synchronized with another (secondary).

If you look at my development kit page, toward the very bottom are links to the individual documentation of those commands.