搭建MHA实现MySQL集群高可用
MHA：Master High Availability，对主节点进行监控，可实现自动故障转移至其它从节点；通过提升某一从节点为新的主节点，基于主从复制实现，还需要客户端配合实现，目前MHA主要支持一主多从的架构，要搭建MHA,要求一个复制集群中必须最少有三台数据库服务器，一主二从，即一台充当master，一台充当备用master，另外一台充当从库。 MHA工作原理： MHA利用 SELECT 1 As Value 指令判断master服务器的健康性,一旦master 宕机,MHA 从宕机崩溃的master保存二进制日志事件（binlog events） 识别含有最新更新的slave 应用差异的中继日志（relay log）到其他的slave 应用从master保存的二进制日志事件（binlog events） 提升一个slave为新的master 使其他的slave连接新的master进行复制 MHA软件 MHA软件由两部分组成，Manager工具包和Node工具包 Manager工具包主要包括以下几个工具： masterha_check_sshmasterha_check_replmasterha_mangermasterha_check_statusmasterha_master_monitormasterha_master_switchmasterha_conf_hostmasterha_stop--conf=app1.cnfmasterha_secondary_check #检查MHA的SSH配置状况#检查MySQL复制状况#启动MHA#检测当前MHA运行状态#检测master是否宕机#故障转移（自动或手动）#添加或删除配置的server信息#停止MHA#两个或多个网络线路检查MySQL主服务器的可用 Node工具包： 这些工具通常由MHA Manager的脚本触发，无需人为操作）主要包括以下几个工具： save_binary_logs apply_diff_relay_logs filter_mysqlbinlog purge_relay_logs #保存和复制master的二进制日志#识别差异的中继日志事件并将其差异的事件应用于其他的slave#去除不必要的ROLLBACK事件（MHA已不再使用此工具）#清除中继日志（不会阻塞SQL线程） MHA自定义扩展： secondary_check_script master_ip_ailover_script shutdown_script report_script init_conf_load_scriptmaster_ip_online_change_script #通过多条网络路由检测master的可用性#更新Application使用的masterip#强制关闭master节点#发送报告#加载初始配置参数#更新master节点ip地址 MHA配置文件： global配置，为各application提供默认配置，默认文件路径 /etc/masterha_default.cnfapplication配置：为每个主从复制集群 实现MHA实战案例 环境：四台主机 10.0.0.7 CentOS7 MHA管理端10.0.0.8 CentOS8 MySQL8.0 Master10.0.0.18CentOS8 MySQL8.0 Slave110.0.0.28 CentOS8 MySQL8.0 Slave2 1.在管理节点上安装两个包mha4mysql-manager和mha4mysql-node 说明： mha4mysql-manager-0.56-0.el6.noarch.rpm 不支持CentOS 8，只支持CentOS7 以下版本mha4mysql-manager-0.58-0.el7.centos.noarch.rpm 支持MySQL5.7和MySQL8.0 ,但和CentOS8版本上的Mariadb -10.3.17不兼容 两个安装包： mha4mysql-managermha4mysql-node 管理端安装两个RPM包： [root@MHA-Manager~]#ll total144 -rw-------.1rootroot1764Apr2412:19anaconda-ks.cfg -rw-r--r--1rootroot0Apr2811:20a.txt -rw-r--r--1rootroot587May1221:13CentOS-8.repo -rw-r--r--1rootroot920Apr2914:50f1.txt -rw-r--r--1rootroot1601Apr2914:50f2.txt -rw-r--r--1rootroot1076May209:38ks-centos8.cfg -rw-r--r--1rootroot949Apr2814:35ks.cfg -rw-r--r--1rootroot81024Jul302020mha4mysql-manager-0.58-0.el7.centos.noarch.rpm -rw-r--r--1rootroot36328Jul302020mha4mysql-node-0.58-0.el7.centos.noarch.rpm -rw-r--r--1rootroot41Apr2708:50test.sh drwxr-xr-x.2rootroot220Apr2414:46yum #这里一定要先装mha4mysql-node，然后再装mha4mysql-manager [root@MHA-Manager~]#yuminstall-ymha4mysql-node-0.58-0.el7.centos.noarch.rpm [root@MHA-Manager~]#yuminstall-ymha4mysql-manager-0.58-0.el7.centos.noarch.rpm 2.在所有MySQL服务器上安装mha4mysql-node包 [root@Master~]#yuminstall-ymha4mysql-node-0.58-0.el7.centos.noarch.rpm [root@Slave1~]#yuminstall-ymha4mysql-node-0.58-0.el7.centos.noarch.rpm [root@Slave2~]#yuminstall-ymha4mysql-node-0.58-0.el7.centos.noarch.rpm 3.在所有节点实现相互之间ssh key验证 [root@MHA-Manager~]#ssh-keygen Generatingpublic/privatersakeypair. Enterfileinwhichtosavethekey(/root/.ssh/id_rsa): Createddirectory'/root/.ssh'. Enterpassphrase(emptyfornopassphrase): Entersamepassphraseagain: Youridentificationhasbeensavedin/root/.ssh/id_rsa. Yourpublickeyhasbeensavedin/root/.ssh/id_rsa.pub. Thekeyfingerprintis: SHA256:C9ovIIe3BWcMBFTZgs0pi9ECVq7166py11oDCLgWfNMroot@MHA-Manager.magedu.com Thekey'srandomartimageis: +---[RSA2048]----+ |o++Oo+| |*=O.| |o*BE| |.o*.++| |.o...=.S| |.o++o..| |+.*+.| |..+.oo| |.o.ooo..| +----[SHA256]-----+ [root@MHA-Manager~]#ssh-copy-id127.0.0.1 [root@MHA-Manager~]#rsync-av.ssh10.0.0.8:/root/ [root@MHA-Manager~]#rsync-av.ssh10.0.0.18:/root/ [root@MHA-Manager~]#rsync-av.ssh10.0.0.28:/root/ 4.在管理节点建立配置文件 [root@MHA-Manager~]#mkdir/etc/mastermha/ [root@MHA-Manager~]#vim/etc/mastermha/app1.conf [serverdefault] user=mhauser#用于远程连接MySQL所有节点的用户,需要有管理员的权限 password=magedu manager_workdir=/data/mastermha/app1/#目录会自动生成,无需手动创建 manager_log=/data/mastermha/app1/manager.log remote_workdir=/data/mastermha/app1/ ssh_user=root#用于实现远程ssh基于KEY的连接,访问二进制日志 repl_user=repluser#主从复制的用户信息 repl_password=magedu ping_interval=1#健康性检查的时间间隔 master_ip_failover_script=/usr/local/bin/master_ip_failover#切换VIP的perl脚本 report_script=/usr/local/bin/sendmail.sh#当执行报警脚本 check_repl_delay=0#默认值为1,表示如果slave中从库落后主库relaylog超过100M，主库不会选择这个从库为新的master，因为这个从库进行恢复需要很长的时间.通过设置参数check_repl_delay=0，mha触发主从切换时会忽略复制的延时，对于设置candidate_master=1的从库非常有用，这样确保这个从库一定能成为最新的master master_binlog_dir=/data/mysql/#指定二进制日志存放的目录,mha4mysql-manager-0.58必须指定,之前版本不需要指定 [server1] hostname=10.0.0.8 candidate_master=1 [server2] hostname=10.0.0.18 candidate_master=1#设置为优先候选master，即使不是集群中事件最新的slave,也会优先当master [server3] hostname=10.0.0.28 #最终文件内容 [root@MHA-Manager~]#cat/etc/mastermha/app1.conf [serverdefault] user=mhauser password=magedu manager_workdir=/data/mastermha/app1/ manager_log=/data/mastermha/app1/manager.log remote_workdir=/data/mastermha/app1/ ssh_user=root repl_user=repluser repl_password=magedu ping_interval=1 master_ip_failover_script=/usr/local/bin/master_ip_failover report_script=/usr/local/bin/sendmail.sh check_repl_delay=0 master_binlog_dir=/data/mysql/ [server1] hostname=10.0.0.8 candidate_master=1 [server2] hostname=10.0.0.18 candidate_master=1 [server3] hostname=10.0.0.28 [root@MHA-Manager~]# 说明：主库宕机谁来接管新的Master 1. 所有从节点日志都是一致的，默认会以配置文件的顺序去选择一个新主2. 从节点日志不一致，自动选择最接近于主库的从库充当新主3. 如果对于某节点设定了权重（candidate_master=1），权重节点会优先选择。但是此节点日志量落后主库超过100M日志的话，也不会被选择。可以配合check_repl_delay=0，关闭日志量的检查，强制选择候选节点 5.相关脚本 [root@MHA-Manager~]#cat/usr/local/bin/sendmail.sh #!/bin/bash echo"MySQLisdown"|mail-s"MHAWarning"15762354477@139.com [root@MHA-Manager~]#chmod+x/usr/local/bin/sendmail.sh [root@MHA-Manager~]#vim/usr/local/bin/master_ip_failover #!/usr/bin/envperl usestrict; usewarningsFATAL=>'all'; useGetopt::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='10.0.0.100/24';#设置VirtualIP my$gateway='10.0.0.254';#网关GatewayIP my$interface='eth0';#指定VIP所在网卡 my$key="1"; my$ssh_start_vip="/sbin/ifconfig$interface:$key$vip;/sbin/arping-I$interface-c3-s$vip$gateway>/dev/null2>&1"; my$ssh_stop_vip="/sbin/ifconfig$interface:$keydown"; 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(); submain{ print"\n\nINSCRIPTTEST====$ssh_stop_vip==$ssh_start_vip===\n\n"; if($commandeq"stop"||$commandeq"stopssh"){ #$orig_master_host,$orig_master_ip,$orig_master_portarepassed. #Ifyoumanagemasteripaddressatglobalcatalogdatabase, #invalidateorig_master_iphere. my$exit_code=1; eval{ print"DisablingtheVIPonoldmaster:$orig_master_host\n"; &stop_vip(); $exit_code=0; }; if($@){ warn"GotError:$@\n"; exit$exit_code; } exit$exit_code; } elsif($commandeq"start"){ #allargumentsarepassed. ##Ifyoumanagemasteripaddressatglobalcatalogdatabase, ##activatenew_master_iphere. ##Youcanalsograntwriteaccess(createuser,setread_only=0,etc)here. my$exit_code=10; eval{ print"EnablingtheVIP-$viponthenewmaster-$new_master_host\n"; &start_vip(); $exit_code=0; }; if($@){ warn$@; exit$exit_code; } exit$exit_code; } elsif($commandeq"status"){ print"CheckingtheStatusofthescript..OK\n"; `ssh$ssh_user\@$orig_master_host\"$ssh_start_vip\"`; exit0; } else{ &usage(); exit1; } } #AsimplesystemcallthatenabletheVIPonthenewmaster substart_vip(){ `ssh$ssh_user\@$new_master_host\"$ssh_start_vip\"`; } #AsimplesystemcallthatdisabletheVIPontheold_master substop_vip(){ `ssh$ssh_user\@$orig_master_host\"$ssh_stop_vip\"`; } subusage{ 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"; } #最终文件内容 [root@MHA-Manager~]#cat/usr/local/bin/master_ip_failover #!/usr/bin/envperl usestrict; usewarningsFATAL=>'all'; useGetopt::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='10.0.0.100/24'; my$gateway='10.0.0.254'; my$interface='eth0'; my$key="1"; my$ssh_start_vip="/sbin/ifconfig$interface:$key$vip;/sbin/arping-I$interface-c3-s$vip$gateway>/dev/null2>&1"; my$ssh_stop_vip="/sbin/ifconfig$interface:$keydown"; 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(); submain{ print"\n\nINSCRIPTTEST====$ssh_stop_vip==$ssh_start_vip===\n\n"; if($commandeq"stop"||$commandeq"stopssh"){ #$orig_master_host,$orig_master_ip,$orig_master_portarepassed. #Ifyoumanagemasteripaddressatglobalcatalogdatabase, #invalidateorig_master_iphere. my$exit_code=1; eval{ print"DisablingtheVIPonoldmaster:$orig_master_host\n"; &stop_vip(); $exit_code=0; }; if($@){ warn"GotError:$@\n"; exit$exit_code; } exit$exit_code; } elsif($commandeq"start"){ #allargumentsarepassed. ##Ifyoumanagemasteripaddressatglobalcatalogdatabase, ##activatenew_master_iphere. ##Youcanalsograntwriteaccess(createuser,setread_only=0,etc)here. my$exit_code=10; eval{ print"EnablingtheVIP-$viponthenewmaster-$new_master_host\n"; &start_vip(); $exit_code=0; }; if($@){ warn$@; exit$exit_code; } exit$exit_code; } elsif($commandeq"status"){ print"CheckingtheStatusofthescript..OK\n"; `ssh$ssh_user\@$orig_master_host\"$ssh_start_vip\"`; exit0; } else{ &usage(); exit1; } } #AsimplesystemcallthatenabletheVIPonthenewmaster substart_vip(){ `ssh$ssh_user\@$new_master_host\"$ssh_start_vip\"`; } #AsimplesystemcallthatdisabletheVIPontheold_master substop_vip(){ `ssh$ssh_user\@$orig_master_host\"$ssh_stop_vip\"`; } subusage{ 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"; } [root@MHA-Manager~]#chmod+x/usr/local/bin/master_ip_failover [root@MHA-Manager~]# 6.实现Master [root@Master~]#yuminstall-ymysql-server [root@Master~]#mkdir/data/mysql/ [root@Master~]#chownmysql:mysql/data/mysql/ [root@Master~]#vim/etc/my.cnf.d/mysql-server.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysql/mysqld.log pid-file=/run/mysqld/mysqld.pid server-id=8 log-bin=/data/mysql/mysql-bin skip_name_resolve=1 general_log#观察结果,非必须项,生产无需启用 [root@Master~]#systemctlenable--nowmysqld.service [root@Master~]#mysql WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis8 Serverversion:8.0.21Sourcedistribution Copyright(c)2000,2020,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand/orits affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. mysql>showmasterlogs; +------------------+-----------+-----------+ |Log_name|File_size|Encrypted| +------------------+-----------+-----------+ |mysql-bin.000001|179|No| |mysql-bin.000002|1201|No| +------------------+-----------+-----------+ 2rowsinset(0.00sec) mysql>createuserrepluser@'10.0.0.%'identifiedby'magedu'; QueryOK,0rowsaffected(0.01sec) mysql>grantreplicationslaveon*.*torepluser@'10.0.0.%'; QueryOK,0rowsaffected(0.00sec) mysql>createusermhauser@'10.0.0.%'identifiedby'magedu'; QueryOK,0rowsaffected(0.01sec) mysql>grantallon*.*tomhauser@'10.0.0.%'; QueryOK,0rowsaffected(0.01sec) mysql>selectuser,hostfrommysql.user; +------------------+-----------+ |user|host| +------------------+-----------+ |mhauser|10.0.0.%| |repluser|10.0.0.%| |mysql.infoschema|localhost| |mysql.session|localhost| |mysql.sys|localhost| |root|localhost| +------------------+-----------+ 6rowsinset(0.00sec) mysql>quit Bye #配置VIP [root@Master~]#ifconfigeth0:110.0.0.100/24 [root@Master~]#ifconfig eth0:flags=4163<UP,BROADCAST,RUNNING,MULTICAST>mtu1500 inet10.0.0.8netmask255.255.255.0broadcast10.0.0.255 inet6fe80::809c:7c3f:dc61:53bbprefixlen64scopeid0x20<link> ether00:0c:29:0a:08:a3txqueuelen1000(Ethernet) RXpackets67605bytes91484149(87.2MiB) RXerrors0dropped0overruns0frame0 TXpackets31053bytes2925668(2.7MiB) TXerrors0dropped0overruns0carrier0collisions0 eth0:1:flags=4163<UP,BROADCAST,RUNNING,MULTICAST>mtu1500 inet10.0.0.100netmask255.255.255.0broadcast10.0.0.255 ether00:0c:29:0a:08:a3txqueuelen1000(Ethernet) lo:flags=73<UP,LOOPBACK,RUNNING>mtu65536 inet127.0.0.1netmask255.0.0.0 inet6::1prefixlen128scopeid0x10<host> looptxqueuelen1000(LocalLoopback) RXpackets0bytes0(0.0B) RXerrors0dropped0overruns0frame0 TXpackets0bytes0(0.0B) TXerrors0dropped0overruns0carrier0collisions0 [root@Master~]# 7.实现Slave [root@Slave1~]#yuminstall-ymysql-server [root@Slave1~]#mkdir/data/mysql/ [root@Slave1~]#chownmysql:mysql/data/mysql/ [root@Slave1~]#vim/etc/my.cnf.d/mysql-server.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysql/mysqld.log pid-file=/run/mysqld/mysqld.pid server_id=18#不同节点此值各不相同 log-bin=/data/mysql/mysql-bin read_only relay_log_purge=0 skip_name_resolve=1#禁止反向解析 general_log#方便观察的设置,生产无需启用 [root@Slave1~]#systemctlenable--nowmysqld.service [root@Slave1~]#mysql WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis8 Serverversion:8.0.21Sourcedistribution Copyright(c)2000,2020,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand/orits affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. mysql>CHANGEMASTERTO ->MASTER_HOST='10.0.0.100', ->MASTER_USER='repluser', ->MASTER_PASSWORD='magedu', ->MASTER_LOG_FILE='mysql-bin.000002', ->MASTER_LOG_POS=1201; QueryOK,0rowsaffected,2warnings(0.05sec) mysql>startslave; QueryOK,0rowsaffected(0.01sec) mysql>showslavestatus\G ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent Master_Host:10.0.0.100 Master_User:repluser Master_Port:3306 Connect_Retry:60 Master_Log_File:mysql-bin.000002 Read_Master_Log_Pos:1201 Relay_Log_File:Slave1-relay-bin.000002 Relay_Log_Pos:324 Relay_Master_Log_File:mysql-bin.000002 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:1201 Relay_Log_Space:534 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:8 Master_UUID:bb4f4671-baa2-11eb-8cf4-000c290a08a3 Master_Info_File:mysql.slave_master_info SQL_Delay:0 SQL_Remaining_Delay:NULL Slave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdates 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: Master_public_key_path: Get_master_public_key:0 Network_Namespace: 1rowinset(0.00sec) mysql>quit Bye [root@Slave1~]# [root@Slave2~]#yuminstall-ymysql-server [root@Slave2~]#mkdir-p/data/mysql/ [root@Slave2~]#chownmysql:mysql/data/mysql/ [root@Slave2~]#vim/etc/my.cnf.d/mysql-server.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysql/mysqld.log pid-file=/run/mysqld/mysqld.pid server_id=28#不同节点此值各不相同 log-bin=/data/mysql/mysql-bin read_only relay_log_purge=0 skip_name_resolve=1#禁止反向解析 general_log#方便观察的设置,生产无需启用 [root@Slave2~]#systemctlenable--nowmysqld.service [root@Slave2~]#mysql WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis8 Serverversion:8.0.21Sourcedistribution Copyright(c)2000,2020,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand/orits affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. mysql>CHANGEMASTERTO ->MASTER_HOST='10.0.0.100', ->MASTER_USER='repluser', ->MASTER_PASSWORD='magedu', ->MASTER_LOG_FILE='mysql-bin.000002', ->MASTER_LOG_POS=1201; QueryOK,0rowsaffected,2warnings(0.03sec) mysql>startslave; QueryOK,0rowsaffected(0.00sec) mysql>showslavestatus\G ***************************1.row*************************** Slave_IO_State:Waitingformastertosendevent Master_Host:10.0.0.100 Master_User:repluser Master_Port:3306 Connect_Retry:60 Master_Log_File:mysql-bin.000002 Read_Master_Log_Pos:1201 Relay_Log_File:Slave2-relay-bin.000002 Relay_Log_Pos:324 Relay_Master_Log_File:mysql-bin.000002 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:1201 Relay_Log_Space:534 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:8 Master_UUID:bb4f4671-baa2-11eb-8cf4-000c290a08a3 Master_Info_File:mysql.slave_master_info SQL_Delay:0 SQL_Remaining_Delay:NULL Slave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdates 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: Master_public_key_path: Get_master_public_key:0 Network_Namespace: 1rowinset(0.00sec) mysql>quit Bye [root@Slave2~]# 8.检查MHA的环境 [root@MHA-Manager~]#masterha_check_ssh--conf=/etc/mastermha/app1.conf SatMay2210:40:362021-[warning]Globalconfigurationfile/etc/masterha_default.cnfnotfound.Skipping. SatMay2210:40:362021-[info]Readingapplicationdefaultconfigurationfrom/etc/mastermha/app1.conf.. SatMay2210:40:362021-[info]Readingserverconfigurationfrom/etc/mastermha/app1.conf.. SatMay2210:40:362021-[info]StartingSSHconnectiontests.. SatMay2210:40:372021-[debug] SatMay2210:40:362021-[debug]ConnectingviaSSHfromroot@10.0.0.8(10.0.0.8:22)toroot@10.0.0.18(10.0.0.18:22).. SatMay2210:40:362021-[debug]ok. SatMay2210:40:362021-[debug]ConnectingviaSSHfromroot@10.0.0.8(10.0.0.8:22)toroot@10.0.0.28(10.0.0.28:22).. Warning:Permanentlyadded'10.0.0.28'(ECDSA)tothelistofknownhosts. SatMay2210:40:372021-[debug]ok. SatMay2210:40:372021-[debug] SatMay2210:40:362021-[debug]ConnectingviaSSHfromroot@10.0.0.18(10.0.0.18:22)toroot@10.0.0.8(10.0.0.8:22).. SatMay2210:40:372021-[debug]ok. SatMay2210:40:372021-[debug]ConnectingviaSSHfromroot@10.0.0.18(10.0.0.18:22)toroot@10.0.0.28(10.0.0.28:22).. SatMay2210:40:372021-[debug]ok. SatMay2210:40:382021-[debug] SatMay2210:40:372021-[debug]ConnectingviaSSHfromroot@10.0.0.28(10.0.0.28:22)toroot@10.0.0.8(10.0.0.8:22).. SatMay2210:40:372021-[debug]ok. SatMay2210:40:372021-[debug]ConnectingviaSSHfromroot@10.0.0.28(10.0.0.28:22)toroot@10.0.0.18(10.0.0.18:22).. SatMay2210:40:382021-[debug]ok. SatMay2210:40:382021-[info]AllSSHconnectiontestspassedsuccessfully. [root@MHA-Manager~]#masterha_check_repl--conf=/etc/mastermha/app1.conf SatMay2210:43:062021-[warning]Globalconfigurationfile/etc/masterha_default.cnfnotfound.Skipping. SatMay2210:43:062021-[info]Readingapplicationdefaultconfigurationfrom/etc/mastermha/app1.conf.. SatMay2210:43:062021-[info]Readingserverconfigurationfrom/etc/mastermha/app1.conf.. SatMay2210:43:062021-[info]MHA::MasterMonitorversion0.58. Creatingdirectory/data/mastermha/app1/..done. SatMay2210:43:062021-[error][/usr/share/perl5/vendor_perl/MHA/Server.pm,ln180]GotMySQLerrorwhenconnecting10.0.0.18(10.0.0.18:3306):1130:Host'10.0.0.7'isnotallowedtoconnecttothisMySQLserver,butthisisnotaMySQLcrash.CheckMySQLserversettings. SatMay2210:43:062021-[error][/usr/share/perl5/vendor_perl/MHA/Server.pm,ln180]GotMySQLerrorwhenconnecting10.0.0.28(10.0.0.28:3306):1130:Host'10.0.0.7'isnotallowedtoconnecttothisMySQLserver,butthisisnotaMySQLcrash.CheckMySQLserversettings. SatMay2210:43:062021-[error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm,ln301]at/usr/share/perl5/vendor_perl/MHA/ServerManager.pmline297. SatMay2210:43:062021-[error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm,ln301]at/usr/share/perl5/vendor_perl/MHA/ServerManager.pmline297. SatMay2210:43:072021-[error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm,ln309]Gotfatalerror,stoppingoperations SatMay2210:43:072021-[error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm,ln427]Errorhappenedoncheckingconfigurations.at/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pmline329. SatMay2210:43:072021-[error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm,ln525]Errorhappenedonmonitoringservers. SatMay2210:43:072021-[info]Gotexitcode1(Notmasterdead). MySQLReplicationHealthisNOTOK! #这里Slave从节点的健康性检查失败是因为Slave从节点的数据库中不存在repluser和mhauser用户，造成管理节点连不到两个从节点服务器，那么这里我在两个从节点上创建这两个账户用户并进行授权 [root@Slave1~]#mysql WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis12 Serverversion:8.0.21Sourcedistribution Copyright(c)2000,2020,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand/orits affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. mysql>createuserrepluser@'10.0.0.%'identifiedby'magedu'; QueryOK,0rowsaffected(0.01sec) mysql>grantreplicationslaveon*.*torepluser@'10.0.0.%'; QueryOK,0rowsaffected(0.00sec) mysql>createusermhauser@'10.0.0.%'identifiedby'magedu'; QueryOK,0rowsaffected(0.01sec) mysql>grantallon*.*tomhauser@'10.0.0.%'; QueryOK,0rowsaffected(0.00sec) mysql>selectuser,hostfrommysql.user; +------------------+-----------+ |user|host| +------------------+-----------+ |mhauser|10.0.0.%| |repluser|10.0.0.%| |mysql.infoschema|localhost| |mysql.session|localhost| |mysql.sys|localhost| |root|localhost| +------------------+-----------+ 6rowsinset(0.00sec) mysql>quit Bye [root@Slave2~]#mysql WelcometotheMySQLmonitor.Commandsendwith;or\g. YourMySQLconnectionidis12 Serverversion:8.0.21Sourcedistribution Copyright(c)2000,2020,Oracleand/oritsaffiliates.Allrightsreserved. OracleisaregisteredtrademarkofOracleCorporationand/orits affiliates.Othernamesmaybetrademarksoftheirrespective owners. Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement. mysql>createuserrepluser@'10.0.0.%'identifiedby'magedu'; QueryOK,0rowsaffected(0.01sec) mysql>grantreplicationslaveon*.*torepluser@'10.0.0.%'; QueryOK,0rowsaffected(0.00sec) mysql>createusermhauser@'10.0.0.%'identifiedby'magedu'; QueryOK,0rowsaffected(0.01sec) mysql>grantallon*.*tomhauser@'10.0.0.%'; QueryOK,0rowsaffected(0.00sec) mysql>selectuser,hostfrommysql.user; +------------------+-----------+ |user|host| +------------------+-----------+ |mhauser|10.0.0.%| |repluser|10.0.0.%| |mysql.infoschema|localhost| |mysql.session|localhost| |mysql.sys|localhost| |root|localhost| +------------------+-----------+ 6rowsinset(0.00sec) mysql>quit Bye #重新检查环境，检查正常 [root@MHA-Manager~]#masterha_check_repl--conf=/etc/mastermha/app1.conf SatMay2215:42:572021-[warning]Globalconfigurationfile/etc/masterha_default.cnfnotfound.Skipping. SatMay2215:42:572021-[info]Readingapplicationdefaultconfigurationfrom/etc/mastermha/app1.conf.. SatMay2215:42:572021-[info]Readingserverconfigurationfrom/etc/mastermha/app1.conf.. SatMay2215:42:572021-[info]MHA::MasterMonitorversion0.58. SatMay2215:42:582021-[info]GTIDfailovermode=0 SatMay2215:42:582021-[info]DeadServers: SatMay2215:42:582021-[info]AliveServers: SatMay2215:42:582021-[info]10.0.0.8(10.0.0.8:3306) SatMay2215:42:582021-[info]10.0.0.18(10.0.0.18:3306) SatMay2215:42:582021-[info]10.0.0.28(10.0.0.28:3306) SatMay2215:42:582021-[info]AliveSlaves: SatMay2215:42:582021-[info]10.0.0.18(10.0.0.18:3306)Version=8.0.21(oldestmajorversionbetweenslaves)log-bin:enabled SatMay2215:42:582021-[info]Replicatingfrom10.0.0.8(10.0.0.8:3306) SatMay2215:42:582021-[info]PrimarycandidateforthenewMaster(candidate_masterisset) SatMay2215:42:582021-[info]10.0.0.28(10.0.0.28:3306)Version=8.0.21(oldestmajorversionbetweenslaves)log-bin:enabled SatMay2215:42:582021-[info]Replicatingfrom10.0.0.8(10.0.0.8:3306) SatMay2215:42:582021-[info]CurrentAliveMaster:10.0.0.8(10.0.0.8:3306) SatMay2215:42:582021-[info]Checkingslaveconfigurations.. SatMay2215:42:582021-[info]Checkingreplicationfilteringsettings.. SatMay2215:42:582021-[info]binlog_do_db=,binlog_ignore_db= SatMay2215:42:582021-[info]Replicationfilteringcheckok. SatMay2215:42:582021-[info]GTID(withauto-pos)isnotsupported SatMay2215:42:582021-[info]StartingSSHconnectiontests.. SatMay2215:43:002021-[info]AllSSHconnectiontestspassedsuccessfully. SatMay2215:43:002021-[info]CheckingMHANodeversion.. SatMay2215:43:012021-[info]Versioncheckok. SatMay2215:43:012021-[info]CheckingSSHpublickeyauthenticationsettingsonthecurrentmaster.. SatMay2215:43:012021-[info]HealthCheck:SSHto10.0.0.8isreachable. SatMay2215:43:012021-[info]MasterMHANodeversionis0.58. SatMay2215:43:012021-[info]Checkingrecoveryscriptconfigurationson10.0.0.8(10.0.0.8:3306).. SatMay2215:43:012021-[info]Executingcommand:save_binary_logs--command=test--start_pos=4--binlog_dir=/data/mysql/--output_file=/data/mastermha/app1//save_binary_logs_test--manager_version=0.58--start_file=mysql-bin.000007 SatMay2215:43:012021-[info]Connectingtoroot@10.0.0.8(10.0.0.8:22).. Creating/data/mastermha/app1ifnotexists..ok. Checkingoutputdirectoryisaccessibleornot.. ok. Binlogfoundat/data/mysql/,uptomysql-bin.000007 SatMay2215:43:012021-[info]Binlogsettingcheckdone. SatMay2215:43:012021-[info]CheckingSSHpublickeyauthenticationandcheckingrecoveryscriptconfigurationsonallaliveslaveservers.. SatMay2215:43:012021-[info]Executingcommand:apply_diff_relay_logs--command=test--slave_user='mhauser'--slave_host=10.0.0.18--slave_ip=10.0.0.18--slave_port=3306--workdir=/data/mastermha/app1/--target_version=8.0.21--manager_version=0.58--relay_dir=/var/lib/mysql--current_relay_log=Slave1-relay-bin.000008--slave_pass=xxx SatMay2215:43:012021-[info]Connectingtoroot@10.0.0.18(10.0.0.18:22).. Checkingslaverecoveryenvironmentsettings.. Relaylogfoundat/var/lib/mysql,uptoSlave1-relay-bin.000008 Temporaryrelaylogfileis/var/lib/mysql/Slave1-relay-bin.000008 Checkingifsuper_read_onlyisdefinedandturnedon..notpresentorturnedoff,ignoring. Testingmysqlconnectionandprivileges.. mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure. done. Testingmysqlbinlogoutput..done. Cleaninguptestfile(s)..done. SatMay2215:43:022021-[info]Executingcommand:apply_diff_relay_logs--command=test--slave_user='mhauser'--slave_host=10.0.0.28--slave_ip=10.0.0.28--slave_port=3306--workdir=/data/mastermha/app1/--target_version=8.0.21--manager_version=0.58--relay_dir=/var/lib/mysql--current_relay_log=Slave2-relay-bin.000010--slave_pass=xxx SatMay2215:43:022021-[info]Connectingtoroot@10.0.0.28(10.0.0.28:22).. Checkingslaverecoveryenvironmentsettings.. Relaylogfoundat/var/lib/mysql,uptoSlave2-relay-bin.000010 Temporaryrelaylogfileis/var/lib/mysql/Slave2-relay-bin.000010 Checkingifsuper_read_onlyisdefinedandturnedon..notpresentorturnedoff,ignoring. Testingmysqlconnectionandprivileges.. mysql:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure. done. Testingmysqlbinlogoutput..done. Cleaninguptestfile(s)..done. SatMay2215:43:022021-[info]Slavessettingscheckdone. SatMay2215:43:022021-[info] 10.0.0.8(10.0.0.8:3306)(currentmaster) +--10.0.0.18(10.0.0.18:3306) +--10.0.0.28(10.0.0.28:3306) SatMay2215:43:022021-[info]Checkingreplicationhealthon10.0.0.18.. SatMay2215:43:022021-[info]ok. SatMay2215:43:022021-[info]Checkingreplicationhealthon10.0.0.28.. SatMay2215:43:022021-[info]ok. SatMay2215:43:022021-[info]Checkingmaster_ip_failover_scriptstatus: SatMay2215:43:022021-[info]/usr/local/bin/master_ip_failover--command=status--ssh_user=root--orig_master_host=10.0.0.8--orig_master_ip=10.0.0.8--orig_master_port=3306 INSCRIPTTEST====/sbin/ifconfigeth0:1down==/sbin/ifconfigeth0:110.0.0.100/24;/sbin/arping-Ieth0-c3-s10.0.0.100/2410.0.0.254>/dev/null2>&1=== CheckingtheStatusofthescript..OK SatMay2215:43:032021-[info]OK. SatMay2215:43:032021-[warning]shutdown_scriptisnotdefined. SatMay2215:43:032021-[info]Gotexitcode0(Notmasterdead). MySQLReplicationHealthisOK. [root@MHA-Manager~]# 9.启动MHA 开启MHA,默认是前台运行,生产环境一般为后台执行 [root@MHA-Manager~]#nohupmasterha_manager--conf=/etc/mastermha/app1.conf&>/dev/null 查看状态 [root@MHA-Manager~]#masterha_check_status--conf=/etc/mastermha/app1.conf app1(pid:1321)isrunning(0:PING_OK),master:10.0.0.8 在Master服务器上进行健康性检查 [root@Master~]#tail-f/var/lib/mysql/Master.log 2021-05-22T08:08:15.494093Z24QuerySELECT1AsValue 2021-05-22T08:08:16.494128Z24QuerySELECT1AsValue 2021-05-22T08:08:17.496217Z24QuerySELECT1AsValue 2021-05-22T08:08:18.498052Z24QuerySELECT1AsValue 2021-05-22T08:08:19.500948Z24QuerySELECT1AsValue 2021-05-22T08:08:20.504335Z24QuerySELECT1AsValue 2021-05-22T08:08:21.507257Z24QuerySELECT1AsValue 2021-05-22T08:08:22.507209Z24QuerySELECT1AsValue 2021-05-22T08:08:23.507075Z24QuerySELECT1AsValue 2021-05-22T08:08:24.509524Z24QuerySELECT1AsValue 2021-05-22T08:08:25.510962Z24QuerySELECT1AsValue 2021-05-22T08:08:26.511363Z24QuerySELECT1AsValue 2021-05-22T08:08:27.513529Z24QuerySELECT1AsValue 10.模拟故障 停掉Master服务器的MySQL服务 [root@Master~]#systemctlstopmysqld.service 查看状态 [root@MHA-Manager~]#masterha_check_status--conf=/etc/mastermha/app1.conf app1isstopped(2:NOT_RUNNING). 验证VIP漂移至新的Master上 [root@Slave1~]#ifconfig eth0:flags=4163<UP,BROADCAST,RUNNING,MULTICAST>mtu1500 inet10.0.0.18netmask255.255.255.0broadcast10.0.0.255 inet6fe80::5a87:1464:beb7:38prefixlen64scopeid0x20<link> ether00:0c:29:9d:3e:68txqueuelen1000(Ethernet) RXpackets70084bytes88687133(84.5MiB) RXerrors0dropped0overruns0frame0 TXpackets35950bytes3857649(3.6MiB) TXerrors0dropped0overruns0carrier0collisions0 eth0:1:flags=4163<UP,BROADCAST,RUNNING,MULTICAST>mtu1500 inet10.0.0.100netmask255.255.255.0broadcast10.0.0.255 ether00:0c:29:9d:3e:68txqueuelen1000(Ethernet) lo:flags=73<UP,LOOPBACK,RUNNING>mtu65536 inet127.0.0.1netmask255.0.0.0 inet6::1prefixlen128scopeid0x10<host> looptxqueuelen1000(LocalLoopback) RXpackets920bytes136130(132.9KiB) RXerrors0dropped0overruns0frame0 TXpackets920bytes136130(132.9KiB) TXerrors0dropped0overruns0carrier0collisions0 [root@Slave1~]# 文章写得有不恰当之处，请多见谅。
