systemctl stop firewalld
systemctl disable firewalld
setenforce 0

hostnamectl set-hostname host51 #4.51主機(jī)上執(zhí)行
hostnamectl set-hostname host52 #4.52主機(jī)上執(zhí)行
hostnamectl set-hostname host53 #4.53主機(jī)上執(zhí)行
hostnamectl set-hostname host54 #4.54主機(jī)上執(zhí)行

echo "192.168.4.51 host51" >> /etc/hosts
echo "192.168.4.52 host52" >> /etc/hosts
echo "192.168.4.53 host53" >> /etc/hosts
echo "192.168.4.54 host54" >> /etc/hosts

    [root@host51 ~]# tar -xvf mysql-5.7.17.tar   #v:顯示詳細(xì)信息
    [root@host51 ~]# yum -y install mysql-community-*.rpm
    [root@host51 ~]# systemctl restart mysqld         #啟動(dòng)服務(wù)
    [root@host51 ~]# systemctl enable mysqld
    [root@host51 ~]# ss -antlp | grep 3306
    [root@host51 ~]# ps -C mysqld         #查看進(jìn)程

    [root@host51 ~]# grep password /var/log/mysqld.log   #獲取初始密碼
    #重置密碼
    [root@host51 ~]# mysqladmin -uroot -p'qg1wpZ;G+deg' password '123qqq...A'   
    [root@host51 ~]# mysql -uroot -p123qqq...A

[root@host54 ~]# ssh-keygen -f /root/.ssh/id_rsa -N ''
[root@host54 ~]# ssh-copy-id root@host51
[root@host54 ~]# ssh-copy-id root@host52
[root@host54 ~]# ssh-copy-id root@host53

[root@host51 ~]# ssh-keygen -f /root/.ssh/id_rsa -N ''
[root@host51 ~]# ssh-copy-id root@host52
[root@host51 ~]# ssh-copy-id root@host53
[root@host52 ~]# ssh-keygen -f /root/.ssh/id_rsa -N ''
[root@host52 ~]# ssh-copy-id root@host51
[root@host52 ~]# ssh-copy-id root@host53
[root@host53 ~]# ssh-keygen -f /root/.ssh/id_rsa -N ''
[root@host53 ~]# ssh-copy-id root@host51
[root@host53 ~]# ssh-copy-id root@host52

[mysqld]
server_id=51
log_bin=master51
relay-log-purge = 0  # 禁止自動(dòng)刪除中繼日志（控制是否自動(dòng)刪除不再需要的中繼日志）

systemctl restart mysqld

mysql> grant replication slave on *.* to repluser@"%" identified by '123qqq...A';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master51.000001 |      441 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

[mysqld]
server_id=52
log_bin=master52
relay-log-purge = 0  # 禁止自動(dòng)刪除中繼日志（控制是否自動(dòng)刪除不再需要的中繼日志）

[mysqld]
server_id=53
log_bin=master53
relay-log-purge = 0  # 禁止自動(dòng)刪除中繼日志（控制是否自動(dòng)刪除不再需要的中繼日志）

[root@host52 ~]# systemctl restart mysqld
[root@host53 ~]# systemctl restart mysqld

mysql> change master to master_host='192.168.4.51',master_user='repluser',master_password='123qqq...A',master_log_file='master51.000001',master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.4.51
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master51.000001
          Read_Master_Log_Pos: 441
               Relay_Log_File: host52-relay-bin.000002
                Relay_Log_Pos: 319
        Relay_Master_Log_File: master51.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....................

mysql> change master to master_host="192.168.4.51",master_user="repluser",master_password="123qqq...A",master_log_file="master51.000001",master_log_pos=441;
Query OK, 0 rows affected, 2 warnings (0.02 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.4.51
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master51.000001
          Read_Master_Log_Pos: 441
               Relay_Log_File: host53-relay-bin.000002
                Relay_Log_Pos: 319
        Relay_Master_Log_File: master51.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
....................            

grant replication slave on *.* to repluser@"%" identified by "123qqq...A";

# 安裝依賴
# 如果 ncftp 不可用，可以跳過
[root@host51 mysql]# yum -y install perl-DBD-MySQL ncftp perl-DBIx-Simple
[root@host52 mysql]# yum -y install perl-DBD-MySQL nfctp perl-DBIx-Simple
[root@host53 mysql]# yum -y install perl-DBD-MySQL nfctp perl-DBIx-Simple
# 使用 Yum 安裝 MHA Node
[root@host51 mysql]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@host52 mysql]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@host53 mysql]# yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm

啟用 EPEL 倉庫
yum -y install epel-release-latest-7.noarch.rpm
yum clean all
yum repolist

# 安裝依賴
yum install -y perl-DBD-MySQL perl-Config-IniFiles perl-Time-HiRes perl-Parallel-ForkManager ncftp perl-Log-Dispatch perl-DBI mha4mysql-node-0.58-0.el7.centos.noarch.rpm
yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

mkdir -p /etc/mha
mkdir -p /var/log/mha

[server default]
# [server default] 段 - 全局默認(rèn)配置
# MySQL 用戶和密碼
# MHA Manager 連接 MySQL 節(jié)點(diǎn)時(shí)使用的用戶名和密碼
# 需要在所有 MySQL 節(jié)點(diǎn)上創(chuàng)建這個(gè)用戶并授予相應(yīng)權(quán)限
user=mha
password=123qqq...A
# MySQL 主從復(fù)制使用的用戶名和密碼
# 用于在故障切換時(shí)在新主庫上配置復(fù)制關(guān)系
repl_user=repluser
repl_password=123qqq...A
# SSH 配置
ssh_user=root
# MHA Manager 工作目錄
manager_workdir=/var/log/mha
manager_log=/var/log/mha/manager.log
# 遠(yuǎn)程工作目錄（在 MySQL 節(jié)點(diǎn)上）
remote_workdir=/var/log/mha
# 主從復(fù)制相關(guān)配置
ping_interval=3
# 二次檢查，防止腦裂
secondary_check_script=masterha_secondary_check -s host52 -s host53 -s host51
master_binlog_dir=/var/lib/mysql
# 故障切換腳本（可選）
#master_ip_failover_script=/etc/mha/master_ip_failover
[server1]
hostname=host51
candidate_master=1
[server2]
hostname=host52
candidate_master=1
[server3]
hostname=host53
no_master=1 # 這個(gè)節(jié)點(diǎn)永遠(yuǎn)不會(huì)被提升為新的 Master

mysql> grant all on *.* to mha@"%" identified by '123qqq...A';
Query OK, 0 rows affected, 1 warning (0.03 sec)

# 檢查 SSH 連接
masterha_check_ssh --conf=/etc/mha/app1.cnf
# 檢查主從復(fù)制狀態(tài)
masterha_check_repl --conf=/etc/mha/app1.cnf

mkdir -p /var/log/mha
nohup masterha_manager --conf=/etc/mha/app1.cnf < /dev/null > /var/log/mha/manager.log 2>&1 &

[root@host54 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:12835) is running(0:PING_OK), master:host51

在master節(jié)點(diǎn)執(zhí)行
systemctl stop mysqld

在 host54 上驗(yàn)證
[root@host54 ~]# tail -f /var/log/mha/manager.log
Started automated(non-interactive) failover.
The latest slave host52(192.168.4.52:3306) has all relay logs for recovery.
Selected host52(192.168.4.52:3306) as a new master.
host52(192.168.4.52:3306): OK: Applying all logs succeeded.
host53(192.168.4.53:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
host53(192.168.4.53:3306): OK: Applying all logs succeeded. Slave started, replicating from host52(192.168.4.52:3306)
host52(192.168.4.52:3306): Resetting slave info succeeded.
Master failover to host52(192.168.4.52:3306) completed successfully.

[root@host53 mysql]# mysql -h host52 -u mha -p123qqq...A -e "show master status"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master52.000001 |      441 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+

[root@host52 mysql]# mysql -h host53 -u mha -p123qqq...A -e "show slave status\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.4.52
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master52.000001
          Read_Master_Log_Pos: 441
               Relay_Log_File: host53-relay-bin.000002
                Relay_Log_Pos: 319
        Relay_Master_Log_File: master52.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
..............................        

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database d2;
Query OK, 1 row affected (0.02 sec)
mysql> create table d2.student(name char(10),number int default 10);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into d2.student values("bob",1);
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |                |
| d2                 |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
mysql> select * from d2.student;
+------+--------+
| name | number |
+------+--------+
| bob  |      1 |
+------+--------+
1 row in set (0.00 sec)

[root@host52 ~]# mysqldump -uroot -p123456 --all-databases --master-data=1 > backup.sql
[root@host52 ~]# scp backup.sql host51:/root/

[root@host51 ~]# systemctl start mysqld
[root@host51 ~]# mysql -uroot -p123456 < backup.sql 
[root@host51 ~]# mysql -uroot -p123456
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |               |
| d2                 |               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
7 rows in set (0.00 sec)
mysql> select * from d2.student;
+------+--------+
| name | number |
+------+--------+
| bob  |      1 |
+------+--------+
1 row in set (0.00 sec)

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| master52.000001 |     1046 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

-- 在舊 Master 上執(zhí)行
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=1046;
Query OK, 0 rows affected, 2 warnings (0.01 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.4.52
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master52.000001
          Read_Master_Log_Pos: 1046
               Relay_Log_File: host51-relay-bin.000002
                Relay_Log_Pos: 319
        Relay_Master_Log_File: master52.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

mysql> create database d3;
Query OK, 1 row affected (0.01 sec)
mysql> create table d3.student(name char(10),number int default 10);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into d3.student values("tom",2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from d3.student;
+------+--------+
| name | number |
+------+--------+
| tom  |      2 |
+------+--------+
1 row in set (0.01 sec)