I created a shell script for Ubuntu 22.04 and MariaDb 10.6.7 that automates the entire process of setting up master/slave replication between two rented virtual private servers (VPS) from different cloud PaaS providers.
With a single command it handles :
Pausing ERPNext and MariaDB on master and slave
Taking a full backup of master with site_config.json and public & private files.
Patching /etc/mysql/mariadb.conf.d/50-server.cnf on master to enable replication
Creating and authorizing a replication slave user on the master database
Downloading master status data and database backup.
Uploading master database backup to slave
Searching for every reference to master site name and replacing with slave site name
Restoring edited master backup onto slave
Patching /etc/mysql/mariadb.conf.d/50-server.cnf on slave to enable replication
Initiating replication from master to slave at the correct execution log index position.
Restarting MariaDb on slave and then on master
Restarting ERPNext on both.
The process takes about 5 minutes on a small database with decent Internet.
When complete, every ERPNext user action on the master appears moments later in ERPNext on the slave.
If the master dies for some reason the slave is available for immediate use as the new master!!
Once the old master is recuperated, a backup of the slave can be restored on the master and the original situation continued as before. This is not master/master replication and no one should ever make changes on the slave while the master is in use. So preferably ERPNext on the slave is shut down.
Master/master replication needs to be between machines on the same fast network. That’s not this. The point of this is self-preservation by controlled streaming from one continent to another (I rent a VPS in Europe and a VPS in the USA). Separate hosting. Separate Internet backbone. Separate power. With someone cutting submarine cables, Putin threatening satellites and weather conditions threatening power sources I want to spread my risk.
If enough people are interested in this I will create a public GitHub repo in order to share it.
I am gratified by your interest in this. I’ll be giving it a couple of hours a day to decouple it from my existing scripts and build in a lot of protections to minimize risk to your existing installations, and to make it easier to understand what it is doing.
You will understand that the scripts have to take on ‘sudo’ privileges in order to be able to alter the MariaDB configuration. I hope you will read the code and understand how I implement that. Please read up on the command ‘sudo -A’ and the SUDO_ASKPASS environment variable. Understanding the use of ‘/dev/shm’ is also a good idea (ignore the naysayers).
I would very strongly insist, before using these scripts for real, that you experiment on 2 snapshottable virtual machines, so if things get ugly you can just revert to your snapshots.
As a kind of bonus, it will include a pair of scripts, …
handleBackup
handleRestore
… which, in their own sub-directory, and with their own environment variable file, control restoring the database of one site as the database of another site, correctly changing site name and db_password as specified.
We seem to be ready for some alpha testing of this tool.
The delay has been due to:
Reducing coupling to our existing code so it becomes a stand alone program.
Reducing dependence on our particular ERPNext configuration.
Adding a lot of explanatory logging so that you can understand what it is doing.
Can we get some volunteers to set up some pairs of disposable virtual private servers running Ubuntu and ERPNext 13??
I say “disposable” because debugging directly in your VPS ourselves would be far more efficient than trying to have you do it for us. Ideally you’d revert the snapshot or reinstall or whatever after we got it working for you. We’d roll the bug fixes into the repository.
