MariaDB Galera Cluster on Ubuntu 20

Rumi,

MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB with support for XtraDB/InnoDB storage engines. It has the following top features.

  • It provides active-active multi-master topology
  • You can read and write to any cluster node
  • It has an automatic node joining
  • Automatic membership control, failed nodes drop from the cluster
  • Has true parallel replication, on row level
  • Direct client connections
sudo apt update && sudo apt -y upgrade
sudo reboot

Setup Hostnames

Here’re the IP plans of my servers-

Configure static hostnames on each of the three servers for DNS reachability.

$ sudo vim /etc/hosts
172.20.5.200 node1.tweenpath.net node1
172.20.5.201 node2.tweenpath.net node2
172.20.5.202 node3.tweenpath.net node3

Install MariaDB on all nodes

Install the latest version of MariaDB with the commands below:

sudo apt update
sudo apt -y install mariadb-server mariadb-client

Start MariaDB

Start MariaDB services on all 3 nodes-

systemctl start mariadb

Check if the service is running by-

systemctl status mariadb

Securing mariadb

Configure MariaDB for first use by running the command below, then run through the configuration appropriately.

$ sudo mysql_secure_installation
In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
... Success!

By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB installation should now be secure.

Thanks for using MariaDB!

Test connection to each of the databases by running the command below:

$ mysql -u root -p

You will be prompted to input a password that you had setup in the previous step.

Configure Galera Cluster

The next step is to configure galera cluster on our three MariaDB hosts. Comment the bind line on the file /etc/mysql/mariadb.conf.d/50-server.cnf which binds MariaDB service to 127.0.0.1

$ sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
#bind-address = 127.0.0.1

Step Configure First Node

Add the following content to the MariaDB configuration file. Remember to modify the hostname at “wsrep_node_address” to the hostname or IP of your first host. Place the below configurable texts right before [options] sections-

binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="vmdbcl2"
wsrep_cluster_address="gcomm://172.20.5.200,172.20.5.201,172.20.5.202"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="172.20.5.200"
wsrep_node_name="node1"

Configure Galera nodes (node2 & node3)

Add the following configuration for node2 and node3 respectively:

On Node2:

binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="vmdbcl2"
wsrep_cluster_address="gcomm://172.20.5.200,172.20.5.201,172.20.5.202"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="172.20.5.201"
wsrep_node_name="node2"

and on Node3

binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so

# Galera Cluster Configuration
wsrep_cluster_name="vmdbcl2"
wsrep_cluster_address="gcomm://172.20.5.200,172.20.5.201,172.20.5.202"

# Galera Synchronization Configuration
wsrep_sst_method=rsync

# Galera Node Configuration
wsrep_node_address="172.20.5.202"
wsrep_node_name="node3"

Now stop mariadb service on all the 3 nodes-

systemctl stop mariadb

Initialize galera cluster and restart MariaDB on Node1 ONLY

sudo galera_new_cluster
sudo systemctl restart mariadb

Start mariadb service on node2 and node3-

systemctl start mariadb

Validate Galera Settings

Login to any of the three nodes as the root user, then confirm that the cluster settings are OK.

$ mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>
Check status

MariaDB [(none)]> show status like 'wsrep_%'; 
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| wsrep_local_state_uuid | c2e29a95-a81f-11eb-a1fc-237e9071c44f |
| wsrep_protocol_version | 10 |
| wsrep_last_committed | 7 |
| 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 | 690 |
| 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_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 | AUTO,AUTO,AUTO |
| 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 | e4af838d-a824-11eb-95d0-72f816b84c68 |
| wsrep_gmcast_segment | 0 |
| wsrep_applier_thread_count | 1 |
| wsrep_cluster_capabilities | |
| wsrep_cluster_conf_id | 3 |
| wsrep_cluster_size | 3 |
| wsrep_cluster_state_uuid | c2e29a95-a81f-11eb-a1fc-237e9071c44f |
| 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.7(ree4f10fc) |
| wsrep_ready | ON |
| wsrep_rollbacker_thread_count | 1 |
| wsrep_thread_count | 2 |
+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+
68 rows in set (0.002 sec)

MariaDB [(none)]>

Confirm that we have a cluster size of 3 under:

wsrep_cluster_size 3

Verify Replication

We can create a test database on any of the nodes and check its availability on the other nodes.

root@node1:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 49
Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database test1;
Query OK, 1 row affected (0.003 sec)

On node2 and node3

root@node2:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 45
Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;

+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test1 |
+--------------------+
4 rows in set (0.001 sec)
root@node3:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 46
Server version: 10.5.9-MariaDB-1:10.5.9+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test1 |
+--------------------+
4 rows in set (0.001 sec)

This confirms that the database created on node1 is replicated across the cluster.

Next, run the following command to check the cluster status:

mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_cluster_size'"

You should get the following output:

+--------------------+-------+
| Variable_name | Value
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+

At this point, your MariaDB cluster is up and running. Now, you will need to test whether the replication is working or not.
Create a Database and Table on the First Node
First, go to the first node and connect to the MariaDB using the following command:

mysql -u root -p

Once you are connected, create a database named classdb:

MariaDB [(none)]> CREATE DATABASE classdb;

Next, switch the database to classdb and create a table named students:

MariaDB [(none)]> USE classdb;
MariaDB [classdb]> CREATE TABLE students (id int, name varchar(20), surname varchar(20));

Next, insert some data into students table:

MariaDB [classdb]> INSERT INTO students VALUES (1,"vyom","patel");
MariaDB [classdb]> INSERT INTO students VALUES (2,"raj","shah");

Now, verify the inserted data with the following command:

MariaDB [schooldb]> SELECT * FROM students;

You should get the following output:

+------+------+---------+
| id | name | surname |
+------+------+---------+
| 1 | vyom | patel |
| 2 | raj | shah |
+------+------+---------+

After this you may need to set up a TCP4 load balancer like haproxy to load balance between these 3 nodes. The load balancer for this configuration is available in this configurtion part-

https://tweenpath.net/setup-haproxy-load-balancer-mariadb-galera-cluster/

Src:
https://computingforgeeks.com/install-mariadb-galera-cluster-on-ubuntu-with-proxysql/
https://cloudinfrastructureservices.co.uk/how-to-setup-mariadb-clustering-on-ubuntu-20-04/

Administrations Configurations (Linux) MySQL

Leave a Reply

Your email address will not be published. Required fields are marked *