How To Automate the Replication of a WordPress System from an Online Server to an Offline Localhost Domain

Created OnApril 19, 2022

Last Updated OnFebruary 25, 2024

byAdmin

This article documents the procedure involved in creating a local working offline replica of an online WordPress website and the automation of this process by means of a shell script.

This exercise involves a Ubuntu 20 server and laptop, both running Apache2 and MySQL/Mariadb 10.

First I installed apache2, mariadb, php, and phpmyadmin on the laptop.

Copy the WordPress Directory

I then copied the current /var/www/wordpress directory to /home/kevin/DATA on the server.

This is then accessible via the NFS share DATA on the laptop.

Then on the laptop I copied this to /var/www/wordpress, having, first of all, renamed the existing wordpress directory instance.

then on laptop:

Create the Website MySQL Database and User

root@asus:~# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 90
Server version: 10.3.29-MariaDB-0ubuntu0.20.10.1 Ubuntu 20.10

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>

Our website WordPress database on the server is called kevwells, so we need to have a database with the same name present on our MariaDB on the laptop:

So create a database as follows on laptop with these parameters:

MySQL settings – You can get this info from your web host ** //
/** The name of the database for WordPress */
define( ‘DB_NAME’, ‘kevwells’ );

/** MySQL database username */
define( ‘DB_USER’, ‘wordpressuser’ );

/** MySQL database password */
define( ‘DB_PASSWORD’, ‘**COMMENTED OUT***’ );

/** MySQL hostname */
define( ‘DB_HOST’, ‘localhost’ );

MariaDB [(none)]> CREATE DATABASE kevwells DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]>

MariaDB [(none)]>

Next, create a user for the database:

MariaDB [(none)]> CREATE USER wordpressuser@localhost;
Query OK, 0 rows affected (0,001 sec)

MariaDB [(none)]>

NOTE no quote marks

to set the password:

ALTER USER ‘wordpressuser’@’localhost’ IDENTIFIED BY ‘passwordcommentedout’;

pay attention to the inverted commas, these have to be the correct type else it wont work with mariadb!

To list all existing users in your database server, you need to query the user table in your mysql database.

SELECT the user and host column from the table as follows:

SELECT user, host FROM mysql.user;

Along the way, I noticed a wrongly entered username above for phpmyadmin, ie ‘phpmyadmin’ , so I deleted this:

MariaDB [(none)]> drop user ‘‘phpmyadmin’’@localhost ;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]>

we got rid of the ‘phpmyadmin’ as this was an error with the apostrophes as part of the user name.

We already have the wordpressuser existing.

So all is well. we just need to make sure the password for our user is the correct one.

Just to be certain, I also then went into http://localhost/phpmyadmin and changed the password for user wordpressuser to **COMMENTED OUT** as above.

to set the password:

ALTER USER ‘wordpressuser’@’localhost’ IDENTIFIED BY ‘passwordcommentedout’;

pay attention to the inverted commas, these have to be the correct type else it wont work with mariadb!

It returned the message:

The password for ‘wordpressuser’@’localhost’ was changed successfully.

So on our laptop have created a kevwells database (as yet empty) and a user called wordpressuser as on the online server.

I then made following changes to the /var/www/wordpress/wp-config.php:

/* define(‘FORCE_SSL_ADMIN’, true);
*/

/*
define( ‘WP_HOME’, ‘https://kevwells.com’ );
define( ‘WP_SITEURL’, ‘https://kevwells.com’ ); */

define(‘WP_HOME’,’http://localhost’);
define(‘WP_SITEURL’,’http://localhost’);

Had to give wordpressuser privilege to database kevwells because we are using the database name kevwells and not wordpress for our website wordpress database!

MariaDB [(none)]> GRANT ALL ON kevwells.* TO wordpressuser@localhost;
Query OK, 0 rows affected (0,001 sec)

MariaDB [(none)]>

MariaDB [(none)]> flush privileges ;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]>

Note that the reply of mariadb says

Query OK, 0 rows affected (0.000 sec)

but it has processed and implemented the change when you do flush privileges.

user wordpressuser then has all privileges granted to kevwells database.

Launch the WordPress Duplicator Plugin

Next, start the Duplicator WordPress Plugin procedure on the server to prepare the copy of the site for download from the server to the laptop.

You need to download both the zip file and the installer.php for duplicator – you carry out both these actions from within the duplicator dashboard in the plugin on WordPress on the server.

Then, on localhost ie laptop, we activate the duplicator installer.php in the WordPress folder on laptop.

This installs the instance onto WordPress and apache on localhost.

Finally, then do the following:

Admin Login Login to the WordPress Admin to finalize this install.

Auto delete installer files after login (recommended)

IMPORTANT FINAL STEPS: Login into the WordPress Admin to remove all installation files and finalize the install process.

This install is NOT complete until all installer files have been completely removed. Leaving any of the installer files on this server can lead to security issues.

Review Install Report
The install report is designed to give you a synopsis of the possible errors and warnings that may exist after the installation is completed.

Test Site
After the install is complete run through your entire site and test all pages and posts.

Final Security Cleanup
When completed with the installation please delete all installation files. Leaving these files on your server can be a security risk! You can remove all these files by logging into your WordPress admin and following the remove notification links or by deleting these file manually. Be sure these files/directories are removed. Optionally it is also recommended to remove the archive.zip/daf file.

dup-installer
installer.php
installer-backup.php
dup-installer-bootlog__[HASH].txt
archive.zip/daf

I moved all the above out of /var/www/wordpress to a directory called /var/www/wordpress_duplicator_myfiles/

NOTE: wordpress admin dashboard username, password, authcode etc is the same on http://localhost/wp-admin as on http://3.222.27.169/wp-admin

Assemble the MySQL Command To Modify the Website URL References

To execute statements from the command line without an interactive prompt, use the -e option:

mysql mydb -e ‘select * from foo’

We need the following command set:

UPDATE wp_options SET option_value = replace(option_value, ‘http://3.222.27.169’, ‘http://localhost’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;
UPDATE wp_posts SET guid = replace(guid, ‘http://3.222.27.169′,’http://localhost’);
UPDATE wp_posts SET post_content = replace(post_content, ‘http://3.222.27.169’, ‘http://localhost’);
UPDATE wp_postmeta SET meta_value = replace(meta_value,’http://3.222.27.169′,’http://localhost’);
FLUSH PRIVILEGES;

The mysql client utility can take a password on the command line with either the -p or –password= options.

If you use -p, there must not be any blank space after the option letter.

BUT: this is insecure because it means a user could view the password – either directly via /proc/$pid/cmdline or via the ps command.

The safest way to do this would be to create a new config file and pass it to mysql using either the –defaults-file= option.

to do this, create a file ~/.my.cnf, make it only accessible by yourself, permission 600.

[client]
user=myuser
password=mypassword

Then you don’t need type password any more. Make sure that you secure this file.

nano /root/.my.cnf

[client]
user=root
password=<yourmysqlrootpassword>

chmod 640 /root/.my.cnf
chown root.root /root/.my.cnf

-rw-r—– 1 root root 38 Apr 19 18:44 .my.cnf

Then run:

mysql –defaults-file=

mysql –defaults-file=/root/.my.cnf -e “show databases ;”

It works. So, now let’s try the full command:

/usr/bin/mysql –defaults-file=/root/.my.cnf -e “USE kevwells ; UPDATE wp_options SET option_value = replace(option_value, ‘https://kevwells.com’, ‘http://localhost’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’; UPDATE wp_posts SET guid = replace(guid, ‘https://kevwells.com’,’http://localhost’); UPDATE wp_posts SET post_content = replace(post_content, ‘https://kevwells.com’, ‘http://localhost’); UPDATE wp_postmeta SET meta_value = replace(meta_value,’https://kevwells.com’,’http://localhost’); FLUSH PRIVILEGES;”

Build a Shell Script to Automate the Replication Process

Next we want to put this and other commands in an executable script under /usr/local/bin as the second part of our backup script routine which downloads the databases from kevwells.com to localhost.

Easiest way to do the downloading would be via the /home/kevin/ NFS Share, this also provides a regular database backup on the share.

Then call up the script using crontab.

Finally, make sure you have the two definitions to the wp-config.php and also the FORCE_SSL_ADMIN directive (we disable this as we are not using SSL on the laptop localhost apache2).

define(‘FORCE_SSL_ADMIN’, false);

define( ‘WP_HOME’, ‘http://localhost’ );
define( ‘WP_SITEURL’, ‘http://localhost’ );

We need a script to download the MySQL WordPress database from the online server at kevwells.com to the laptop, and then to make the necessary URL adjustments.

Do not simply copy database files from one machine to the other. This can lead to database inconsistencies. In any case with a dynamic online database with real-time changes this would be a recipe for disaster.

The correct way to export and import a mysql database between two machines is to use mysqldump and mysqlimport.

On an online machine with continual write actions, you would also need to pause the database while performing the export-import action. However as this is simply a database for a WordPress website with few write actions taking place, we can ignore this and go straight to the export routine.

To do this, we use mysqldump.

Use mysqldump –help to see what options are available.

The basic structure of our command will be, on the server:

mysqldump –quick db_name | gzip > db_name.gz

and to restore, ie import on the other machine:

mysqladmin create db_name

gunzip < db_name.gz | mysql db_name

using the mysql cli client the command would be (but we will use mysqladmin in our case)

root@asus:/usr/local/bin# mysql -p -uwordpressuser kevwells < /home/kevin/DATA/KEVWELLS.COM/kevwells.sql
Enter password:
root@asus:/usr/local/bin#

on the server we use:

mysqldump –quick db_name | gzip > db_name.gz
mysqldump –defaults-file=/root/.my.cnf –quick kevwells | gzip > /home/kevin/DATA/KEVWELLS.COM/kevwells.sql.gz

So,

root@gemini:~#
root@gemini:~#
root@gemini:~# mysqldump –defaults-file=/root/.my.cnf –quick kevwells | gzip > /home/kevin/DATA/KEVWELLS.COM/kevwells.sql.gz
root@gemini:~# mysqldump –defaults-file=/root/.my.cnf –quick mysql | gzip > /home/kevin/DATA/KEVWELLS.COM/mysql.sql.gz
root@gemini:~#

then on laptop:

Transfer both .sql files with database contents to the target machine and run these commands there:

mysqladmin create db_name

gunzip < db_name.gz | mysql db_name

You need to delete the old database .sql files first, else the mysqlimport command will fail:

root@asus:~# rm /home/kevin/DATA/KEVWELLS.COM/kevwells.sql
root@asus:~# rm /home/kevin/DATA/KEVWELLS.COM/mysql.sql
root@asus:~#

then perform the imports:

root@asus:~# mysqlimport kevwells | gunzip /home/kevin/DATA/KEVWELLS.COM/kevwells.sql.gz
root@asus:~#

root@asus:~# mysqlimport mysql | gunzip /home/kevin/DATA/KEVWELLS.COM/mysql.sql.gz
root@asus:~#

After you import the mysql database onto the laptop, execute mysqladmin flush-privileges so the laptop mysql server reloads the grant table info:

mysqladmin flush-privileges

root@asus:~# mysqladmin flush-privileges
root@asus:~#

Some other commands:

root@asus:/home/kevin/DATA/KEVWELLS.COM# mysqladmin status
Uptime: 32382 Threads: 7 Questions: 7651 Slow queries: 0 Opens: 155 Flush tables: 1 Open tables: 148 Queries per second avg: 0.236
root@asus:/home/kevin/DATA/KEVWELLS.COM#

We can implement the automation in various ways.

One way would be to have two scripts, one on server which executes first via crontab, and the other on the laptop which executes later via crontab.

The server script would perform the mysqldump, the laptop script would do the mysqlimport.

Another way would be remote ssh – but we would have to give root ssh key permission on the server from the laptop for this. Then we would do everything from the one script on the laptop.

We will use this method to keep things simpler and means there is just one script that controls the entire process.

Set up the SSH for Remote Login without Password Prompt

First, copy the ssh key for root to geminivpn

use geminivpn and not gemini as wifi routers do not always permit outgoing ssh connections!

on gemini:

In the /etc/ssh/sshd_config you must have the directive:

PermitRootLogin yes

then restart sshd:

systemctl restart sshd

Next, assemble the script on the laptop:

Check Mysql Server Command Functionality

First let’s check the functionality on the server:

root@gemini:/etc/ssh# mysqladmin status
Uptime: 1458129 Threads: 7 Questions: 4569013 Slow queries: 0 Opens: 358 Flush tables: 1 Open tables: 337 Queries per second avg: 3.133
root@gemini:/etc/ssh#

and let’s see if we have our ssh command set correctly and if it works for mysqladmin commands:

root@asus:/home/kevin# ssh root@geminivpn “mysqladmin status”
Uptime: 1458134 Threads: 7 Questions: 4569014 Slow queries: 0 Opens: 358 Flush tables: 1 Open tables: 337 Queries per second avg: 3.133
root@asus:/home/kevin#

everything good.

Check the Functions.php:

there was also a problem with /var/www/wordpress# nano wp-content/themes/canvas/functions.php

This contained:

update_option( ‘siteurl’, ‘http://3.222.27.169’ );
update_option( ‘home’, ‘http://3.222.27.169’ );

These directives were changing the site name in the WordPress database back to kevwells.com from localhost.

So we have to make sure this is modified when we copied the WordPress files across. This is not dependent on the MySQL databases far as I can gather at this stage, it is solely in the functions.php of the canvas theme used for the site.

I have now commented this out on both kevwells.com and localhost.

Assemble the Script

So, next step is to assemble our script on the laptop:

root@asus:/usr/local/bin# cat kevwells.com_db_replication.sh

#!/bin/bash
# Script: /usr/local/bin/kevwells.com_db_replication.sh
# Date Created: 19.4.2022
# Last Modified 25.02.2024
# Author: Kevin Wells
# Purpose: export wordpress database for kevwells.com website from NFS share on kevinvm1vpn to laptop
# and modify database domain url from http://kevwells.com to http://localhost
# How called: via crontab. Can also be called manually
# Requires: mysql/mariadb, mysqladmin, mysql client, ssh, ssh keys installed on server for passwordless remote command execution
# /root/.my.cnf containing mysql password for root, gzip/gunzip on both machines
# Location: asus laptop /usr/local/bin

set -x

SERVER=”kevinvm1vpn”
WEBSITESOURCE=”/home/kevin/NFSVOLUME/DATAVOLUME/KEVWELLS.COM”
LOCALSOURCE=”/media/kevin/KEVINVM1VPN/srv/nfs4/NFSSHARE/DATAVOLUME/KEVWELLS.COM”

## not needed, doing it on the server locally:
## scp -r root@$SERVER:/var/www/wordpress/* root@$SERVER:/home/kevin/NFSVOLUME/DATAVOLUME/KEVWELLS.COM/

# first, make sure the wordpress cache is flushed to disk:
ssh root@$SERVER “wp cache flush –path=/var/www/wordpress –allow-root “

# first delete the old .sql database file from last time:
##dont need to do this remote, is a problem with the rm command in any case, but delete it locally
##ssh root@$SERVER runuser -l kevin -c ‘rm -f $WEBSITESOURCE/all_databases.sql’
# delete locally:
rm -f $LOCALSOURCE/all_databases.sql

# create the database export from server kevwells.com:

#ssh -i /root/KevinVM1.pem ubuntu@kevinvm1vpn “mysqldump –all-databases > /home/kevin/DATAVOLUME/KEVWELLS.COM/all_databases.sql”

ssh root@$SERVER “mysqldump –all-databases > $WEBSITESOURCE/all_databases.sql”

# next import the databases into the laptop mysql:
# no username or password needed as this is defined in the /root/.my.cnf file:

mysql < $LOCALSOURCE/all_databases.sql

# should not be necessary, but to be on safe side:
chown -R mysql.mysql /var/lib/mysql/kevwells

/usr/bin/mysql -e “USE kevwells ; UPDATE wp_options SET option_value = replace(option_value, ‘https://kevwells.com’, ‘http://localhost’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’; UPDATE wp_posts SET guid = replace(guid, ‘https://kevwells.com’,’http://localhost’); UPDATE wp_posts SET post_content = replace(post_content, ‘https://kevwells.com’, ‘http://localhost’); UPDATE wp_postmeta SET meta_value = replace(meta_value,’https://kevwells.com’,’http://localhost’); FLUSH PRIVILEGES;”

/usr/local/bin/wp cache flush –path=/var/www/wordpress –allow-root

# then execute mysqladmin flush-privileges so the laptop mysql server reloads the grant table info:

mysqladmin flush-privileges

# finally, we can reset the permalinks to plain, we do this using the wordpress wp-cli tool:

#wp option get permalink_structure –path=/var/www/wordpress –allow-root
#this should return the following output – or similar tag category:
#/%postname%/

#wp option update permalink_structure ” ” –path=/var/www/wordpress –allow-root

/usr/bin/php -f /usr/local/bin/wp option update permalink_structure ‘ ‘ –path=/var/www/wordpress –allow-root

#this should return the following output:
#Success: Updated ‘permalink_structure’ option.

# wp option get permalink_structure –path=/var/www/wordpress –allow-root
#this should return the following output: (ie empty line)

# END OF SCRIPT

Testing the script with bash option set -x:

root@asus:/home/kevin/shellscripts# ./kevwells.com_db_replication.sh
+ SERVER=kevinvm1vpn
+ WEBSITESOURCE=/home/kevin/NFSVOLUME/DATAVOLUME/KEVWELLS.COM
+ LOCALSOURCE=/media/kevin/KEVINVM1VPN/srv/nfs4/NFSSHARE/DATAVOLUME/KEVWELLS.COM
+ ssh root@kevinvm1vpn ‘wp cache flush –path=/var/www/wordpress –allow-root ‘
PHP Warning: Undefined array key “HTTP_HOST” in /var/www/wordpress/wp-content/plugins/force-https-littlebizzy.disabled/core/redirect.php on line 91
Warning: Some code is trying to do a URL redirect. Backtrace:
#0 /var/www/wordpress/wp-includes/class-wp-hook.php(312): WP_CLI\Utils\wp_redirect_handler(‘…’)
#1 /var/www/wordpress/wp-includes/plugin.php(205): WP_Hook->apply_filters(‘…’, Array)
#2 /var/www/wordpress/wp-includes/pluggable.php(1396): apply_filters(‘…’, ‘…’, 301)
#3 /var/www/wordpress/wp-content/plugins/force-https-littlebizzy.disabled/core/redirect.php(91): wp_redirect(‘…’, 301)
#4 /var/www/wordpress/wp-content/plugins/force-https-littlebizzy.disabled/core/redirect.php(68): FHTTPS_Core_Redirect->redirect()
#5 /var/www/wordpress/wp-includes/class-wp-hook.php(310): FHTTPS_Core_Redirect->start(”)
#6 /var/www/wordpress/wp-includes/class-wp-hook.php(334): WP_Hook->apply_filters(NULL, Array)
#7 /var/www/wordpress/wp-includes/plugin.php(517): WP_Hook->do_action(Array)
#8 /var/www/wordpress/wp-settings.php(495): do_action(‘…’)
#9 phar:///usr/local/bin/wp/vendor/wp-cli/wp-cli/php/WP_CLI/Runner.php(1317): require(‘…’)
#10 phar:///usr/local/bin/wp/vendor/wp-cli/wp-cli/php/WP_CLI/Runner.php(1235): WP_CLI\Runner->load_wordpress()
#11 phar:///usr/local/bin/wp/vendor/wp-cli/wp-cli/php/WP_CLI/Bootstrap/LaunchRunner.php(28): WP_CLI\Runner->start()
#12 phar:///usr/local/bin/wp/vendor/wp-cli/wp-cli/php/bootstrap.php(78): WP_CLI\Bootstrap\LaunchRunner->process(Object(WP_CLI\Bootstrap\BootstrapState))
#13 phar:///usr/local/bin/wp/vendor/wp-cli/wp-cli/php/wp-cli.php(27): WP_CLI\bootstrap()
#14 phar:///usr/local/bin/wp/php/boot-phar.php(11): include(‘…’)
#15 /usr/local/bin/wp(4): include(‘…’)
+ rm -f /media/kevin/KEVINVM1VPN/srv/nfs4/NFSSHARE/DATAVOLUME/KEVWELLS.COM/all_databases.sql
+ ssh root@kevinvm1vpn ‘mysqldump –all-databases > /home/kevin/NFSVOLUME/DATAVOLUME/KEVWELLS.COM/all_databases.sql’
+ mysql
./kevwells.com_db_replication.sh: line 50: /media/kevin/KEVINVM1VPN/srv/nfs4/NFSSHARE/DATAVOLUME/KEVWELLS.COM/all_databases.sql: No such file or directory
+ chown -R mysql.mysql /var/lib/mysql/kevwells
+ /usr/bin/mysql -e ‘USE kevwells ; UPDATE wp_options SET option_value = replace(option_value, ‘\”https://kevwells.com’\”, ‘\”http://localhost’\”) WHERE option_name = ‘\”home’\” OR option_name = ‘\”siteurl’\”; UPDATE wp_posts SET guid = replace(guid, ‘\”https://kevwells.com’\”,’\”http://localhost’\”); UPDATE wp_posts SET post_content = replace(post_content, ‘\”https://kevwells.com’\”, ‘\”http://localhost’\”); UPDATE wp_postmeta SET meta_value = replace(meta_value,’\”https://kevwells.com’\”,’\”http://localhost’\”); FLUSH PRIVILEGES;’
+ /usr/local/bin/wp cache flush –path=/var/www/wordpress –allow-root
Success: The cache was flushed.
+ mysqladmin flush-privileges
+ /usr/bin/php -f /usr/local/bin/wp option update permalink_structure ‘ ‘ –path=/var/www/wordpress –allow-root
Success: Value passed for ‘permalink_structure’ option is unchanged.
root@asus:/home/kevin/shellscripts#

root@asus:/usr/local/bin#

Make sure the script is executable!

root@asus:/usr/local/bin# chmod 750 kevwells.com_db_replication.sh
root@asus:/usr/local/bin# ls -l kevwells.com_db_replication.sh
-rwxr-x— 1 root root 2007 Apr 20 10:57 kevwells.com_db_replication.sh
root@asus:/usr/local/bin#

And finally, schedule the script to run using crontab on the laptop:

9 2 * * * /usr/local/bin/kevwells.com_db_replication.sh

Additional Notes on the Replication Process

Some WordPress plugins can interfere with the replication and correct rendition of the website from https://kevwells.com to http://localhost.

In particular, check that:

Really Simple SSL is “deactivated, but still using https” (select this option in the plugin settings in the WP Dashboard).

Also I have disabled Permalinks Moved Permanently plugin.

Provided these plugins are disabled and the replication script runs correctly with the MySQL DB Update URL commands executed to change siteurl links from https://kevwells.com to http://localhost then the replication should be error-free and there is no need for the permalinks to be saved in the WP Dashboard as is otherwise usually the case with WordPress site transfers and domain name changes.

Tags:

Navigation

How Can We Help?

How To Automate the Replication of a WordPress System from an Online Server to an Offline Localhost Domain

Copy the WordPress Directory

Create the Website MySQL Database and User

Launch the WordPress Duplicator Plugin

Assemble the MySQL Command To Modify the Website URL References

Build a Shell Script to Automate the Replication Process

Set up the SSH for Remote Login without Password Prompt

Check Mysql Server Command Functionality

Assemble the Script

Additional Notes on the Replication Process