一、搭建MySQL主从
1.MySQL主库搭建
创建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_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_master_timeout=5000 rpl_semi_sync_master_wait_point=AFTER_SYNC rpl_semi_sync_master_wait_for_slave_count=1
3.在主库以及所有从库创建复制、root用户并授权
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 replication client,replication slave 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 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;
7.安装mha node，所有master和slave都安装，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.rpm2.在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/
2.1配置mha
mkdir -p /etc/masterha mkdir -p /u01/mha cp /usr/local/mha4mysql-manager-0.58/conf/app1.cnf /etc/masterha vim /etc/masterha #配置参数 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.53 -s 192.168.10.52 report_script=/usr/local/bin/send_report remote_workdir=/tmp password=root123 user=root ping_interval=1 repl_password=repuser123 repl_user=repuser ssh_user=root [server1] hostname=192.168.10.51 candidate_master=1 [server2] hostname=192.168.10.52 candidate_master=1 [server3] hostname=192.168.10.53 candidate_master=1
修改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.250/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";
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";
}
3.测试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
4.启动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.. [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
参考资料1：https://www.cnblogs.com/sky-cheng/p/10972424.html
参考资料2：https://www.cnblogs.com/panwenbin-logs/p/8306906.html