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/write” solution. Since only the primary node was responsible for handling database read and write requests on a daily basis, real-time synchronization between databases was not necessary: whenever I added a new post, modified content, or approved/replied to comments, causing changes in the WordPress database, if I happened to be in the mood, I would manually export the wordpress database from the primary node's MariaDB into a wordpress.sql file and place it in Syncthing's synchronization directory. Next, Syncthing would synchronize this file to the designated directories of the hot standby and disaster recovery nodes. Once the inotify scripts on these two nodes detected changes in the directory, they would automatically trigger 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 an active-active architecture of home data center ”primary-write, secondary-read” + Racknerd Chicago node ”primary-read”. Normally, regular access traffic hits the content in the APO cache, while access requests that need to go back to the source will route back to different nodes through multiple connectors in a single Cloudflare Tunnel. Since Cloudflare Tunnel's multi-connector load balancing strategy is quite ”capricious and willful”, although theoretically, in a multi-connector scenario, back-to-source requests should preferentially hit the Chicago node closest to the user, in actual use, it still cannot guarantee that all requests will behave obediently. Especially when the database synchronization between the primary and secondary nodes is not yet complete, issues such as lost comments, post rollbacks, or even inconsistent page content display may occur.

More importantly, the past method of relying on me to manually export wordpress.sql and trigger synchronization, although simple to operate, indeed lacked technical depth. It was not only mechanical and boring but also very easy to forget, lacking any ”cool factor” and feeling somewhat unfitting for my identity~~. Therefore, under the new architecture, in order to ensure as much as possible that after the data changes on the ”primary-write, secondary-read” node in the home data center, it can be synchronized to the Chicago ”primary-read” node in a short time to avoid content discrepancies when back-to-source traffic hits the Chicago ”primary-read” node, I had to rethink the data synchronization mechanism. I hoped to implement a smarter, more automated database change perception and automated synchronization workflow—which is also why I didn't write about the active-active architecture this week but wrote this article instead, because this is a key problem that must be solved to achieve the goal of daily automated operations after upgrading to the WordPress active-active architecture.

2 How to Determine If Content Updates Have Occurred on the “Primary-Write” WordPress Node

1 WordPress Database Change Perception

In the previous section, I mentioned ”more automated database change perception.” To achieve this function, it involves how to choose the most suitable 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 precise way. The binlog of MySQL/MariaDB records all write operations (INSERT, UPDATE, DELETE) to the database, making it highly suitable for master-slave replication, incremental synchronization, or data recovery. However, this requires enabling binlog at the database layer, configuration file support, and parsing log content or introducing middleware systems, which comes with 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 into an independent table or send them to a message queue. This method is more flexible, but it modifies the original database structure and can easily introduce extra overhead and side effects.
• Using file system-level change detection(such as inotify): Theoretically, it can monitor whether database files have changes in read/write behavior, but in practice, this can only be achieved on Linux, and the file change frequency of MariaDB is extremely high (which does not equate to substantial content changes). It is also unavailable on platforms like macOS, making it unsuitable for precise detection (my WordPress primary node runs on macOS).
• Periodic full comparison (such as checksum or diff): Brute-force and simple, but inefficient, and unable to achieve real-time response to changes. However, returning to the specific scenario of WordPress, we do not need to perceive all database changes in such a ”full” manner.The vast majority of important data updates are concentrated in a few core tables:
• wp_posts: Posts, pages, and attachments are all in this table. The field post_modified_gmt represents the last modification time, and post_date_gmt represents the creation time;
• wp_comments: All comment data is here, and the key field is comment_date_gmt;

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

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 certain query is later than the last recorded one, it indicates that the WordPress site has undergone an actual update (whether it is adding a new comment, editing a post, publishing a page, etc.), at which point the database export and synchronization process can be triggered.

This method does not require modifying the database structure, does not rely on binlog, and does not introduce middleware,making the deployment extremely lightweight and highly suitable as a ”change perception signal” for the automated synchronization system.

2 Creating a Script to Periodically Query Database Changes

2.1 MariaDB Deployed via Source Code

2.1.1 Preliminary Work: Add a ”.my.cnf” File in the Working Directory (Optional)

Summary of Steps:

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

cd ~

2. Create the .my.cnf file (recommended to use an 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=你的数据库密码

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

chmod 600 .my.cnf

5. Test if it takes effect:

mysql -e "SHOW DATABASES;"

Description

• As long as you execute the mysql or mysqldump command with this user, it will automatically read from ~/.my.cnf [client] section information.
• If you have multiple users logging into the system,each user can have their own ~/.my.cnf file, without interfering with each other.
• It is not recommended to place .my.cnf in system-level directories (such as /etc/my.cnf) to expose passwords; placing it in the user's home directory is safer and more controllable.
• This section is not mandatory. However, if you do not want to explicitly specify the username and password in the detection script using ”mysql -uxxx -pxxx”, you can do this. After all, explicitly specifying them is not very secure. Of course, if you don't mind, you can skip this section.

2.1.2 Creating the Query Script

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

cd ~
mkdir -p script
cd script

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

vim database-query.sh

3. Write the following content and save:

#!/bin/bash(date +%Y%m%d%H%M%S)(mysql -N -e "SELECT MAX(GREATEST(post_modified_gmt, post_date_gmt)) FROM wp_posts;" DB_NAME)(mysql -N -e "SELECT MAX(comment_date_gmt) FROM wp_comments;" DB_NAME)"{LATEST_POST_TIME}|{LATEST_COMMENT_TIME}"STATE_FILE" ]; then"CURRENT_STATE">"STATE_FILE"CURRENT_STATE"(cat "STATE_FILE")"CURRENT_STATE" != "PREV_STATE" ]; then"CURRENT_STATE" > "STATE_FILE"DUMP_DIR/wp_dump_DATE_NOW.sql"DB_NAME > "DUMP_FILE"DUMP_FILE" user@chicago:/data/wp_sync/

Note: The end of the script associates a method to synchronize the wordpress.sql file after detecting database changes. This is just a demonstration. The specific method to choose depends on your own environment, such as choosing scp, rsync, or simply achieving automatic synchronization through Syncthing. If you want to automatically synchronize via Syncthing, you need to replace it with the Syncthing synchronized file directory when exporting the database.

4. Set execution permissions for the script:

chmod +x database-query.sh

In addition, a script that does not use.my.cnfto store the account and password, but directly uses-u -pparameters is also provided:

#!/bin/bash(date +%Y%m%d%H%M%S)(mysql -uroot -pyourpassword -N -e "SELECT MAX(GREATEST(post_modified_gmt, post_date_gmt)) FROM wp_posts;" DB_NAME)(mysql -uroot -pyourpassword -N -e "SELECT MAX(comment_date_gmt) FROM wp_comments;" DB_NAME)"{LATEST_POST_TIME}|{LATEST_COMMENT_TIME}"STATE_FILE" ]; then"CURRENT_STATE">"STATE_FILE"CURRENT_STATE"(cat "STATE_FILE")"CURRENT_STATE" != "PREV_STATE" ]; then"CURRENT_STATE" > "STATE_FILE"DUMP_DIR/wp_dump_DATE_NOW.sql"DB_NAME > "DUMP_FILE"DUMP_FILE" user@chicago:/data/wp_sync/

Note 1: The biggest problem with this method is that when executingps aux | grep mysqlin another terminal, you might directly see:

mysql -uroot -ppassword -e ...

In multi-user shared environments and CI/CD builds, there is apotential password leakage risk, though it might not matter much if it is just for your personal use environment.

Note 2: After the primary write node successfully exports the sql file of the wordpress database, you can actually send system-level notifications to your phone via Bark:

curl -s "https://bark.example.com/your_token/wordpress通知/wordpress数据库导入成功" > /dev/null

Of course, the node that imports the sql file of the wordpress database can also send notifications to the phone via Bark in the same way.

2.2 MariaDB Deployed via Docker

2.2.1 Preliminary Work: Create a ”.my.cnf” File on the Host Machine and Mount It into the MariaDB Container

Create the “.my.cnf” file (host machine)

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

vim /docker/mariadb/.my.cnf

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

[client]
user=root
password=你的数据库密码

Note:

• user= Must match the user you use to connect to MariaDB (usually root).
• password= Please enter the actual password, do not add quotes.
• It is recommended to set this file's permissions to 600 (readable only by you):

chmod 600 /docker/mariadb/.my.cnf

Mount the ”.my.cnf” file to the container

Use the ”-v” parameter of the docker run command to mount the host's ”.my.cnf” inside the container:

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

Explanation: ”-v /docker/mariadb/.my.cnf:/root/.my.cnf:ro” means mounting your host's configuration file into the home directory of the root user inside the container, where ”:ro” indicates a read-only mount, which is more secure.

If deploying via docker-compose, you can mount it in the following way:

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

2.2.2 Creating the Query Script

This query script runs on the host machine. The creation steps are similar to before, so I won't repeat them. The final script content is as follows:

#!/bin/bash"{OUTPUT}.tmp"(docker exec CONTAINER \USER -pPASS -e "DB.wp_posts), DB.wp_comments)"STATE_FILE" ]; then"LATEST_TIME">"STATE_FILE"(cat "STATE_FILE")"LATEST_TIME" -gt "LAST_TIME" ]; then CONTAINER \USER -pPASS --single-transaction --max_allowed_packet=64MDB \"TMP_OUTPUT"? -eq 0 ]; then"TMP_OUTPUT" "OUTPUT" OUTPUT"LATEST_TIME" > "STATE_FILE"TMP_OUTPUT"

3 Periodically Running the Database Query Script

The implementation method varies across different systems. For Linux or macOS systems, you can usecrontabto achieve this:

crontab -e

Add the following content (check every 5 minutes):

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

Notes

• The default container name in the script is mariadb, you can use docker ps to check the actual container name.
• If the container is started via docker-compose, the container name might be yourproject_mariadb_1, and you will need to modify CONTAINER= in the script.
• If you specified –volume during docker run, you can also place the STATE_FILE in the host's persistent directory.

4 Running the Database Change Monitoring Script as a Service (Optional)

4.1 Disadvantages of the “cron” Method

Generally speaking, if it is just in a local environment and you want to run the detection script very lightly, using cron is actually sufficient. It is simple and stable, requiring only a scheduled task to execute the database change detection logic every few minutes, making it very suitable for unattended routine needs.

But for me, the situation is a bit more complex. I don't want this detection scriptto run 24/7 all year round, because every Monday I publish new WordPress posts, accompanied by a series of content organization tasks, such as adjusting post formatting, updating the sitemap, temporarily enabling the automatic translation feature of the TranslatePress plugin to translate new post content, and then disabling the plugin's automatic translation feature once the translation is complete (to save money~).

These operations are concentrated within one or two hours, during which the database content is actuallyfrequently changingAt this time, if the detection script is still running, it will frequently detect database ”changes”, triggering unnecessary synchronization processes, and even affecting content that has not yet been finalized.

Therefore, my ideal way of using it is:Stop the database change detection script before publishing posts every Monday, and manually resume running after the publishing process and all necessary operations are completedThis control flow seems very tedious under the cron mechanism—I can't possibly temporarily modify the crontab configuration, comment out the task every Monday, and then change it back after publishing, right?

In this scenario,wrapping the detection script as a system service (such as a systemd service under Linux or a launchd plist item under macOS) is more appropriateThis way, I can pause the task using systemctl stop detector or launchctl unload just like controlling a normal service, and resume running with a single command when needed. It is both controllable and worry-free.

4.2 Systemd Service under Linux

Taking 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 content:

[Unit]
Description=WordPress Change Detection Service

[Service]
ExecStart=/root/script/database-query.sh
Restart=always

[Install]
WantedBy=multi-user.target

Then run the following commands:

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

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

systemctl stop database-query.service

4.3 Launchd Managing Daemon Processes under macOS

macOS does not 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 grant execution permissions:

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

2. Create a LaunchAgent file (e.g., ~/Library/LaunchAgents/com.local.databasequery.plist):

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN"
 "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>Label</key>
  <string>com.local.databasequery</string>
  <key>ProgramArguments</key>
  <array>
    <string>/usr/local/bin/database-query.sh</string>
  </array>
  <key>RunAtLoad</key>
  <true/>
  <key>StartInterval</key>
  <integer>300</integer>
  <key>StandardOutPath</key>
  <string>~/databasequery.log</string>
  <key>StandardErrorPath</key>
  <string>~/databasequery.err</string>
</dict>
</plist>

3. Load and start:

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

4. Stop/Unload:

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

5. Check log output:

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

Advantages of doing this compared to crontab:

3 Summary

This part of the content can be considered as my implementation of automated O&M under the WordPress active-active node architecture,the last piece of the puzzleStrictly speaking, even without implementing this database change perception mechanism,manually exporting the wordpress.sql file and putting it into the Syncthing synchronization directory after each updatewould still achieve data synchronization. After all, operations like publishing articles and moderating comments only happen when I am clearly ”free and in the mood,” so everything is under control.

But the problem lies precisely in this ”sense of control”—it is not reliable. Forgetting to export once or a synchronization delay of a few minutes didn't matter under the previous ”single-point read/write” structure, but under the ”primary-write secondary-read + remote primary-read”active-active architecturesetup (where back-to-source traffic is also uncontrollable), such slackness could cause content discrepancies or even trigger data conflicts. As the system architect,I cannot allow my own laziness to become a variable in stability.。

Therefore, adhering to the engineering philosophy of "better to have it and not need it than to need it and not have it," I still added this small automation module.It won't work every day, but once needed, it is there, quietly automating what should have been done manually.

Perhaps,no one but me cares whether this mechanism exists or whether it is precise;but as the sole user and maintainer of this system, I know that this is not just about ”running faster,” but about building aself-consistent sense of order:the system will remember when I forget, and run when I am not around.

Ultimately, writing this mechanism is not just to add another feature, but to allow me to worry lessand have more certainty that ”it will definitely be fine.”。

Note: When WordPress receives a new comment, regardless of whether it has been moderated, it is immediately written to the wp_comments table in the database, with its status identified by the comment_approved field: ’0’ represents unapproved, ’1’ represents approved, ’spam’ represents spam, and ”trash” represents deleted. Therefore, even if a comment is in the ‘pending moderation’ state, it will still trigger a database change. Furthermore, when I manually moderate and approve the comment, the comment_approved field is updated to '1', which also constitutes a database write operation. In other words, from submission to final approval, a single comment will trigger at least two database synchronization events under my current architecture.

Under the current ”change-driven synchronization” mechanism, although this behavior brings extra synchronization operations, it actually serves as a guarantee for content consistency.