Database High Availability can be done in two modes. One is Primary-Secondary configuration which allows one DB server to act as Primary and perform Read-Write operations. The data is replicated to Secondary nodes which can perform Read-Only operations. Another mode is Multi-Master configuration which allows Multiple DB Servers to act as Primary and all of them can be used to perform Read-Write operations.
Galera is an independent solution which provides a Multi-Master system for MySQL and MariaDB. The solution provides synchronous clustering capability among multiple database servers with data consistency guarantees.
MariaDB with Galera can be deployed on a single machine, but in order to achieve database high-availability and avoid split brain issues, it is recommended to have minimum of 3 nodes (odd numbered) deployed in different data centers. All the nodes should have same specs as the performance of cluster is dependent on the lowest spec node.
MariaDB Installation
The following deployment is based on Ubuntu 20.04 with MariaDB 10.6.
Open https://mariadb.org/download, select MariaDB Repositories tab, choose OS Distribution, MariaDB version and closest mirror. The site will provide you with commands to install appropriate MariDB repository.
$ sudo apt-get install software-properties-common dirmngr apt-transport-https $ sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc' $ sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el,s390x] \ https://mirror.23m.com/mariadb/repo/10.6/ubuntu focal main'
Update the repo cache and Install the MariaDB server package.
$ sudo apt update $ sudo apt install mariadb-server
Cluster Communication Ports
The MariaDB Galera cluster requires following ports for communication among the cluster members.
Standard MySQL Port: 3306
For MySQL clients and State Snapshot Transfers that use the mysqldump
Galera Replication Port: 4567
For Galera Replication Traffic, uses both UDP and TCP transport
IST Port: 4568
For Incremental State Transfers.
SST Port: 4444
For all State Snapshot Transfer methods other than mysqldump.
MariaDB Galera Cluster Configuration
Edit the configuration file /etc/mysql/mariadb.conf.d/60-galera.cnf and configure cluster configuration parameters as follows. The wsrep_cluster_address is URL which contains comma separated list of all cluster nodes. The node_address and node_name should be configured with the address and name of the node being configured.
[galera] bind-address=0.0.0.0 binlog_format=ROW default-storage-engine=InnoDB innodb_autoinc_lock_mode=2 # Galera Provider Configuration wsrep_on=ON # Path to the Galera library wsrep_provider=/usr/lib/galera/libgalera_smm.so # Galera Cluster Configuration wsrep_cluster_name="Radius Accounting_Cluster" wsrep_cluster_address="gcomm://172.16.16.10,172.16.17.10,172.16.18.10" # Galera Synchronization Configuration wsrep_sst_method=rsync # Galera Node Configuration wsrep_node_address="172.16.16.10" wsrep_node_name="clusterdb1"
MariaDB Performance Tuning
Increase the numbers of connections. Open a separate port for administrative purpose and monitoring in case the primary connections get filled/locked.
[galera] bind-address=0.0.0.0 max_connections = 2400 # configure an extra port for administrative connections extra_port = 33306 extra_max_connections = 10
Change following parameters for handling high connections load.
# Increase IO as per hardware capability innodb_io_capacity = 2400 # Pool size 2/3 of System Memory innodb_buffer_pool_size = 10G # Divide memory pool into subpools, each atleast 1 Gig in size. innodb_buffer_pool_instances = 10 innodb_read_io_threads=4 innodb_write_io_threads=8 innodb_log_buffer_size = 256M innodb_log_file_size = 1G innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT_NO_FSYNC innodb_autoinc_lock_mode=2 # Use a moderately large Gcache size. wsrep_provider_options="gcache.size=10G; gcache.page_size=10G;" # Use A Thread Pool thread_handling = pool-of-threads thread_stack = 192K thread_cache_size = 4 thread_pool_size = 8 # Number of threads per core. thread_pool_oversubscribe = 3
Galera Cluster Startup (Bootstrap)
To start the MariaDB galera cluster first time make sure that MariaDB process on all nodes is in stopped state. Then on first node start the cluster using galera_new_cluster command. On all other nodes star the MariaDB process normally.
# On Cluster Server 1 $ sudo galera_new_cluster # On Cluster Server 2 $ sudo systemctl start mariadb # On Cluster Server 3 $ sudo systemctl start mariadb
Cluster Status
To verify running cluster status and to make sure everything is working as expected following commands can be used to get cluster status.
Verify cluster size on all nodes. It should be “3” in our our case.
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | wsrep_cluster_size | 3 | +--------------------+-------+ 1 row in set (0.002 sec)
Verify all nodes are in Primary status.
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'wsrep_cluster_status'; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | wsrep_cluster_status | Primary | +----------------------+---------+ 1 row in set (0.003 sec)
Verify all nodes report same status UUID.
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'wsrep_cluster_state_uuid'; +--------------------------+--------------------------------------+ | Variable_name | Value | +--------------------------+--------------------------------------+ | wsrep_cluster_state_uuid | e79bc394-8853-11ec-bef2-6dd88325d34c | +--------------------------+--------------------------------------+ 1 row in set (0.003 sec)
References
https://galeracluster.com/
https://mariadb.com/kb/en/configuring-mariadb-galera-cluster/
https://www.digitalocean.com/community/tutorials/how-to-configure-a-galera-cluster-with-mariadb-on-ubuntu-18-04-servers
https://thoughtsimproved.wordpress.com/2020/01/07/tuning-a-mariadb-galera-cluster-for-performance/
https://severalnines.com/database-blog/improve-performance-galera-cluster-mysql-or-mariadb
https://mariadb.com/kb/en/mariadb-galera-cluster-known-limitations/