ERPNext High Availability Setup with Galera and Proxysql

Photo by Kvistholt Photography on Unsplash

This is the first blog post on building a ERPNext Service Oriented Architecture. In this blog post I will explain you how to setup a high availability ERPNext cluster using Galera and Proxsql. This tutorial only considers the ERPNext development setup installed directly on the operating system (No docker involvement). For this setup we will use 6 virtual private servers (VPS) and the role of each VPS is as follows,

3 Nodes for Galera Cluster (MariaDB) 2 Nodes for ERPNext application (one primary and one secondary) 1 Node for Proxysql and Nginx server

All the above nodes run on Ubuntu 22.04

Galera Cluster setup

Install mariadb server and client in all the three nodes. The galera cluster comes together with the mariadb database.

apt-get install mariadb-server mariadb-client

Secure all the mariadb installations

mysql_secure_installation

Now on each cluster edit the /etc/mysql/mariadb.conf.d/50-server.cnf file as follows,

1. Comment the existing bind-address
2. Add the following configuration

[galera]
 wsrep_on=ON
 wsrep_provider=/usr/lib/galera/libgalera_smm.so
 wsrep_node_name='galera-3'
 wsrep_node_address=10.0.0.2

 wsrep_cluster_name='erpnext-cluster'
 wsrep_cluster_address="gcomm://10.0.0.6,10.0.0.5,10.0.0.2"

 wsrep_provider_options="gcache.size=300M;gcache.page_size=300M"
 wsrep_slave_threads=4
 wsrep_sst_method=rsync
 binlog_format=ROW
 bind-address=0.0.0.0
 default_storage_engine   = InnoDB
 innodb_autoinc_lock_mode = 2

Change the wsrep_cluster_address (all the addresses in the cluster), wsrep_node_address and wsrep_node_name accordingly

In one of the nodes, start the cluster by issuing the following command in the terminal

galera_new_cluster

On all the other nodes restart the mariadb, this allows the nodes to join the Galera cluster. !Warning: Do not run the following command in the node you initiated Galera (Ran above command)

 systemctl restart mariadb

Validate the cluster by checking the cluster size

 show status like 'wsrep_cluster_size';

Proxysql setup

Now lets setup the proxysql. The SQL traffic generated by ERPNext will be routed through the proxysql load balancer.

Download and install the proxysql using wget and dpkg

[embed]Download and Install ProxySQL Download and Install ProxySQL You can install the packages directly by first downloading from the Github Releases Page…proxysql.com

Then install the mysql-client

apt install mysql-client

Once both the proxysql and mysql-client are installed, login to the proxysql using the default username and the password

mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin>'

and change the default password to something you prefer.

update global_variables set variable_value='admin:newpassword' where variable_name='admin-admin_credentials';

Then load the configuration to the runtime from the memory and save it to disk for persistence.

load admin variables to runtime;

save admin variables to disk;

Create a monitor user for node monitoring and update it in proxysql global variables.

create user 'monitor'@'%' identified by 'monitorpassword';

flush privileges;

update global_variables set variable_value='monitor' where variable_name='mysql-monitor_username';

update global_variables set variable_value='monitorpassword' where variable_name='mysql-monitor_password';

Set the monitor intervals in the proxysql.

update global_variables set variable_value='2000' where variable_name in ('mysql-monitor_connect_interval', 'mysql-monitor_ping_interval', 'mysql-monitor_read_only_interval');

Finally Load the configuration to run time and persist it to disk.

load mysql variables to runtime;

save mysql variables to disk;

Add the backend nodes to the proxy server using host groups. A host group essentially determines if a backend belongs to a mysql reader database or a writer database. Please note that a writer database can do both reading and writing. To do this we should update the mysql_replication_hostgroups table. Here we choose 1 as writer host group and 2 as reader host group.

insert into main.mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment) values (1, 2, 'galera_cluster');

Now add the mysql nodes to the proxysql server table.

insert into main.mysql_servers (hostgroup_id, hostname, port) values (1, '10.0.0.6', 3306);

insert into main.mysql_servers (hostgroup_id, hostname, port) values (1, '10.0.0.5', 3306);

insert into main.mysql_servers (hostgroup_id, hostname, port) values (1, '10.0.0.2', 3306);

Once done, persist the configuration.

load mysql variables to runtime;

save mysql variables to disk;

load mysql servers to runtime;

save mysql servers to disk;

Confirm that the servers are reachable using the proxysql monitor.

select * from monitor.mysql_server_connect_log order by time_start_us desc limit 3;

select * from monitor.mysql_server_ping_log order by time_start_us desc limit 3;

Finally on one of the Galera cluster nodes, create a root user so ERPNext can access and create the databases for the sites. The new user will be replicated automatically on the other nodes that is participating the cluster

create user 'root'@'%' identified by 'rootpassword';

grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges

Please note that with grant option is essential for ERPNext to create the databases and users properly.

ERPNext Node Setup

Install Frappe bench and init a bench in one of the ERPNext nodes. Set the mariadb host ip address to one of the Galera cluster node ip address

bench set-mariadb-host 10.0.0.6

To install the frappe bench you can follow the official documentation. Please add the mysql configuration mentioned in the documentation, to my.cnf in each node.

[embed]Installation How To Create Custom Fields During App Installationfrappeframework.com

Now create a site, get the payments and erpnext apps and install them on the site. This process with create a database and a user in the galera cluster. The username and the database name are equal (random name).

bench new-site erp.site --mariadb-root-password rootpassword

bench get-app payments

bench get-app erpnext

bench --site erp.site install-app payments

bench --site erp.site install-app erpnext

Now on the Galera cluster, the database user has a host ip of the ERPNext node (e.g. 10.0.0.3). This we must change to the proxysql ip since we are accessing it via the proxysql. Assume the proxysql ip is 10.0.0.7 and the database name and the username is _3434edfd35432

rename user '_3434edfd35432'@'10.0.0.3' to '_3434edfd35432'@'10.0.0.7'

Now in order to pass the traffic through the proxysql we must add the corresponding user to the proxy sql user table. The password can be found in the site_config.json file inside the erp.site folder.

insert into mysql_users (username, password, default_hostgroup) values ('_3434edfd35432', 'daKdkmKSF13', 1)

Validate if the user is created properly

select * from mysql_users;

and update the mariadb host ip in the ERPNext node.

bench set-mariadb-host 10.0.0.7

On the other ERPNext node, create a new site called erp.site and install the ERPNext and payments applications.

Then replace the content of the new site_config.json with the configuration of the of the previously created ERPNext site.

Finally install Nginx in the proxy server or in another VPS and configure it with the failover option. Following shows a simple configuration.

upstream erpnext {
        server <IP of the main server (node-1)> fail_timeout=5s max_fails=1;
        server <IP of the backup server (node-2)> backup;
}
server {
        listen 80 default_server;
        server_name _;

        location / {
                proxy_pass http://erpnext;
        }
}

Now you have a Highly Available ERPNext cluster!! Turn off some nodes (both ERPNext and Galera) and play around.

Next Stop: Bringing the Redis database out of the ERPNext application server to a Redis Cluster and deployment the service oriented architecture in production.

If you want help setting up an ERPNext infrastructure, please contact me on Fiverr :)

My Fiverr Gig: https://www.fiverr.com/share/poemBo

References

https://computingforgeeks.com/install-mariadb-galera-cluster-on-ubuntu-with-proxysql/

[embed]Galera Cluster for MySQL In our April 2022 release of Galera Manager, we included the ability to have fully managed clusters on DigitalOcean…galeracluster.com

[embed]ProxySQL ProxySQL is fully compatible with Amazon (AWS) Aurora, Amazon RDS, Oracle MySQL, InnoDB Cluster & Group Replication…proxysql.com