Here is a text how to install this on the source and distension server’s. Chat GPT help me make it but I don’t think it can make one that only copies the changes to the database:
Here’s the final version of the instructions, including the step to securely store the MySQL password using the .my.cnf file:
Automating WordPress Database and Content Sync Between Servers
This guide will help you set up an automated process to sync a WordPress database and content between two servers, rday1 and server, using WP-CLI, SCP, and rsync.
Prerequisites
-
Servers: You have two servers, rday1 and server.
-
WP-CLI: Install WP-CLI on both servers.
-
SSH Key Authentication: Set up passwordless SSH login between server and rday1.
-
Secure MySQL Password Storage: Create a .my.cnf file on the server to store your MySQL credentials securely.
Step 1: Create a Script to Copy the Database from rday1 to server
Script on rday1: WP-copy-DB_to_server.sh
#!/bin/bash
# Define paths
export_path="/root"
wp_path="/var/www/html"
# Export the database and capture the output
output=$(wp db export --allow-root --path=$wp_path 2>&1)
# Extract the actual backup file name from the output
backup_file_name=$(echo "$output" | grep -oP "Exported to '\K[^']+")
# Check if the backup file exists
if [ -f "$export_path/$backup_file_name" ]; then
echo "Backup file found: $export_path/$backup_file_name"
# Transfer the backup file to the destination server
scp "$export_path/$backup_file_name" root@server:/root/
else
echo "Error: Backup file not found"
fi
Step 2: Create a Script to Import the Database and Sync Content on server
Script on server: Get-WP-DB-from_rday1.sh
#!/bin/bash
# Import the database
wp db import /root/backup.sql --allow-root --path=/var/www/html/server
# Apply any domain migration script if needed
mysql --defaults-file=/root/.my.cnf wordpressdb < /root/wordpress-change-domain-migration.sql
The wordpress-change-domain-migration.sql looks like this:
SET @oldsite='https://rday.me/blog';
SET @newsite='http://server';
UPDATE wp_options SET option_value = replace(option_value, @oldsite, @newsite) WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET post_content = replace(post_content, @oldsite, @newsite);
UPDATE wp_links SET link_url = replace(link_url, @oldsite, @newsite);
UPDATE wp_postmeta SET meta_value = replace(meta_value, @oldsite, @newsite);
/* only uncomment next line if you want all your current posts to post to RSS again as new */
#UPDATE wp_posts SET guid = replace(guid, @oldsite, @newsite)
# Synchronize wp-content/uploads directory
rsync -avP --delete root@rday1:/var/www/html/wp-content/uploads/ /var/www/html/server/wp-content/uploads/
Step 3: Set Up Cron Jobs
-
On rday1: Run the WP-copy-DB_to_server.sh script at 2 AM daily.
crontab -e
Add the following line:
0 2 * * * /root/WP-copy-DB_to_server.sh
-
On server: Run the Get-WP-DB-from_rday1.sh script at 2:20 AM daily.
crontab -e
Add the following line:
20 2 * * * /root/Get-WP-DB-from_rday1.sh
The Get-WP-DB-from_rday1.sh looks like this:
#!/bin/bash
Import the WordPress database
wp db import /root/backup.sql --allow-root --path=/var/www/html/server
Run the MySQL script
mysql wordpressdb < /root/wordpress-change-domain-migration.sql
Synchronize the wp-content/uploads directory
rsync -avP --delete root@rday1:/var/www/html/wp-content/uploads/ /var/www/html/server/wp-content/uploads
This document should now fully guide anyone through setting up the automated database and content sync between two WordPress servers, including securely managing MySQL passwords.
Thank you.
