MySQL's high-availability implementation: MySQL series 14

MySQL high can be implemented in three ways: multi-master mode (Multi master MySQL), MHA (master higher availability) and Galera Cluster:wresp

First, MHA

? Monitoring the main node, can realize automatic failover to other slave nodes, by upgrading a Slave node to the new master node, based on master-slave replication implementation, but also needs the client to implement, the current MHA mainly support a master multi-slave architecture, to build MHA, requires a replication cluster must have at least three database servers, One master two from, that is, one serves as master, one serves as the standby master, and the other acts as a slave library.

1. MHA Working principle

• Save binary log events from the Master of Downtime crashes (Binlog events)
• Identify slave that contain the latest updates
• Apply the difference of the trunk log (relay log) to the other slave
• Apply binary log events saved from master (Binlog events)
• Raise a Slave for the new master
• Make other slave connect to the new master for replication

2. MHA Software

The MHA software consists of two parts, the Manager Toolkit and the node Toolkit;

The Manager toolkit mainly includes the following tools:

• Masterha_check_ssh checking SSH configuration status of MHA
• MASTERHA_CHECK_REPL Check MySQL replication status
• Masterha_manger Start MHA
• Masterha_check_status Detecting current MHA running state
• Masterha_master_monitor Checking Master for downtime
• Masterha_master_switch failover (automatic or manual)
• Masterha_conf_host adding or removing configured server information

Node Toolkit: These tools are typically triggered by MHA manager scripts, eliminating the need for human action

• Save_binary_logs Saving and copying the binary log of master
• Apply_diff_relay_logs identify the difference between the relay log events and apply their differential events to other slave
• Filter_mysqlbinlog Removing unnecessary rollback events (MHA no longer uses this tool)
• Purge_relay_logs clearing the trunk log (does not block SQL threads)

Tip: In order to minimize the loss of data due to the failure of the main library hardware, it is recommended to configure the MHA as a semi-synchronous copy of MySQL 5.5.

Custom extensions:

• Secondary_check_script: Detecting master availability through multiple network routes
• Master_ip_ailover_script: Update the Masterip used by application
• Shutdown_script: Forcing the master node to shut down
• Report_script: Send Report
• Init_conf_load_script: Loading Initial configuration parameters
• Master_ip_online_change_script: Update master node IP address

: Https://code.google.com/archive/p/mysql-master-ha/downloads

3, the realization of MHA

Environment: Based on key authentication, reference: https://www.cnblogs.com/L-dongf/p/9058265.html , time must be synchronized, execution:ntpdate cn.pool.ntp.org

1) Manager node

[[email protected] ~]# yum install mha4mysql-manager-0.56-0.el6.noarch.rpm Mha4mysql-node-0.56-0.el6.noarch.rpm-y #安装mha软件 [[email protected] ~]# SCP mha4mysql-node-0.56-0.el6.noarch.rpm 192.168.0.7:[[email protected] ~]# SCP mha4mysql-node-0.56-0.el6.noarch.rpm 192.168.0.8:[[email protected] ~]# SCP mha4mysql-node-0.56-0.el6.noarch.rpm 192.168.0.9:[[email protected] ~]# mkdir/etc/mha/[[email protected ] ~]# vim/etc/mha/cluster1.cnf[server default]user=mhauserpassword=mhapassmanager_workdir=/data/mastermha/ Cluster1/manager_log=/data/mastermha/cluster1/manager.logremote_workdir=/data/mastermha/cluster1/ssh_user= Rootrepl_user=repluserrepl_password=replpassping_interval=1 #每秒检测一次 [Server1]hostname=192.168.0.7candidate_ Master=1 #可以成为主节点 [Server2]hostname=192.168.0.8candidate_master=1 #可以成为主节点 [Server3]hostname=192.168.0.9[[email  protected] ~]# masterha_check_ssh--conf=/etc/mha/cluster1.cnf #检查ssh秘钥环境 All SSH connection tests passed succes Sfully. [[EMail protected] ~]# masterha_check_repl--conf=/etc/mha/cluster1.cnf #检查MySQL状态 MySQL Replication Health is OK. [[email protected] ~]# yum install screen-y[[email protected] ~]# screen-s MHA #mha是工作在前台的进程, can not be detected by the terminal real-time [[Emai L protected] ~]# Masterha_manager--conf=/etc/mha/cluster1.cnf #开始监测

2) Master Node

[[email protected] ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y  #安装node包[[email protected] ~]# vim /etc/my.cnf[mysqld]server_id=1log_binbinlog_format=rowskip_name_resolve[[email protected] ~]# systemctl start mariadbMariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO [email protected]'192.168.0.%' IDENTIFIED BY 'replpass';  #创建主从复制账号，可能切换为主的节点都要创建此账号MariaDB [(none)]> SHOW MASTER LOGS;+--------------------+-----------+| Log_name           | File_size |+--------------------+-----------+| mariadb-bin.000001 |       401 |+--------------------+-----------+MariaDB [(none)]> GRANT ALL ON *.* TO [email protected]'192.168.0.%' IDENTIFIED BY 'mhapass';  #创建mha的管理用户，确保所有节点都已经同步此账号

3) Slave node

[[email protected] ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y[[email protected] ~]# vim /etc/my.cnf[mysqld]read_only=1log_binbinlog_format=rowserver_id=2relay_log_purge=0skip_name_resolve=1[[email protected] ~]# systemctl start mariadbMariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO [email protected]'192.168.0.%' IDENTIFIED BY 'replpass';MariaDB [(none)]> CHANGE MASTER TO    -> MASTER_HOST='192.168.0.7',  #此时主节点为0.7    -> MASTER_USER='repluser',    -> MASTER_PASSWORD='replpass',    -> MASTER_PORT=3306,    -> MASTER_LOG_FILE='mariadb-bin.000001',    -> MASTER_LOG_POS=401,    -> MASTER_CONNECT_RETRY=10;MariaDB [(none)]> START SLAVE;

4) Slave2 node

[[email protected] ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y[[email protected] ~]# vim /etc/my.cnf[mysqld]server_id=3read_only=1relay_log_purge=0skip_name_resolve=1[[email protected] ~]# systemctl start mariadbMariaDB [(none)]> CHANGE MASTER TO    -> MASTER_HOST='192.168.0.7',    -> MASTER_USER='repluser',    -> MASTER_PASSWORD='replpass',    -> MASTER_PORT=3306,    -> MASTER_LOG_FILE='mariadb-bin.000001',    -> MASTER_LOG_POS=401,    -> MASTER_CONNECT_RETRY=10;MariaDB [(none)]> START SLAVE;

4) Testing

When Master's service stops:[[email protected] ~]# systemctl stop mariadb

Execute on slave2: MariaDB [(none)]> SHOW SLAVE STATUS\G command, seeMaster_Server_Id: 2

Description: Successful Switchover

4, MHA monitoring re-launch

将故障的master修复后重新上线，手动配置成为现在主节点的从[[email protected] ~]# systemctl start mariadbMariaDB [(none)]> CHANGE MASTER TO    -> MASTER_HOST='192.168.0.8',  #此时的主为0.8    -> MASTER_USER='repluser',    -> MASTER_PASSWORD='replpass',    -> MASTER_PORT=3306,    -> MASTER_LOG_FILE='mariadb-bin.000001',    -> MASTER_LOG_POS=482,    -> MASTER_CONNECT_RETRY=10;MariaDB [(none)]> START SLAVE;

管理节点：[[email protected] ~]# screen -S mha[[email protected] ~]# masterha_manager --conf=/etc/mha/cluster1.cnf  #重新开启监测

Second, Galera Cluster

? Galera Cluster: MySQL cluster with integrated Galera plug-in, is a new, data-not-shared, highly redundant high-availability scheme, currently Galera Cluster has two versions, respectively Percona XTRADB The Cluster and mariadb Cluster,galera are inherently multi-master, with a multi-master cluster architecture that is a robust, high-availability solution with outstanding performance in data consistency, integrity, and performance.

? Three nodes constitute a cluster, unlike the normal master-slave architecture, they can be used as the primary node, three nodes are equivalent, called the multi-master architecture, when the client to write or read data, the connection of which instance is the same, read the data is the same, after writing to a node, The cluster itself synchronizes the new data to other nodes, which does not share any data and is a highly redundant architecture

Characteristics:

• Multi-Master Architecture: The real multi-point read and write cluster, at any time to read and write data, are up-to-date;
• Synchronous replication: Data synchronization between different nodes of the cluster, no delay, after the database is hung, the data will not be lost;
• Concurrent replication: Support parallel execution for better performance when you apply data from a node;
• Failover: In the event of database failure, it is easy to switch because of multi-point write support;
• Hot plug: During the service, if the database is hung, as long as the monitoring program is found to be fast enough, no service time will be very small. During node failure, the node itself has a very small impact on the cluster;
• Automatic node cloning: In the new node, or downtime maintenance, incremental data or basic data do not need manual backup to provide, Galera cluster will automatically pull the online node data, the final cluster will become consistent;
• Transparent to the application: the maintenance of the cluster is transparent to the application.

Working principle:

Refer to the official documentation:

Http://galeracluster.com/documentation-webpages/galera-documentation.pdf

Http://galeracluster.com/documentation-webpages/index.html

https://mariadb.com/kb/en/mariadb/getting-started-with-mariadb-galera-cluster/

Requires at least three nodes, cannot install Mariadb-server package, need to install a specific package

The realization of Galera cluster

Tsinghua Open source image source: https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.60/yum/centos7-amd64/

# vim /etc/yum.repos.d/mariadb_galera_server.repo[mariadb]name=mariadb_galera_server.repobaseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.60/yum/centos7-amd64/gpgcheck=0# yum install MariaDB-Galera-server -y

? 1) mysql-1

[[email protected] ~]# vim /etc/my.cnf.d/server.cnf[galera]wsrep_provider=/usr/lib64/galera/libgalera_smm.sowsrep_cluster_address="gcomm://192.168.0.7,192.168.0.8,192.168.0.9" #将所有IP都定义在此binlog_format=rowdefault_storage_engine=InnoDBinnodb_autoinc_lock_mode=2bind-address=0.0.0.0wsrep_cluster_name='my_wsrep_cluster'wsrep_node_name='node1'wsrep_node_address='192.168.0.7'

? 2) Mysql-2

[[email protected] ~]# vim /etc/my.cnf.d/server.cnf[galera]wsrep_provider=/usr/lib64/galera/libgalera_smm.sowsrep_cluster_address="gcomm://192.168.0.7,192.168.0.8,192.168.0.9"binlog_format=rowdefault_storage_engine=InnoDBinnodb_autoinc_lock_mode=2bind-address=0.0.0.0wsrep_cluster_name='my_wsrep_cluster'wsrep_node_name='node2'wsrep_node_address='192.168.0.8'

? 3) mysql-3

[[email protected] ~]# vim /etc/my.cnf.d/server.cnf[galera]wsrep_provider=/usr/lib64/galera/libgalera_smm.sowsrep_cluster_address="gcomm://192.168.0.7,192.168.0.8,192.168.0.9"binlog_format=rowdefault_storage_engine=InnoDBinnodb_autoinc_lock_mode=2bind-address=0.0.0.0wsrep_cluster_name='my_wsrep_cluster'wsrep_node_name='node3'wsrep_node_address='192.168.0.9'

? 4) Start

[[email protected] ~]# /etc/init.d/mysql start --wsrep-new-cluster #第一台启动加此参数[[email protected] ~]# /etc/init.d/mysql start #后续服务直接启动即可[[email protected] ~]# /etc/init.d/mysql start

? 5) Testing

? Operate the database on any one node, synchronize operations with other nodes, and if the same record is in operation, only one node operation succeeds.

? 6) View work status

MariaDB [(none)]> SHOW VARIABLES LIKE 'wsrep_%'\GMariaDB [(none)]> SHOW STATUS LIKE 'wsrep_%'\GMariaDB [(none)]> SHOW STATUS LIKE 'wsrep_cluster_size';+--------------------+-------+| Variable_name      | Value |+--------------------+-------+| wsrep_cluster_size | 3     |  #集群中有三台节点在线+--------------------+-------+

Record time 2018.6.17 father's Day

MySQL's high-availability implementation: MySQL series 14