MySQL之 MHA

集群 主从复制模式（ser2_master ser4_slave ser5-slave )
ser2_master 的 MySQL 数据库配置

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.01 sec)

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

mysql> create user repl@'%' identified by 'westos';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to repl@'%';
Query OK, 0 rows affected (0.01 sec)

ser4/5_slave 的 MySQL 数据库配置

mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user root@localhost identified by 'westos';
Query OK, 0 rows affected (0.01 sec)

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

mysql> change master to master_host='172.25.1.2', master_user='repl', master_password='westos', master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

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

mysql> show slave status\G;

#lftp 172.25.254.250
cd pub/docs/mysql/
mirror MHA-7
get master_ip_failover master_ip_online_change send_report
exit

scp master_ip_failover master_ip_online_change send_report ser1:/etc/mha/app1/
[root@ser1 app1]# chmod +x master_ip_failover master_ip_online_change # 添加可执行权限

scp -r MHA-7 ser1:~

[root@ser1 ~]# cd MHA-7/
[root@ser1 MHA-7]# ls

mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
mha4mysql-manager-0.58.tar.gz
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm

#yum install -y *.rpm
scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm ser2:~
scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm ser4:~
scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm ser5:~

#tar zxf mha4mysql-manager-0.58.tar.gz
#cd mha4mysql-manager-0.58
#ls

AUTHORS  COPYING  lib          MANIFEST       README  samples  tests
bin      debian   Makefile.PL  MANIFEST.SKIP  rpm     t

#cd samples/conf/
#ls

app1.cnf  masterha_default.cnf

#mkdir /etc/mha
#cat masterha_default.cnf app1.cnf > /etc/mha/app.cnf
#vim /etc/mha/app.cnf

条件一：SSH 免密登陆，远程访问node 文件

# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:oMwANssuiVmkyxbLYy9Cg9rtNn/9a7bj/13OjQZCmXs root@ser1
The key's randomart image is:
+---[RSA 2048]----+
|.o.              |
|o+o              |
|.+o   .    o     |
|*=o+ . .  +      |
|B@  +   S. .     |
|*.+.      o E    |
|o....    . o .  .|
|. ..o   . . + .++|
|   ..o..   ==*o.*|
+----[SHA256]-----+

# ssh-copy-id 172.25.1.1
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"
The authenticity of host '172.25.1.1 (172.25.1.1)' can't be established.
ECDSA key fingerprint is SHA256:9qMT14xsbX+MwfeYxgUhO6dsmJpxhljSnsFBuFxKbS0.
ECDSA key fingerprint is MD5:59:23:22:ca:80:4c:6d:1c:19:36:e7:78:12:27:f3:2c.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
root@172.25.1.1's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh '172.25.1.1'"
and check to make sure that only the key(s) you wanted were added.

# ls .ssh/
authorized_keys  id_rsa  id_rsa.pub  known_hosts
# scp -r .ssh/ ser2:
# scp -r .ssh/ ser4:
# scp -r .ssh/ ser5:

条件二：设置MySQL 远程访问的 'root’用户

[root@ser2 ~]# mysql -uroot -pwestos
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 11
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2020, 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> create user root@'%' identified by 'westos';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to root@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> 

测试：

masterha_check_repl --conf=/etc/mha/app.cnf

masterha_check_ssh --conf=/etc/mha/app.cnf

故障迁移

[root@ser1 app1]# vim master_ip_failover

[root@ser1 app1]# /etc/mha/app1/master_ip_failover --command=status --orig_master_host=ser2 --orig_master_ip=172.25.1.2 --orig_master_port=3306 --new_master_host=ser4 --new_master_ip=172.25.1.4 --new_master_port=3306

IN SCRIPT TEST====/sbin/ip addr del 172.25.1.100/24 dev eth0==/sbin/ip addr add 172.25.1.100/24 dev eth0===

Checking the Status of the script.. OK             # 手动测试状态 OK

vim /etc/mha/app.cnf

 master_ip_failover_script= /etc/mha/app1/master_ip_failover
 master_ip_online_change_script= /etc/mha/app1/master_ip_online_change

[root@ser1 app1]# vim master_ip_online_change

[root@ser1 app1]# nohup masterha_manager --conf=/etc/mha/app.cnf & # 开启监测，并后台运行
[1] 9671
[root@ser1 app1]# nohup: ignoring input and appending output to ‘nohup.out’ # 完成 故障迁移后 ，退出进程并生成日志 ‘nohup.out’

[1]+ Done nohup masterha_manager --conf=/etc/mha/app.cnf

当前所管理的集群 MySQL的 Master 上添加 vip : 172.25.1.2/24

[root@ser2 ~]# ip addr add 172.25.1.100/24 dev eth0
[root@ser2 ~]# ip addr show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 52:54:00:1d:d6:ef brd ff:ff:ff:ff:ff:ff
    inet 172.25.1.2/24 brd 172.25.1.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet 172.25.1.100/24 scope global secondary eth0
       valid_lft forever preferred_lft forever

测试一 ，MHA 自动故障迁移：
Shutting down 集群 Master (ser2) # 停用 ，模拟故障

[root@ser2 ~]# /etc/init.d/mysqld stop
Shutting down MySQL......... SUCCESS! 

[root@ser2 ~]# mysql -h 172.25.1.100 -uroot -pwestos            # 远程访问 vip 的 MySQL 数据库
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 8.0.21 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MySQL [(none)]> select @@hostname;                             # 当前 Master 用户 (已由 ser2 切换 ser4 )
+------------+
| @@hostname |
+------------+
| ser4       |                    
+------------+
1 row in set (0.00 sec)

测试二 ，MHA 手动故障迁移：

[root@ser2 ~]# mysql -uroot -pwestos
mysql> change master to master_host='172.25.1.3', master_user='repl', master_password='westos', master_auto_position=1;

[root@ser1 mha]# masterha_master_switch --help
Usage:
    # For master failover                                    # Master  非存活状态，执行以下操作

    masterha_master_switch --master_state=dead
    --global_conf=/etc/masterha_default.cnf
    --conf=/usr/local/masterha/conf/app1.cnf --dead_master_host=host1

    # For online master switch                               # Master  存活状态 ，执行以下操作

    masterha_master_switch --master_state=alive
    --global_conf=/etc/masterha_default.cnf
    --conf=/usr/local/masterha/conf/app1.cnf

    See online reference
    (http://code.google.com/p/mysql-master-ha/wiki/masterha_master_switch)
    for details.