High MySQL Availability Based on MHA and Galera Cluster

High MySQL Availability Based on MHA and Galera Cluster

MHA: Master HA is an open-source MySQL high-availability program. It provides the automatic master failover function for the MySQL Master-slave replication architecture. When MHA detects a master node failure, it will escalate the slave node with the latest data to the new master node. During this period, MHA will obtain additional information on the slave node to avoid consistency issues. MHA also provides the master node online switching function, that is, switching master/slave nodes as needed

MHA service has two roles: Management Node (MHA Manager) and data Node (MHA Node)

MHA Manager: it is usually deployed on an independent server to manage multiple master/slave clusters. Each master/slave cluster is called an application

MHA Node: runs on each MySQL Server (master/slave/manager). It accelerates failover by monitoring scripts with the logs parsing and cleanup functions.

Galera Cluster

Global replication is implemented based on wsrep protocol. Read and Write operations can be performed on any node without any delay and data loss will not occur. When a server goes down, the backup server automatically takes over.

1. High Availability of MySQL based on Galera Cluster

Prerequisites: CentOS 7

Node 1	IP: 172.18.42.200
Node 2	IP: 172.18.42.201
Node 3	IP: 172.18.42.202

1. Deploy Node 1
(1) install the Galera Service
[Root @ node0 ~] # Yum install MariaDB-Galera-server-y

(2) edit the configuration file
[Root @ node0 ~] # Rpm-ql galera # view Related Files
/Usr/lib64/galera/libgalera_smm.so
[Root @ node0 ~] # Vim/etc/my. cnf. d/server. cnf
[Galera]
# Mandatory settings # forcible settings
Wsrep_provider =/usr/lib64/galera/libgalera_smm.so # the provider of wsrep, which is generally a plug-in and may be different from the installed versions.
Wsrep_cluster_address = "gcomm: // 172.18.42.200, 172.18.42.201, 172.18.42.202" # specify nodes of the Galera-Cluster
Binlog_format = row # binary log format. The default format is row. We do not recommend that you change the format.
Default_storage_engine = InnoDB # specify the engine used
Innodb_autoinc_lock_mode = 2 # Lock format
Bind-address = 0.0.0.0 # address that wsrep listens to at work
Wsrep_cluster_name = 'mycluster' # specify the name of the Galera Cluster

(3) initialize the cluster at the first startup.
[Root @ node0 ~] #/Etc/rc. d/init. d/mysql start -- wsrep-new-cluster # start the mysql service on a node

2. Deploy Node 2
(1) install the MariaDB-Galera-server Service
[Root @ node0 ~] # Yum install MariaDB-Galera-server-y

(2) start the service
[Root @ node2 ~] # Service mysql start
Starting MySQL... SST in progress, setting sleep higher. SUCCESS!

3. Deployment Node 3
(1) install the MariaDB-Galera-server Service
[Root @ node0 ~] # Yum install MariaDB-Galera-server-y

(2) start the service
[Root @ node2 ~] # Service mysql start
Starting MySQL... SST in progress, setting sleep higher. SUCCESS!

4. Connect the three nodes to the mysql service, and then create a database to check whether the other two nodes are copied.
[Root @ node0 ~] # Mysql # Node 1 connect to the mysql Service
MariaDB [(none)]> create database MaGeRepo; # create the database "MaGeRepo" on node 1 and view
MariaDB [(none)]> show databases;
+ -------------------- +
| Database
+ -------------------- +
| Information_schema
| MaGeRepo
| Mysql
| Performance_schema
| Test
+ -------------------- +
[Root @ node1 ~] # Mysql # connect Node 2 to the mysql Service
MariaDB [(none)]> show databases; # view the database
+ -------------------- +
| Database
+ -------------------- +
| Information_schema
| MaGeRepo
| Mysql
| Performance_schema
| Test
+ -------------------- +
[Root @ node2 ~] # Mysql # connect Node 3 to the mysql Service
MariaDB [(none)]> show databases; # view the database
+ -------------------- +
| Database
+ -------------------- +
| Information_schema
| MaGeRepo
| Mysql
| Performance_schema
| Test
+ -------------------- +
# Database Synchronization

5. Shut down Node 2 and create the table "MaGe" in the Database "MaGeRepo".
MariaDB [MaGeRepo]> create table MaGe (ID int unsigned auto_increment not null primary key, Name char (10); # create the table "MaGe" on node 1"
MariaDB [MaGeRepo]> insert into MaGe (Name) values ('mage'), ('lweim '); # insert data "Lweim" and "MaGe" in node 1"
MariaDB [MaGeRepo]> insert into MaGe (Name) values ('wtc '), ('wzx'); # insert data "Wtc" and "Wzx" in node 3"

6. Enable mysql server on node 2 and view its database
[Root @ node1 ~] # Service mysql start # start Node 2
Starting MySQL... SST in progress, setting sleep higher. SUCCESS!
[Root @ node1 ~] # Mysql # connect to the mysql Service
MariaDB [MaGeRepo]> select * from MaGe; # View data in the table
+ ---- + ------- +
| ID | Name
+ ---- + ------- +
| 1 | MaGe
| 3 | Lweim
| 4 | Wtc
| 6 | Wzx
+ ---- + ------- +

It can be seen that after a server goes down, even if the data changes, the data can be synchronized after the server goes online again. However, it should be noted that the ID does not increase in the order of automatic growth, the solution is as follows:
A: set a global allocation ID generator to solve the inconsistent ID sequence during data insertion.
B: manually specify the ID to Prevent Automatic Generation.

For more details, please continue to read the highlights on the next page:

1 2 Next Page