Total: $0.00

Migrate Wordpress Database and Files

By IT Dan, 06/19/2018 - 19:49

I wanted a script to automate the copying of a live WordPress website to a "staging" site on the same machine. This was tested on Debian Jessie.

Before you begin:
In this case make sure the same database user has the appropriate permissions on both databases. And also note that the RELOAD privilege is needed to use the `mysqldump --lock-all-tables` option on the existing live database.

Script below:


echo "deleting old files"
rm --recursive --force $base_directory/$new_directory
mkdir $base_directory/$new_directory
echo "copying new files"
cp --archive $base_directory/$old_directory/. $base_directory/$new_directory/
echo "dumping old database"
mysqldump --lock-all-tables --result-file=$db_name_old.sql --user=$db_user --password=$db_pass $db_name_old 
echo "droping new database"
mysql --user=$db_user --password=$db_pass -e "DROP DATABASE IF EXISTS $db_name_new"
mysql --user=$db_user --password=$db_pass -e "CREATE DATABASE $db_name_new"
echo "importing database"
mysql --user=$db_user --password=$db_pass $db_name_new < $db_name_old.sql
mysql --user=$db_user --password=$db_pass -e "USE $db_name_new; UPDATE ${db_prefix}options SET option_value = replace(option_value, 'https://$old_host', 'https://$new_host') WHERE option_name = 'home' OR option_name = 'siteurl';"
mysql --user=$db_user --password=$db_pass -e "USE $db_name_new; UPDATE ${db_prefix}posts SET guid = replace(guid, 'https://$old_host','https://$new_host');"
mysql --user=$db_user --password=$db_pass -e "USE $db_name_new; UPDATE ${db_prefix}posts SET post_content = replace(post_content, 'https://$old_host', 'https://$new_host');"
mysql --user=$db_user --password=$db_pass -e "USE $db_name_new; UPDATE ${db_prefix}postmeta SET meta_value = replace(meta_value,'https://$old_host','https://$new_host');"
echo "updating config file"
sed --in-place s/$old_directory/$new_directory/g $base_directory/$new_directory/wp-config.php
sed --in-place "s/'DB_NAME', '$db_name_old'/'DB_NAME', '$db_name_new'/g" $base_directory/$new_directory/wp-config.php
echo "done" 

Should be pretty easy to follow.

Some notes:

  • Your password will be visible to anyone else logged into the server by including it in the mysql commands. If not an acceptable risk, search for the `~/.my.cnf` method.
  • It doesn't delete the dumped database, I thought it is nice to have a backup of the original database just in case.
  • The first `sed` command was to update the WP Super Cache line that points to the cache location.
  • The second `sed` command is to replace the database name. I'm using the same database user and password, but the script could be modified to allow different credentials by copying and editing this line to replace them.