In this article, you will learn how to install and configure a MariaDB cluster with Galera. You can also add HAProxy to balance the load between the MariaDB nodes.
A MariaDB cluster must always be set up with an odd number of nodes. The reason is simple: if there are only two nodes and one of them fails, there is no way to tell which one is corrupt.
At this point, you can set up a cluster with 3 MariaDB nodes.
For this article, you will need to use 4 servers running Ubuntu 22.04:
- kb-mariadb-01 10.20.10.26
- kb-mariadb-02 10.20.10.27
- kb-mariadb-03 10.20.10.29
- kb-haproxy-mariadb 10.20.10.28
Install MYSQL
To configure the cluster, the first thing you need to do is install MYSQL on all nodes (on Ubuntu 22, the command below automatically installs galera 4):
# apt update && apt upgrade
# apt -y install mariadb-serverTo secure the MYSQL installation, you can run the following script. For the option to change the root password, selecting "n" is recommended.
# mysql_secure_installationKeep in mind that all databases must use the same format, and using the InnoDB format is recommended. For example, MyISAM is currently not supported. You can check the default format with:
# SHOW VARIABLES LIKE 'default_storage_engine';If any database is not compatible, you can use the following commands:
# MariaDB [(none)]> use database_name;
# MariaDB [(none)]> alter table table_name engine = InnoDB;Configure the nodes
Now you need to configure the cluster. To do this, modify the /etc/mysql/mariadb.conf.d/60-galera.cnf file on all nodes:
[galera]
wsrep_on = ON # Enables write-set replication.
wsrep_provider = /usr/lib/galera/libgalera_smm.so #Specifies the location of the wsrep library.
wsrep_cluster_name = "Galera Cluster" #Name for the cluster.
wsrep_cluster_address = "gcomm://10.20.10.26,10.20.10.27,10.20.10.29" #Defines the node IPS.
binlog_format = row #Format of the binlog, the file used for replication.
default_storage_engine = InnoDB #This engine or XtraDB is always recommended; the others will cause problems.
innodb_autoinc_lock_mode = 2 #Controls how auto-increment values in InnoDB tables are locked during inserts.
innodb_force_primary_key = 1 #Requires a primary key on all tables.
innodb_doublewrite = 1 #Double write buffer.
# Allow the server to accept connections on all interfaces.
bind-address = 0.0.0.0 # Make MariaDB listen on all ports
# Additional settings.
wsrep_slave_threads = 4 #Sets the number of threads used to process write sets from other nodes. More threads can speed up replication.
innodb_flush_log_at_trx_commit = 0 #Ensures that the InnoDB log buffer is written to the file once per second, rather than at each transaction commit.
wsrep_node_name = SegundoNodo #Sets a name for an individual node so that you can easily identify each node when viewing the logs.
wsrep_node_address = "10.20.10.27" #Sets the IP address for an individual node.
# The following line will save error messages to error.log.
log_error = /var/log/mysql/error.logYou will need to modify:
- The IPs in wsrep_cluster_address.
- The name of each node in wsrep_node_name.
- The IP of the node used in wsrep_node_address
Now we will stop the MariaDB service.
# systemctl stop mariadbStart the cluster
The configuration is now ready, and you can start the MariaDB cluster. To do this, run the following on kb-mariadb-01:
# galera_new_clusterNow restart MariaDB on the other nodes.
# systemctl start mariadbYou can check the status by running:
# mysql -u root -p -e "SHOW STATUS LIKE 'wsrep%';"
Enter password:
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| wsrep_local_state_uuid | a9d57bc3-f737-11ee-bbf2-3f397ed75daa |
| wsrep_protocol_version | 10 |
| wsrep_last_committed | 3 |
| wsrep_replicated | 0 |
| wsrep_replicated_bytes | 0 |
| wsrep_repl_keys | 0 |
| wsrep_repl_keys_bytes | 0 |
| wsrep_repl_data_bytes | 0 |
| wsrep_repl_other_bytes | 0 |
| wsrep_received | 7 |
| wsrep_received_bytes | 760 |
| wsrep_local_commits | 0 |
| wsrep_local_cert_failures | 0 |
| wsrep_local_replays | 0 |
| wsrep_local_send_queue | 0 |
| wsrep_local_send_queue_max | 1 |
| wsrep_local_send_queue_min | 0 |
| wsrep_local_send_queue_avg | 0 |
| wsrep_local_recv_queue | 0 |
| wsrep_local_recv_queue_max | 2 |
| wsrep_local_recv_queue_min | 0 |
| wsrep_local_recv_queue_avg | 0.142857 |
| wsrep_local_cached_downto | 1 |
| wsrep_flow_control_paused_ns | 0 |
| wsrep_flow_control_paused | 0 |
| wsrep_flow_control_sent | 0 |
| wsrep_flow_control_recv | 0 |
| wsrep_flow_control_active | false |
| wsrep_flow_control_requested | false |
| wsrep_cert_deps_distance | 0 |
| wsrep_apply_oooe | 0 |
| wsrep_apply_oool | 0 |
| wsrep_apply_window | 0 |
| wsrep_apply_waits | 0 |
| wsrep_commit_oooe | 0 |
| wsrep_commit_oool | 0 |
| wsrep_commit_window | 0 |
| wsrep_local_state | 4 |
| wsrep_local_state_comment | Synced |
| wsrep_cert_index_size | 0 |
| wsrep_causal_reads | 0 |
| wsrep_cert_interval | 0 |
| wsrep_open_transactions | 0 |
| wsrep_open_connections | 0 |
| wsrep_incoming_addresses | 10.20.10.26:0,10.20.10.27:0,10.20.10.29:0 |
| wsrep_cluster_weight | 3 |
| wsrep_desync_count | 0 |
| wsrep_evs_delayed | |
| wsrep_evs_evict_list | |
| wsrep_evs_repl_latency | 0/0/0/0/0 |
| wsrep_evs_state | OPERATIONAL |
| wsrep_gcomm_uuid | a9d45264-f737-11ee-85cb-ea88e81c5deb |
| wsrep_gmcast_segment | 0 |
| wsrep_applier_thread_count | 4 |
| wsrep_cluster_capabilities | |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | a9d57bc3-f737-11ee-bbf2-3f397ed75daa |
| wsrep_cluster_status | Primary |
| wsrep_connected | ON |
| wsrep_local_bf_aborts | 0 |
| wsrep_local_index | 2 |
| wsrep_provider_capabilities | :MULTI_MASTER:CERTIFICATION:PARALLEL_APPLYING:TRX_REPLAY:ISOLATION:PAUSE:CAUSAL_READS:INCREMENTAL_WRITESET:UNORDERED:PREORDERED:STREAMING:NBO: |
| wsrep_provider_name | Galera |
| wsrep_provider_vendor | Codership Oy <info@codership.com> |
| wsrep_provider_version | 4.9(rcece3ba2) |
| wsrep_ready | ON |
| wsrep_rollbacker_thread_count | 1 |
| wsrep_thread_count | 5 |
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+From mysql, use the command show status like 'wsrep_cluster_size'; to check directly that all 3 nodes are present:
MariaDB [(none)]> show status like 'wsrep_cluster_size';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.001 sec)Install and configure HAProxy
HAProxy is software that allows you to balance the load between multiple servers. It is commonly used to load balance web services, but it can load balance any service that runs over the TCP protocol. In this case, it will balance the load between the 3 MariaDB nodes. To install HAProxy, run:
# apt update
# apt install haproxyNow configure HAProxy by adding the following code to nano/etc/haproxy/haproxy.cfg.
frontend Haproxy
bind *:3306
mode tcp
default_backend galera_cluster
backend galera_cluster
balance roundrobin
mode tcp
option tcpka
option mysql-check user haproxy
server node1 10.20.10.26:3306 check weight 1
server node2 10.20.10.27:3306 check weight 1
server node3 10.20.10.29:3306 check weight 1
frontend stats
bind *:8083
mode http
stats enable
stats refresh 10s
stats uri /stats
stats realm HAProxy\ Statistics
stats auth haproxy:TestKBCreate a user so that HAProxy can check the 2 nodes, on one of the 2 nodes, for example haproxy:
MariaDB [(none)]> CREATE USER haproxy@10.20.10.28;Now go to your browser and open http://IP_Publica_HAProxy:8083/stats. You will be prompted for a username and password; enter haproxy and TestKB.
Your MariaDB cluster is now running with load balancing.
Remember, if you have questions about this or any other matter related to your servers at Clouding, feel free to email soporte@clouding.io We're here for whatever you need. Get in touch!