Home / How To / How to set MariaDB Galera Multi-Master Synchronous Replication with Debian 10

How to set MariaDB Galera Multi-Master Synchronous Replication with Debian 10



MariaDB offers two different high availability (HA) and cluster solutions. The first is the standard MariaDB master / slave replication that can be configured in different topologies mainly for load balancing, HA and backup purposes. The other is MariaDB Galera, a multi-master synchronous cluster solution. The main functions are as follows:

  • Multi-master: All nodes in a Galera cluster can perform both read and write operations and offer better scalability.
  • Nodes can be automatically connected to a cluster and ejected in the event of failure. [19659003] Galera replication is synchronous, which means that changes to one node are guaranteed to apply to the other nodes. In theory, this ensures that no data is lost when a node fails.

This guide guides you through the installation of MariaDB and its configuration in a Galera cluster. We will use three Debian 1

0 nodes for demonstration, although any number (≥3) nodes can be used. Setting two nodes in a Galera cluster is technically possible but does not give fault tolerance as a failed node will cause the other node to stop.

Requirements

  • Three or more Debian 10 instances.
  • Access to root user or any user with sudo privileges.
  • $ EDITOR environment variable should be set.

NOTE: Galera clusters can work over WAN or LAN. If your nodes share a private network, use private IP addresses where applicable. Otherwise, WAN addresses should be used.

If you are using a sudo user, open and use a root shell for the length of this installation with:

  sudo -s 

Step 1: Install MariaDB

This step should be run on all nodes.

Use the following commands to install MariaDB, the Galera library and Rsync. The latter is used by Galera.

  apt update
apt install -y mariadb-server mariadb-client galera-3 rsync

Make sure MariaDB service is enabled:

  systemctl enable mariadb.service

Secure your MariaDB instances with mysql_secure_installation script:

  mysql_secure_installation

Answer the questions that appear below and make sure you select a strong password for the MySQL root user.

  Enter the current root password (enter none): Press 
Setting root password? [Y/n] y
New password: your_password
Enter new password: your_password
Delete anonymous users? [Y/n] y
Remove root login remotely? [Y/n] y
Delete the test database and access it? [Y/n] y
Reload privilege tables now? [Y/n] y
Clear! If you have completed all of the above steps, your MariaDB
installation should now be secure. 

Step 2: Configure MariaDB

This step should be performed on all nodes.

Stop MariaDB service on all nodes:

  systemctl stop mariadb.service

By default, the MariaDB daemon only listens to connections on localhost. For the cluster to function, this should be changed to an externally available address. To do so, edit the option file /etc/mysql/mariadb.conf.d/50-server.cnf :

  $ EDITOR /etc/mysql/mariadb.conf.d/50-server. CNF

Find the following line:

  bind-address = 127.0.0.1

If you are using a private network for the cluster and do not want to expose MariaDB to other networks (ie WAN), enter the local IPv4 address for each node. Otherwise, use 0.0.0.0 which instructs MariaDB to listen to all interfaces. For example:

  bind-address = 0.0.0.0

Save the change and exit your text editor.

We will now configure cluster-related options. Create a new optional file:

  $ EDITOR /etc/mysql/mariadb.conf.d/99-cluster.cnf

Enter the following reasonable configuration in the file and replace the IP addresses. It should be identical on all nodes.

  [galera]

wsrep_on = on wsrep_provider = /lib/galera/libgalera_smm.so wsrep_cluster_address = gcomm: //192.0.2.1,192.0.2.2,192.0.2.3 wsrep_cluster_name = galera_cluster_0 default_storage_engine = InnoDB innodb_autoinc_lock_mode = 2 innodb_doublewrite = 1 binlog_format = ROW
  • wsrep_on = on enables write set replication, the underlying functionality used by Galera.
  • wsrep_provider specifies the path to the gallery library. It is provided by the package galera-3 on /lib/galera/libgalera_smm.so on Debian 10.
  • wsrep_cluster_address should contain at least one other address cluster member. To recommend all members of the cluster is recommended. No special order is necessary.
  • wsrep_cluster_name should be unique to the cluster and should be identical on all nodes in the same gala cluster.
  • The remaining options are required for Galera to function properly and should not be changed.

Step 3: Start the cluster

Make sure MariaDB is stopped / inactive on all nodes before continuing:

  systemctl status mariadb.service

To start the cluster, a node must first create it. On Debian 10, this can be done with the galera_new_cluster script. The script should only be run on one node and only once to initiate the cluster.

  galera_new_cluster

This will start MariaDB on the current node. Make sure it is run with:

  systemctl status mariadb.service

Then start MariaDB on the other nodes with:

  systemctl start mariadb.service

The cluster should now be in operation.

Step 4: Test

To ensure that the cluster works as intended, select any node and log in to MariaDB:

  mysql -u root -p

Release the following statement to create a database:

> CREATE DATABASE test0;
>  q

Then check for this new database on all other nodes :

  mysql -u root -p -e "SHOW DATABASES;"

The above command should return a list containing test0 :

  + -------------------- +
| Database |
+ -------------------- +
| information_schema |
| mysql |
| performance_schema |
| test0 |
+ -------------------- +

You may want to test more carefully by writing to the cluster from each node. When you are satisfied with testing, clean up all unnecessary databases from the cluster. Any node can be used.

  mysql -u root -p -e "DROP DATABASE test0;" 

Step 5: Troubleshooting Tips

Use the following question to view information about the current state of the node / cluster:

  mysql -u root -p -e "SELECT * FROM information_schema.global_status WHERE variable name IN (& # 39; WSREP_CLUSTER_STATUS & # 39 ;, & # 39; WSREP_LOCAL_STATE_COMMENT & # 39 ;, & # 39; WSREP_CLUSTER_SIZE & # 39 ;, & # 39; WSREP_SV & # 39;

A healthy cluster of three nodes should return the following:

  + --------------------------- + ---- - ----------- +
| VARIABLE_NAME | VARIABLE_VALUE |
+ --------------------------- + ---------------- +
| WSREP_CLUSTER_SIZE | 3 |
| WSREP_CLUSTER_STATUS | Primary |
| WSREP_EVS_DELAYED | |
| WSREP_EVS_REPL_LATENCY | 0/0/0/0/0 |
| WSREP_LOCAL_STATE_COMMENT | Synchronized |
| WSREP_READY | ON |
+ --------------------------- + ---------------- +
  • WSREP_CLUSTER_SIZE represents the current number of nodes in the cluster component.
  • WSREP_CLUSTER_STATUS represents the state of the cluster component and not the cluster as a whole.
  • [19459017VSSRE_SDREVS] shows a list of delayed nodes. An empty value is expected from healthy clusters.
  • WSREP_EVS_REPL_LATENCY shows replication latency in the format min / avg / max / stddev / sampler . The values ​​are displayed in seconds. Very high latencies can lead to degraded performance.
  • WSREP_LOCAL_STATE_COMMENT shows the current node state.
  • WSREP_READY indicates whether the node can accept questions.

When a node in a 3-node cluster loses the connection cluster is divided into a primary component consisting of 2 nodes and a non-primary component. The primary component is not affected by the power failure and resumes normal operation. From the perspective of the non-primary component, the query shown above would return the following:

  + --------------------------- + - ----------------------------------------------- --- ----------------------------------------------- --- -------------------------- +
| VARIABLE_NAME | VARIABLE_VALUE |
+ --------------------------- + --------------------------- -------------------------------------------------- -------------------------------------------------- ------- +
| WSREP_CLUSTER_SIZE | 1 |
| WSREP_CLUSTER_STATUS | non-primary |
| WSREP_EVS_DELAYED | 6b7864f2-fe7d-11e9-84ab-93e58c0d2907: tcp: //192.0.2.1: 4567: 3, a421be89-fe7d-11e9-a91e-7e62f7562e58: tcp: //192.0.2.3: 4567: 2 |
| WSREP_EVS_REPL_LATENCY | 0/0/0/0/0 |
| WSREP_LOCAL_STATE_COMMENT | Initiated |
| WSREP_READY | AV |
+ --------------------------- + --------------------------- -------------------------------------------------- -------------------------------------------------- ------- +

Notice the value WSREP_EVS_DELAYED indicating connection problems to the other nodes.

On primary component nodes, the same question returns:

  + ---------- ----------------- + -------- ------------------------ -------------------------- ------ +
| VARIABLE_NAME | VARIABLE_VALUE |
+ --------------------------- + --------------------------- ------------------------------------------- +
| WSREP_CLUSTER_SIZE | 2 |
| WSREP_CLUSTER_STATUS | Primary |
| WSREP_EVS_DELAYED | a2217526-fe7d-11e9-8692-1f2f0cdb403d: tcp: //192.0.2.2: 4567: 2 |
| WSREP_EVS_REPL_LATENCY | 0/0/0/0/0 |
| WSREP_LOCAL_STATE_COMMENT | Synchronized |
| WSREP_READY | ON |
+ --------------------------- + --------------------------- ------------------------------------------- +

No manual intervention is required to recover from a single node failure. When the failed node reconnects to the cluster, it automatically syncs with the cluster.

More information

For advanced configuration options, see Galera Cluster System Variables.


Source link