MySQL multi-master, multi-active Galera cluster deployment and use

MySQL multi-master, multi-active Galera cluster deployment and use

Galera is a tool that allows MySQL to synchronize and replicate data. It enables real dual-master and multi-master data. The client connects to different MySQL instances for read and write operations, just like operating on the same instance, the data between multiple MySQL databases is completely consistent. Galera improves the overall read throughput while ensuring data consistency. Therefore, Galera is better than MySQL's built-in master-slave mode.

Galera does not have the sharding function. If you want to use Galera and table sharding, you can use it with Cobar. If you use Galara in Cobar, you can obtain higher reliability and data consistency, avoid data inconsistency caused by some Cobar switchover and other non-switchover issues.

Step 1: download and install

When using galera, you also need a Galera library, officially http://galeracluster.com/downloads.
The MySQL/MariaDB version required by Galera has an additional wsrep patch, so normal versions cannot be used directly. MySQL versions can be downloaded from Galera official website. Currently, MySQL versions 5.5 and 5.6 are available.
MariaDB version can be downloaded from MariaDB official website to https://downloads.mariadb.org/mariadb-galera/, MariaDB 10.0 corresponds to MySQL 5.6.

The dependent libraries that need to be installed before the source code compilation of galera include boost, scons, check, and openssl. you can install them as needed. Galera uses scons for installation. Therefore, unlike conventional commands, you only need to execute a script.

$ Scripts/build. sh

After compilation, generate the libgalera_smm.so file in the current directory.

Step 2: Configure

Galera only needs to add several lines of wsrep related content to the MySQL configuration file my. cnf.

[Mysqld]
# Configure the path of the previously compiled libgalera_smm.so File
Wsrep_provider =/home/lyw/c2/galera-3-25.3.12/lib1_a_smm.so
 
# Configure the ip Address: port of each instance in the Cluster
Wsrep_cluster_address = 'gcomm: // 192.168.1.8: 24011,192.168 .1.8: 24021,192.168 .1.8: 24031'
 
# Configure your own ip: port. Each configuration is different.
Wsrep_node_address = '192. 168.1.8: 100'

Other configurations are configured according to your own situation. I have configured three, my11.cnf, my21.cnf, and my31.cnf.

Step 3: Initialize

Just like MySQL, execute the mysql_install_db script for each database:

$ Scripts/mysql_install_db -- defaults-file = etc/my11.cnf
$ Scripts/mysql_install_db -- defaults-file = etc/my21.cnf
$ Scripts/mysql_install_db -- defaults-file = etc/my31.cnf

Step 4: Start

For the preceding three MySQL instances, we need to start the first MySQL instance and set the -- wsrep_cluster_address = gcomm: // parameter:

$ Bin/mysqld_safe -- defaults-file = etc/my11.cnf -- wsrep_cluster_address = gcomm ://&

After the first server is successfully started, we will start the next two servers. In this case, the above parameters are not required. In fact, the values in the configuration file are used:

$ Bin/mysqld_safe -- defaults-file = etc/my21.cnf &
$ Bin/mysqld_safe -- defaults-file = etc/my31.cnf &

In this way, the entire cluster is started. If a process crashes and needs to be restarted later, you do not need to add the -- wsrep_cluster_address parameter.

Step 5: Test

After the startup, we need to test whether it is successful. I am using the mysql client here. You can use your favorite client tool. Note that the connection port here is the MySQL port, not the Galera port above.
$ Bin/mysql-uroot-h127.0.0.1-P14011
MariaDB [(none)]> create database lyw;

$ Bin/mysql-uroot-h127.0.0.1-P14021
MariaDB [(none)]> show databases like 'lyw ';
+ ---------------- +
| Database (lyw) |
+ ---------------- +
| Lyw |
+ ---------------- +
1 row in set (0.00 sec)

$ Bin/mysql-uroot-h127.0.0.1-P14031
MariaDB [(none)]> show databases like 'lyw ';
+ ---------------- +
| Database (lyw) |
+ ---------------- +
| Lyw |
+ ---------------- +
1 row in set (0.00 sec)

It can be seen that an operation is performed in one library, and the same modification is made in other libraries. Galera is started successfully.
We can also check the wsrep-related parameters to determine whether the startup is successful:

MariaDB [(none)]> show variables like 'wsrep _ on ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Wsrep_on | ON |
+ --------------- + ------- +
 
MariaDB [(none)]> show status like 'wsrep _ connected ';
+ ----------------- + ------- +
| Variable_name | Value |
+ ----------------- + ------- +
| Wsrep_connected | ON |
+ ----------------- + ------- +
 
MariaDB [(none)]> show status like 'wsrep _ ready ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Wsrep_ready | ON |
+ --------------- + ------- +

If the value of wsrep_on is ON, the startup is successful.
If the value of wsrep_connected is ON, the connection is to the cluster.
If the value of wsrep_ready is ON, the SQL request is ready. This value is the most critical.
Parameters of wsrep headers are related to galera, and other parameters are gradually studied during use.

This article permanently updates the link address: