hydd's LInux notes Day61

Day61

Overview of MHA cluster

MHA introduction

Introduction to MHA

MHA(Master high Availability)

It is developed by DeNA company in Japan. It is an excellent solution to realize high availability of MySQL. The automatic failover operation of the database can be completed within 0 ~ 30 seconds. MHA can ensure the consistency of data to the greatest extent in the process of failover, so as to achieve high availability in the real sense.

MHA composition

MHA Manger (management node)

  1. Manage all database servers
  2. It can be deployed separately on a separate machine
  3. It can also be deployed on a database server

MHA Node

  1. On the mysql server where the data is stored
  2. Run on each Mysql server

MHA working process

MHA cluster architecture

MHA working process

The mster node in the cluster is detected regularly by the Manager

When the master fails, the Manager automatically promotes the slave with the latest data to the master

topological structure

IP planning

Topological graph

Deploy MHA cluster

Prepare cluster environment

Configure ssh password free login

1) Configuring database server 192.168.4.51

[root@host51 ~]# SSH keygen / / create a secret key pair
[root@host51 ~]# ssh-copy-id  root@192.168.4.52 //Pass the public key to the host52 host
[root@host51 ~]# ssh-copy-id  root@192.168.4.53 //Pass the public key to the host53 host

2) Configuring database server 192.168.4.52

[root@host52 ~]# SSH keygen / / create a secret key pair
[root@host52 ~]# ssh-copy-id  root@192.168.4.51 //Pass the public key to the host51 host
[root@host52 ~]# ssh-copy-id  root@192.168.4.53 //Pass the public key to the host53 host

3) Configuring database server 192.168.4.53

[root@host53 ~]# SSH keygen / / create a secret key pair
[root@host53 ~]# ssh-copy-id  root@192.168.4.51 //Pass the public key to the host51 host
[root@host53 ~]# ssh-copy-id  root@192.168.4.52 //Pass the public key to the host52 host

4) Configuration management server 192.168.4.57

[root@mgm57 ~]# SSH keygen / / create a secret key pair
[root@mgm57 ~]# ssh-copy-id  root@192.168.4.51 //Pass the public key to the host51 host
[root@mgm57 ~]# ssh-copy-id  root@192.168.4.52 //Pass the public key to the host52 host
[root@mgm57 ~]# ssh-copy-id  root@192.168.4.53 //Pass the public key to the host52 host

Install dependent packages

Install the perl package that comes with the system and the shared perl package on all hosts

# yum -y install perl - * / / install the perl package that comes with your system

# cd mha-soft-student

# yum – y install perl - * / / install the shared perl package

Configure MySQL one master multi slave structure

1) Configure master server 192.168.4.51

[root@host51 ~]# vim /etc/my.cnf
[root@host51 ~]# vim /etc/my.cnf
[mysqld]
log-bin=master51 //Log name
server_id=51 //Specify server_id
:wq
[root@host51 ~]# systemctl  restart  mysqld
[root@host51 ~]# mysql  -uroot  -p123qqq...A
    mysql> grant  replication slave  on  *.*  to repluser@"%"  identified by "123qqq...A"; //Add users who synchronize data connections from the server
mysql> show master status; //View log information
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000001 |     441 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+

2) Configure slave server 192.168.4.52

[root@host52 ~]# vim /etc/my.cnf

[mysqld]

server_id=52 //Specify server_id

:wq

[root@host52 ~]# systemctl restart mysqld / / restart the database service

[root@host52 ~]# mysql -uroot – p123qqq... A / / database administrator login

mysql> change master to //Specify master server information

master_host="192.168.4.51", //IP address

master_user="repluser", //Authorized user

master_password="123qqq...A", //Authorized user password

master_log_file="master51.000001", //binlog log

master_log_pos=441; //Offset

mysql> start slave; //Start the slave process

mysql> exit ; //Disconnect

[root@host52 ~]# mysql -uroot –p123qqq...A –e "show slave status\G" | grep 192.168.4.51

Master_Host: 192.168.4.51 //Ip address of primary server

[root@host52 ~]# mysql -uroot –p123qqq...A –e "show slave status\G" | grep –i yes

Slave_IO_Running: Yes //I0 thread is normal

Slave_SQL_Running: Yes //SQL thread OK

3) Configure slave server 192.168.4.53

[root@host53 ~]# vim /etc/my.cnf

[mysqld]

server_id=53 //Specify server_id

:wq

[root@host53 ~]# systemctl restart mysqld / / restart the database service

[root@host53 ~]# mysql -uroot – p123qqq... A / / database administrator login

mysql> change master to //Specify master server information

master_host="192.168.4.51", //IP address

master_user="repluser", //Authorized user

master_password="123qqq...A", //Authorized user password

master_log_file="master51.000001", //binlog log

master_log_pos=441; //Offset

mysql> start slave; //Start the slave process

mysql> exit ; //Disconnect

[root@host53 ~]# mysql -uroot –p123qqq...A –e "show slave status\G" | grep 192.168.4.51

Master_Host: 192.168.4.51 //Ip address of primary server

[root@host53 ~]# mysql -uroot –p123qqq...A –e "show slave status\G" | grep –i yes

Slave_IO_Running: Yes //I0 thread is normal

Slave_SQL_Running: Yes //SQL thread OK

Configuration management node

1) Install software

[root@mgm57 ~]# cd mha-soft-student/
[root@mgm57 mha-soft-student]#
[root@mgm57 mha-soft-student]# rpm -ivh  mha4mysql-node-0.56-0.el6.noarch.rpm / / install MHA node package
[root@mgm57 mha-soft-student]#
[root@mgm57 mha-soft-student]# RPM - QA | grep MHA / / check whether the installation succeeds
mha4mysql-node-0.56-0.el6.noarch
[root@mgm57 mha-soft-student]#
[root@mgm57 mha-soft-student]# tar -zxvf mha4mysql-manager-0.56.tar.gz / / unzip the MHA manager package
[root@mgm57 mha-soft-student]# ls  
app1.cnf            mha4mysql-manager-0.56        
mha4mysql-node-0.56-0.el6.noarch.rpm
master_ip_failover  mha4mysql-manager-0.56.tar.gz
[root@mgm57 mha-soft-student]# cd mha4mysql-manager-0.56 / / enter the source directory
[root@mgm57 mha4mysql-manager-0.56]# ls / / view the file list
AUTHORS  COPYING  inc  Makefile.PL  META.yml  rpm      t
bin      debian   lib  MANIFEST     README    samples  tests
[root@mgm57 mha4mysql-manager-0.56]#
[root@mgm57 mha4mysql-manager-0.56]# perl Makefile.PL / / configuration
[root@mgm57 mha4mysql-manager-0.56]#  make / / compile
[root@mgm57 mha4mysql-manager-0.56]#  make install / / install
[root@mgm57 mha4mysql-manager-0.56]# ls /root/perl5/bin / / view the installed commands
masterha_check_repl    masterha_conf_host       masterha_master_switch
masterha_check_ssh     masterha_manager         masterha_secondary_check
masterha_check_status  masterha_master_monitor  masterha_stop

2) Edit Master profile

[root@mgm57 ~ ]#  MKDIR / etc / MHA / / create a working directory
[root@mgm57 ~ ]#  cp mha4mysql-manager-0.56/sample/conf/app1.cnf /etc/mha / / / copy the template file
[root@mgm57 ~ ]#  vim /etc/mha/app1.cnf / / edit the main configuration file
[server default]     //Management service default configuration
manager_workdir=/etc/mha    //working directory
manager_log=/etc/mha/manager.log   //log file
                master_ip_failover_script=/etc/mha/master_ip_failover //Failover script
ssh_user=root  //Access ssh service users
ssh_port=22    //ssh service port
repl_user=repluser           //Master server data synchronization authorized user
repl_password=123qqq...A    //password
user=root                   //Monitor users
password=123qqq...A               //password
[server1]   //Specify the first database server
hostname=192.168.4.51    //server ip address 
port=3306                //Service port
candidate_master=1               //Campaign master server

[server2]  //Specify the second database server
hostname=192.168.4.52
port=3306
candidate_master=1  
            
[server3]  //Specify the third database server
hostname=192.168.4.53
port=3306
candidate_master=1

3) Create failover script

[root@mgm57 ~]# cp mha-soft-student/master_ip_failover /etc/mha/
[root@mgm57 ~]# vim +35 /etc/mha/master_ip_failover
my $vip = '192.168.4.100/24';  # Virtual IP / / define the VIP address
my $key = "1"; //Define variable $key
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip"; //Deploy vip address command
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down"; //Release vip address command
:wq
[root@mgm57 ~]# chmod +x  /etc/mha/master_ip_failover / / add execution permission to the script

4) Deploy the vip address on the current master server

[root@host51 ~]# Ifconfig eth0:1 / / view before deployment 
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        ether 52:54:00:d8:10:d7  txqueuelen 1000  (Ethernet)
[root@host51 ~]# Ifconfig eth0:1 192.168.4.100 / / deploy vip address
[root@host51 ~]# Ifconfig eth0:1 / / view after deployment
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.4.100  netmask 255.255.255.0  broadcast 192.168.4.255
        ether 52:54:00:d8:10:d7  txqueuelen 1000  (Ethernet)

Configure data node

1) On all database servers, install the MHA node package

]# cd /root/mha-soft-student/
]#  rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

2) Add monitoring users on all data servers

You can only execute the authorization command on the host host51, and host52 and host53 will automatically synchronize the authorization

]# mysql – uroot – p password
mysql> grant all on  *.*  to root@"%" identified by "123qqq...A";
mysql> exit;

3) Add two slave servers and connect users synchronously

Adding users from server host52

[root@host52]# mysql – uroot – p password
mysql> grant replication slave on  *.*  to repluser@"%" identified by "123qqq...A";
mysql> exit;

Adding users from server host53

[root@host53]# mysql – uroot – p password

4) Modify database service operation parameters

Modify master server host51

[root@host51 ~]# vim /etc/my.cnf
[mysqld]
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" //Loading module
rpl_semi_sync_master_enabled=1  //Enable master module
rpl_semi_sync_slave_enabled=1     //Enable slave module
relay_log_purge=0   //Disable automatic deletion of relay log files
:wq
[root@host51 ~]#  Systemctl restart mysqld / / restart the service

Modify slave server host52

[root@host52 ~]# vim /etc/my.cnf
[mysqld]
log-bin=master52
plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" //Loading module
rpl_semi_sync_master_enabled=1  //Enable master module
rpl_semi_sync_slave_enabled=1     //Enable slave module
relay_log_purge=0   //Disable automatic deletion of relay log files
:wq
[root@host52 ~]#  Systemctl restart mysqld / / restart the service

Modify slave server host53

[root@host53 ~]# vim /etc/my.cnf

[mysqld]

log-bin=master53

plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" //Loading module

rpl_semi_sync_master_enabled=1 //Enable master module

rpl_semi_sync_slave_enabled=1 //Enable slave module

relay_log_purge=0 //Disable automatic deletion of relay log files

:wq

[root@host53 ~]# systemctl restart mysqld / / restart the service

Test configuration

Test cluster environment

In the management host, test the ssh configuration

 [root@mgm57 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf / / execute the test command
Thu Jun 20 15:33:48 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 15:33:48 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu Jun 20 15:33:48 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
Thu Jun 20 15:33:48 2019 - [info] Starting SSH connection tests..
Thu Jun 20 15:33:49 2019 - [debug]
Thu Jun 20 15:33:48 2019 - [debug]  Connecting via SSH from root@192.168.4.51(192.168.4.51:22) to root@192.168.4.52(192.168.4.52:22)..
Thu Jun 20 15:33:49 2019 - [debug]   ok.
Thu Jun 20 15:33:49 2019 - [debug]  Connecting via SSH from root@192.168.4.51(192.168.4.51:22) to root@192.168.4.53(192.168.4.53:22)..
Thu Jun 20 15:33:49 2019 - [debug]   ok.
Thu Jun 20 15:33:50 2019 - [debug]
Thu Jun 20 15:33:49 2019 - [debug]  Connecting via SSH from root@192.168.4.52(192.168.4.52:22) to root@192.168.4.51(192.168.4.51:22)..
Thu Jun 20 15:33:49 2019 - [debug]   ok.
Thu Jun 20 15:33:49 2019 - [debug]  Connecting via SSH from root@192.168.4.52(192.168.4.52:22) to root@192.168.4.53(192.168.4.53:22)..
Thu Jun 20 15:33:49 2019 - [debug]   ok.
Thu Jun 20 15:33:50 2019 - [debug]   ok.
Thu Jun 20 15:33:50 2019 - [debug]  Connecting via SSH from root@192.168.4.53(192.168.4.53:22) to root@192.168.4.52(192.168.4.52:22)..
Thu Jun 20 15:33:50 2019 - [debug]   ok.
Thu Jun 20 15:33:51 2019 - [info] All SSH connection tests passed successfully.//Test success prompt

2) In the management host, test the master-slave synchronization

 [root@host57 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf / / execute the test command
Thu Jun 20 15:37:46 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
Thu Jun 20 15:37:46 2019 - [info] MHA::MasterMonitor version 0.56.
Thu Jun 20 15:37:47 2019 - [info] GTID failover mode = 0
Thu Jun 20 15:37:47 2019 - [info] Dead Servers: //mysql server without stop
Thu Jun 20 15:37:47 2019 - [info] Alive Servers://List of hosts running mysql service
Thu Jun 20 15:37:47 2019 - [info]   192.168.4.51(192.168.4.51:3306)
Thu Jun 20 15:37:47 2019 - [info]   192.168.4.52(192.168.4.52:3306)
Thu Jun 20 15:37:47 2019 - [info]   192.168.4.53(192.168.4.53:3306)
Thu Jun 20 15:37:47 2019 - [info] Alive Slaves:
Thu Jun 20 15:37:47 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu Jun 20 15:37:47 2019 - [info]   192.168.4.53(192.168.4.53:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jun 20 15:37:47 2019 - [info]     Replicating from 192.168.4.51(192.168.4.51:3306)
Thu Jun 20 15:37:47 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu Jun 20 15:37:47 2019 - [info] Current Alive Master: 192.168.4.51(192.168.4.51:3306)
Thu Jun 20 15:37:47 2019 - [info] Checking slave configurations..
Thu Jun 20 15:37:47 2019 - [info]  read_only=1 is not set on slave 192.168.4.52(192.168.4.52:3306).
Thu Jun 20 15:37:47 2019 - [info]  read_only=1 is not set on slave 192.168.4.53(192.168.4.53:3306).
Thu Jun 20 15:37:47 2019 - [info] Checking replication filtering settings..
Thu Jun 20 15:37:47 2019 - [info]  binlog_do_db= , binlog_ignore_db=
Thu Jun 20 15:37:47 2019 - [info]  Replication filtering check ok.
Thu Jun 20 15:37:47 2019 - [info] GTID (with auto-pos) is not supported
Thu Jun 20 15:37:47 2019 - [info] Starting SSH connection tests..
Thu Jun 20 15:37:49 2019 - [info] All SSH connection tests passed successfully.
Thu Jun 20 15:37:49 2019 - [info] Checking MHA Node version..
Thu Jun 20 15:37:50 2019 - [info]  Version check ok.
Thu Jun 20 15:37:50 2019 - [info] Checking SSH publickey authentication settings on the current master..
Thu Jun 20 15:37:50 2019 - [info] HealthCheck: SSH to 192.168.4.51 is reachable.
Thu Jun 20 15:37:50 2019 - [info] Master MHA Node version is 0.56.
Thu Jun 20 15:37:50 2019 - [info] Checking recovery script configurations on 192.168.4.51(192.168.4.51:3306)..
Thu Jun 20 15:37:50 2019 - [info]   Connecting to root@192.168.4.51(192.168.4.51:22)..
  Creating /var/tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /var/lib/mysql, up to master51.000002
Thu Jun 20 15:37:50 2019 - [info] Binlog setting check done.
Thu Jun 20 15:37:50 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu Jun 20 15:37:50 2019 - [info]   Connecting to root@192.168.4.52(192.168.4.52:22)..
  Checking slave recovery environment settings..
    Opening /var/lib/mysql/relay-log.info ... ok.
    Relay log found at /var/lib/mysql, up to host52-relay-bin.000006
    Temporary relay log file is /var/lib/mysql/host52-relay-bin.000006
    Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Thu Jun 20 15:37:51 2019 - [info]   Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.4.53 --slave_ip=192.168.4.53 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.17-log --manager_version=0.56 --relay_log_info=/var/lib/mysql/relay-log.info  --relay_dir=/var/lib/mysql/  --slave_pass=xxx
Thu Jun 20 15:37:51 2019 - [info]   Connecting to root@192.168.4.53(192.168.4.53:22)..
  Checking slave recovery environment settings..
    Opening /var/lib/mysql/relay-log.info ... ok.
    Relay log found at /var/lib/mysql, up to host53-relay-bin.000006
    Temporary relay log file is /var/lib/mysql/host53-relay-bin.000006
    Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Thu Jun 20 15:37:52 2019 - [info] Slaves settings check done.
Thu Jun 20 15:37:52 2019 - [info]
192.168.4.51(192.168.4.51:3306) (current master)
 +--192.168.4.52(192.168.4.52:3306)
 +--192.168.4.53(192.168.4.53:3306)
Thu Jun 20 15:37:52 2019 - [info] Checking replication health on 192.168.4.52..
Thu Jun 20 15:37:52 2019 - [info]  ok.
Thu Jun 20 15:37:52 2019 - [info] Checking replication health on 192.168.4.53..
Thu Jun 20 15:37:52 2019 - [info]  ok.
Thu Jun 20 15:37:52 2019 - [info] Checking master_ip_failover_script status:
Thu Jun 20 15:37:52 2019 - [info]   /etc/mha/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.4.51 --orig_master_ip=192.168.4.51 --orig_master_port=3306
Thu Jun 20 15:37:52 2019 - [info]  OK.
Thu Jun 20 15:37:52 2019 - [warning] shutdown_script is not defined.
Thu Jun 20 15:37:52 2019 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.//Test success prompt

3) Start management service

[root@mgm57 ~]# masterha_ manager --conf=/etc/mha/app1. cnf  --remove_ dead_ master_ conf \--ignore_ last_ / / execute the fail command
Thu Jun 20 17:05:58 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 17:05:58 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu Jun 20 17:05:58 2019 - [info] Reading server configuration from /etc/mha/app1.cnf.. 

4) View service status

[root@mgm57 ~]#  masterha_check_status  --conf=/etc/mha/app1.cnf / / execute the command
app1 (pid:15806) is running(0:PING_OK), master:192.168.4.51 //Service running, monitoring the main server 192.168.4.51
[root@mgm57 ~]# ls /etc/mha / / / view the list of working directory files
app1.cnf  app1.master_status.health  manager.log  master_ip_failover

Step 2: access the cluster

1) Add a connected user accessing data to the main server 51

]# mysql  -uroot -p123qqq...A
mysql> create database db9;
Query OK, 1 row affected (0.05 sec)
mysql> create table db9.a (id int);
Query OK, 0 rows affected (0.63 sec)
mysql> grant select,insert on db9.* to yaya55@"%" identified by "123qqq...A";
Query OK, 0 rows affected, 1 warning (0.08 sec)
mysql>exit

2) The client 50 connects to the vip address to access the cluster

host50~]# mysql -h192.168.4.100  -uyaya55  -p123qqq...A
mysql> select  * from  db9.a;
mysql> insert into db9.a values(100);
mysql> select  * from db9.a;
+------+
| id   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)
mysql>exit

2) The client 50 connects to the vip address to access the cluster

host50~]# mysql -h192.168.4.100  -uyaya55  -p123qqq...A
mysql> select  * from  db9.a;
mysql> insert into db9.a values(100);
mysql> select  * from db9.a;
+------+
| id   |
+------+
|  100 |
+------+
1 row in set (0.00 sec)
mysql>exit

3) Viewing data from server host52

 [root@host52 ~]# mysql -uroot -p123qqq...A -e "select  * from db9.a"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|  100 |
+------+

4) Viewing data from server host53

[root@host53 ~]# mysql -uroot -p123qqq...A -e "select  * from db9.a"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|  100 |
+------+

Test high availability

11) Stop the mysql service of the master server 51

host51~]# systemctl  stop  mysqld

2) View the monitoring information output by the management service

 [root@mgm57~]#masterha_manager --conf=/etc/mha/app1.cnf  --remove_dead_master_conf \> --ignore_last_failover
Thu Jun 20 17:05:58 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 17:05:58 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu Jun 20 17:05:58 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
  Creating /var/tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /var/lib/mysql, up to master51.000002
Thu Jun 20 17:35:59 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 17:35:59 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu Jun 20 17:35:59 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
[root@host57 ~]#
[root@mgm57 ~]# masterha_check_status  --conf=/etc/mha/app1.cnf
app1 is stopped(2:NOT_RUNNING).  //Monitoring that the main server is down, the management service stops automatically
[root@mgm57 ~]#

3) The client is still connected to the vip address and can access the data

client50]# ping -c 2 192.168.4.100 / / can ping the vip address
PING 192.168.4.100 (192.168.4.100) 56(84) bytes of data.
64 bytes from 192.168.4.100: icmp_seq=1 ttl=255 time=0.222 ms
64 bytes from 192.168.4.100: icmp_seq=2 ttl=255 time=0.121 ms
--- 192.168.4.71 ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 999ms
rtt min/avg/max/mdev = 0.121/0.171/0.222/0.052 ms
client50]# mysql -h192.168.4.100 -uyaya55 -p123qqq...A / / connect to the vip address
mysql> insert into db9.a values(200); //insert record
mysql> select  * from db9.a;//Query record
+------+
| id   |
+------+
|  100 |
|  200 |
+------+

4) View vip address

Check the vip address on the host52 host, indicating that the host52 host is elected as the main server

 [root@host52 ~]# ifconfig  eth0:1
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.4.100  netmask 255.255.255.0  broadcast 192.168.4.255
        ether 52:54:00:f5:c4:6a  txqueuelen 1000  (Ethernet)

The vip address is not found in the host53 host, which indicates that the host53 host is the slave server of the current host52

 [root@host53 ~]# Ifconfig eth0:1 / / no vip address found
eth0:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        ether 52:54:00:28:22:2e  txqueuelen 1000  (Ethernet)
[root@host53 ~]# mysql -uroot -p123qqq...A -e "show slave status\G" | grep -i 192
mysql: [Warning] Using a password on the command line interface can be insecure.
                  Master_Host: 192.168.4.52  //Ip address of primary server
[root@host53 ~]#
[root@host53 ~]# mysql -uroot -p123qqq...A -e "show slave status\G" | grep -i yes
mysql: [Warning] Using a password on the command line interface can be insecure.
             Slave_IO_Running: Yes  //IO thread OK
            Slave_SQL_Running: Yes  //SQL thread OK
 [root@host53 ~]# mysql -uroot -p123qqq... A - e "select * from DB9. A" / / automatically synchronize data
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+
| id   |
+------+
|  100 |
|  200 |
+------+

Repair the failed server

1) Configure database server

Start the database service of host51 host

host51~]# systemctl  start  mysqld

Consistent with the master server data

[root@host52 ~]#  mysqldump -uroot -p123qqq... A  --master-data  db9 > db9. SQL / / perform a full backup on the primary server host52
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@host52 ~]#
[root@host52 ~]# scp db9. sql   root@192.168.4.51 : / root / / copy the backup file to host host51
db9.sql  100% 1918     3.1MB/s   00:00
[root@host52 ~]#
host51 ~]# mysql -uroot -p123qqq... A db9 < /root/db9. SQL / / host51 host uses backup files to recover data
mysql: [Warning] Using a password on the command line interface can be insecure.

Specify master server information

 [root@host51 ~]# grep master52 /root/db9.sql / / view the log name and offset
CHANGE MASTER TO MASTER_LOG_FILE='master52.000001', MASTER_LOG_POS=895;
[root@host51 ~]# mysql -uroot -p123qqq...A
mysql>change master to master_host="192.168.4.52",master_user="repluser",master_password="123qqq...A",master_log_file="master52.000001",master_log_pos=895;
Query OK, 0 rows affected, 2 warnings (0.14 sec)

Start the slave process

mysql> start slave;

View status information

[root@host51 ~]# mysql -uroot -p123qqq...A -e "show slave status\G" |grep 192.168.4.52
mysql: [Warning] Using a password on the command line interface can be insecure.
                  Master_Host: 192.168.4.52 //Primary server ip address
[root@host51 ~]#
[root@host51 ~]# mysql -uroot -p123qqq...A -e "show slave status\G" |grep -i yes
mysql: [Warning] Using a password on the command line interface can be insecure.
             Slave_IO_Running: Yes //IO thread status is normal
            Slave_SQL_Running: Yes //The SQL thread status is normal

2) Configuration management server

Modify the configuration file and add the database server host51

]# vim /etc/mha/app1.cnf
 [server1 ]
hostname=192.168.4.51
port=3306
candidate_master=1
:wq

Test cluster environment

[root@mgm57 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf / / test SSH
Thu Jun 20 15:33:48 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 15:33:48 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu Jun 20 15:33:48 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
Thu Jun 20 15:33:48 2019 - [info] Starting SSH connection tests..
Thu Jun 20 15:33:49 2019 - [debug]
Thu Jun 20 15:33:48 2019 - [debug]  Connecting via SSH from root@192.168.4.51(192.168.4.51:22) to root@192.168.4.52(192.168.4.52:22)..
Thu Jun 20 15:33:49 2019 - [debug]   ok.
Thu Jun 20 15:33:49 2019 - [debug]  Connecting via SSH from root@192.168.4.51(192.168.4.51:22) to root@192.168.4.53(192.168.4.53:22)..
Thu Jun 20 15:33:49 2019 - [debug]   ok.
Thu Jun 20 15:33:50 2019 - [debug]
Thu Jun 20 15:33:49 2019 - [debug]  Connecting via SSH from root@192.168.4.52(192.168.4.52:22) to root@192.168.4.51(192.168.4.51:22)..
Thu Jun 20 15:33:49 2019 - [debug]   ok.
Thu Jun 20 15:33:49 2019 - [debug]  Connecting via SSH from root@192.168.4.52(192.168.4.52:22) to root@192.168.4.53(192.168.4.53:22)..
Thu Jun 20 15:33:49 2019 - [debug]   ok.
Thu Jun 20 15:33:50 2019 - [debug]   ok.
Thu Jun 20 15:33:50 2019 - [debug]  Connecting via SSH from root@192.168.4.53(192.168.4.53:22) to root@192.168.4.52(192.168.4.52:22)..
Thu Jun 20 15:33:50 2019 - [debug]   ok.
Thu Jun 20 15:33:51 2019 - [info] All SSH connection tests passed successfully.//success
 [root@mgm57 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf / / test master-slave synchronization
Thu Jun 20 15:37:46 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..
Thu Jun 20 15:37:46 2019 - [info] MHA::MasterMonitor version 0.56.
Thu Jun 20 15:37:47 2019 - [info] GTID failover mode = 0
Thu Jun 20 15:37:47 2019 - [info] Dead Servers:
Thu Jun 20 15:37:47 2019 - [info] Alive Servers:
Thu Jun 20 15:37:47 2019 - [info]   192.168.4.51(192.168.4.51:3306)
Thu Jun 20 15:37:47 2019 - [info]   192.168.4.52(192.168.4.52:3306)
Thu Jun 20 15:37:47 2019 - [info]   192.168.4.53(192.168.4.53:3306)
Thu Jun 20 15:37:47 2019 - [info] Alive Slaves:
Thu Jun 20 15:37:47 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu Jun 20 15:37:47 2019 - [info]   192.168.4.53(192.168.4.53:3306)  Version=5.7.17-log (oldest major version between slaves) log-bin:enabled
Thu Jun 20 15:37:47 2019 - [info]     Replicating from 192.168.4.51(192.168.4.51:3306)
Thu Jun 20 15:37:47 2019 - [info]     Primary candidate for the new Master (candidate_master is set)
Thu Jun 20 15:37:47 2019 - [info] Current Alive Master: 192.168.4.51(192.168.4.51:3306)
Thu Jun 20 15:37:47 2019 - [info] Checking slave configurations..
Thu Jun 20 15:37:47 2019 - [info]  read_only=1 is not set on slave 192.168.4.52(192.168.4.52:3306).
Thu Jun 20 15:37:47 2019 - [info]  read_only=1 is not set on slave 192.168.4.53(192.168.4.53:3306).
Thu Jun 20 15:37:47 2019 - [info] Checking replication filtering settings..
Thu Jun 20 15:37:47 2019 - [info]  binlog_do_db= , binlog_ignore_db=
Thu Jun 20 15:37:47 2019 - [info]  Replication filtering check ok.
Thu Jun 20 15:37:47 2019 - [info] GTID (with auto-pos) is not supported
Thu Jun 20 15:37:47 2019 - [info] Starting SSH connection tests..
Thu Jun 20 15:37:49 2019 - [info] All SSH connection tests passed successfully.
Thu Jun 20 15:37:49 2019 - [info] Checking MHA Node version..
Thu Jun 20 15:37:50 2019 - [info]  Version check ok.
Thu Jun 20 15:37:50 2019 - [info] Checking SSH publickey authentication settings on the current master..
Thu Jun 20 15:37:50 2019 - [info] HealthCheck: SSH to 192.168.4.51 is reachable.
Thu Jun 20 15:37:50 2019 - [info] Master MHA Node version is 0.56.
Thu Jun 20 15:37:50 2019 - [info] Checking recovery script configurations on 192.168.4.51(192.168.4.51:3306)..
Thu Jun 20 15:37:50 2019 - [info]   Connecting to root@192.168.4.51(192.168.4.51:22)..
  Creating /var/tmp if not exists..    ok.
  Checking output directory is accessible or not..
   ok.
  Binlog found at /var/lib/mysql, up to master51.000002
Thu Jun 20 15:37:50 2019 - [info] Binlog setting check done.
Thu Jun 20 15:37:50 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Thu Jun 20 15:37:50 2019 - [info]   Connecting to root@192.168.4.52(192.168.4.52:22)..
  Checking slave recovery environment settings..
    Opening /var/lib/mysql/relay-log.info ... ok.
    Relay log found at /var/lib/mysql, up to host52-relay-bin.000006
    Temporary relay log file is /var/lib/mysql/host52-relay-bin.000006
    Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Thu Jun 20 15:37:51 2019 - [info]   Connecting to root@192.168.4.53(192.168.4.53:22)..
  Checking slave recovery environment settings..
    Opening /var/lib/mysql/relay-log.info ... ok.
    Relay log found at /var/lib/mysql, up to host53-relay-bin.000006
    Temporary relay log file is /var/lib/mysql/host53-relay-bin.000006
    Testing mysql connection and privileges..mysql: [Warning] Using a password on the command line interface can be insecure.
 done.
    Testing mysqlbinlog output.. done.
    Cleaning up test file(s).. done.
Thu Jun 20 15:37:52 2019 - [info] Slaves settings check done.
Thu Jun 20 15:37:52 2019 - [info]
192.168.4.51(192.168.4.51:3306) (current master)
 +--192.168.4.52(192.168.4.52:3306)
 +--192.168.4.53(192.168.4.53:3306)
Thu Jun 20 15:37:52 2019 - [info] Checking replication health on 192.168.4.52..
Thu Jun 20 15:37:52 2019 - [info]  ok.
Thu Jun 20 15:37:52 2019 - [info] Checking replication health on 192.168.4.53..
Thu Jun 20 15:37:52 2019 - [info]  ok.
Thu Jun 20 15:37:52 2019 - [info] Checking master_ip_failover_script status:
Thu Jun 20 15:37:52 2019 - [info]   /etc/mha/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.4.51 --orig_master_ip=192.168.4.51 --orig_master_port=3306
Thu Jun 20 15:37:52 2019 - [info]  OK.
Thu Jun 20 15:37:52 2019 - [warning] shutdown_script is not defined.
Thu Jun 20 15:37:52 2019 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK. //success

Restart management service

]# masterha_stop  --conf=/etc/mha/app1.cnf / / stop the management service
Stopped app1 successfully.
]# masterha_manager --conf=/etc/mha/app1.cnf  --remove_dead_master_conf \
--ignore_last_failover //Start management service
Thu Jun 20 17:05:58 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 17:05:58 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf..
Thu Jun 20 17:05:58 2019 - [info] Reading server configuration from /etc/mha/app1.cnf..

View status

mgm57 ~]# masterha_check_status  --conf=/etc/mha/app1.cnf
app1 (pid:15806) is running(0:PING_OK), master:192.168.4.52 //Service operation, monitoring server 52
[root@mgm57 ~]#

Jun 20 15:37:52 2019 - [warning] shutdown_script is not defined.
Thu Jun 20 15:37:52 2019 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK. // success

Restart management service



]# masterha_stop --conf=/etc/mha/app1.cnf / / stop the management service
Stopped app1 successfully.
]# masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf
–ignore_last_failover / / start the management service
Thu Jun 20 17:05:58 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Thu Jun 20 17:05:58 2019 - [info] Reading application default configuration from /etc/mha/app1.cnf...
Thu Jun 20 17:05:58 2019 - [info] Reading server configuration from /etc/mha/app1.cnf...

View status



mgm57 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:15806) is running(0:PING_OK), master:192.168.4.52. / / the service is running. Monitor the server 52
[root@mgm57 ~]#

Tags: Linux Database MySQL CentOS MHA

Posted by eva21 on Thu, 05 May 2022 22:26:27 +0300