MARAIDB High-availability--MHA

Basic knowledge MHA Simple function Introduction

MHA is currently a popular solution for MySQL high availability, MHA can achieve automatic fault detection and failover in a short period of time, typically within 10-30 seconds, and in the replication framework, MHA is a good solution to the data consistency problem during replication, Because there is no need to add additional servers to the existing replication, only one manager node is needed, and one manager can manage multiple sets of replication, which can greatly save the number of servers, in addition, simple installation, no performance loss, And the advantages of not having to modify an existing replication deployment.
MHA also provides the ability to switch between the main library and the current running main library to a new primary library (by upgrading from the library to the primary library) in about 0.5-2 seconds.

MHA Working principle

1. Save the binary log file from the master of the downtime crash;
2. Identify the slave that contain the latest updates;
3. Apply the relay log of the difference to other slave;
4. Apply the binary log file saved from master;
5. Automatically promote a slave as the new master work;
6. Make the other slave connected to the new master for replication.

Composition of the MHA

MHA software consists of two parts, the Manager Toolkit and the Node toolkit
The Manager toolkit mainly includes the following tools:
1.masterha_check_ssh checking SSH configuration status of MHA
2.MASTERHA_CHECK_REPL Check MySQL replication status
3.masterha_manger Start MHA
4.masterha_check_status Detecting current MHA running state
5.masterha_master_monitor Checking Master for downtime
6.masterha_master_switch system failover (automatic or manual)
7.masterha_conf_host adding or removing configured server information

Node Toolkit: These tools are typically triggered by MHA manager scripts, without the need for human action) to master
Here are a few tools to include:
1.save_binary_logs Saving and copying the binary log of master
The 2.apply_diff_relay_logs identifies the difference in the relay log event and applies its differential events to other
The slave
3.filter_mysqlbinlog removal of unnecessary rollback events (MHA no longer uses this tool)
4.purge_relay_logs clearing the trunk log (does not block SQL threads)
Note: In order to minimize the loss of data due to the failure of the main library hardware, the configuration MHA
It is recommended to configure the semi-synchronous replication

Custom extensions:
1.secondary_check_script: Detecting master availability through multiple network routes
2.master_ip_ailover_script: Update the Masterip used by application
3.shutdown_script: Forcing the master node to shut down
4.report_script: Send Report
5.init_conf_load_script: Loading Initial configuration parameters
6.master_ip_online_change_script: Update master node IP address

Configuration file:
Global configuration, providing default configuration for each application
Application configuration: For each master-slave replication cluster

Simulation Experiment Experiment Architecture diagram

Manager	192.168.99.130
Master	192.168.99.131
Slave1	192.168.99.135
Slave2	192.168.99.136

This is illustrated below:

Preparation of the experimental process

Four CENTOS7 hosts were prepared, respectively, as Manager,master,slave1 and Slave2. The database is using maraidb10.2.15

Do basic software Environment cleanup, 4 hosts must be

#关闭selinuxsetenforce 0#清空防火墙规则 iptables -F iptables -X#进行时间同步，由于是做集群实验，所以时间不同步会导致实验失败。ntpdate cn.pool.ntp.org

Do four hosts SSH-based mutual access

# 生成密钥ssh-keygen# 配置ssh访问ssh-copy-id 192.168.99.130scp -pr .ssh 192.168.99.131:/root/scp -pr .ssh 192.168.99.135:/root/scp -pr .ssh 192.168.99.136:/root/

When you are done, verify
SSH 192.168.99.131
SSH 192.168.99.135
SSH 192.168.99.136
...

Configure the Management node

Installing the MHA package requires the support of the Epel source.

Create the corresponding directory

mkdir /etc/mhamkdir /mha/test -pv

Modifying a configuration file

Vim vim/etc/mha/test.cnf# fill in the following fields [server default] #数据库的管理账号和密码user the working directory used by =mhapassword=mha#manager, and if not set, the default is to use/var/ tmp.manager_workdir=/mha/test/#MHA the absolute directory and file name of the manager log file and, if not set, prints directly to standard output and standard error output. When performing interactive failover, MHA The manager ignores the Manager_log setting and directly agrees to the standard output and the standard error output. manager_log=/mha/test/manager.log# Each MHA node, MHA the absolute path of the directory to work with. If the directory does not exist , MHA is automatically created, and if the permissions are insufficient, MHA node terminates unexpectedly, noting that MHA Manager and MHA node do not check the disk space available for this directory, and you need to ensure that you have enough free space. The default remote_workdir is '/var/ TMP '. remote_workdir=/mha/test/# account and password for database backup based on SSH login ssh_user=root# repl_user=slaverepl_password=slave# This parameter declares the interval between MHA Manager pings (ping by executing SQL) master. When three pings fail, MHA Manager considers this MySQL master down, from downtime to detection of downtime, the maximum elapsed time is four times times this parameter , the default value for this parameter is 3 (3 seconds). If MHA Manager has failed multiple connections because of a permissions issue, this does not assume that master deadping_interval=1# specifies the directory of the binary log, if the primary purpose of the parameter is after the master MySQL outage, In order to copy the required Binlog event via SSH. This parameter is required because it is not available to automatically retrieve the binary log directory when MySQL is down. #默认情况下, the value of Master_binlog_dir is "/var/lib/mysql/,/var/log/mysql/" and/var/lib/mysql/is the default MySQL output directory for most MySQL releases. You can set multiple directories, separated by commas. For example (/DATA1,/DATA2,/DATA3) master_binlog_dir=/data/binlog/#节点配置 [server1]hostname=192.168.99.131# declaration can be used as a primary qualification candidate_master=1[server2]hostname= 192.168.99.135candidate_master=1[server3]hostname=192.168.99.136

Management Node Basic configuration complete

Node Configuration Install nodes Package

Installing the MHA node package on Master,slave1,slave2

yum install  mha4mysql-node-0.56-0.el6.noarch.rpm

Master node Configuration

To edit a database configuration file

vim /etc/my.cnf[mysqld]# 数据和二进制日志分离datadir=/data/mysqllog_bin=/data/binlog/mysql-bin#配置唯一idserver_id=1#跳过名称解析，这里必须配置skip_name_resolve=1#关闭清理中继日志relay_log_purge=0

After you finish editing the configuration file, start the database.

systemctl start mariadb

Authorization to manage accounts and backup accounts

#这里先查看二进制日志是为了，之后从数据库可以直接同步主库的账号授权信息。MariaDB [(none)]>show master logs;MariaDB [(none)]> grant replication slave on *.* to [email protected]‘192.168.99.%‘ identified by‘slave‘;MariaDB [(none)]> grant all on *.* to [email protected]‘192.168.99.%‘ identified by ‘mha‘;

It is recommended to turn on semi-synchronous replication, mariadb10.3 can be directly opened without the need to install plugins.

# 安装半同步复制的主从插件MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘; # 启动半同步复制MariaDB [(none)]>  SET GLOBAL rpl_semi_sync_master_enabled=1;MariaDB [(none)]>  SET GLOBAL rpl_semi_sync_slave_enabled=1;#查看状态MariaDB [(none)]> show variables like ‘%semi%‘;

Slave node Configuration

To edit a database configuration file

vim /etc/my.cnf[mysqld]datadir=/data/mysqllog_bin=/data/binlog/mysql-bin#不同节点id号必须不同server_id=2skip_name_resolve=1relay_log_purge=0

Start the database

systemctl start mariadb

Turn on semi-synchronous replication

MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_master SONAME ‘semisync_master.so‘;MariaDB [(none)]> INSTALL PLUGIN rpl_semi_sync_slave SONAME ‘semisync_slave.so‘; MariaDB [(none)]>  SET GLOBAL rpl_semi_sync_master_enabled=1;MariaDB [(none)]>  SET GLOBAL rpl_semi_sync_slave_enabled=1;

Configuring synchronization Information

MariaDB [(none)]> change master to     -> master_host=‘192.168.99.131‘,    -> master_user=‘slave‘,    -> master_password=‘slave‘,        #二进制日志文件和起始点为之前，在master节点查看到的信息，不能写错    -> master_log_file=‘mysql-bin.000001‘,    -> master_log_pos=703;

Start synchronization and view synchronization information

MariaDB [(none)]> start slave;MariaDB [(none)]> show slave status\G

Manage node check status and start MHA

#检查ssh密钥登陆是否正常masterha_check_ssh --conf=/etc/mha/test.cnf #检查备份配置是否正常masterha_check_repl --conf=/etc/mha/test.cnf #启动mha开启监控，由于默认在前台执行，所以可以考虑转到后台或者开启会话再执行masterha_manager --conf=/etc/mha/test.cnf   启动mha

MHA Configuration Complete Note:

1. After master is down, MHA will automatically select the eligible nodes in the standby node to become the new master;
2. The node of the outage needs to be manually added to the cluster after the repair, to be configured as slave server;
3. If the data is updated during the outage, manually synchronize the new master's data and initiate the synchronization, as new from;
4. After the repair is completed, you want to restart the MHA monitor to delete the/mha/test/directory under the complete end of the file, otherwise you can not restart monitoring;
5. Repair complete re-run monitoring command
Masterha_manager--conf=/etc/mha/test.cnf

MHA RPM package is downloaded in Google Code, but the version is a bit old, here I use a package
Official Downloads
Https://code.google.com/archive/p/mysql-master-ha/downloads
Personal sharing, CENTOS7 can also be installed
Https://pan.baidu.com/s/1AGKc7iSZBUAonO9QSqwF-Q

MARAIDB High-availability--MHA