MySQL高可用集群架构-MHA架构
该软件由两部分组成：MHA Manager（管理节点）和MHA Node（数据节点）。MHA Manager可以单独部署在一台独立的机器上管理多个master-slave集群，也可以部署在一台slave节点上。MHA Node运行在每台MySQL服务器上，MHA Manager会定时探测集群中的master节点，当master出现故障时，它可以自动将最新数据的slave提升为新的master，然后将所有其他的slave重新指向新的master。整个故障转移过程对应用程序完全透明。MHA可以与半同步复制结合起来。如果只有一个slave已经收到了最新的二进制日志，MHA可以将最新的二进制日志应用于其他所有的slave服务器上，因此可以保证所有节点的数据一致性。
MHA工作过程：（1）从宕机崩溃的master保存二进制日志事件（binlog events）;
（2）识别含有最新更新的slave；
（3）应用差异的中继日志（relay log）到其他的slave；
（4）应用从master保存的二进制日志事件（binlog events）；
（5）提升一个slave为新的master；
（6）使其他的slave连接新的master进行复制；
全部机器都是为rhel7
IP | 主机名 | 角色 |
192.168.88.67 | server67 | Manager |
192.168.88.68 | server68 | Master |
192.168.88.69 | server69 | Slave1,candicate master |
192.168.88.70 | server70 | Slave |
在全部的节点安装mha-node
（1）保持充足的yum源 #vim aliyun.repo [aliyun-os] name=aliyun-os baseurl=https://mirrors.aliyun.com/centos/7/os/x86_64/ enabled=1 gpgcheck=0 [aliyun-epel] name=aliyun-epel baseurl=https://mirrors.aliyun.com/epel/7/x86_64/ enabled=1 gpgcheck=0 [aliyun-extra] name=aliyun-extra baseurl=https://mirrors.aliyun.com/centos/7/extras/x86_64/ enabled=1 gpgcheck=0 #yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN -y #rz #ls mha4mysql-node-0.54-0.el6.noarch.rpm #rpm -ivh mha4mysql-node-0.54-0.el6.noarch.rpm #cd /usr/bin # ll app* filter* purge* save* -r-xr-xr-x 1 root root 15498 Apr 20 10:05 apply_diff_relay_logs -r-xr-xr-x 1 root root 4807 Apr 20 10:05 filter_mysqlbinlog -r-xr-xr-x 1 root root 7401 Apr 20 10:05 purge_relay_logs -r-xr-xr-x 1 root root 7263 Apr 20 10:05 save_binary_logs
在192.168.88.67安装Mangaer管理节点
[root@server67 ~]# yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker perl-CPAN -y [root@server67 ~]# rz [root@server67 ~]# rpm -ivh mha4mysql-manager-0.55-0.el6.noarch.rpm 准备中... ################################# [100%] 正在升级/安装... 1:mha4mysql-manager-0.55-0.el6 ################################# [100%] [root@server67 ~]# ll /usr/bin/mast* masterha_check_ssh 检查MHA的SSH配置状况 masterha_check_repl 检查MySQL复制状况 masterha_manger 启动MHA masterha_check_status 检测当前MHA运行状态 masterha_master_monitor 检测master是否宕机 masterha_master_switch 控制故障转移（自动或者手动） masterha_conf_host 添加或删除配置的server信息
各个主机之间相互进行ssh免密登录
[root@server67 ~]# ssh-keygen -t rsa Generating public/private rsa key pair. Enter file in which to save the key (/root/.ssh/id_rsa): Created directory '/root/.ssh'. 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: b6:d5:97:25:6d:6d:f6:e1:77:54:3d:3c:71:fb:64:5c root@server67 The key's randomart image is: +--[ RSA 2048]----+ | ..E| | *B| | .o#| | . .@+| | S . . o.=| | . o . o| | . | | | | | +-----------------+ [root@server67 ~]# [root@server67 ~]# ssh-copy-id 192.168.88.68 The authenticity of host '192.168.88.68 (192.168.88.68)' can't be established. ECDSA key fingerprint is 07:6c:ee:2d:a6:22:ef:59:0a:e3:a4:ed:4e:ed:4b:4b. 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@192.168.88.68's password: Number of key(s) added: 1 Now try logging into the machine, with: "ssh '192.168.88.68'" and check to make sure that only the key(s) you wanted were added. [root@server67 ~]# ssh-copy-id 192.168.88.69 [root@server67 ~]# ssh-copy-id 192.168.88.70
在192.168.88.68配置主数据库服务器
[root@server68 ~]# mysql -uroot -p mysql> create database pekeka; Query OK, 1 row affected (0.00 sec) mysql> use pekeka; Database changed mysql> create table test(id int,name varchar(20)); Query OK, 0 rows affected (0.00 sec) mysql> exit [root@server68 ~]# vim /etc/my.cnf ... log-bin=mysql-bin-master #启用二进制日志 server-id=1 #本机数据库ID 标示 binlog-do-db=pekeka #可以被从服务器复制的库，二进制需要同步的数据库名 binlog-ignore-db=mysql #不可以被从服务器复制的库 [root@server68 ~]# systemctl restart mysqld [root@server68 ~]# mysql -uroot -p mysql> grant replication slave on *.* to repl@'192.168.88.%' identified by 'GuangZhou_123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> grant replication slave on *.* to root@'192.168.88.%' identified by 'GuangZhou_123'; #监控用户 mysql> flush privileges; mysql> show master status; [root@server68 ~]# mysqldump -uroot -p -B pekeka >pekeka.sql #导出数据库 并且传到两个从数据库 [root@server68 ~]# scp pekeka.sql 192.168.88.69:~ pekeka.sql 100% 1930 1.9KB/s 00:00 [root@server68 ~]# scp pekeka.sql 192.168.88.70:~ pekeka.sql
在192.168.88.69导入数据库并配置从服务：
[root@server69 ~]# mysql -uroot -pJaking@vip.163.com < pekeka.sql [root@server69 ~]# vim /etc/my.cnf ... log-bin=mysql-slave1 #启用二进制日志 server-id=2 #本机数据库ID 标示 binlog-do-db=pekeka #可以被从服务器复制的库，二进制需要同步的数据库名 binlog-ignore-db=mysql #不可以被从服务器复制的库 log_slave_updates=1 #只有开启log_slave_updates，从库binlog才会记录主库同步的操作日志 [root@server69 ~]# systemctl restart mysqld [root@server69 ~]# mysql -uroot -p mysql> grant replication slave on *.* to 'repl'@'192.168.88.%' identified by 'GuangZhou_123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host='192.168.88.68',master_user='repl',master_password='GuangZhou_123'; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *.* to root@'192.168.88.%' identified by 'GuangZhou_123'; #监控用户 mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.88.68 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-master.000001 Read_Master_Log_Pos: 448 Relay_Log_File: server69-relay-bin.000002 Relay_Log_Pos: 675 Relay_Master_Log_File: mysql-bin-master.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | pekeka | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> set global read_only=1; ##设置从服务器只读 Query OK, 0 rows affected (0.00 sec)
在192.168.88.70导入数据库并配置从服务：
[root@server70 ~]# mysql -uroot -p < pekeka.sql [root@server70 ~]# vim /etc/my.cnf ... log-bin=mysql-slave2 #启用二进制日志 server-id=3 #本机数据库ID 标示 binlog-do-db=pekeka #可以被从服务器复制的库，二进制需要同步的数据库名 binlog-ignore-db=mysql #不可以被从服务器复制的库 log_slave_updates=1 #只有开启log_slave_updates，从库binlog才会记录主库同步的操作日志 [root@server70 ~]# systemctl restart mysqld [root@server70 ~]# mysql -uroot -p mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | pekeka | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> grant replication slave on *.* to 'repl'@'192.168.88.%' identified by 'GuangZhou_123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> change master to master_host='192.168.88.68',master_user='repl',master_password='GuangZhou_123'; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> grant replication slave on *.* to root@'192.168.88.%' identified by 'GuangZhou_123'; #监控用户 mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.88.68 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin-master.000001 Read_Master_Log_Pos: 448 Relay_Log_File: server69-relay-bin.000002 Relay_Log_Pos: 675 Relay_Master_Log_File: mysql-bin-master.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes mysql> set global read_only=1; ##设置从服务器只读 Query OK, 0 rows affected (0.00 sec)
测试MySQL主从状态
192.168.88.68： mysql> use pekeka; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> INSERT INTO test(id,name) VALUES('1','Linux'); Query OK, 1 row affected (0.00 sec) mysql>exit 192.168.88.69 mysql> use pekeka Database changed mysql> show tables; +------------------+ | Tables_in_pekeka | +------------------+ | test | +------------------+ 1 row in set (0.00 sec) mysql> select * from test; +------+-------+ | id | name | +------+-------+ | 1 | Linux | +------+-------+ 1 row in set (0.00 sec) 192.168.88.70 mysql> use pekeka Database changed mysql> show tables; +------------------+ | Tables_in_pekeka | +------------------+ | test | +------------------+ 1 row in set (0.00 sec) mysql> select * from test; +------+-------+ | id | name | +------+-------+ | 1 | Linux | +------+-------+ 1 row in set (0.00 sec)
配置MHA
创建MHA的工作目录，并且创建相关配置文件 [root@server67 bin]# mkdir -p /etc/masterha [root@server67 bin]# mkdir -p /var/log/masterha/app1 [root@server67 bin]# vim /etc/masterha/app1.cnf [server default] manager_workdir=/var/log/masterha/app1 //设置manager的工作目录 manager_log=/var/log/masterha/app1/manager.log //设置manager的日志 master_binlog_dir=/data/mysql master_ip_failover_script=/usr/local/bin/master_ip_failover //设置自动failover时候的切换脚本 master_ip_online_change_script=/usr/local/bin/master_ip_online_change //设置手动切换时候的切换脚本 password=GuangZhou_123 #配置监控用户的密码 user=root ping_interval=1 remote_workdir=/tmp repl_password=GuangZhou_123 ##用户repl的密码 repl_user=repl report_script=/usr/local/send_report shutdown_script="" ssh_user=root [server1] hostname=192.168.88.68 port=3306 [server2] hostname=192.168.88.69 port=3306 candidate_master=1 check_repl_delay=0 [server3] hostname=192.168.88.70 port=3306 在从数据库中关闭中继日志自动删除功能：mysql> set global relay_log_purge=0 [root@server67 bin]# masterha_check_ssh --conf=/etc/masterha/app1.cnf ##检查Mangaer和各个节点node的状态 Tue Nov 22 17:10:39 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Nov 22 17:10:39 2022 - [info] Reading application default configurations from /etc/masterha/app1.cnf.. Tue Nov 22 17:10:39 2022 - [info] Reading server configurations from /etc/masterha/app1.cnf.. Tue Nov 22 17:10:39 2022 - [info] Starting SSH connection tests.. Tue Nov 22 17:10:40 2022 - [debug] Tue Nov 22 17:10:39 2022 - [debug] Connecting via SSH from root@192.168.88.68(192.168.88.68:22) to root@192.168.88.69(192.168.88.69:22).. Tue Nov 22 17:10:39 2022 - [debug] ok. Tue Nov 22 17:10:39 2022 - [debug] Connecting via SSH from root@192.168.88.68(192.168.88.68:22) to root@192.168.88.70(192.168.88.70:22).. Tue Nov 22 17:10:39 2022 - [debug] ok. Tue Nov 22 17:10:40 2022 - [debug] Tue Nov 22 17:10:40 2022 - [debug] Connecting via SSH from root@192.168.88.69(192.168.88.69:22) to root@192.168.88.68(192.168.88.68:22).. Tue Nov 22 17:10:40 2022 - [debug] ok. Tue Nov 22 17:10:40 2022 - [debug] Connecting via SSH from root@192.168.88.69(192.168.88.69:22) to root@192.168.88.70(192.168.88.70:22).. Tue Nov 22 17:10:40 2022 - [debug] ok. Tue Nov 22 17:10:41 2022 - [debug] Tue Nov 22 17:10:40 2022 - [debug] Connecting via SSH from root@192.168.88.70(192.168.88.70:22) to root@192.168.88.68(192.168.88.68:22).. Tue Nov 22 17:10:40 2022 - [debug] ok. Tue Nov 22 17:10:40 2022 - [debug] Connecting via SSH from root@192.168.88.70(192.168.88.70:22) to root@192.168.88.69(192.168.88.69:22).. Tue Nov 22 17:10:40 2022 - [debug] ok. Tue Nov 22 17:10:41 2022 - [info] All SSH connection tests passed successfully.
实现：
1.设置vip [root@server68 ~]# ifconfig ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.88.68 netmask 255.255.255.0 broadcast 192.168.88.255 inet6 fe80::250:56ff:fe25:cd42 prefixlen 64 scopeid 0x20<link> ether 00:50:56:25:cd:42 txqueuelen 1000 (Ethernet) RX packets 315122 bytes 277695943 (264.8 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 856699 bytes 2003039238 (1.8 GiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536 inet 127.0.0.1 netmask 255.0.0.0 inet6 ::1 prefixlen 128 scopeid 0x10<host> loop txqueuelen 1 (Local Loopback) RX packets 0 bytes 0 (0.0 B) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 0 bytes 0 (0.0 B) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 [root@server68 ~]# ip addr add 192.168.88.65/24 brd 192.168.88.255 dev ens33 label ens33:1 [root@server68 ~]# arping -c 1 192.168.88.65 ARPING 192.168.88.65 from 192.168.88.68 ens33 Sent 1 probes (1 broadcast(s)) Received 0 response(s) [root@server68 ~]# ifconfig ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.88.68 netmask 255.255.255.0 broadcast 192.168.88.255 inet6 fe80::250:56ff:fe25:cd42 prefixlen 64 scopeid 0x20<link> ether 00:50:56:25:cd:42 txqueuelen 1000 (Ethernet) RX packets 315122 bytes 277695943 (264.8 MiB) RX errors 0 dropped 0 overruns 0 frame 0 TX packets 856699 bytes 2003039238 (1.8 GiB) TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0 ens33:1: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500 inet 192.168.88.65 netmask 255.255.255.0 broadcast 192.168.88.255 ether 00:50:56:25:cd:42 txqueuelen 1000 (Ethernet) 2.编写自动切换脚本 [root@server67 bin]# vim /usr/local/bin/master_ip_failover #!/usr/bin/env perl 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.88.65'; my $brdc = '192.168.88.255'; my $ifdev = 'ens33'; my $key = '1'; 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:$key $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"; } 出现了一堆的错误，一直报错 8 --orig_master_ip=192.168.88.68 --orig_master_port=3306 Backslash found where operator expected at /usr/local/bin/master_ip_failover line 73, near "$ssh_user\" Tue Nov 22 20:47:53 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln216] Failed to get master_ip_failover_script status with return code 255:0. Tue Nov 22 20:47:53 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln386] Error happend on checking configurations. at /usr/bin/masterha_check_repl line 48. Tue Nov 22 20:47:53 2022 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln482] Error happened on monitoring servers. 总结：（1）确保所有的依赖安装完成 （2）主从数据库全部都要授权给Manager （3）可能是自动漂移脚本存在格式问题，或者没有执行权限 [root@server67 bin]#rz [root@server67 bin]#ls master_ip_failover [root@server67 bin]#vim master_ip_failover #!/usr/bin/env perl # Copyright (C) 2011 DeNA Co.,Ltd. # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA ## Note: This is a sample script and is not complete. Modify the script based on your environment. use strict; use warnings FATAL => 'all'; use Getopt::Long; use MHA::DBHelper; my ( $command, $ssh_user, $orig_master_host, $orig_master_ip, $orig_master_port, $new_master_host, $new_master_ip, $new_master_port, $new_master_user, $new_master_password ); my $vip = '192.168.88.65/24'; my $key = '1'; my $ssh_start_vip = "/sbin/ifconfig bond0:$key $vip"; my $ssh_stop_vip = "/sbin/ifconfig bond0:$key down"; 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, 'new_master_user=s' => \$new_master_user, 'new_master_password=s' => \$new_master_password, ); 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"; }; 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 \"`; } sub stop_vip() { return 0 unless ($ssh_user); `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"; } Tue Nov 22 21:22:14 2022 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Tue Nov 22 21:22:14 2022 - [info] Reading application default configurations from /etc/masterha/app1.cnf.. Tue Nov 22 21:22:14 2022 - [info] Reading server configurations from /etc/masterha/app1.cnf.. Tue Nov 22 21:22:14 2022 - [info] MHA::MasterMonitor version 0.55. Tue Nov 22 21:22:15 2022 - [info] Dead Servers: Tue Nov 22 21:22:15 2022 - [info] Alive Servers: Tue Nov 22 21:22:15 2022 - [info] 192.168.88.68(192.168.88.68:3306) Tue Nov 22 21:22:15 2022 - [info] 192.168.88.69(192.168.88.69:3306) Tue Nov 22 21:22:15 2022 - [info] 192.168.88.70(192.168.88.70:3306) Tue Nov 22 21:22:15 2022 - [info] Alive Slaves: Tue Nov 22 21:22:15 2022 - [info] 192.168.88.69(192.168.88.69:3306) Version=5.7.24-log (oldest major version between slaves) log-bin:enabled Tue Nov 22 21:22:15 2022 - [info] Replicating from 192.168.88.68(192.168.88.68:3306) Tue Nov 22 21:22:15 2022 - [info] Primary candidate for the new Master (candidate_master is set) Tue Nov 22 21:22:15 2022 - [info] 192.168.88.70(192.168.88.70:3306) Version=5.7.24-log (oldest major version between slaves) log-bin:enabled Tue Nov 22 21:22:15 2022 - [info] Replicating from 192.168.88.68(192.168.88.68:3306) Tue Nov 22 21:22:15 2022 - [info] Current Alive Master: 192.168.88.68(192.168.88.68:3306) Tue Nov 22 21:22:15 2022 - [info] Checking slave configurations.. Tue Nov 22 21:22:15 2022 - [info] Checking replication filtering settings.. Tue Nov 22 21:22:15 2022 - [info] binlog_do_db= pekeka, binlog_ignore_db= mysql Tue Nov 22 21:22:15 2022 - [info] Replication filtering check ok. Tue Nov 22 21:22:15 2022 - [info] Starting SSH connection tests.. Tue Nov 22 21:22:17 2022 - [info] All SSH connection tests passed successfully. Tue Nov 22 21:22:17 2022 - [info] Checking MHA Node version.. Tue Nov 22 21:22:17 2022 - [info] Version check ok. Tue Nov 22 21:22:17 2022 - [info] Checking SSH publickey authentication settings on the current master.. Tue Nov 22 21:22:17 2022 - [info] HealthCheck: SSH to 192.168.88.68 is reachable. Tue Nov 22 21:22:17 2022 - [info] Master MHA Node version is 0.54. Tue Nov 22 21:22:17 2022 - [info] Checking recovery script configurations on the current master.. Tue Nov 22 21:22:17 2022 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/var/lib/mysql --output_file=/tmp/save_binary_logs_test --manager_version=0.55 --start_file=mysql-bin-master.000001 Tue Nov 22 21:22:17 2022 - [info] Connecting to root@192.168.88.68(192.168.88.68).. Creating /tmp if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /var/lib/mysql, up to mysql-bin-master.000001 Tue Nov 22 21:22:18 2022 - [info] Master setting check done. Tue Nov 22 21:22:18 2022 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Tue Nov 22 21:22:18 2022 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.88.69 --slave_ip=192.168.88.69 --slave_port=3306 --workdir=/tmp --target_version=5.7.24-log --manager_version=0.55 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Tue Nov 22 21:22:18 2022 - [info] Connecting to root@192.168.88.69(192.168.88.69:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to server69-relay-bin.000002 Temporary relay log file is /var/lib/mysql/server69-relay-bin.000002 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. Tue Nov 22 21:22:18 2022 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.88.70 --slave_ip=192.168.88.70 --slave_port=3306 --workdir=/tmp --target_version=5.7.24-log --manager_version=0.55 --relay_log_info=/var/lib/mysql/relay-log.info --relay_dir=/var/lib/mysql/ --slave_pass=xxx Tue Nov 22 21:22:18 2022 - [info] Connecting to root@192.168.88.70(192.168.88.70:22).. Checking slave recovery environment settings.. Opening /var/lib/mysql/relay-log.info ... ok. Relay log found at /var/lib/mysql, up to server70-relay-bin.000002 Temporary relay log file is /var/lib/mysql/server70-relay-bin.000002 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. Tue Nov 22 21:22:18 2022 - [info] Slaves settings check done. Tue Nov 22 21:22:18 2022 - [info] 192.168.88.68 (current master) +--192.168.88.69 +--192.168.88.70 Tue Nov 22 21:22:18 2022 - [info] Checking replication health on 192.168.88.69.. Tue Nov 22 21:22:18 2022 - [info] ok. Tue Nov 22 21:22:18 2022 - [info] Checking replication health on 192.168.88.70.. Tue Nov 22 21:22:18 2022 - [info] ok. Tue Nov 22 21:22:18 2022 - [info] Checking master_ip_failover_script status: Tue Nov 22 21:22:18 2022 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.88.68 --orig_master_ip=192.168.88.68 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig bond0:1 down==/sbin/ifconfig bond0:1 192.168.88.65/24=== Checking the Status of the script.. OK Tue Nov 22 21:22:18 2022 - [info] OK. Tue Nov 22 21:22:18 2022 - [warning] shutdown_script is not defined. Tue Nov 22 21:22:18 2022 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK. 2.开启MHA Manager监控 [root@server67 bin]# nohup masterha_manager --conf=/etc/masterha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 & [1] 4303 [root@server67 bin]# masterha_check_status --conf=/etc/masterha/app1.cnf app1 (pid:4303) is running(0:PING_OK), master:192.168.88.68 3.查看启动日志 [root@server67 bin]# tail -n 20 /var/log/masterha/app1/manager.log Cleaning up test file(s).. done. Tue Nov 22 21:23:58 2022 - [info] Slaves settings check done. Tue Nov 22 21:23:58 2022 - [info] 192.168.88.68 (current master) +--192.168.88.69 +--192.168.88.70 Tue Nov 22 21:23:58 2022 - [info] Checking master_ip_failover_script status: Tue Nov 22 21:23:58 2022 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.88.68 --orig_master_ip=192.168.88.68 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig bond0:1 down==/sbin/ifconfig bond0:1 192.168.88.65/24=== Checking the Status of the script.. OK Tue Nov 22 21:23:58 2022 - [info] OK. Tue Nov 22 21:23:58 2022 - [warning] shutdown_script is not defined. Tue Nov 22 21:23:58 2022 - [info] Set master ping interval 1 seconds. Tue Nov 22 21:23:58 2022 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes. Tue Nov 22 21:23:58 2022 - [info] Starting ping health check on 192.168.88.68(192.168.88.68:3306).. Tue Nov 22 21:23:58 2022 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond.. 4.模拟故障 [root@server68 bin]# systemctl start mysqld [root@server67 bin]# tail -f /var/log/masterha/app1/manager.log app1: MySQL Master failover 192.168.88.68 to 192.168.88.69 succeeded Master 192.168.88.68 is down! Check MHA Manager logs at server67:/var/log/masterha/app1/manager.log for details. Started automated(non-interactive) failover. Invalidated master IP address on 192.168.88.68. The latest slave 192.168.88.69(192.168.88.69:3306) has all relay logs for recovery. Selected 192.168.88.69 as a new master. 在192.168.88.70中查看 mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.88.69 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-slave1.000001 Read_Master_Log_Pos: 1157 Relay_Log_File: server70-relay-bin.000002 Relay_Log_Pos: 323 Relay_Master_Log_File: mysql-slave1.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes 证明ip漂移成功，主数据库成功转移到了192.168.88.69 在主库中插入信息：mysql> INSERT INTO test(id,name) VALUES('2','aliyun'); Query OK, 1 row affected (0.00 sec) 在从库中一样可以发现信息： mysql> select * from test; +------+--------+ | id | name | +------+--------+ | 1 | Linux | | 2 | aliyun | +------+--------+ 2 rows in set (0.00 sec)
