Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Script to download MySQL database & files from live to development server
#!/bin/bash
#===============================================================================
# This is a template for a script I use on a lot of sites to copy the database
# (MySQL) and any uploaded files to the development site, and modify the
# database as required.
#
# The script should be on the development server. The live site can either be on
# the same server, or a remote server connected via SSH.
#
# Most of the editable settings are at the top, for easy setup, but it can be
# customised as much as necessary.
#===============================================================================
set -o nounset -o pipefail
# I typically keep this script in the root of the site
cd $(dirname $0)
#===============================================================================
# Settings
#===============================================================================
# Development site
dev_db='devdbname'
dev_db_user='devdbusername'
dev_db_pwd='devdbpassword'
dev_files_path='path/to/files' # Absolute or relative to the current directory
# Directory to backup the development database to
backups_dir='db-backups'
# Live site
live_host='myliveserver.co.uk' # Blank if local
live_user='livesshusername' # Blank if local
live_db='livedbname'
live_db_user='livedbusername'
live_db_pwd='livedbpassword'
live_files_path='/var/www/path/to/files' # Absolute or relative to $HOME (if using SSH) or current directory (if local)
# MySQL script to run after downloading the development database
read -r -d '' mysql_script <<'END_MYSQL_SCRIPT'
UPDATE wp_options
SET option_value = 'me@example.com'
WHERE option_name = 'admin_email';
UPDATE wp_posts
SET post_content = REPLACE(post_content, 'http://www.example.com', 'http://dev.example.com');
UPDATE wp_postmeta
SET meta_value = REPLACE(meta_value, 'http://www.example.com', 'http://dev.example.com')
WHERE LEFT(meta_value, 2) != 'a:';
UPDATE wp_users
SET user_email = CONCAT('me+user-', ID, '@example.com');
END_MYSQL_SCRIPT
# PHP script to run after downloading the development database
# (Useful for altering serialized data, which is tricky/impossible to do through SQL)
read -r -d '' php_script <<'END_PHP_SCRIPT'
// Load WordPress API
require_once 'www/wp-load.php';
// Deactivate plugins
require_once 'www/wp-admin/includes/plugin.php';
deactivate_plugins('google-analytics-for-wordpress/googleanalytics.php');
deactivate_plugins('w3-total-cache/w3-total-cache.php');
// Update serialized options
$options = get_option("si_contact_form");
if ($options) {
$options['email_to'] = 'Administrator,me@example.com';
update_option("si_contact_form$i", $options);
}
END_PHP_SCRIPT
#===============================================================================
ask() {
# http://djm.me/ask
while true; do
if [ "${2:-}" = "Y" ]; then
prompt="Y/n"
default=Y
elif [ "${2:-}" = "N" ]; then
prompt="y/N"
default=N
else
prompt="y/n"
default=
fi
# Ask the question - use /dev/tty in case stdin is redirected from somewhere else
read -p "$1 [$prompt] " REPLY </dev/tty
# Default?
if [ -z "$REPLY" ]; then
REPLY=$default
fi
# Check if the reply is valid
case "$REPLY" in
Y*|y*) return 0 ;;
N*|n*) return 1 ;;
esac
done
}
# Make sure this isn't run accidentally
ask 'Are you sure you want to overwrite the development site?' || exit
# Check the settings have been filled in above
if [ -z "$live_db" ]; then
echo "This script has not been configured correctly." >&2
exit 1
fi
# Take ownership of files, to ensure they are overwritten properly later
if [ -n "$dev_files_path" ]; then
echo "Taking ownership of files..."
sudo chown -R "$USER" "$dev_files_path" || exit
fi
# Backup database
if [ -n "$backups_dir" ]; then
echo "Backing up existing development database..."
if [ ! -d "$backups_dir" ]; then
mkdir $backups_dir || exit
fi
mysqldump --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "$dev_db_user" "$dev_db_pwd") "$dev_db" | bzip2 -9 > "$backups_dir/`date +%Y-%m-%d-%H.%M.%S`.sql.bz2" || exit
fi
# Empty database
echo "Clearing existing development database..."
(
echo "SET FOREIGN_KEY_CHECKS = 0;"
# Tables
mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "$dev_db_user" "$dev_db_pwd") "$dev_db_name" -Ne "SHOW FULL TABLES WHERE Table_Type = 'BASE TABLE'" \
| cut -f1 \
| while read -r table; do
if [ -n "$table" ]; then
echo "DROP TABLE \`$table\`;"
fi
done
# Views
mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "$dev_db_user" "$dev_db_pwd") "$dev_db_name" -Ne "SHOW FULL TABLES WHERE Table_Type = 'VIEW'" \
| cut -f1 \
| while read -r view; do
if [ -n "$view" ]; then
echo "DROP VIEW \`$view\`;"
fi
done
# Procedures
mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "$dev_db_user" "$dev_db_pwd") "$dev_db_name" -Ne "SHOW PROCEDURE STATUS" \
| cut -f2 \
| while read -r procedure; do
if [ -n "$procedure" ]; then
echo "DROP PROCEDURE \`$procedure\`;"
fi
done
# Functions
mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "$dev_db_user" "$dev_db_pwd") "$dev_db_name" -Ne "SHOW FUNCTION STATUS" \
| cut -f2 \
| while read -r function; do
if [ -n "$function" ]; then
echo "DROP FUNCTION \`$function\`;"
fi
done
echo "SET FOREIGN_KEY_CHECKS = 1;"
echo "ALTER DATABASE DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;"
) | mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "$dev_db_user" "$dev_db_pwd") "$dev_db" || exit
# Copy database
echo "Copying database..."
if [ -n "$live_host" ]; then
printf "[client]\nuser = %s\npassword = %s" "$live_db_user" "$live_db_pwd" | ssh "$live_user@$live_host" "set -o pipefail; mysqldump --defaults-extra-file=<(cat) '$live_db' | bzip2 -9" | bunzip2 | mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "$dev_db_user" "$dev_db_pwd") "$dev_db" || exit
else
mysqldump --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "$live_db_user" "$live_db_pwd") "$live_db" | mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "$dev_db_user" "$dev_db_pwd") "$dev_db" || exit
fi
# Update database
if [ -n "$mysql_script" -o -n "$php_script" ]; then
echo "Updating database..."
fi
if [ -n "$mysql_script" ]; then
echo "$mysql_script" | mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "$dev_db_user" "$dev_db_pwd") "$dev_db" || exit
fi
if [ -n "$php_script" ]; then
echo "<?php $php_script" | php || exit
fi
# Copy files
if [ -n "$dev_files_path" -a -n "$live_files_path" ]; then
echo "Copying files..."
if [ -n "$live_host" ]; then
rsync -r --links --delete --stats "$live_user@$live_host:$live_files_path/" "$dev_files_path" || exit
else
rsync -r --links --delete --stats "$live_files_path/" "$dev_files_path" || exit
fi
echo
chmod ugo+rwX -R "$dev_files_path" || exit
fi
# Done
echo "Done."
@cannycookie

This comment has been minimized.

Copy link

commented Nov 1, 2013

Thanks dude, that's really useful, I run this process so many times manually, can't believe I've not bothered to work a script out already.

@davejamesmiller

This comment has been minimized.

Copy link
Owner Author

commented Aug 22, 2014

In MySQL 5.6+ you will get this warning message:

Warning: Using a password on the command line interface can be insecure.

At some point I will work out a better solution (e.g. putting the password in a temp file), but for now it's right that it's insecure and you should probably only use this if you trust the other users on both your development and live machines.

@moses-gangipogu

This comment has been minimized.

Copy link

commented Sep 12, 2014

👍

@davejamesmiller

This comment has been minimized.

Copy link
Owner Author

commented Nov 13, 2014

I have fixed the password warning - the password should no longer show up in ps on either the local or remote server.

@chika94

This comment has been minimized.

Copy link

commented Dec 25, 2017

Hello @davejamesmiller, am new new here... how do i implement the script above

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.