Made a script to auto backup WordPress to another WordPress

I not sure but guess people here are good at making scripts I got one to auto copy my WordPress database and then changes in it the new URL and then copy the media.

But it copies 100% of the data base the Media only what is new.

Any one know how to make a script to only copy the changes to the Database?

I know right inside WordPress you can just add text any place no problem remove and edit any thing so it should be able to do the same when copying the database.

Hope some one can help.

Thank you.

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

  1. Servers: You have two servers, rday1 and server.

  2. WP-CLI: Install WP-CLI on both servers.

    • To install WP-CLI, run the following commands on each server:
      curl -O https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar
      chmod +x wp-cli.phar
      mv wp-cli.phar /usr/local/bin/wp
      
    • Verify the installation:
      wp --info
      
  3. SSH Key Authentication: Set up passwordless SSH login between server and rday1.

    • Generate an SSH key pair on the server:
      ssh-keygen -t rsa -b 4096
      
    • Copy the public key to rday1:
      ssh-copy-id root@rday1
      
  4. Secure MySQL Password Storage: Create a .my.cnf file on the server to store your MySQL credentials securely.

    • On server, create the .my.cnf file in the root’s home directory:
      nano /root/.my.cnf
      
    • Add the following content, replacing your_password with your actual MySQL root password:
      [client]
      user=root
      password=your_password
      
    • Save and close the file, then secure it by restricting access:
      chmod 600 /root/.my.cnf
      
    • This allows MySQL commands to run without exposing the password on the command line.

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.

That’s why I cam here wanted to know the WordPress Database name and with the Bigbear WordPress install I don’t know how to list the mysql database name for that WordPress. I did install mysql but it just has a bank WordPress database. Can only guess because this database is in docker.

Need WP-CLI installed in the docker of the WordPress. I run this and it errors:

root@casaos:~# sh Get-WP-DB-from_rday1.sh
OCI runtime exec failed: exec failed: unable to start container process: exec: “wp”: executable file not found in $PATH: unknown
receiving incremental file list

sent 889 bytes received 2,273,459 bytes 909,739.20 bytes/sec
total size is 4,721,398,341,463 speedup is 2,075,934.88
root@casaos:~#

How can I install WP-CLI in this docker?

Wow I my just reload this with something like Ubunut fresh. To hard to work with.

The little script looks like this were it gets the error:

root@casaos:~# cat Get-WP-DB-from_rday1.sh
#!/bin/bash

Import WordPress database using WP-CLI inside the container

docker exec -i big-bear-wordpress-app-1 wp db import /var/www/html/backup.sql --allow-root --path=/DATA/AppData/big-bear-wordpress/html

Run the SQL command inside the MySQL container

docker exec -i big-bear-wordpress-db-1 mysql wordpress < /root/wordpress-change-domain-migration.sql

Synchronize the uploads directory

rsync -avP --delete root@rday1:/var/www/html/wp-content/uploads/ /DATA/AppData/big-bear-wordpress/html/wp-content/uploads

root@casaos:~#