Project Background
For the past few days, I have been thinking about how to "freeload on Cloudflare's free plan tunnel to achieve a multi-node WordPress nearest access and disaster recovery solution", but there is a major prerequisite to implementing this solution: the content of these node WordPress sites must be identical, in other words, the data of the multi-node WordPress must be synchronized.
Looking back at my current data synchronization solution for multiple WordPress nodes, it still relies on the WPvivid backup plugin on the main WordPress site to regularly generate a complete backup of the entire WordPress (program files + database), which is then synchronized in real-time via Syncthing to the WPvivid backup plugin directory of other WordPress sites, and finally synchronized manually via ”one-click restore” when needed... Isn't that too low-tech? And I don't even have a scheduled restore plan~ (mainly out of laziness, and it depends on my mood at the time).
All in all, this crude synchronization solution no longer fits my currentidentityscenario, and urgently needs an upgrade.
Thoughts on Database Synchronization Solutions
Since Syncthing is one of my current underlying supporting technologies, the synchronization of the WordPress program data itself is no longer an issue. The key lies in what solution should be adopted for synchronizing the database content corresponding to the multi-node WordPress. Generally speaking, there are 3 options:
Directly using the same MariaDB database
If multiple sites only serve as redundancy: by default, blog access requests only go to the main site, switching to the backup site only if the main site fails, and switching to the disaster recovery site only if the backup site fails, then using a single database is fine because only one WordPress site is reading and writing at any given time. However, in this case, if the sole database encounters an issue, everything collapses. Furthermore, if my home data center itself loses internet or power, the database is gone too. A standalone WordPress on the disaster recovery site is useless without a database, so this option is directly passed.
Adopting database master-slave replication
Considering that both the main and backup WordPress sites are in the home data center, we could consider having both connect to the same MariaDB database within the home data center, while the disaster recovery site runs a separate MariaDB database. The home data center's MariaDB would act as the master, and the disaster recovery site's MariaDB as the slave, configured with master-slave replication. This would definitely work without issues (Tailscale, as one of the underlying supporting technologies, deserves a lot of credit here).
But the problem with this approach is: the database inside the home data center still has a single point of failure; and master-slave replication across a wide area network (WAN) might fail if latency is high; additionally, I only have data updates once every few days or even longer, so setting up this kind of replication feels like using a sledgehammer to crack a nut (and it's still syncing over the WAN~), so this is also passed.
Running a MariaDB database locally on each WordPress site
This approach is actually the current setup, which creates database redundancy within the home data center: the main site and the backup site are deployed on a Mac mini and an Intel mini respectively, with WordPress and a MariaDB database on each node, and each node's WordPress reads and writes to its local MariaDB database. WordPress achieves active-standby redundancy externally through load balancing, while the two MariaDB databases are independent (of course, MariaDB databases can also be load-balanced, but I was too lazy to bother, and it doesn't make much sense). At the same time, the disaster recovery site also has WordPress and a MariaDB database deployed, so that when problems occur in the home data center, it does not affect the normal operation of the disaster recovery site's WordPress (the disaster recovery site accesses its local database, independent of the database in the home data center). The only problem is how to ensure the immediate update of the databases on all nodes (previously, I couldn't think of a good way and could only rely on the WPvivid backup plugin).
After having the multi-point scheduled synchronization solution for Chevereto (see article:Home Data Center Series: Chevereto Image Hosting Multi-Node Scheduled Auto-Sync Tutorial), the last pitfall has been filled. WordPress synchronization can also use the same idea: on the main WordPress site, Syncthing is used to synchronize the WordPress program data and the exported wordpress.sql file to other nodes, and then other WordPress nodes respectively import the wordpress.sql file into their respective MariaDB databases (theoretically, no matter how many nodes there are, they can be handled in the same way).
As for the issue of timely updates, it is actually very easy to solve. I'll keep you in suspense and explain it slowly later, but before that, I need to re-plan the WordPress folder synchronized in the Syncthing solution.
Deploying Syncthing to Synchronize the WordPress Folder
Previously, the WordPress folder synchronized in Syncthing was only the folder of the WPvivid backup plugin (mainly to synchronize the backup files saved locally by the WPvivid plugin on the main WordPress site to the WPvivid plugin directory of other nodes, making it convenient for one-click recovery when needed). Now, it needs to be changed to synchronize the main WordPress site using-vparameters to mount the entire directory ”html” to Docker:
For Syncthing configuration details, please refer to the previous article:Docker Series: Detailed Tutorial on Implementing Multi-Point Folder Synchronization Based on Syncthing Using Docker, which I won't repeat here. For subsequent testing, a new aliyun node has been added to Syncthing this time. Its docker run command format is as follows:
docker run --name syncthing-aliyun -d --restart=always --net=public-net \
--hostname=syncthing-aliyun \
-e TZ=Asia/Shanghai \
-p 8384:8384 \
-p 22000:22000/tcp \
-p 22000:22000/udp \
-p 21027:21027/udp \
-v /docker/syncthing/config:/config \
-v /docker/wordpress:/data1 \
lscr.io/linuxserver/syncthing:latest
Add this node on the synchronization source syncthing-macmini using the Tailscale address, and complete the synchronization settings for the WordPress folder on all nodes.
Effect on syncthing-macmini:
Effect on syncthing-intermini:
Effect on syncthing-tc:
Effect on syncthing-aliyun:
Once this state is reached, subsequent synchronization with Syncthing will basically run without issues. The most troublesome step is finally done.
Four points to note:
For directories mapped inside the container, such as/docker/wordpressin the command above, the permissions of the wordpress folder on the host machine of each node must be changed to 777, otherwise synchronization will fail.
If this kind of error occurs:
It can be resolved by manually creating a folder named ”.stfolder” inside the wordpress folder:
If the status of the wordpress folder on other nodes is not ”Up to Date”:
but instead a red ”Out of Sync”, first look for the cause of the failure:
The ultimate tricks that work are changing permissions, removing the folder on the out-of-sync Syncthing node, deleting the contents of the folder, restarting the Syncthing container, then adding the ”.stfolder” folder, and re-sharing from the host—tinkering back and forth like this until all statuses are finally normal.
It is recommended that, except for the main WordPress node, other nodes directly empty the WordPress folder (if there are already files in it), and then create a new ”.stfolder” folder before starting to add the shared folder from the main node and completing the synchronization.
Note: Syncthing may have other errors. You can search more online, as there are quite a few articles, and I certainly cannot cover all the issues here.
Deploying and Initializing MariaDB
Then there is the database issue, because the ”wp-config.php” configuration files of each wordpress node are all the same, for example:
Therefore, it is necessary to ensure that the way wordpress accesses the mariadb database on each node is the same (in this article, they can all be accessed through mariadb01). Generally, the normal way is to place both the mariadb database docker and the wordpress docker in the same non-default bridge when creating a new database, as in the command for syncthing on the newly created Alibaba Cloud node above--net=public-net, and then ensure that the docker name of mariadb on all nodes is the same as set in ”wp-config.php”, which is mariadb01 in my case:
The creation command for mariadb01 is as follows:
docker run --name=mariadb01 -d --restart=always --net=public-net \
-p 3306:3306 \
-v /docker/mariadb/db:/var/lib/mysql \
-e MARIADB_ROOT_PASSWORD=yourpassword \
mariadb:10.11
After creating the mariadb database for each node, initialization is also required: create the wordpress database and its corresponding account and password, and grant access permissions of the wordpress database to this account (for operations related to initializing the database, please refer to the article:Tips and Tricks Series: Creating a New Empty Database and Granting Permissions to Corresponding Users, of course, you can also use methods you are familiar with to initialize, such as command line, phpmyadmin, etc.).
MariaDB Database Synchronization
Exporting the WordPress Database from the Main Site
With the experience of chevereto database synchronization, I still adopt the same method: export the wordpress database in the mariadb database on the main wordpress site to the newly created DB directory in the wordpress synchronization folder configured by syncthing:
Create a new ”export_wordpress_database.sh” script to export, the script content is as follows:
#!/bin/sh
# 远程ssh登录默认不会加载环境变量，主动加载一下，这是为了远程ssh执行此脚本做的准备，本地运行脚本不需要
source ~/.bash_profile
# 执行导出wordpress库命令，导出到已经配置了syncthing同步的wordpress/dbfolder'
Don't forget to give execution permissions to the script:
chmod +x export_wordpress_database.sh # 需要进入正确的路径下执行
Then place the script in any path. For example, I put it in the ”startup” directory newly created under the macos user directory. I put all scripts that need to be run via remote ssh in this directory for easy management, as shown below:
Now you only need to run this script to generate a full backup file wordpress.sql of the wordpress database on the main wordpress site in the wordpress/db directory. As long as the syncthing synchronization directory configuration is normal, the wordpress.sql file can be quickly synchronized to the DB directories of all other wordpress sites:
Main wordpress site (macmini):
Backup wordpress site (intermini):
Disaster recovery wordpress site (Tencent Cloud server):
Note: syncthing has its own default interval scanning time (3600 seconds). The final time to complete data synchronization varies depending on the size of the files to be synchronized and the connection rate of each syncthing node. My wordpress database here is tens of megabytes, and I feel it takes at most 1-2 minutes for all nodes to complete synchronization. However, this is the time after the change is scanned. As for when the scan starts, it depends on luck, but it is guaranteed to be scanned within 3600 seconds~.
Importing the WordPress Database into MariaDB on Other Nodes
We still need to rely on shell scripts to complete this. Create a new script ”wordpress_import.sh” in the mariadb mount folder on all other wordpress nodes except the main wordpress site, with the following content:
#!/bin/sh
mysql -uroot -pp@ssw0rd < /var/lib/mysql/wordpress.sql
# 运行完成
echo 'end'
This script needs to be run in the mariadb container to import the wordpress.sql file synchronized from the main wordpress site under the local pathOriginal:into the local mariadb.
Note 1: Why is the<left side empty? Because the wordpress.sql file exported previously using the mysqldump command with the--database wordpressparameter contains commands like "create wordpress", as follows:
So it does not need to be specified. Also, don't forget to give it execution permissions:
chmod +x wordpress_import.sh # 需要进入正确的路径下执行
Note 2: Why create the script ”wordpress_import.sh” in the mariadb mount folder? Because files on the host file system cannot be directly imported inside the mariadb container, a workaround is needed.
Then create a new shell script ”import_wordpress_database.sh”, with the following content:
#!/bin/sh
# 远程ssh登录默认不会加载环境变量，主动加载一下，这是为了远程ssh执行此脚本做的准备，本地运行脚本不需要
source ~/.bashrc #注意，不同系统环境变量文件名不同，例如：macos是bash_profile，debian是bashrc，请根据自己实际环境修改
# 直接删除wordpress库，删表太麻烦了，而且怕某个版本变动增加新表，不过貌似不用删也行
docker exec -u root mariadb01 mysql -uroot -pyourpassword -e "drop database if exists wordpress;"
# 将同步过来的wordpress.sql复制到mariadb的工作目录中,这就是上面说的变通
cp /docker/wordpress/db/wordpress.sql /docker/mariadb/db/01/wordpress.sql
# 将主wordpress站点导出的wordpress.sql导入本地mariadb数据库中
docker exec -u root mariadb01 bash /var/lib/mysql/wordpress_import.sh
# 运行完成
echo 'end'
Place the script in your preferred path, I put it directly in the /root/script directory.
After the above operations are completed, you only need to run/root/import_wordpress_database.shthis script to complete:
Step 1: Delete theWordPressdatabase in the mariadb database of this node (actually, it seems this step is not necessary, but it's cleaner this way);
Step 2: Copy the latest wordpress.sql file in /docker/wordpress/db to the local mount directory of the mariadb container
Step 3: Import wordpress.sql into the mariadb container.
Deploying WordPress on Other Nodes
After the previous steps, the wordpress program files and databases on all other wordpress nodes except the main wordpress node have been synchronized. If these nodes already had wordpress nodes deployed, they can be accessed directly. Since my Alibaba Cloud node is newly added, I need to deploy the wordpress container, which only requires the following command:
docker run --name=wordpress -d --restart=always --net=public-net \
-p 80:80 \
-v /docker/wordpress/html:/var/www/html \ #注意挂载的wordpress文件夹路径不要出差
wordpress
-p 80:80 \
-v /docker/wordpress/html:/var/www/html \ # Note: Make sure the mounted wordpress folder path is correctTips and Tricks Series: Simple setup tutorial for WordPress supporting multi-domain access。
Using inotify to Monitor Folder Changes and Execute Scripts
Process Logic
wordpress
Then you can just access it directly.
Installing and Deploying inotify
apt update
apt install inotify-tools
Note: By default, wordpress restricts the access domain name. If you want to remove the access restriction, please refer to my other article:
Actually, the previous steps are all manual operations, which can only be considered a wordpress multi-node synchronization solution, and have nothing to do with the ”near" real-time synchronization solution mentioned in my title. Indeed, so next is the key point: inotify.”inotify is a tool under Linux for monitoring folder changes. It can monitor changes in folder content and then execute specified scripts. Doesn't this connect with our previous operations? When I run the script "export_wordpress_database.sh" to export the wordpress database on the main wordpress site, a new wordpress.sql script will be generated in the "wordpress/db" directory of the main site; the generation of the new wordpress.sql script is detected by syncthing and synchronized to the db directory on all other wordpress nodes; inotify running on other wordpress nodes monitors the "wordpress/db" folder on the local node, and once a change is detected, it executes the local "import_wordpress_database.sh" script to complete the import of the wordpress database, and then the wordpress synchronization of all nodes is completed, perfect!: with the following content:
#!/bin/bash
watchdir="/docker/wordpress/db" # Define what needs to be monitored, here I monitor the db directory
script="/root/import_wordpress_database.sh" # Define the script to be executed
while
inotifywait -e modify $watchdir; # -e modify dir is monitoringwhether files in the dir directory are modified -o modifylog writes the monitored events (here, file modified) tothe modifylog log
do
timestamp=$(date)
sleep 1m # Wait for 1 minute, because syncing files via syncthing takes some time, if you don't wait it will keep recording file modifications
bash $script # Execute the script to import the wordpress.sql file
done
Grant execution permission:
chmod +x monitor.sh
Then just add monitor.sh to startup (see my other article:Debian Series: 3 Common Ways to Set Commands or Scripts to Run at Startup)。
Finally, ensure that monitor.sh is running normally on all other wordpress nodes：
Verifying the Actual Effect of the Solution
Execute the script ”export_wordpress_database.sh” on the main wordpress node”
Enter the export directory to confirm:
Verify if syncthing has successfully synchronized
Since the default syncthing rescan interval is 1 hour, and I didn't want to wait that long, I clicked rescan (note that the time I clicked was 08:06):
What did I do between the export time of 08:01 and 08:06? It was because my Bear egg cooker buzzed to remind me that the eggs were done, so I went to have breakfast first...
Actually, at this point, if syncthing is running normally, the synchronization is basically completed in an instant (mainly because the wordpress.sql file is not large, only 38 MB). You can see the folder modify event information in the inotifywait terminal window, taking the Alibaba Cloud node as an example:
Then we go to the other nodes one by one to confirm, first confirming whether the syncthing synchronization was successful.
Backup site:
Tencent disaster recovery site:
Alibaba disaster recovery site:
It can be seen that the syncthing synchronization was successful.
Finally, let's confirm whether inotifywait successfully monitored/docker/wordpress/dbthe changes to the wordpress.sql file in the folder and executed the script ”import_wordpress_database.sh”. If we look at the script execution content:
then let's look at several nodes in turn/docker/mariadb/db/01/wordpress.sqlfor the modification time of the file (actually, we should look at the execution of the last commanddocker exec -u root mariadb01 bash /var/lib/mysql/wordpress_import.sh, but I can't think of a good way to prove this execution result off the top of my head, as none of them feel authoritative enough, so let's just look at the generation time of wordpress.sql from the previous step).
Backup node:
Tencent node:
Alibaba node:
It was basically one minute after I clicked the ”Rescan” button (08:06) for the syncthing wordpress folder, which proves that inotifywait can indeed monitor
/docker/wordpress/dbfolder changes and execute the specified script:import_wordpress_database.sh, completing my:wordpress multi-node "semi-automatic", "near" real-time synchronization solution's final puzzle piece.
Note 1: The so-called ”semi-automatic” means that after I finish updating the content of the main wordpress site, I need to manually run a database export script ”export_wordpress_database.sh”, while the rest is fully automatic. To be fair, ”semi-automatic” is a bit humble; it should be ”0.9 automatic”, but after all, there is still a gap from ”fully automatic”. It is not impossible to achieve full automation, but it requires further in-depth study of how wordpress works, mainly how to determine if wordpress has updated content (articles, posts, pages), etc., because plugins and wordpress versions can be updated automatically, so these cannot be used as a basis for judgment (actually, we could start by detecting new content in the database, holy crap, just thinking about it is exciting).
I hesitated when I thought of this, and I really didn't want to study it any deeper. I simply used manually running the script to output the wordpress.sql file to the designated folder to trigger the subsequent process. This actually violates my principle of ”go ahead when there are difficulties, and create difficulties to go ahead even when there are none”, but it's really too tiring and hurts my brain cells. I'll continue researching when I'm in the mood in the future.
Also: After using it for a period of time, it is still not possible to directly synchronize the entire html directory of wordpress, mainly because of reasons like main node version upgrades or plugin updates. Directly synchronizing the entire html directory to other nodes often causes wordpress ”fatal errors”. The best way is still to only synchronize the directory where the wordpress.sql file is located. This way, in the future, it will just be pure wordpress database export, file synchronization, and import, which works fine.
Note 2: The so-called ”near” real-time synchronization is because syncthing's default full scan interval is 1 hour (it depends a bit on luck; sometimes it scans very quickly, sometimes there is no response for half a day, which is why I manually clicked the ”Rescan” button during testing). 1 hour is completely acceptable for my needs. After all, it's just a personal blog, not some enterprise-grade high-standard application, so I'm too lazy to change it. If any friends really have high-standard requirements, just reduce syncthing's ”full scan interval”:
Summary
Actually, if you just want to synchronize published articles between multiple wordpress sites, there are simpler methods: for example, using plugins. There is more than one such plugin. Although there are some settings and the usage effects have quite a few flaws, compared to my method, it is really much simpler. And the method I used before, ”scheduled backup based on WPvivid backup plugin combined with syncthing synchronization plus manual recovery when needed” (such a long modifier still feels quite low), is not unusable.
But for me, by constantly tinkering, constantly learning new technologies (for example, this article forced me to study shell scripting), and constantly optimizing solutions, even if it's just a tiny improvement each time and learning a little bit of new knowledge, after accumulating over time, who knows, maybe one day I will achieve great mastery! (Sunflower in hand, hahahaha, the world is mine, hahahaha, sounds familiar, doesn't it?)
Of course, as mentioned at the beginning of the article, this article can only be regarded as research on pre-requisite technology. Ultimately, I still need to research: ”Using Cloudflare's free plan tunnel for free to achieve multi-node wordpress nearest access and disaster recovery solution”, I don't know if it will work, but let's research and see first.