MHA High Speed Available Clusters

MHA High Speed Available Clusters

Article Directory

Preface

1. Cluster Server

Connect multiple servers together using server clustering to provide data and programs running in the cluster High Availability And manageability.Server clustering provides three main cluster technical advantages: higher availability.Allows services and applications in a server cluster to continue to provide services under hardware or software component failures or during planned maintenance.Higher Scalability .Support by adding multiple processors (in Windows Up to eight of Server 2003 Enterprise Edition s are available in Windows Server 2003 Datacenter Edition up to 32) and additional memory (in Enterprise Edition, random access memory [RAM] up to 8 GB, in Windows Server 2003 Datacenter Edition up to 64 GB) to extend the server.Higher manageability.Allows administrators to manage devices and resources across the cluster as they manage a single computer.The Cluster Service is a complementary cluster technology for Windows (to extend Windows Server 2003 and Windows) 2000 Basics operating system And one of the provided).Another cluster technology is Network Load Balancing (NLB).As a complement to server clusters, this technology can be targeted to front-end applications and services such as Internet or Intranet sites, Web-based applications, media streaming, and Microsoft Terminal Services ) to support highly available and scalable clusters.This white paper is based solely on the architecture and functionality of server clusters. It introduces the terminology, concepts, design goals, key components and the intended direction of development of server clusters.The Details section at the end of this white paper provides a reference list from which you can learn more about server clustering and NLB technology.The background of computer cluster development has been in use for more than ten years.As one of the earliest cluster technology designers, G. Pfister defined clustering as "a parallel or distributed system consisting of a fully interconnected collection of computers that can be used as a unified computing resource".Combining several server machines into a unified cluster allows multiple servers to share computing load without the user or administrator having to know the details.For example, if any resource in the server cluster fails, the cluster as a whole can use the resources on other servers in the cluster to continue serving users, regardless of whether the component that fails is hardware or software resources.In other words, when a resource fails, users connected to a server cluster may experience a temporary performance degradation without losing full access to the service.Administrators can add new resources by rolling through the upgrade process when higher processing power is required.During this process, the cluster will remain online as a whole, not only will it be available to users, but its performance will also be improved after upgrading.Windows Server 2003 Enterprise Edition and Windows Server 2003 Datacenter Edition operating system It is designed and developed to meet the needs of users and businesses for clustering technology.The primary objective is to develop a cluster that meets the needs of most business organizations and organizations operating system Services, not just for small and specific segments of the market.Microsoft market research shows that as small and medium-sized businesses become increasingly dependent on databases and e-mail for their day-to-day operations, they have a strong and growing demand for highly available systems.Easy to install and manage is considered the most critical requirement for an organization of this size.Microsoft surveys also show that those with high performance and High Availability Large enterprises with high requirements are also increasingly interested in Windows-based servers.

2. Clustering

In my understanding, a cluster is a group Independent of each other Through high speed Network Connectivity Computers, which form a group and are managed in a single system mode.When a client interacts with a cluster, the cluster appears to be a separate server.Cluster configurations are used to improve availability and scalability.

3.MHA

MHA (Master High Availability) is currently a relatively mature solution for MySQL high availability. It was developed by youshimaton, a Japanese DeNA company, and is an excellent set of high availability software for failover and master-slave promotion in MySQL high availability environments.During the MSQL failover process, MHA can automatically complete the database failover operation within 10-30 seconds, and during the failover process, MHA can ensure the data consistency to the maximum extent to achieve a true high availability.

MHA also provides the ability to switch online primary libraries, which can safely switch the currently running primary libraries to a new one (by upgrading the secondary libraries to primary libraries), in about 0.5-2 seconds.

The software consists of two parts: MHA Manager (management node) and MHA Node (data node).MHA Manager can be deployed separately on a single machine to manage multiple master-slave clusters or on a single slave node.MHA Node runs on each MySQL server and the MHA Manager periodically detects master nodes in the cluster,** When the master fails, it automatically upgrades the slave of the latest data to the new master, and then redirects all other slaves to the new master.** The entire failover process is fully transparent to the application.

1. Summary of advantages of MHA

1)Masterfailover and slave promotion can be done very quickly

Fast automatic failover

2)Mastercrash does not result in data inconsistency

Main Library Crash No Data Consistency Issues

3)Noneed to modify current MySQL settings (MHA works with regular MySQL)

No major changes need to be made to the current mysql environment

4)Noneed to increase lots of servers

No additional servers need to be added (hundreds of replications can be managed with just one manager)

5)Noperformance penalty

Excellent performance, works in semi-synchronous and asynchronous replication. When monitoring mysql status, only ping packets are sent to the master every N seconds (default 3 seconds), so there is no impact on performance.You can understand that the performance of MHA is the same as that of a simple master-slave replication framework.

6)Works with any storage engine

MHA supports all storage engines supported by replication and is not limited to innodb

2.MHA Workflow

1. Infrastructure of MHA for Enterprise Website

2. Causes of MHA Formation

1. In the online network, we may encounter a lot of problems. Let's assume that when the master master master server is down, how do we remove the master host from the cluster by MHA, and then select the standby server as the master host to keep the business running uninterrupted?

2. Topology in line networks

3.MHA Workflow

1) Save the master binary log of the downtime.

2) Find the latest slave for the binlog location point.

3) Repair other slaves with relay log on the latest slave at the binlog location point.

4) Restore the binary logs saved on the downtime master to the slave with the latest location.

5) Promote the slave containing the latest location binlog to master.

6) Repoint other slaves to the newly promoted master and turn on master-slave replication.

Monitor all node s MHA function description:

2. Failover

A prerequisite is that three nodes exist and two sublibraries exist

(1) Select the primary premise, in the order of configuration files, but if the primary library is more than 100M relay-log after this node, it will not be selected

(2) If you set weights, the band will always be switched; in the case of multiple locations and multiple centers, the weights will generally be set at local nodes.

(3) Select slave1 as the new owner

(4) Save the binlog log log of the main library

3. Rebuild master-slave

(1) Exclude MHA from problematic nodes

Perform phase 1 data compensation, slave2 missing partial completion 90

(2)slave1 switches role to new master, pointing slave2 to new master slave1

slave2 change master to slave1

(3) Second stage data compensation

Apply the saved binlog of the new master and the missing portion of the original master to the new master.

(4) Virtual IP drift to the new master, not aware of application transparency

(5) Notify administrator of failover

3. Build MHA High Availability Clusters

1. Master-Slave Synchronization Base Configuration

1.MHA Cluster Topology

2. Install mysql database (master, slave1, slave2 all need to be installed. Write one to save everyone's time)

//Install ntp, sync time
[root@localhost ~]# yum -y install ntp ntpdate
//Configure ntp's master profile
[root@localhost ~]# vim /etc/ntp.conf
//Local clock source
server 127.127.73.0  
//Set the time level to 8
fudge 127.127.73.0 stratum 8 
//Re-configure from server
//This next step is important because we need to synchronize the time from the server with the master server
//[root@slave1 mysql]# /usr/sbin/ntpdate 192.168.73.140
//[root@slave2 mysql]# /usr/sbin/ntpdate 192.168.73.140
[root@localhost ~]# systemctl start ntpd
//Close Firewall
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# setenforce 0
//Install MySQL 5.7
//Install the source compilation package for MySQL
[root@localhost ~]# yum -y install ncurses ncurses-devel bison cmake gcc gcc-c++
//Create MySQL Program User
[root@localhost ~]# useradd -s /sbin/nologin mysql
//Unzip the package under the / opt directory
[root@localhost ~]# tar -zxvf mysql-boost-5.7.20.tar.gz -C /opt
[root@localhost ~]# cd /opt/mysql-5.7.20/
//cmake Compile and Install MySQL 5.7
[root@localhost mysql-5.7.20]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1
[root@localhost mysql-5.7.20]# make 
[root@localhost mysql-5.7.20]# make install
//Give MySQL the owner and group of all files under the / usr/local/mysql directory
[root@localhost mysql-5.7.20]# chown -R mysql:mysql /usr/local/mysql/
//Configure my.cnf file for MySQL
[root@localhost mysql-5.7.20]# vi /etc/my.cnf
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

//Modify the owner and group of my.cnf
[root@localhost mysql-5.7.20]# chown mysql:mysql /etc/my.cnf
//Configuring environment variables for mysql
[root@localhost mysql-5.7.20]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
[root@localhost mysql-5.7.20]# echo 'export PATH' >> /etc/profile
//Loading environment variables
[root@localhost mysql-5.7.20]# source /etc/profile
//Initialize mysql database
[root@localhost mysql-5.7.20]# cd /usr/local/mysql/
[root@localhost mysql]# bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
[root@localhost mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
[root@localhost mysql]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service.
[root@localhost mysql]# netstat -ntap | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      22547/mysqld 

3. Modify the hostname to distinguish it and see how it is running

master host

[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# su
[root@master ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Six 2020-01-11 08:40:15 CST; 32min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 1331 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1331 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid

1 11 08 January:40:13 localhost.localdomain mysqld[1234]: 2020-01-11T00:40:13.769557Z 0 [Note] IPv6 is available.
1 11 08 January:40:13 localhost.localdomain mysqld[1234]: 2020-01-11T00:40:13.769594Z 0 [Note]   - '::' resolves...:';
1 11 08 January:40:13 localhost.localdomain mysqld[1234]: 2020-01-11T00:40:13.769639Z 0 [Note] Server socket cre...:'.
1 11 08 January:40:14 localhost.localdomain mysqld[1234]: 2020-01-11T00:40:14.554678Z 0 [Note] Event Scheduler: ...nts
1 11 08 January:40:14 localhost.localdomain mysqld[1234]: 2020-01-11T00:40:14.555387Z 0 [Note] /usr/local/mysql/...ns.
1 11 08 January:40:14 localhost.localdomain mysqld[1234]: Version: '5.7.20'  socket: '/usr/local/mysql/mysql.soc...ion
1 11 08 January:40:14 localhost.localdomain mysqld[1234]: 2020-01-11T00:40:14.555402Z 0 [Note] Executing 'SELECT...ck.
1 11 08 January:40:14 localhost.localdomain mysqld[1234]: 2020-01-11T00:40:14.555405Z 0 [Note] Beginning of list...les
1 11 08 January:40:15 localhost.localdomain mysqld[1234]: 2020-01-11T00:40:15.452007Z 0 [Note] End of list of no...les
1 11 08 January:40:15 localhost.localdomain systemd[1]: Started MySQL Server.
Hint: Some lines were ellipsized, use -l to show in full.
[root@master ~]# systemctl status ntpd
● ntpd.service - Network Time Service
   Loaded: loaded (/usr/lib/systemd/system/ntpd.service; disabled; vendor preset: disabled)
   Active: active (running) since Six 2020-01-11 09:11:06 CST; 1min 41s ago
  Process: 2253 ExecStart=/usr/sbin/ntpd -u ntp:ntp $OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 2256 (ntpd)
   CGroup: /system.slice/ntpd.service
           └─2256 /usr/sbin/ntpd -u ntp:ntp -g

1 January 1109:11:06 localhost.localdomain ntpd[2256]: Listening on routing socket on fd #23 for interface updates
1 January 1109:11:06 localhost.localdomain systemd[1]: Started Network Time Service.
1 January 1109:11:07 localhost.localdomain ntpd[2256]: refclock_newpeer: clock type 73 invalid
1 January 1109:11:07 localhost.localdomain ntpd[2256]: 127.127.73.0 interface 127.0.0.1 -> (none)
1 January 1109:11:07 localhost.localdomain ntpd[2256]: 0.0.0.0 c016 06 restart
1 January 1109:11:07 localhost.localdomain ntpd[2256]: 0.0.0.0 c012 02 freq_set kernel 0.000 PPM
1 January 1109:11:07 localhost.localdomain ntpd[2256]: 0.0.0.0 c011 01 freq_not_set
1 January 1109:11:13 localhost.localdomain ntpd[2256]: 0.0.0.0 c61c 0c clock_step +0.578610 s
1 January 1109:11:14 localhost.localdomain ntpd[2256]: 0.0.0.0 c614 04 freq_mode
1 January 1109:11:15 localhost.localdomain ntpd[2256]: 0.0.0.0 c618 08 no_sys_peer

slave1 host

[root@localhost ~]# hostnamectl set-hostname slave1
[root@localhost ~]# su
[root@slave1 ~]# systemctl status ntpd
● ntpd.service - Network Time Service
   Loaded: loaded (/usr/lib/systemd/system/ntpd.service; disabled; vendor preset: disabled)
   Active: active (running) since Six 2020-01-11 09:18:58 CST; 38s ago
  Process: 2352 ExecStart=/usr/sbin/ntpd -u ntp:ntp $OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 2355 (ntpd)
   CGroup: /system.slice/ntpd.service
           └─2355 /usr/sbin/ntpd -u ntp:ntp -g

1 January 1109:18:58 slave1 ntpd[2355]: Listening on routing socket on fd #23 for interface updates
1 January 1109:18:58 slave1 systemd[1]: Started Network Time Service.
1 January 1109:18:58 slave1 ntpd[2355]: refclock_newpeer: clock type 73 invalid
1 January 1109:18:58 slave1 ntpd[2355]: 127.127.73.0 interface 127.0.0.1 -> (none)
1 January 1109:18:58 slave1 ntpd[2355]: 0.0.0.0 c016 06 restart
1 January 1109:18:58 slave1 ntpd[2355]: 0.0.0.0 c012 02 freq_set kernel 0.000 PPM
1 January 1109:18:58 slave1 ntpd[2355]: 0.0.0.0 c011 01 freq_not_set
1 January 1109:19:07 slave1 ntpd[2355]: 0.0.0.0 c61c 0c clock_step -0.138434 s
1 January 1109:19:07 slave1 ntpd[2355]: 0.0.0.0 c614 04 freq_mode
1 January 1109:19:08 slave1 ntpd[2355]: 0.0.0.0 c618 08 no_sys_peer
[root@slave1 ~]# systemctl status ntpdate
● ntpdate.service - Set time via NTP
   Loaded: loaded (/usr/lib/systemd/system/ntpdate.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
[root@slave1 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Six 2020-01-11 08:45:48 CST; 34min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 1355 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1355 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid

1 11 08 January:45:47 localhost.localdomain mysqld[1284]: 2020-01-11T00:45:47.549220Z 0 [Note] IPv6 is available.
1 11 08 January:45:47 localhost.localdomain mysqld[1284]: 2020-01-11T00:45:47.549246Z 0 [Note]   - '::' resolves...:';
1 11 08 January:45:47 localhost.localdomain mysqld[1284]: 2020-01-11T00:45:47.549278Z 0 [Note] Server socket cre...:'.
1 11 08 January:45:48 localhost.localdomain mysqld[1284]: 2020-01-11T00:45:48.218099Z 0 [Note] Event Scheduler: ...nts
1 11 08 January:45:48 localhost.localdomain mysqld[1284]: 2020-01-11T00:45:48.219130Z 0 [Note] /usr/local/mysql/...ns.
1 11 08 January:45:48 localhost.localdomain mysqld[1284]: Version: '5.7.20'  socket: '/usr/local/mysql/mysql.soc...ion
1 11 08 January:45:48 localhost.localdomain mysqld[1284]: 2020-01-11T00:45:48.219150Z 0 [Note] Executing 'SELECT...ck.
1 11 08 January:45:48 localhost.localdomain mysqld[1284]: 2020-01-11T00:45:48.219154Z 0 [Note] Beginning of list...les
1 11 08 January:45:48 localhost.localdomain mysqld[1284]: 2020-01-11T00:45:48.828705Z 0 [Note] End of list of no...les
1 11 08 January:45:48 localhost.localdomain systemd[1]: Started MySQL Server.
Hint: Some lines were ellipsized, use -l to show in full.

slave2 host

[root@localhost ~]# hostnamectl set-hostname slave2
[root@localhost ~]# su
[root@slave2 ~]# systemctl status ntpd
● ntpd.service - Network Time Service
   Loaded: loaded (/usr/lib/systemd/system/ntpd.service; disabled; vendor preset: disabled)
   Active: active (running) since Six 2020-01-11 09:31:58 CST; 1min 4s ago
  Process: 2458 ExecStart=/usr/sbin/ntpd -u ntp:ntp $OPTIONS (code=exited, status=0/SUCCESS)
 Main PID: 2461 (ntpd)
   CGroup: /system.slice/ntpd.service
           └─2461 /usr/sbin/ntpd -u ntp:ntp -g

1 January 1109:31:58 localhost.localdomain ntpd[2461]: Listen normally on 6 ens33 fe80::d440:5edc:e02e:f317 UDP 123
1 January 1109:31:58 localhost.localdomain ntpd[2461]: Listening on routing socket on fd #23 for interface updates
1 January 1109:31:58 localhost.localdomain ntpd[2461]: refclock_newpeer: clock type 73 invalid
1 January 1109:31:58 localhost.localdomain ntpd[2461]: 127.127.73.0 interface 127.0.0.1 -> (none)
1 January 1109:31:58 localhost.localdomain ntpd[2461]: 0.0.0.0 c016 06 restart
1 January 1109:31:58 localhost.localdomain ntpd[2461]: 0.0.0.0 c012 02 freq_set kernel 0.000 PPM
1 January 1109:31:58 localhost.localdomain ntpd[2461]: 0.0.0.0 c011 01 freq_not_set
1 January 1109:32:05 localhost.localdomain ntpd[2461]: 0.0.0.0 c61c 0c clock_step +0.185545 s
1 January 1109:32:05 localhost.localdomain ntpd[2461]: 0.0.0.0 c614 04 freq_mode
1 January 1109:32:06 localhost.localdomain ntpd[2461]: 0.0.0.0 c618 08 no_sys_peer
[root@slave2 ~]# systemctl status ntpdate
● ntpdate.service - Set time via NTP
   Loaded: loaded (/usr/lib/systemd/system/ntpdate.service; disabled; vendor preset: disabled)
   Active: inactive (dead)
[root@slave2 ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Six 2020-01-11 08:47:07 CST; 46min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 1361 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─1361 /usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/mysqld.pid

1 11 08 January:47:02 localhost.localdomain mysqld[1266]: 2020-01-11T00:47:02.638376Z 0 [Note] IPv6 is available.
1 11 08 January:47:02 localhost.localdomain mysqld[1266]: 2020-01-11T00:47:02.638394Z 0 [Note]   - '::' resolves...:';
1 11 08 January:47:02 localhost.localdomain mysqld[1266]: 2020-01-11T00:47:02.638422Z 0 [Note] Server socket cre...:'.
1 11 08 January:47:04 localhost.localdomain mysqld[1266]: 2020-01-11T00:47:04.560179Z 0 [Note] Event Scheduler: ...nts
1 11 08 January:47:04 localhost.localdomain mysqld[1266]: 2020-01-11T00:47:04.575284Z 0 [Note] /usr/local/mysql/...ns.
1 11 08 January:47:04 localhost.localdomain mysqld[1266]: Version: '5.7.20'  socket: '/usr/local/mysql/mysql.soc...ion
1 11 08 January:47:04 localhost.localdomain mysqld[1266]: 2020-01-11T00:47:04.575312Z 0 [Note] Executing 'SELECT...ck.
1 11 08 January:47:04 localhost.localdomain mysqld[1266]: 2020-01-11T00:47:04.575317Z 0 [Note] Beginning of list...les
1 11 08 January:47:07 localhost.localdomain mysqld[1266]: 2020-01-11T00:47:07.205380Z 0 [Note] End of list of no...les
1 11 08 January:47:07 localhost.localdomain systemd[1]: Started MySQL Server.
Hint: Some lines were ellipsized, use -l to show in full.

manager host

[root@localhost ~]# hostnamectl set-hostname manager
[root@localhost ~]# su
[root@manager ~]# 

client Client

[root@localhost ~]# hostnamectl set-hostname client
[root@localhost ~]# su
[root@client ~]#

2. Configure master, slave1, slave2 master-slave synchronization

1. Modify the main profile

master host

[root@master ~]# vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin = master-bin
log-slave-updates = true
[root@master ~]# systemctl restart mysqld

slave1 host

[root@slave1 ~]# vim /etc/my.cnf
[mysqld]
server-id = 11
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
[root@slave1 ~]# systemctl restart mysqld

slave2 host

[root@slave2 ~]# vim /etc/my.cnf
[mysqld]
server-id = 12
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
[root@slave2 ~]# systemctl restart mysqld

2. Title all database servers

master server

//Log in to master's mysql database
[root@master ~]# mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20-log Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

//Open permissions to write from server
mysql> grant replication slave on *.* to 'myslave'@'192.168.73.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//User mha creating manager
mysql> grant all privileges on *.* to 'mha'@'192.168.73.%' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//Refresh Permissions
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

//By checking mysql master-slave error through mha, it is reported that two slave libraries cannot connect to the master library through the host name, so all databases are authorized below.
//Increase mha login master privileges
mysql> grant all privileges on *.* to 'mha'@'master' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//Increase mha's access to slave1
mysql> grant all privileges on *.* to 'mha'@'slave1' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//Increase mha's access to slave2
mysql> grant all privileges on *.* to 'mha'@'slave2' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

//Refresh Permissions
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

slave1 server

[root@slave1 ~]# mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication slave on *.* to 'myslave'@'192.168.73.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.73.%' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'master' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'slave1' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'slave2' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

slave2 server

[root@slave2 ~]# mysql -uroot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant replication slave on *.* to 'myslave'@'192.168.73.%' identified by '123';
Query OK, 0 rows affected, 1 warning (1.07 sec)

mysql> grant all privileges on *.* to 'mha'@'192.168.73.%' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'master' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'slave1' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'mha'@'slave2' identified by 'manager';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

6. Turn on master-slave synchronization

master server

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |     1897 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

slave1 server

mysql> change master to master_host='192.168.73.140',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=1897;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

mysql> start slave;
Query OK, 0 rows affected (0.02 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.73.140
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 1897
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1897
              Relay_Log_Space: 526
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: a101d92d-3202-11ea-b018-000c290cd2cd
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

//Turn on read-only functionality from the server
mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec

slave2 server

mysql> change master to master_host='192.168.73.140',master_user='myslave',master_password='123',master_log_file='master-bin.000001',master_log_pos=1897;
Query OK, 0 rows affected, 2 warnings (0.34 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.73.140
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 1897
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1897
              Relay_Log_Space: 526
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: a101d92d-3202-11ea-b018-000c290cd2cd
             Master_Info_File: /usr/local/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.01 sec)

mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)

3. Verify master-slave synchronization

Build a database named test on the master

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.05 sec)

View on slave1

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.11 sec)

View on slave2

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.12 sec)

The two master-slave libraries created on the master master master server are normally viewed from the server, indicating that the master-slave synchronization was successful.

4. Install MHAnode node

MHA software package is different for each operating system version, here is centos7.4 must choose version 0.57, node component must be installed on all servers, and finally manager component must be installed on the MHA-manager node, because manager depends on node component, the following are operation demonstrations on master to install node component

1. Install the gmake compiler (on all servers)

tar zxvf cmake-2.8.6.tar.gz -C /opt
cd /opt/cmake-2.8.6/
./configure 
gmake
gmake install

2. Install mh-dependent environments and node nodes on all servers

yum install epel-release --nogpgcheck -y
[root@mha_manager ~]# yum install -y perl-DBD-MySQL \
perl-Config-Tiny \
perl-Log-Dispatch \
perl-Parallel-ForkManager \
perl-ExtUtils-CBuilder \
perl-ExtUtils-MakeMaker \
perl-CPAN
tar zxvf mha/mha4mysql-node-0.57.tar.gz 
cd mha4mysql-node-0.57/
yum install perl-Module-Install -y
perl Makefile.PL 
//There's a place where you need to type y or n, so let's just type y
make
make install

3. Install the manager component on the manager server

tar zxvf mha4mysql-manager-0.57.tar.gz
cd mha4mysql-manager-0.57/
perl Makefile.PL
make
make install

5. Configure ssh to avoid interactive login

1. Push manager's key pairs to three mysql servers

[root@manager ~]# ssh-keygen -t rsa
	'//Create Asymmetric Key Pair'
    '//Since you want to avoid a secret landing, all three will return directly without setting a password'
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
'//Upload Secret Key to Node Server'
[root@manager ~]# ssh-copy-id 192.168.73.140
Are you sure you want to continue connecting (yes/no)? yes
root@192.168.73.140's password: 	//Enter Password'
    '//Set up another server in the same way'
[root@manager ~]# ssh-copy-id 192.168.73.141
[root@manager ~]# ssh-copy-id 192.168.73.138

2. Push slave1 and slave2 above master

ssh-keygen -t rsa
ssh-copy-id 192.168.73.141
ssh-copy-id 192.168.73.138

3. Push master and slave2 on top of slave1

ssh-keygen -t rsa
ssh-copy-id 192.168.73.140
ssh-copy-id 192.168.73.138

4. Push master and slave1 above slave2

ssh-keygen -t rsa
ssh-copy-id 192.168.73.140
ssh-copy-id 192.168.73.141

6. Configure the components of mha-manager

1. Copy the script to the / usr/local/bin directory

[root@manager ~]# cp -ra /root/mha4mysql-manager-0.57/samples/scripts /usr/local/bin
[root@manager samples]# ls -l /usr/local/bin/scripts/
-rwxr-xr-x. 1 1001 1001  3648 5 Month 31, 2015 master_ip_failover  When switching automatically VIP Managed scripts
-rwxr-xr-x. 1 1001 1001  9870 5 Month 31, 2015 master_ip_online_change  Online Switching VIP Management 
-rwxr-xr-x. 1 1001 1001 11867 5 Month 31, 2015 power_manager  Script to shut down the host after a failure occurs
-rwxr-xr-x. 1 1001 1001  1360 5 Month 31, 2015 send_report  Script to send an alert after failover
[root@manager ~]# cp /usr/local/bin/scripts/master_ip_failover /usr/local/bin/	'//Script for VIP Management on Auto Switch'

2. Modify master_ip_failover script

[root@manager ~]# vim /usr/local/bin/master_ip_failover 
'//Delete content, rewrite script'
#!/usr/bin/env perl '//Write the first line on the top line, without spaces'
use strict;
use warnings FATAL => 'all';

use Getopt::Long;

my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '192.168.73.100';
my $brdc = '192.168.73.255';
my $ifdev = 'ens33';
my $key = '1';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
my $exit_code = 0;
#my $ssh_start_vip = "/usr/sbin/ip addr add $vip/24 brd $brdc dev $ifdev label $ifdev:$key;/usr/sbin/arping -q -A -c 1 -I $ifdev $vip;iptables -F;";
#my $ssh_stop_vip = "/usr/sbin/ip addr del $vip/24 dev $ifdev label $ifdev:$key";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);

exit &main();

sub main {

print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

if ( $command eq "stop" || $command eq "stopssh" ) {

my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {

my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}

sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}

3. Create mha directory and modify configuration file

[root@manager ~]# mkdir /etc/masterha
[root@manager ~]# cp /root/mha4mysql-manager-0.57/samples/conf/app1.cnf /etc/masterha/
[root@manager ~]# vim /etc/masterha/app1.cnf 
[server default]
manager_log=/var/log/masterha/app1/manager.log
manager_workdir=/var/log/masterha/app1
master_binlog_dir=/usr/local/mysql/data
master_ip_failover_script=/usr/local/bin/master_ip_failover
master_ip_online_change_script=/usr/local/bin/master_ip_online_change
password=manager
ping_interval=1
remote_workdir=/tmp
repl_password=123456
repl_user=myslave
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.73.141 -s 192.168.73.138
shutdown_script=""
ssh_user=root
user=mha

[server1]
hostname=192.168.73.140
port=3306

[server2]
candidate_master=1
hostname=192.168.73.141
check_repl_delay=0
port=3306

[server3]
hostname=192.168.73.138
port=3306

7. Test ssh connection to mysql

1. Verify the key pair file

[root@manager ~]# masterha_check_ssh -conf=/etc/masterha/app1.cnf
Fri Jan 10 01:14:10 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Fri Jan 10 01:14:10 2020 - [info] Reading application default configuration from /etc/masterha/app1.cnf..
Fri Jan 10 01:14:10 2020 - [info] Reading server configuration from /etc/masterha/app1.cnf..
Fri Jan 10 01:14:10 2020 - [info] Starting SSH connection tests..
Fri Jan 10 01:14:11 2020 - [debug] 
Fri Jan 10 01:14:10 2020 - [debug]  Connecting via SSH from root@192.168.73.141(192.168.73.141:22) to root@192.168.73.138(192.168.73.138:22)..
Fri Jan 10 01:14:11 2020 - [debug]   ok.
Fri Jan 10 01:14:12 2020 - [debug] 
Fri Jan 10 01:14:10 2020 - [debug]  Connecting via SSH from root@192.168.73.138(192.168.73.138:22) to root@192.168.73.141(192.168.73.141:22)..
Fri Jan 10 01:14:11 2020 - [debug]   ok.
Fri Jan 10 01:14:12 2020 - [info] All SSH connection tests passed successfully.

2. Test mysql master-slave connection

[root@manager ~]# masterha_check_repl -conf=/etc/masterha/app1.cnf

8. Configure virtual ip and start MHA

1. Configure virtual ip

[root@master ~]# /sbin/ifconfig ens33:1 192.168.73.100/24

2. Start mha

[root@manager ~]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > 
[root@manager ~]# /var/log/masterha/app1/manager.log 2>&1 &
//View the current master node
[root@manager ~]# masterha_check_status --conf=/etc/masterha/app1.cnf
//View current log information
[root@manager ~]# cat /var/log/masterha/app1/manager.log 

9. Simulated failure

Start monitoring, view log records, simulate master server failures

[root@manager ~]# tailf /var/log/masterha/app1/manager.log 
[root@master ~]# pkill -9 mysqld 

10. Test results

1. View above slave1

[root@slave1 ~]# ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.73.141  netmask 255.255.255.0  broadcast 192.168.73.255
        inet6 fe80::159a:a8d1:5769:74d0  prefixlen 64  scopeid 0x20<link>
        ether 00:0c:29:34:57:c1  txqueuelen 1000  (Ethernet)
        RX packets 347068  bytes 28229347 (26.9 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 528201  bytes 67755670 (64.6 MiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.73.100  netmask 255.255.255.0  broadcast 192.168.73.255
        ether 00:0c:29:34:57:c1  txqueuelen 1000  (Ethernet)

2.mha_manager View

//Dynamic display of subsequent information
Generating relay diff files from the latest slave succeeded.
192.168.73.138(192.168.73.138:3306): OK: Applying all logs succeeded. Slave started, replicating from 192.168.73.141(192.168.73.141:3306)
192.168.73.141(192.168.73.141:3306): Resetting slave info succeeded.
Master failover to 192.168.73.141(192.168.73.141:3306) completed successfully.

3. Install mysql on the client

yum -y install mysql
mysql -umha -pmanager -h 192.168.73.100 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.20 Source distribution

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.05 sec)
92 original articles published, 39 praised, 5585 visits
Private letter follow

Tags: MySQL ssh socket Oracle

Posted on Fri, 10 Jan 2020 20:24:38 -0800 by amithn12