Synchronizing WordPress multi-site database and media files from production to staging environment

We have a multi-site  WordPress setup that we want to stage development to, so therefore we need to have an automated way to synchronize the data to our staging environment.

We will setup a sync account on both servers, setup ssh access via keys and get a dump of the production database, replace the staging database and update it to work on our staging environment. We will then copy all uploaded files.

If you want to just get something working fast, you can do the preparations step and then download this script.

Preparations

Create user accounts

Run the following command on both computers to create a sync user, we call him wpsync:

useradd wpsync
mkdir /home/wpsync
chown wpsync:wpsync /home/wpsync

Setup certificates

On staging server, run the following command and hit enter (default) on all questions:

#staging
sudo -u wpsync ssh-keygen -t rsa

Copy the contents of /home/wpsync/id_rsa.pub from the staging server to /home/wpsync/.ssh/authorized_keys on the production server

#staging
cat id_rsa.pub ssh-rsa <KEYDATA> wpsync@staging

#production
mkdir /home/wpsync/.ssh
echo "ssh-rsa <KEYDATA> wpsync@staging" > /home/wpsync/.ssh/authorized_keys

Accept host signature, answer yes:

#staging
sudo -u wpsync ssh production.example.com exit

Setup MySQL access on production server (MySQL documentation)

CREATE USER 'wpsync'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT,LOCK TABLES ON wordpress_mu.* TO 'wpsync'@'localhost';

Setup MySQL access on staging server (MySQL documentation)

CREATE USER 'wpsync'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON wordpress_mu.* TO 'wpsync'@'localhost';

The manual way

Copy database from production to staging

We need to dump production database, import it to staging, and update the URLs in the database.

#staging
sudo -u wpsync ssh production.example.com "mysqldump wordpress_mu --password=password" > /home/wpsync/wordpress_mu.dump
mysql wordpress_mu --password=password < /home/wpsync/wordpress_mu.dump

Execute the following on staging MySQL database.
This script will update the site URL references in the WordPress database

delimiter //
DROP PROCEDURE IF EXISTS update_wp_procedure;
CREATE PROCEDURE update_wp_procedure()
BEGIN
        DECLARE done INT DEFAULT 0;
        DECLARE tblName TEXT;
        DECLARE tblCursor CURSOR FOR SELECT table_name FROM information_schema.TABLES where table_schema = 'wordpress_mu' and ( table_name LIKE 'wp_%_options' OR table_name = 'wp_options');
        DECLARE tblCursor2 CURSOR FOR SELECT table_name FROM information_schema.TABLES where table_schema = 'wordpress_mu' and ( table_name LIKE 'wp_%_posts' OR table_name = 'wp_posts');
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

        OPEN tblCursor;
        read_loop: LOOP
                FETCH tblCursor INTO tblName;
                IF done THEN
                        LEAVE read_loop;
                END IF;
                SET @cmd = CONCAT('update ',tblName,' set option_value = replace(option_value,''production.example.com'',''staging.example.com'') where option_name IN (''siteurl'', ''home'');');
                PREPARE stmt FROM @cmd;
                EXECUTE stmt;
                DROP PREPARE stmt;
        END LOOP;
        CLOSE tblCursor;

        OPEN tblCursor2;
        SET done = 0;
        read_loop2: LOOP
                FETCH tblCursor2 INTO tblName;
                IF done THEN
                        LEAVE read_loop2;
                END IF;

                SET @cmd = CONCAT('update ', tblName, ' set post_content = replace(post_content,''production.example.com'',''staging.example.com'');');
                PREPARE stmt FROM @cmd;
                EXECUTE stmt;
                DROP PREPARE stmt;
        END LOOP;
        CLOSE tblCursor2;
END//

delimiter ;

CALL update_wp_procedure();
DROP PROCEDURE update_wp_procedure;

update ${TBL_PREFIX}blogs set domain = replace(domain,'production.example.com','staging.example.com');
update ${TBL_PREFIX}site set domain = 'staging.example.com' where domain = 'production.example.com';

Copy files from production to staging

#staging
mkdir --parents /home/wpsync/wp-content/blogs.dir
mkdir --parents /home/wpsync/wp-content/uploads
chown -R wpsync /home/wpsync/wp-content
sudo -u wpsync scp -r -p production.example.com:/path/to/production/wordpress/wp-content/blogs.dir/* /home/wpsync/wp-content/blogs.dir/
sudo -u wpsync scp -r -p production.example.com:/path/to/production/wordpress/wp-content/uploads/* /home/wpsync/wp-content/uploads/
rm -fr /path/to/staging/wordpress/wp-content/blogs.dir /path/to/staging/wordpress/wp-content/uploads
mv /home/wpsync/wp-content/blogs.dir /path/to/staging/wordpress/wp-content/
mv /home/wpsync/wp-content/uploads /path/to/staging/wordpress/wp-content/
chown -R www-data:www-data /path/to/staging/wordpress/wp-content/blogs.dir
chown -R www-data:www-data /path/to/staging/wordpress/wp-content/uploads

The script

I have created a shell script doing all stuff described in “The manual way”, you can grab it here. Just update the variables in the top with values for your environment.

One thought on “Synchronizing WordPress multi-site database and media files from production to staging environment

Leave a Reply

Your email address will not be published. Required fields are marked *


1 + = ten

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>