Centos7下MySQL5.7-MHA高可用环境搭建
一、搭建主从环境
1.MySQL主库搭建
grouadd mysql
useradd -r -g mysql -s /sbin/nologin mysql
卸载MySQL以及mariadb
rpm -qa | grep mysql && rpm -qa | grep mariadb rpm -e --nodeps 包名
创建MySQL相关目录
cd u01 && tar xf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz mv mysql-5.7.29-linux-glibc2.12-x86_64 mysql/ mkdir -p mysql/{data,logs} chown -R mysql:mysql mysql/ && chmod -R 755 mysql
编辑主库my.cnf配置文件
vim /etc/my.cnf [mysql] default-character-set=utf8 socket=/tmp/mysql.sock [mysqld] #skip-name-reslove port=3306 basedir=/u01/mysql/ datadir=/u01/mysql/data/ character-set-server=utf8 default_storage_engine=InnoDB innodb_buffer_pool_size=200M max_allowed_packet=16M explicit_defaults_for_timestamp=1 lower_case_table_names=1 log-output=FILE general_log=0 general_log_file=/u01/mysql/logs/mysql-general.log slow_query_log=ON slow_query_log_file=/u01/mysql/logs/mysql-query.log long_query_time=10 log-error=/u01/mysql/logs/mysql-error.log bind-address=192.168.10.51 server_id=513306 skip_name_resolve=ON expire_logs_days=7 innodb_support_xa=1 binlog_cache_size=1M max_binlog_size=2048M log_bin_trust_function_creators=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1 transaction-isolation=READ-COMMITTED gtid_mode=ON enforce_gtid_consistency=1 log-slave-updates=1 binlog_gtid_simple_recovery=1 log_bin=/u01/mysql/logs/mysql-bin log_bin_index=/u01/mysql/logs/mysql-bin.index binlog_format=ROW binlog_rows_query_log_events=on plugin_dir=/u01/mysql/lib/plugin plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled=1 loose_rpl_semi_sync_slave_enabled=1 loose_rpl_semi_sync_master_timeout=5000 rpl_semi_sync_master_wait_point=AFTER_SYNC rpl_semi_sync_master_wait_for_slave_count=1
初始化数据库，密码在log-error中，并修改初始密码
/u01/mysql/bin/mysqld --initialize --user=mysql --basedir=/u01/mysql/ --datadir=/u01/mysql/data ln -vs /u01/mysql/support-files/mysql.server /etc/init.d/mysqld ln -vs /u01/mysql/bin/mysql /usr/bin/ /etc/init.d/mysqld start && mysql -uroot -p #修改初始密码 alter user 'root'@'localhost' identified by 'rootroot'; commit; exit;
2.搭建MySQL从库，步骤与主库一样，不在赘述，附从库my.cnf配置文件
[mysql] default-character-set=utf8 socket=/tmp/mysql.sock [mysqld] #skip-name-reslove port=3306 basedir=/u01/mysql/ datadir=/u01/mysql/data/ character-set-server=utf8 default_storage_engine=InnoDB innodb_buffer_pool_size=200M max_allowed_packet=16M explicit_defaults_for_timestamp=1 lower_case_table_names=1 log-output=FILE general_log=0 general_log_file=/u01/mysql/logs/mysql-general.log slow_query_log=ON slow_query_log_file=/u01/mysql/logs/mysql-query.log long_query_time=10 log-error=/u01/mysql/logs/mysql-error.log bind-address=192.168.10.52 server_id=523306 skip_name_resolve=ON expire_logs_days=7 innodb_support_xa=1 binlog_cache_size=1M max_binlog_size=2048M log_bin_trust_function_creators=1 innodb_flush_log_at_trx_commit=1 sync_binlog=1 transaction-isolation=READ-COMMITTED gtid_mode=ON enforce_gtid_consistency=1 log-slave-updates=1 binlog_gtid_simple_recovery=1 log_bin=/u01/mysql/logs/mysql-bin log_bin_index=/u01/mysql/logs/mysql-bin.index binlog_format=ROW binlog_rows_query_log_events=on relay_log=/u01/mysql/logs/mysql-relay.log read_only=1 plugin_dir=/u01/mysql/lib/plugin plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled=1 loose_rpl_semi_sync_slave_enabled=1 loose_rpl_semi_sync_master_timeout=5000 rpl_semi_sync_master_wait_point=AFTER_SYNC rpl_semi_sync_master_wait_for_slave_count=1
3.在主库创建用于主从同步复制用户并授予权限
mysql -uroot -proot create user 'repuser'@'%' identified with mysql_native_password by 'repuser123'; grant replication client,replication slave on *.* to 'repuser'@'%'; flush privileges; select user,host from mysql.user;
创建root用户，后面mha-manager配置中会用到
mysql -uroot -proot create user 'root'@'%' identified with mysql_native_password by 'root123'; grant all privileges on *.* to 'root'@'%'; flush privileges; select user,host from mysql.user;
4.在从库上简历slave与master的连接
mysql -uroot -proot stop slave; reset master; reset slave; change master to master_host='192.168.10.51', master_port=3306, master_user='repuser', master_password='repuser123', master_auto_position=1;
查看主从状态
start slave;
show salve status \G
SHOW PROCESSLIST;
5.数据同步有验证，主库创建表，在从库查看是否有相应的表
create user 'eheng'@'%' identified with mysql_native_password by 'oracle'; grant all privileges on newings1.* to 'eheng'@'%'; flush privileges; create database newings1; use newings1; create table newings11(id int,name varchar(40));
6.从库错误处理
①.Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. cd /u01/mysql/data rm -rf auto.cnf ②.Error 'Operation CREATE USER failed for 'repuser'@'%'' on query. Default database: ''. Query: 'CREATE USER 'repuser'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*D6B70D90834FFD0E0516A62778445C486176C003'' #停止所有从库,并删除从库和主库的repuser并重新创建repuser用户 stop slave; drop user 'repuser'@'%'; reset master; reset slave; change master to master_host='192.168.10.51', master_port=3306, master_user='repuser', master_password='repuser123', master_auto_position=1;
二、配置MHA高可用
1.配置服务器ssh互信
#配置Hosts vim /etc/hosts 192.168.10.51 master 192.168.10.52 slave1 192.168.10.53 slave2 #配置ssh互信 cd /root ssh-keygen cd .ssh cp id_rsa.pub authorized_keys scp -r .ssh/ root@slave1:/root/ scp -r .ssh/ root@slave2:/root/
2.安装mha-node和mha-manager软件，rpm请自行下载
yum install perl-DBD-MySQL perl-DBI mysql-libs perl-ExtUtils-MakeMaker perl-Class-Load perl-Params-Validate \ perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-YAML-Tiny perl-PAR-Dist perl-Module-ScanDeps \ perl-Module-CoreList perl-Module-Build perl-CPAN perl-YAML perl-CPANPLUS perl-File-Remove perl-Module-Install -y tar xf CPAN-1.9205.tar.gz mv CPAN-1.9205 ../ cd ../CPAN-1.9205/ perl Makefile.PL make && make install cd ../src/ tar xf ExtUtils-MakeMaker-6.31.tar.gz mv ExtUtils-MakeMaker-6.31 ../ cd ../ExtUtils-MakeMaker-6.31/ perl Makefile.PL make && make install cd ../src/ tar xf mha4mysql-node-0.58.tar.gz mv mha4mysql-node-0.58 ../ cd ../mha4mysql-node-0.58/ perl Makefile.PL make && make install rpm -ivh perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm rpm -ivh --nodeps perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm
3.在master服务器上安装mha-manager
tar xf mha4mysql-manager-0.58.tar.gz mv mha4mysql-manager-0.58 ../ cd ../mha4mysql-manager-0.58/ perl Makefile.PL make && make install
安装完成后会在/usr/local/bin下生成以下脚本
total 124 4 drwxr-xr-x. 2 root root 4096 Jul 23 23:23 . 0 drwxr-xr-x. 16 root root 238 Jul 23 23:23 .. 20 -r-xr-xr-x. 1 root root 17639 Jul 23 23:18 apply_diff_relay_logs 8 -r-xr-xr-x. 1 root root 4807 Jul 23 23:18 filter_mysqlbinlog 4 -r-xr-xr-x. 1 root root 1995 Jul 23 23:23 masterha_check_repl 4 -r-xr-xr-x. 1 root root 1779 Jul 23 23:23 masterha_check_ssh 4 -r-xr-xr-x. 1 root root 1865 Jul 23 23:23 masterha_check_status 4 -r-xr-xr-x. 1 root root 3201 Jul 23 23:23 masterha_conf_host 4 -r-xr-xr-x. 1 root root 2517 Jul 23 23:23 masterha_manager 4 -r-xr-xr-x. 1 root root 2165 Jul 23 23:23 masterha_master_monitor 4 -r-xr-xr-x. 1 root root 2373 Jul 23 23:23 masterha_master_switch 8 -r-xr-xr-x. 1 root root 5172 Jul 23 23:23 masterha_secondary_check 4 -r-xr-xr-x. 1 root root 1739 Jul 23 23:23 masterha_stop 4 -rwxr-xr-x. 1 root root 3648 Jul 23 23:23 master_ip_failover 12 -rwxr-xr-x. 1 root root 9870 Jul 23 23:23 master_ip_online_change 12 -rwxr-xr-x. 1 root root 11867 Jul 23 23:23 power_manager 12 -r-xr-xr-x. 1 root root 8337 Jul 23 23:18 purge_relay_logs 8 -r-xr-xr-x. 1 root root 7525 Jul 23 23:18 save_binary_logs 4 -rwxr-xr-x. 1 root root 1360 Jul 23 23:23 send_report
将安装目录下的示例脚本复制到/usr/local/bin目录下，但这些脚本其实并不完整，需要自己调整，脚本是由软件开发者提供，开启一个参数需有一个对应的脚本，没有则会抛出错误
cp /usr/local/mha4mysql-manager-0.58/samples/scripts/ * /usr/local/bin/
4.配置MHA参数
mkdir -p /etc/masterha mkdir -p /u01/mha cp /usr/local/mha4mysql-manager-0.58/conf/app1.cnf /etc/masterha vim /etc/masterha #配置参数 [server default] manager_workdir=/u01/mha manager_log=/u01/mha/manager.log master_binlog_dir=/u01/mysql/logs/ master_ip_failover_script=/usr/local/bin/master_ip_failover master_ip_online_change_script=/usr/local/bin/master_ip_online_change secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.10.52 -s 192.168.10.51 -s 192.168.10.52 report_script=/usr/local/bin/send_report remote_workdir=/tmp password=root@123 user=root ping_interval=1 repl_password=repuser123 repl_user=repuser ssh_user=root [server1] hostname=192.168.10.52 candidate_master=1 port=3306 [server2] hostname=192.168.10.51 candidate_master=1 check_repl_delay=0 port=3306 [server3] hostname=192.168.10.53 candidate_master=1 check_repl_delay=0 port=3306
修改master_ip_failover脚本
#!/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.10.50/24'; # Virtual IP my $gateway = '192.168.10.1'; #Gateway IP my $interface = 'enp0s3'; my $key = "1"; my $ssh_start_vip = "/sbin/ifconfig $interface:$key $vip;/sbin/arping -I $interface -c 3 -s $vip $gateway >/dev/null 2>&1"; my $ssh_stop_vip = "/sbin/ifconfig $interface:$key down";
$ssh_user="root"; 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" ) { #$orig_master_host, $orig_master_ip, $orig_master_port are passed. # If you manage master ip address at global catalog database, # invalidate orig_master_ip here. 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" ) { # all arguments are passed. # If you manage master ip address at global catalog database, # activate new_master_ip here. # You can also grant write access (create user, set read_only=0, etc) here. 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"; `ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`; exit 0; } else { &usage(); exit 1; } } # A simple system call that enable the VIP on the new master 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"; }
5.测试mha ssh连接
[root@master bin]# masterha_check_ssh --conf=/etc/masterha/app1.cnf Sat Jul 24 01:01:46 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sat Jul 24 01:01:46 2021 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Sat Jul 24 01:01:46 2021 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Sat Jul 24 01:01:46 2021 - [info] Starting SSH connection tests.. Sat Jul 24 01:01:48 2021 - [debug] Sat Jul 24 01:01:46 2021 - [debug] Connecting via SSH from root@192.168.10.51(192.168.10.51:22) to root@192.168.10.52(192.168.10.52:22).. Sat Jul 24 01:01:47 2021 - [debug] ok. Sat Jul 24 01:01:47 2021 - [debug] Connecting via SSH from root@192.168.10.51(192.168.10.51:22) to root@192.168.10.53(192.168.10.53:22).. Sat Jul 24 01:01:48 2021 - [debug] ok. Sat Jul 24 01:01:49 2021 - [debug] Sat Jul 24 01:01:47 2021 - [debug] Connecting via SSH from root@192.168.10.53(192.168.10.53:22) to root@192.168.10.51(192.168.10.51:22).. Sat Jul 24 01:01:48 2021 - [debug] ok. Sat Jul 24 01:01:48 2021 - [debug] Connecting via SSH from root@192.168.10.53(192.168.10.53:22) to root@192.168.10.52(192.168.10.52:22).. Sat Jul 24 01:01:49 2021 - [debug] ok. Sat Jul 24 01:01:49 2021 - [debug] Sat Jul 24 01:01:47 2021 - [debug] Connecting via SSH from root@192.168.10.52(192.168.10.52:22) to root@192.168.10.51(192.168.10.51:22).. Sat Jul 24 01:01:48 2021 - [debug] ok. Sat Jul 24 01:01:48 2021 - [debug] Connecting via SSH from root@192.168.10.52(192.168.10.52:22) to root@192.168.10.53(192.168.10.53:22).. Sat Jul 24 01:01:48 2021 - [debug] ok. Sat Jul 24 01:01:49 2021 - [info] All SSH connection tests passed successfully.
测试复制
[root@master bin]# masterha_check_repl --conf=/etc/masterha/app1.cnf Sat Jul 24 01:13:02 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sat Jul 24 01:13:02 2021 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Sat Jul 24 01:13:02 2021 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Sat Jul 24 01:13:02 2021 - [info] MHA::MasterMonitor version 0.58. Sat Jul 24 01:13:03 2021 - [info] GTID failover mode = 0 Sat Jul 24 01:13:03 2021 - [info] Dead Servers: Sat Jul 24 01:13:03 2021 - [info] Alive Servers: Sat Jul 24 01:13:03 2021 - [info] 192.168.10.51(192.168.10.51:3306) Sat Jul 24 01:13:03 2021 - [info] 192.168.10.52(192.168.10.52:3306) Sat Jul 24 01:13:03 2021 - [info] 192.168.10.53(192.168.10.53:3306) Sat Jul 24 01:13:03 2021 - [info] Alive Slaves: Sat Jul 24 01:13:03 2021 - [info] 192.168.10.52(192.168.10.52:3306) Version=5.7.29-log (oldest major version between slaves) log-bin:enabled Sat Jul 24 01:13:03 2021 - [info] GTID ON Sat Jul 24 01:13:03 2021 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Sat Jul 24 01:13:03 2021 - [info] Primary candidate for the new Master (candidate_master is set) Sat Jul 24 01:13:03 2021 - [info] 192.168.10.53(192.168.10.53:3306) Version=5.7.29-log (oldest major version between slaves) log-bin:enabled Sat Jul 24 01:13:03 2021 - [info] GTID ON Sat Jul 24 01:13:03 2021 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Sat Jul 24 01:13:03 2021 - [info] Primary candidate for the new Master (candidate_master is set) Sat Jul 24 01:13:03 2021 - [info] Current Alive Master: 192.168.10.51(192.168.10.51:3306) Sat Jul 24 01:13:03 2021 - [info] Checking slave configurations.. Sat Jul 24 01:13:03 2021 - [info] Checking replication filtering settings.. Sat Jul 24 01:13:03 2021 - [info] binlog_do_db= , binlog_ignore_db= Sat Jul 24 01:13:03 2021 - [info] Replication filtering check ok. Sat Jul 24 01:13:03 2021 - [info] GTID (with auto-pos) is not supported Sat Jul 24 01:13:03 2021 - [info] Starting SSH connection tests.. Sat Jul 24 01:13:06 2021 - [info] All SSH connection tests passed successfully. Sat Jul 24 01:13:06 2021 - [info] Checking MHA Node version.. Sat Jul 24 01:13:06 2021 - [info] Version check ok. Sat Jul 24 01:13:06 2021 - [info] Checking SSH publickey authentication settings on the current master.. Sat Jul 24 01:13:07 2021 - [info] HealthCheck: SSH to 192.168.10.51 is reachable. Sat Jul 24 01:13:07 2021 - [info] Checking recovery script configurations on 192.168.10.51(192.168.10.51:3306).. Sat Jul 24 01:13:07 2021 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/u01/mysql/logs/ --output_file=/tmp/save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin.000001 Sat Jul 24 01:13:07 2021 - [info] Connecting to root@192.168.10.51(192.168.10.51:22).. Creating /tmp if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /u01/mysql/logs/, up to mysql-bin.000001 Sat Jul 24 01:13:07 2021 - [info] Binlog setting check done. Sat Jul 24 01:13:07 2021 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Sat Jul 24 01:13:07 2021 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.10.52 --slave_ip=192.168.10.52 --slave_port=3306 --workdir=/tmp --target_version=5.7.29-log --manager_version=0.58 --relay_log_info=/u01/mysql/data/relay-log.info --relay_dir=/u01/mysql/data/ --slave_pass=xxx Sat Jul 24 01:13:07 2021 - [info] Connecting to root@192.168.10.52(192.168.10.52:22).. Checking slave recovery environment settings.. Opening /u01/mysql/data/relay-log.info ... ok. Relay log found at /u01/mysql/logs, up to mysql-relay.000002 Temporary relay log file is /u01/mysql/logs/mysql-relay.000002 Checking if super_read_only is defined and turned on.. not present or turned off, ignoring. 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. Sat Jul 24 01:13:07 2021 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.10.53 --slave_ip=192.168.10.53 --slave_port=3306 --workdir=/tmp --target_version=5.7.29-log --manager_version=0.58 --relay_log_info=/u01/mysql/data/relay-log.info --relay_dir=/u01/mysql/data/ --slave_pass=xxx Sat Jul 24 01:13:07 2021 - [info] Connecting to root@192.168.10.53(192.168.10.53:22).. Checking slave recovery environment settings.. Opening /u01/mysql/data/relay-log.info ... ok. Relay log found at /u01/mysql/logs, up to mysql-relay.000002 Temporary relay log file is /u01/mysql/logs/mysql-relay.000002 Checking if super_read_only is defined and turned on.. not present or turned off, ignoring. 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. Sat Jul 24 01:13:08 2021 - [info] Slaves settings check done. Sat Jul 24 01:13:08 2021 - [info] 192.168.10.51(192.168.10.51:3306) (current master) +--192.168.10.52(192.168.10.52:3306) +--192.168.10.53(192.168.10.53:3306) Sat Jul 24 01:13:08 2021 - [info] Checking replication health on 192.168.10.52.. Sat Jul 24 01:13:08 2021 - [info] ok. Sat Jul 24 01:13:08 2021 - [info] Checking replication health on 192.168.10.53.. Sat Jul 24 01:13:08 2021 - [info] ok. Sat Jul 24 01:13:08 2021 - [info] Checking master_ip_failover_script status: Sat Jul 24 01:13:08 2021 - [info] /usr/local/bin/master_ip_failover --command=status --ssh_user=root --orig_master_host=192.168.10.51 --orig_master_ip=192.168.10.51 --orig_master_port=3306 IN SCRIPT TEST====/sbin/ifconfig enp0s3:1 down==/sbin/ifconfig enp0s3:1 192.168.10.250/24;/sbin/arping -I enp0s3 -c 3 -s 192.168.10.250/24 192.168.10.1 >/dev/null 2>&1=== Checking the Status of the script.. OK Sat Jul 24 01:13:08 2021 - [info] OK. Sat Jul 24 01:13:08 2021 - [warning] shutdown_script is not defined. Sat Jul 24 01:13:08 2021 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.
测试复制过程中出现的问题
[root@master bin]# masterha_check_repl --conf=/etc/masterha/app1.cnf Sat Jul 24 00:00:10 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sat Jul 24 00:00:10 2021 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Sat Jul 24 00:00:10 2021 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Sat Jul 24 00:00:10 2021 - [info] MHA::MasterMonitor version 0.58. Sat Jul 24 00:00:11 2021 - [info] GTID failover mode = 0 Sat Jul 24 00:00:11 2021 - [info] Dead Servers: Sat Jul 24 00:00:11 2021 - [info] Alive Servers: Sat Jul 24 00:00:11 2021 - [info] 192.168.10.51(192.168.10.51:3306) Sat Jul 24 00:00:11 2021 - [info] 192.168.10.52(192.168.10.52:3306) Sat Jul 24 00:00:11 2021 - [info] 192.168.10.53(192.168.10.53:3306) Sat Jul 24 00:00:11 2021 - [info] Alive Slaves: Sat Jul 24 00:00:11 2021 - [info] 192.168.10.52(192.168.10.52:3306) Version=5.7.29-log (oldest major version between slaves) log-bin:enabled Sat Jul 24 00:00:11 2021 - [info] GTID ON Sat Jul 24 00:00:11 2021 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Sat Jul 24 00:00:11 2021 - [info] Primary candidate for the new Master (candidate_master is set) Sat Jul 24 00:00:11 2021 - [info] 192.168.10.53(192.168.10.53:3306) Version=5.7.29-log (oldest major version between slaves) log-bin:enabled Sat Jul 24 00:00:11 2021 - [info] GTID ON Sat Jul 24 00:00:11 2021 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Sat Jul 24 00:00:11 2021 - [info] Primary candidate for the new Master (candidate_master is set) Sat Jul 24 00:00:11 2021 - [info] Current Alive Master: 192.168.10.51(192.168.10.51:3306) Sat Jul 24 00:00:11 2021 - [info] Checking slave configurations.. Sat Jul 24 00:00:11 2021 - [info] Checking replication filtering settings.. Sat Jul 24 00:00:11 2021 - [info] binlog_do_db= , binlog_ignore_db= Sat Jul 24 00:00:11 2021 - [info] Replication filtering check ok. Sat Jul 24 00:00:11 2021 - [info] GTID (with auto-pos) is not supported Sat Jul 24 00:00:11 2021 - [info] Starting SSH connection tests.. Sat Jul 24 00:00:13 2021 - [info] All SSH connection tests passed successfully. Sat Jul 24 00:00:13 2021 - [info] Checking MHA Node version.. Sat Jul 24 00:00:14 2021 - [info] Version check ok. Sat Jul 24 00:00:14 2021 - [info] Checking SSH publickey authentication settings on the current master.. Sat Jul 24 00:00:14 2021 - [info] HealthCheck: SSH to 192.168.10.51 is reachable. Sat Jul 24 00:00:14 2021 - [info] Checking recovery script configurations on 192.168.10.51(192.168.10.51:3306).. Sat Jul 24 00:00:14 2021 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/u01/mysql/logs/ --output_file=/var/tmp/save_binary_logs_test --manager_version=0.58 --start_file=mysql-bin.000001 Sat Jul 24 00:00:14 2021 - [info] Connecting to root@192.168.10.51(192.168.10.51:22).. Creating /var/tmp if not exists.. ok. Checking output directory is accessible or not.. ok. Binlog found at /u01/mysql/logs/, up to mysql-bin.000001 Sat Jul 24 00:00:14 2021 - [info] Binlog setting check done. Sat Jul 24 00:00:14 2021 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers.. Sat Jul 24 00:00:14 2021 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='root' --slave_host=192.168.10.52 --slave_ip=192.168.10.52 --slave_port=3306 --workdir=/var/tmp --target_version=5.7.29-log --manager_version=0.58 --relay_log_info=/u01/mysql/data/relay-log.info --relay_dir=/u01/mysql/data/ --slave_pass=xxx Sat Jul 24 00:00:14 2021 - [info] Connecting to root@192.168.10.52(192.168.10.52:22).. Can't exec "mysqlbinlog": Too many levels of symbolic links at /usr/local/share/perl5/MHA/BinlogManager.pm line 106. mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options at /usr/local/bin/apply_diff_relay_logs line 532. Sat Jul 24 00:00:15 2021 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln208] Slaves settings check failed! Sat Jul 24 00:00:15 2021 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln416] Slave configuration failed. Sat Jul 24 00:00:15 2021 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/local/bin/masterha_check_repl line 48. Sat Jul 24 00:00:15 2021 - [error][/usr/local/share/perl5/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers. Sat Jul 24 00:00:15 2021 - [info] Got exit code 1 (Not master dead). MySQL Replication Health is NOT OK!
针对上面问题的解决办法
#在profile加入mysql环境变量，master以及所有slave都执行，并刷新环境变量 PATH=$PATH:$HOME/bin:/u01/mysql/bin export PATH #如果错误继续出现，则是使用下面的办法，master以及所有slave都执行 ln -vs /u01/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog ln -vs /u01/mysql/bin/mysql /usr/local/bin/mysql
6.设置master服务器VIP
[root@master bin]# ./master_ip_failover --command=start --new_master_host=192.168.10.52 IN SCRIPT TEST====/sbin/ifconfig enp0s3:1 down==/sbin/ifconfig enp0s3:1 192.168.10.50/24;/sbin/arping -I enp0s3 -c 3 -s 192.168.10.50/24 192.168.10.1 >/dev/null 2>&1=== Enabling the VIP - 172.20.10.7/24 on the new master - 192.168.10.51 [root@master bin]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever inet6 ::1/128 scope host valid_lft forever preferred_lft forever 2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 08:00:27:3b:47:3f brd ff:ff:ff:ff:ff:ff inet 192.168.10.51/28 brd 192.168.10.15 scope global noprefixroute enp0s3 valid_lft forever preferred_lft forever inet 192.168.10.50/24 brd 1192.168.10.255 scope global enp0s3:1 valid_lft forever preferred_lft forever inet6 240e:473:c30:2880:d153:2a16:9124:b870/64 scope global noprefixroute dynamic valid_lft 604733sec preferred_lft 86333sec inet6 fe80::3a3e:f029:d89:67e5/64 scope link noprefixroute valid_lft forever preferred_lft forever
#删除vip
[root@localhost scripts]# ./master_ip_failover --command=stop --orig_master_host=192.168.10.52
IN SCRIPT TEST====/usr/sbin/ifconfig enp0s3:1 down==/usr/sbin/ifconfig em1:1 192.168.10.50/24===
Disabling the VIP on old master: 192.168.10.51
7.启动、停止并查看mha状态
[root@master bin]# masterha_manager --conf=/etc/masterha/app1.cnf & [1] 21163 [root@master bin]# Sat Jul 24 01:08:45 2021 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Sat Jul 24 01:08:45 2021 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Sat Jul 24 01:08:45 2021 - [info] Reading server configuration from /etc/masterha/app1.cnf.. #查看mha进程 [root@master bin]# ps -ef|grep masterha root 21163 15903 1 01:08 pts/1 00:00:00 perl /usr/local/bin/masterha_manager --conf=/etc/masterha/app1.cnf root 21293 15903 0 01:08 pts/1 00:00:00 grep --color=auto masterha #查看mha状态 [root@master bin]# masterha_check_status --conf=/etc/masterha/app1.cnf app1 (pid:21163) is running(0:PING_OK), master:192.168.10.51 #停止mha masterha_stop --conf=/etc/masterha/app1.cnf
8.测试故障转移（故障转移后需重新手动启动mha）
[root@master .ssh]# ps -ef | grep mysqld mysql 1809 1 0 13:26 ? 00:00:02 /u01/mysql/bin/mysqld --defaults-file=/etc/my.cnf root 3606 2121 0 14:51 pts/3 00:00:00 grep --color=auto mysqld [root@master .ssh]# kill -9 1809 /*查看manager.log tail -300f /u01/mha/manager.log*/ Thu May 16 14:51:55 2024 - [warning] Got error on MySQL select ping: 2006 (MySQL server has gone away) Thu May 16 14:51:55 2024 - [info] Executing secondary network check script: /usr/local/bin/masterha_secondary_check -s 192.168.10.52 -s 192.168.10.51 -s 192.168.10.53 --user=root --master_host=192.168.10.51 --master_ip=192.168.10.51 --master_port=3306 --master_user=root --master_password=root@123 --ping_type=SELECT Thu May 16 14:51:55 2024 - [info] Executing SSH check script: exit 0 Thu May 16 14:51:55 2024 - [info] HealthCheck: SSH to 192.168.10.51 is reachable. Monitoring server 192.168.10.52 is reachable, Master is not reachable from 192.168.10.52. OK. Monitoring server 192.168.10.51 is reachable, Master is not reachable from 192.168.10.51. OK. Thu May 16 14:51:56 2024 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.10.51' (111)) Thu May 16 14:51:56 2024 - [warning] Connection failed 2 time(s).. Monitoring server 192.168.10.53 is reachable, Master is not reachable from 192.168.10.53. OK. Thu May 16 14:51:56 2024 - [info] Master is not reachable from all other monitoring servers. Failover should start. Thu May 16 14:51:57 2024 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.10.51' (111)) Thu May 16 14:51:57 2024 - [warning] Connection failed 3 time(s).. Thu May 16 14:51:58 2024 - [warning] Got error on MySQL connect: 2003 (Can't connect to MySQL server on '192.168.10.51' (111)) Thu May 16 14:51:58 2024 - [warning] Connection failed 4 time(s).. Thu May 16 14:51:58 2024 - [warning] Master is not reachable from health checker! Thu May 16 14:51:58 2024 - [warning] Master 192.168.10.51(192.168.10.51:3306) is not reachable! Thu May 16 14:51:58 2024 - [warning] SSH is reachable. Thu May 16 14:51:58 2024 - [info] Connecting to a master server failed. Reading configuration file /etc/masterha_default.cnf and /etc/masterha/app1.cnf again, and trying to connect to all servers to check server status.. Thu May 16 14:51:58 2024 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Thu May 16 14:51:58 2024 - [info] Reading application default configuration from /etc/masterha/app1.cnf.. Thu May 16 14:51:58 2024 - [info] Reading server configuration from /etc/masterha/app1.cnf.. Thu May 16 14:51:59 2024 - [info] GTID failover mode = 1 Thu May 16 14:51:59 2024 - [info] Dead Servers: Thu May 16 14:51:59 2024 - [info] 192.168.10.51(192.168.10.51:3306) Thu May 16 14:51:59 2024 - [info] Alive Servers: Thu May 16 14:51:59 2024 - [info] 192.168.10.52(192.168.10.52:3306) Thu May 16 14:51:59 2024 - [info] 192.168.10.53(192.168.10.53:3306) Thu May 16 14:51:59 2024 - [info] Alive Slaves: Thu May 16 14:51:59 2024 - [info] 192.168.10.52(192.168.10.52:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled Thu May 16 14:51:59 2024 - [info] GTID ON Thu May 16 14:51:59 2024 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Thu May 16 14:51:59 2024 - [info] Primary candidate for the new Master (candidate_master is set) Thu May 16 14:51:59 2024 - [info] 192.168.10.53(192.168.10.53:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled Thu May 16 14:51:59 2024 - [info] GTID ON Thu May 16 14:51:59 2024 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Thu May 16 14:51:59 2024 - [info] Primary candidate for the new Master (candidate_master is set) Thu May 16 14:51:59 2024 - [info] Checking slave configurations.. Thu May 16 14:51:59 2024 - [info] Checking replication filtering settings.. Thu May 16 14:51:59 2024 - [info] Replication filtering check ok. Thu May 16 14:51:59 2024 - [info] Master is down! Thu May 16 14:51:59 2024 - [info] Terminating monitoring script. Thu May 16 14:51:59 2024 - [info] Got exit code 20 (Master dead). Thu May 16 14:51:59 2024 - [info] MHA::MasterFailover version 0.58. Thu May 16 14:51:59 2024 - [info] Starting master failover. Thu May 16 14:51:59 2024 - [info] Thu May 16 14:51:59 2024 - [info] * Phase 1: Configuration Check Phase.. Thu May 16 14:51:59 2024 - [info] Thu May 16 14:52:00 2024 - [info] GTID failover mode = 1 Thu May 16 14:52:00 2024 - [info] Dead Servers: Thu May 16 14:52:00 2024 - [info] 192.168.10.51(192.168.10.51:3306) Thu May 16 14:52:00 2024 - [info] Checking master reachability via MySQL(double check)... Thu May 16 14:52:00 2024 - [info] ok. Thu May 16 14:52:00 2024 - [info] Alive Servers: Thu May 16 14:52:00 2024 - [info] 192.168.10.52(192.168.10.52:3306) Thu May 16 14:52:00 2024 - [info] 192.168.10.53(192.168.10.53:3306) Thu May 16 14:52:00 2024 - [info] Alive Slaves: Thu May 16 14:52:00 2024 - [info] 192.168.10.52(192.168.10.52:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled Thu May 16 14:52:00 2024 - [info] GTID ON Thu May 16 14:52:00 2024 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Thu May 16 14:52:00 2024 - [info] Primary candidate for the new Master (candidate_master is set) Thu May 16 14:52:00 2024 - [info] 192.168.10.53(192.168.10.53:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled Thu May 16 14:52:00 2024 - [info] GTID ON Thu May 16 14:52:00 2024 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Thu May 16 14:52:00 2024 - [info] Primary candidate for the new Master (candidate_master is set) Thu May 16 14:52:00 2024 - [info] Starting GTID based failover. Thu May 16 14:52:00 2024 - [info] Thu May 16 14:52:00 2024 - [info] ** Phase 1: Configuration Check Phase completed. Thu May 16 14:52:00 2024 - [info] Thu May 16 14:52:00 2024 - [info] * Phase 2: Dead Master Shutdown Phase.. Thu May 16 14:52:00 2024 - [info] Thu May 16 14:52:00 2024 - [info] Forcing shutdown so that applications never connect to the current master.. Thu May 16 14:52:00 2024 - [info] Executing master IP deactivation script: Thu May 16 14:52:00 2024 - [info] /usr/local/bin/master_ip_failover --orig_master_host=192.168.10.51 --orig_master_ip=192.168.10.51 --orig_master_port=3306 --command=stopssh --ssh_user=root IN SCRIPT TEST====/sbin/ifconfig enp0s3:1 down==/sbin/ifconfig enp0s3:1 192.168.10.50/24;/sbin/arping -I enp0s3 -c 3 -s 192.168.10.50/24 192.168.10.1 >/dev/null 2>&1=== Disabling the VIP on old master: 192.168.10.51 Thu May 16 14:52:00 2024 - [info] done. Thu May 16 14:52:00 2024 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master. Thu May 16 14:52:00 2024 - [info] * Phase 2: Dead Master Shutdown Phase completed. Thu May 16 14:52:00 2024 - [info] Thu May 16 14:52:00 2024 - [info] * Phase 3: Master Recovery Phase.. Thu May 16 14:52:00 2024 - [info] Thu May 16 14:52:00 2024 - [info] * Phase 3.1: Getting Latest Slaves Phase.. Thu May 16 14:52:00 2024 - [info] Thu May 16 14:52:00 2024 - [info] The latest binary log file/position on all slaves is mysql-bin.000001:2411 Thu May 16 14:52:00 2024 - [info] Retrieved Gtid Set: 4cca7a8d-1344-11ef-b997-0800273b473f:1-10 Thu May 16 14:52:00 2024 - [info] Latest slaves (Slaves that received relay log files to the latest): Thu May 16 14:52:00 2024 - [info] 192.168.10.52(192.168.10.52:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled Thu May 16 14:52:00 2024 - [info] GTID ON Thu May 16 14:52:00 2024 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Thu May 16 14:52:00 2024 - [info] Primary candidate for the new Master (candidate_master is set) Thu May 16 14:52:00 2024 - [info] 192.168.10.53(192.168.10.53:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled Thu May 16 14:52:00 2024 - [info] GTID ON Thu May 16 14:52:00 2024 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Thu May 16 14:52:00 2024 - [info] Primary candidate for the new Master (candidate_master is set) Thu May 16 14:52:00 2024 - [info] The oldest binary log file/position on all slaves is mysql-bin.000001:2411 Thu May 16 14:52:00 2024 - [info] Retrieved Gtid Set: 4cca7a8d-1344-11ef-b997-0800273b473f:1-10 Thu May 16 14:52:00 2024 - [info] Oldest slaves: Thu May 16 14:52:00 2024 - [info] 192.168.10.52(192.168.10.52:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled Thu May 16 14:52:00 2024 - [info] GTID ON Thu May 16 14:52:00 2024 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Thu May 16 14:52:00 2024 - [info] Primary candidate for the new Master (candidate_master is set) Thu May 16 14:52:00 2024 - [info] 192.168.10.53(192.168.10.53:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled Thu May 16 14:52:00 2024 - [info] GTID ON Thu May 16 14:52:00 2024 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Thu May 16 14:52:00 2024 - [info] Primary candidate for the new Master (candidate_master is set) Thu May 16 14:52:00 2024 - [info] Thu May 16 14:52:00 2024 - [info] * Phase 3.3: Determining New Master Phase.. Thu May 16 14:52:00 2024 - [info] Thu May 16 14:52:00 2024 - [info] Searching new master from slaves.. Thu May 16 14:52:00 2024 - [info] Candidate masters from the configuration file: Thu May 16 14:52:00 2024 - [info] 192.168.10.52(192.168.10.52:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled Thu May 16 14:52:00 2024 - [info] GTID ON Thu May 16 14:52:00 2024 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Thu May 16 14:52:00 2024 - [info] Primary candidate for the new Master (candidate_master is set) Thu May 16 14:52:00 2024 - [info] 192.168.10.53(192.168.10.53:3306) Version=5.7.44-log (oldest major version between slaves) log-bin:enabled Thu May 16 14:52:00 2024 - [info] GTID ON Thu May 16 14:52:00 2024 - [info] Replicating from 192.168.10.51(192.168.10.51:3306) Thu May 16 14:52:00 2024 - [info] Primary candidate for the new Master (candidate_master is set) Thu May 16 14:52:00 2024 - [info] Non-candidate masters: Thu May 16 14:52:00 2024 - [info] Searching from candidate_master slaves which have received the latest relay log events.. Thu May 16 14:52:00 2024 - [info] New master is 192.168.10.52(192.168.10.52:3306) Thu May 16 14:52:00 2024 - [info] Starting master failover.. Thu May 16 14:52:00 2024 - [info] From: 192.168.10.51(192.168.10.51:3306) (current master) +--192.168.10.52(192.168.10.52:3306) +--192.168.10.53(192.168.10.53:3306) To: 192.168.10.52(192.168.10.52:3306) (new master) +--192.168.10.53(192.168.10.53:3306) Thu May 16 14:52:00 2024 - [info] Thu May 16 14:52:00 2024 - [info] * Phase 3.3: New Master Recovery Phase.. Thu May 16 14:52:00 2024 - [info] Thu May 16 14:52:00 2024 - [info] Waiting all logs to be applied.. Thu May 16 14:52:00 2024 - [info] done. Thu May 16 14:52:00 2024 - [info] Getting new master's binlog name and position.. Thu May 16 14:52:00 2024 - [info] mysql-bin.000002:2783 Thu May 16 14:52:00 2024 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='192.168.10.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repuser', MASTER_PASSWORD='xxx'; Thu May 16 14:52:00 2024 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: mysql-bin.000002, 2783, 06c2615e-1345-11ef-9df7-0800278cf066:1-2, 4cca7a8d-1344-11ef-b997-0800273b473f:1-10 Thu May 16 14:52:00 2024 - [info] Executing master IP activate script: Thu May 16 14:52:00 2024 - [info] /usr/local/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=192.168.10.51 --orig_master_ip=192.168.10.51 --orig_master_port=3306 --new_master_host=192.168.10.52 --new_master_ip=192.168.10.52 --new_master_port=3306 --new_master_user='root' --new_master_password=xxx Unknown option: new_master_user Unknown option: new_master_password IN SCRIPT TEST====/sbin/ifconfig enp0s3:1 down==/sbin/ifconfig enp0s3:1 192.168.10.50/24;/sbin/arping -I enp0s3 -c 3 -s 192.168.10.50/24 192.168.10.1 >/dev/null 2>&1=== Enabling the VIP - 192.168.10.50/24 on the new master - 192.168.10.52 Thu May 16 14:52:01 2024 - [info] OK. Thu May 16 14:52:01 2024 - [info] Setting read_only=0 on 192.168.10.52(192.168.10.52:3306).. Thu May 16 14:52:01 2024 - [info] ok. Thu May 16 14:52:01 2024 - [info] ** Finished master recovery successfully. Thu May 16 14:52:01 2024 - [info] * Phase 3: Master Recovery Phase completed. Thu May 16 14:52:01 2024 - [info] Thu May 16 14:52:01 2024 - [info] * Phase 4: Slaves Recovery Phase.. Thu May 16 14:52:01 2024 - [info] Thu May 16 14:52:01 2024 - [info] Thu May 16 14:52:01 2024 - [info] * Phase 4.1: Starting Slaves in parallel.. Thu May 16 14:52:01 2024 - [info] Thu May 16 14:52:01 2024 - [info] -- Slave recovery on host 192.168.10.53(192.168.10.53:3306) started, pid: 3680. Check tmp log /u01/mha/192.168.10.53_3306_20240516145159.log if it takes time.. Thu May 16 14:52:03 2024 - [info] Thu May 16 14:52:03 2024 - [info] Log messages from 192.168.10.53 ... Thu May 16 14:52:03 2024 - [info] Thu May 16 14:52:01 2024 - [info] Resetting slave 192.168.10.53(192.168.10.53:3306) and starting replication from the new master 192.168.10.52(192.168.10.52:3306).. Thu May 16 14:52:01 2024 - [info] Executed CHANGE MASTER. Thu May 16 14:52:02 2024 - [info] Slave started. Thu May 16 14:52:02 2024 - [info] gtid_wait(06c2615e-1345-11ef-9df7-0800278cf066:1-2, 4cca7a8d-1344-11ef-b997-0800273b473f:1-10) completed on 192.168.10.53(192.168.10.53:3306). Executed 0 events. Thu May 16 14:52:03 2024 - [info] End of log messages from 192.168.10.53. Thu May 16 14:52:03 2024 - [info] -- Slave on host 192.168.10.53(192.168.10.53:3306) started. Thu May 16 14:52:03 2024 - [info] All new slave servers recovered successfully. Thu May 16 14:52:03 2024 - [info] Thu May 16 14:52:03 2024 - [info] * Phase 5: New master cleanup phase.. Thu May 16 14:52:03 2024 - [info] Thu May 16 14:52:03 2024 - [info] Resetting slave info on the new master.. Thu May 16 14:52:03 2024 - [info] 192.168.10.52: Resetting slave info succeeded. Thu May 16 14:52:03 2024 - [info] Master failover to 192.168.10.52(192.168.10.52:3306) completed successfully. Thu May 16 14:52:03 2024 - [info] ----- Failover Report ----- app1: MySQL Master failover 192.168.10.51(192.168.10.51:3306) to 192.168.10.52(192.168.10.52:3306) succeeded Master 192.168.10.51(192.168.10.51:3306) is down! Check MHA Manager logs at master:/u01/mha/manager.log for details. Started automated(non-interactive) failover. Invalidated master IP address on 192.168.10.51(192.168.10.51:3306) Selected 192.168.10.52(192.168.10.52:3306) as a new master. 192.168.10.52(192.168.10.52:3306): OK: Applying all logs succeeded. 192.168.10.52(192.168.10.52:3306): OK: Activated master IP address. 192.168.10.53(192.168.10.53:3306): OK: Slave started, replicating from 192.168.10.52(192.168.10.52:3306) 192.168.10.52(192.168.10.52:3306): Resetting slave info succeeded. Master failover to 192.168.10.52(192.168.10.52:3306) completed successfully. Thu May 16 14:52:03 2024 - [info] Sending mail..
/*查看是否已漂移*/
[root@slave1 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:8c:f0:66 brd ff:ff:ff:ff:ff:ff
inet 192.168.10.52/28 brd 192.168.10.15 scope global noprefixroute enp0s3
valid_lft forever preferred_lft forever
inet 192.168.10.50/24 brd 192.168.10.255 scope global enp0s3:1
valid_lft forever preferred_lft forever
inet6 240e:473:c30:2880:fa39:7e94:12f3:cb8b/64 scope global noprefixroute dynamic
valid_lft 604738sec preferred_lft 86338sec
inet6 240e:471:ca0:630e:b975:7966:e330:664d/64 scope global noprefixroute dynamic
valid_lft 592520sec preferred_lft 74120sec
inet6 fe80::c067:486c:4162:b61d/64 scope link noprefixroute
valid_lft forever preferred_lft forever
从日志可以看出mha已将192.168.10.52提升为了master，同时重启故障数据库，并以备库身份重新加入集群
[root@master ~]# systemctl start mysqld mysql -uroot -proot@123 reset slave; change master to master_host='192.168.10.52', master_port=3306, master_user='repuser', master_password='repuser123', master_auto_position=1; start slave; show slave status \G;
9.相关资料
https://www.cnblogs.com/panwenbin-logs/p/8306906.html https://www.cnblogs.com/sky-cheng/p/10972424.html
