1 Introduction

My previous blog architecture was a home data center (primary node + hot standby node) + Tencent Cloud (disaster recovery node), which is a typical "single node read and write" solution. Since only the primary node is responsible for processing database read and write requests on a daily basis, there is no need for real-time synchronization between databases: whenever I add a new article, modify the content, or approve or reply to a comment, causing the WordPress database to change, if I happen to be in the mood, I will manually export the wordpress library in the MariaDB of the primary node as a wordpress.sql file and put it in the synchronization directory of Syncthing. Next, Syncthing will synchronize this file to the specified directories of the hot standby node and the disaster recovery node. After the inotify script on these two nodes detects the directory changes, it automatically triggers the database import script to import the wordpress.sql file into the corresponding MariaDB, thereby completing the data synchronization between the primary node and other nodes.

However, now, the blog architecture has been upgraded to a dual-active architecture of "primary write and secondary read" in the home data center + "primary read" in the Chicago node of Racknerd. Normal access traffic will hit the content in the APO cache, and access requests that need to be returned to the source will be returned to different nodes through multiple connectors in a single tunnel of Cloudflare Tunnel. Because the multi-connector load balancing strategy of Cloudflare Tunnel is quite "arbitrary and capricious", although in theory, in a multi-connector scenario, the return request will give priority to hitting the Chicago node closest to the user, but in actual use, it is still impossible to guarantee that all requests will be obedient, especially when the database between the master and slave nodes has not been synchronized, there may be problems such as loss of comments, article rollbacks, and even inconsistent page display content.

More importantly, in the past, I manually exported wordpress.sql and triggered synchronization. Although the operation was simple, it did not have much technical content. It was not only mechanical and boring, but also easy to forget. It was not "cool" and did not fit my identity. Therefore, under the new architecture, in order to ensure that the "primary write and secondary read" nodes in the home data center can be synchronized to the Chicago "primary read node" in a short time after the data is changed, and to avoid content differences when the back-source traffic hits the Chicago "primary read" node, I have to rethink the data synchronization mechanism, hoping to achieve a set of smarter and more automatic database change perception and automatic synchronization processes - this is also the reason why I did not write about the active-active architecture this week but wrote this article, because this is the key problem that must be solved to achieve the purpose of daily automated operation and maintenance after upgrading to the active-active architecture of wordpress.

2 How to determine whether the content of the "main write" node WordPress has been updated

2.1 WordPress database change awareness

In the previous section, I mentioned "more automatic database change awareness". To achieve this function, it involves how to choose the most appropriate database content change detection mechanism. Generally speaking, there are several main solutions we can consider:

• Using the binlog (Binary Log) mechanism：This is the most direct and accurate way. MySQL/MariaDB's binlog records all write operations (INSERT, UPDATE, DELETE) to the database, which is very suitable for master-slave replication, incremental synchronization or data recovery. However, this requires enabling binlog at the database layer, configuration file support, parsing log content or introducing a middleware system, which has high deployment and maintenance costs.
• Using the Trigger Mechanism: You can set AFTER INSERT/UPDATE/DELETE triggers for key tables in the database to write change records to an independent table or send them to a message queue. This method is more flexible, but it modifies the original database structure and is prone to introducing additional burdens and side effects.
• Using file system level change detection(Such as inotify): In theory, it is possible to monitor whether the database files have changes in read and write behavior, but in practice this can only be achieved on Linux, and the frequency of MariaDB file changes is very high (which does not mean that the content has actually changed). It is also not available on platforms such as macOS, and is not suitable for accurate detection (my WordPress master node runs on macos).
• Periodic full comparison (such as checksum or diff): Violent and simple, but inefficient, and unable to respond to changes in real time. However, returning to the specific scenario of WordPress, we do not need to perceive all database changes so "fully".Most important data updates are concentrated in several core tables:
• wp_posts：Articles, pages, and attachments are all in this table. The field post_modified_gmt indicates the last modification time, and post_date_gmt indicates the creation time;
• wp_comments: All comment data is here, the key field is comment_date_gmt;

Therefore, we can use the following two SQL statements to quickly obtain whether the WordPress content has changed recently:

SELECT MAX(GREATEST(post_modified_gmt, post_date_gmt)) FROM wp_posts; SELECT MAX(comment_date_gmt) FROM wp_comments;

If the maximum timestamp of a query is later than the last record, it means that the WordPress site has actually been updated (whether it is adding comments, editing articles, publishing pages, etc.), then the database export and synchronization process can be triggered.

This method does not require changing the database structure, does not rely on binlog, and does not introduce middleware.The deployment is extremely lightweight and is very suitable as a "change perception signal" for automatic synchronization systems.

2.2 Create a script to query database changes regularly

2.2.1 MariaDB deployed from source code

2.2.1.1 Preliminary work: Add a ".my.cnf" file to the working directory (optional)

Summary of steps:

1. Open the terminal and enter the current user's home directory:

cd ~

2. Create a .my.cnf file (recommended editor, such as nano or vim):

vim .my.cnf

3. Write the following content and save it (fill in according to your actual database username and password):

[client] user=root password=your database password

4. Set permissions (to prevent other users from reading):

chmod 600 .my.cnf

5. Test whether it works:

mysql -e "SHOW DATABASES;"

illustrate

• Whenever you execute mysql or mysqldump command with this user, it will automatically read the file in ~/.my.cnf. [client] Segment information.
• If you have multiple users logged into the system,Each user can have his own ~/.my.cnf Files do not interfere with each other.
• It is not recommended to place .my.cnf in a system-level directory (such as /etc/my.cnf) to expose the password. It is safer and more controllable to place it in the user's home directory.
• This section is not necessary, but it can be done if you do not want to use the "mysql -uxxx -pxxx" method directly in the detection script to display the specified username and password. After all, the specified method is not very safe. Of course, friends who do not mind can skip this section.

2.2.1.2 Creating a query script

1. Enter the current user's home directory, create a new script directory and enter:

cd ~ mkdir -p script cd script

2. Create database-query.sh (recommended editor, such as nano or vim):

vim database-query.sh

3. Write the following and save:

#!/bin/bash # Set path STATE_FILE="/usr/local/var/wp_sync_state" DUMP_DIR="/usr/local/var/wp_dumps" DB_NAME="wordpress" DATE_NOW=(date +%Y%m%d%H%M%S) # Run SQL query to get the latest modification time LATEST_POST_TIME=(mysql -N -e "SELECT MAX(GREATEST(post_modified_gmt, post_date_gmt)) FROM wp_posts;" DB_NAME) LATEST_COMMENT_TIME=(mysql -N -e "SELECT MAX(comment_date_gmt) FROM wp_comments;" DB_NAME) CURRENT_STATE="{LATEST_POST_TIME}|{LATEST_COMMENT_TIME}" # If the state file does not exist, initialize and exit if [ ! -f "STATE_FILE" ]; then echo "CURRENT_STATE">"STATE_FILE" echo "First run, record the current status:CURRENT_STATE" exit 0 fi # Read the previous state PREV_STATE=(cat "STATE_FILE") # Compare the current state with the previous if [ "CURRENT_STATE" != "PREV_STATE" ]; then echo "Database changes, triggering export and synchronization..." # Update status file echo "CURRENT_STATE" > "STATE_FILE" # Export database (note to replace with your actual path) DUMP_FILE="DUMP_DIR/wp_dump_DATE_NOW.sql" mysqldumpDB_NAME > "DUMP_FILE" # Synchronous action (you can replace it with rsync/scp, etc.) # scp "DUMP_FILE" user@chicago:/data/wp_sync/ else echo "No changes, no need to sync." fi

Note: The script finally associates the method of synchronizing the wordpress.sql file after discovering database changes. This is just a demonstration. The specific method you choose depends on your own environment, such as scp, rsync, or simply automatic synchronization through syncthing. If you want to synchronize automatically through syncthing, you need to replace the file directory synchronized by syncthing when exporting the database.

4. Set execution permissions for the script:

chmod +x database-query.sh

In addition, there is also a.my.cnfTo store account passwords, use directly-u -pParameters of the script:

#!/bin/bash # Set path STATE_FILE="/usr/local/var/wp_sync_state" DUMP_DIR="/usr/local/var/wp_dumps" DB_NAME="wordpress" DATE_NOW=(date +%Y%m%d%H%M%S) # Run SQL query to get the latest modification time LATEST_POST_TIME=(mysql -uroot -pyourpassword -N -e "SELECT MAX(GREATEST(post_modified_gmt, post_date_gmt)) FROM wp_posts;" DB_NAME) LATEST_COMMENT_TIME=(mysql -uroot -pyourpassword -N -e "SELECT MAX(comment_date_gmt) FROM wp_comments;" DB_NAME) CURRENT_STATE="{LATEST_POST_TIME}|{LATEST_COMMENT_TIME}" # If the state file does not exist, initialize and exit if [ ! -f "STATE_FILE" ]; then echo "CURRENT_STATE">"STATE_FILE" echo "First run, record the current status:CURRENT_STATE" exit 0 fi # Read the previous state PREV_STATE=(cat "STATE_FILE") # Compare the current state with the previous if [ "CURRENT_STATE" != "PREV_STATE" ]; then echo "Database changes, triggering export and synchronization..." # Update status file echo "CURRENT_STATE" > "STATE_FILE" # Export database (note to replace with your actual path) DUMP_FILE="DUMP_DIR/wp_dump_DATE_NOW.sql" mysqldumpDB_NAME > "DUMP_FILE" # Synchronous action (you can replace it with rsync/scp, etc.) # scp "DUMP_FILE" user@chicago:/data/wp_sync/ else echo "No changes, no need to sync." fi

Note 1: The biggest problem with this method is that it is executed in another terminalps aux | grep mysqlcommand, you may see directly:

mysql -uroot -ppassword -e ...

It exists in multi-person shared environments and CI/CD buildsPotential risk of password leakageIf it is just for your own personal use, it doesn’t really matter.

Note 2: After the main writing node successfully exports the sql file of the wordpress library, it can actually send system-level notifications to the mobile phone through bark:

curl -s "https://bark.example.com/your_token/wordpressNotice/wordpress database import successful" > /dev/null

Of course, the node that imports the SQL file of the WordPress library can also send notifications to the mobile phone through bark in the same way.

2.2.2 MariaDB deployed by Docker

2.2.2.1 Preliminary work: Create a ".my.cnf" file on the host and mount it to the mariadb container

1. Create a ".my.cnf" file (host machine)

Create in a directory on the host machine (such as the docker directory of mariadb):

vim /docker/mariadb/.my.cnf

The content is as follows (taking the root user as an example):

[client] user=root password=your database password

Notice:

• user= should be the same as the user you use to connect to MariaDB (usually root).
• password= Please fill in the actual password without quotation marks.
• The recommended file permissions are 600 (only you can read):

chmod 600 /docker/mariadb/.my.cnf

2. Mount the ".my.cnf" file to the container

Use the "-v" parameter of the docker run format command to mount the host's ".my.cnf" into the container:

-v /docker/mariadb/.my.cnf:/root/.my.cnf:ro

Note: "-v /docker/mariadb/.my.cnf:/root/.my.cnf:ro" means mounting your host configuration file to the home directory of the root user in the container. The ":ro" means read-only mounting, which is safer.

If it is deployed in docker-compose mode, you can mount it in the following way:

    volumes: - /docker/mariadb/.my.cnf:/root/.my.cnf:ro

2.2.2.2 Creating a query script

This query script is run on the host machine. The creation steps are similar to the previous ones, so I will not repeat them. The final script content is as follows:

#!/bin/bash export PATH="/opt/homebrew/bin:/usr/local/bin:/usr/bin:/bin:/usr/sbin:/sbin" # Docker container name (MariaDB container) CONTAINER=mariadb # Database name DB=wordpress # MySQL user and password (it is recommended to set a low-privilege backup user) USER=root PASS='p@ssw0rd' # Output SQL path (Syncthing synchronization directory) OUTPUT="/mnt/sync/wordpress/db/wordpress.sql" # Temporary file to prevent synchronization before writing is completed TMP_OUTPUT="{OUTPUT}.tmp" # Status file path STATE_FILE=/tmp/wp-db-last-update.txt # Get the latest modification time of the WordPress database (article + comment) LATEST_TIME=(docker exec CONTAINER \ mysql -N -uUSER -pPASS -e " SELECT UNIX_TIMESTAMP( GREATEST( (SELECT MAX(GREATEST(post_modified_gmt, post_date_gmt)) FROMDB.wp_posts), (SELECT MAX(comment_date_gmt) FROM DB.wp_comments) ) ); ") # If the status file does not exist, initialize if [ ! -f "STATE_FILE" ]; then echo "LATEST_TIME">"STATE_FILE" echo "[INIT] Status file initialization completed" exit 0 fi # Read the last recorded time LAST_TIME=(cat "STATE_FILE") # comparison judgment if [ "LATEST_TIME" -gt "LAST_TIME" ]; then echo "[+] Database changes detected, start export" # Export SQL to temporary file docker exec CONTAINER \ mysqldump -uUSER -pPASS --single-transaction --max_allowed_packet=64MDB \ > "TMP_OUTPUT" # Check if export is successful before overwriting if [? -eq 0 ]; then mv "TMP_OUTPUTOUTPUT" echo "[✓] Exported successfully and moved to OUTPUT" echo "LATEST_TIME" > "STATE_FILE" else echo "[✗] Export failed, keep the last status" rm -f "TMP_OUTPUT" fi else echo "[=] No database changes, skip export" fi

2.3 Scheduled execution of database query scripts

The implementation method of different systems is different. For Linux or macOS systems, you can usecrontabTo achieve:

crontab -e

Add the following content (check every 5 minutes):

*/5 * * * * /path/to/database_query.sh >> /tmp/wp-detect.log 2>&1

Precautions

• The default container name in the script is mariadb. You can use docker ps to view the actual container name.
• If the container is started via docker-compose, the container name may be yourproject_mariadb_1, and you need to change CONTAINER= in the script.
• If you specify --volume when running docker run, you can also put STATE_FILE in a persistent directory on the host.

2.4 Run the database change monitoring script as a service (optional)

2.4.1 Disadvantages of the “cron” method

Generally speaking, if you just want to run the detection script very lightly in the local environment, it is enough to use cron. It is simple and stable. You only need to set a scheduled task to execute the database change detection logic every few minutes, which is very suitable for unattended routine needs.

But for me, the situation is more complicated. I don't want this detection script toOperates 24/7, because every Monday I publish a new WordPress article, accompanied by a series of content organization work, such as adjusting the article format, updating the site map, temporarily turning on the automatic translation function of the TranslatePress plug-in to translate the newly added article content, and then turning off the automatic translation function of the plug-in after the translation is completed (to save money~).

These operations are completed in one or two hours, and the database content during this period is actuallyFrequently changingIf the detection script is still running at this time, it will frequently detect "changes" in the database, triggering unnecessary synchronization processes and even affecting the content of the article that has not yet been finalized.

So my ideal way to use it would be:Before publishing an article every Monday, stop the database change detection script and resume it manually after the publishing process and all necessary operations are completed.This control process is very cumbersome under the cron mechanism - I can't possibly modify the crontab configuration temporarily every Monday, comment out the tasks, and then change them back after the release, right?

In this scenario,It is more appropriate to package the detection script as a system service (such as systemd service under Linux or launchd plist item under macOS)This way, I can use systemctl stop detector or launchctl unload to suspend the task just like controlling a normal service, and resume it with one command when needed. It is both controllable and worry-free.

2.4.2 systemd service under Linux

Let’s take the previous script “database_query.sh” as an example. If its path is “/root/script/database-query.sh”

Create a new service file:

vim /etc/systemd/system/database-query.service

Paste and save the following:

[Unit] Description=WordPress Change Detection Service [Service] ExecStart=/root/script/database-query.sh Restart=always [Install] WantedBy=multi-user.target

Then run the following command:

systemctl daemon-reexec systemctl daemon-reload systemctl enable database-query.service systemctl start database-query.service

When you need to temporarily stop detection, just run the following command:

systemctl stop database-query.service

2.4.3 launchd management daemon under macos

macOS doesn't have systemctl, but it has launchd. You can create a plist file, for example:

1. Save the script to /usr/local/bin/database-query.sh and give it executable permissions:

chmod +x /usr/local/bin/database-query.sh

2. Create a LaunchAgent file (for example ~/Library/LaunchAgents/com.local.databasequery.plist):

Label com.local.databasequery ProgramArguments /usr/local/bin/database-query.sh RunAtLoad StartInterval 300 StandardOutPath ~/databasequery.log StandardErrorPath ~/databasequery.err

3. Loading startup:

launchctl bootstrap gui/$(id -u) ~/Library/LaunchAgents/com.local.databasequery.plist launchctl list | grep com.local.databasequery

4. Stop/Uninstall:

launchctl bootout gui/$(id -u) ~/Library/LaunchAgents/com.local.databasequery.plist

5. Check the log output:

tail -f ~/databasequery.log tail -f ~/databasequery.err

Compared with crontab, this has the following advantages:

3 Conclusion

This part is my experience in implementing automated operation and maintenance under the WordPress dual-active node architecture.The final piece of the puzzleStrictly speaking, even if we don’t implement this database change awareness mechanism,Manually export the wordpress.sql file after each update and put it into the Syncthing synchronization directory, data synchronization can be completed in the same way. After all, the posting of articles and the review of comments are all done when I am clearly "free and in a good mood", so everything is under control.

But the problem lies in this sense of control - it is not reliable. Forgetting to export once or delaying synchronization for a few minutes is not a big deal in the previous "single point read and write" structure, but it can be solved in the "primary write and secondary read + remote primary read" structure.Active-Active ArchitectureIf the data is not back to the source (and the return to the source is uncontrollable), such negligence may cause content differences and even data conflicts.I don't allow my laziness to become a variable of stability.

Therefore, adhering to the engineering philosophy of "it is not necessary, but it must be there", I still added this small automation module.It doesn't come into play every day, but when you need it, it's there, quietly automating things that should be done manually.

perhaps,No one except me cares whether this mechanism exists or is accurate.; But as the only user and maintainer of this system, I know that this is not just to "run faster", but also to build aSelf-consistent sense of order: The system will remember when I forget and work when I am not there.

Ultimately, I wrote this mechanism not just to add another feature, but to make me worry less.More confidence that "it will be fine".

Note: When WordPress receives a new comment, regardless of whether it has been reviewed or not, it will be immediately written to the wp_comments table in the database, and the status is indicated by the comment_approved field: '0' means unapproved, '1' means approved, 'spam' means spam, and 'trash' means deleted. Therefore, even if the comment is in the "pending review" state, it will still trigger a database change. Furthermore, when I manually review and approve the comment, the comment_approved field will be updated to '1', which is also a database write operation. In other words, from the submission of a comment to its final approval, at least two database synchronization events will be triggered under my current architecture.

Under the current "change-driven synchronization" mechanism, this behavior will bring additional synchronization operations, but it is a guarantee for content consistency.