前期准备
服务器版本：CentOS Linux release 7.6.1810 (Core)
mysql_mha_manager : 192.168.10.215
mysql_mha_01 : 192.168.10.216
mysql_mha_02 : 192.168.10.217
mysql_mha_03 : 192.168.10.218
一. 安装MHA工具
1. mysql_mha_manager服务器
安装依赖
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm -y
yum install perl-DBD-MySQL -y
yum install perl-Config-Tiny -y
yum install perl-Log-Dispatch perl-Parallel-ForkManager -y
yum install perl-Parallel-ForkManager -y
安装node
rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm
安装manager
rpm -ivh mha4mysql-manager-0.57-0.el7.noarch.rpm
2. mysql_mha_01服务器
安装依赖
yum install perl-DBD-MySQL -y
安装node
rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm
3. mysql_mha_02服务器
安装依赖
yum install perl-DBD-MySQL -y
安装node
rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm
4. mysql_mha_03服务器
安装依赖
yum install perl-DBD-MySQL -y
安装node
rpm -ivh mha4mysql-node-0.57-0.el7.noarch.rpm
二. ssh免密
1. mysql_mha_manager
192.168.10.215
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.216
ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.217
ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.218
2. mysql_mha_01
192.168.10.216
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.217
ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.218
3. mysql_mha_02
192.168.10.217
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.216
ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.218
4. mysql_mha_03
192.168.10.218
ssh-keygen -t rsa
ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.216
ssh-copy-id -i /root/.ssh/id_rsa root@192.168.10.217
三. 安装mysql
所有机器创建帐号
groupadd mysql
useradd -g mysql mysql
初次启动报错
[root@mysql_mha_01 tmp]# service mysqld start
Starting MySQL.2019-03-12T14:27:25.072241Z mysqld_safe error: log-error set to '/data/mysql/logs/mysql.log', however file don't exists. Create writable for user 'mysql'.
ERROR! The server quit without updating PID file (/data/mysql/run/mysql.pid).
解决方案：
初次启动mysql：sudo -u mysql service mysqld start
再次启动mysql：service mysqld start|stop|restart
四. 修改hosts文件
mysql_mha_01/02/03、mysql_mha_manager
vi /etc/hosts
192.168.10.216 mysql_mha_01
192.168.10.217 mysql_mha_02
192.168.10.218 mysql_mha_03
五. mysql主从配置
mysql_mha_01/02/03关闭防火墙
systemctl stop firewalld.service
1. mysql_mha_01
grant replication slave,replication client ON *.* to 'repl'@'192.168.10.%' identified by 'repl';
flush privileges;
mysqldump --master-data=2 --single-transaction -uroot -p --all-databases > mysql_mha_01.sql
mysqldump --master-data=1 --single-transaction -uroot -p --all-databases > mysql_mha_02.sql
more mysql_mha_01.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql_mha01-bin.000003', MASTER_LOG_POS=1448;
scp mysql_mha_01.sql root@192.168.10.217:/tmp
scp mysql_mha_01.sql root@192.168.10.218:/tmp
2. mysql_mha_02
mysql -uroot -p < mysql_mha_01.sql
mysql -uroot -p
CHANGE MASTER TO MASTER_HOST='192.168.10.216',
MASTER_USER='repl', MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql_mha01-bin.000003', MASTER_LOG_POS=1448;
start slave;
show slave status\G
3. mysql_mha_03
mysql -uroot -p < mysql_mha_01.sql
mysql -uroot -p
CHANGE MASTER TO MASTER_HOST='192.168.10.216',
MASTER_USER='repl', MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql_mha01-bin.000003', MASTER_LOG_POS=1448;
start slave;
show slave status\G
六. mha的manager配置文件
1. mysql_mha_manager服务器
mkdir -p /etc/mha
vi /etc/mha/mha.cnf
[server default]
manager_workdir=/etc/mha/
manager_log=/etc/mha/manager.log
master_binlog_dir=/data/mysql/binlog/
user=root
password=123456
ping_interval=1
remote_workdir=/tmp
repl_password=repl
repl_user=repl
secondary_check_script= /usr/bin/masterha_secondary_check -s 192.168.10.217 -s 192.168.10.218 --user=root --master_host=192.168.10.216 --master_ip=192.168.10.216 --master_port=3306
master_ip_failover_script="/etc/mha/scripts/master_ip_failover"
#master_ip_online_change_script="/etc/mha/scripts/master_ip_online_change"
#shutdown_script=""
ssh_user=root
[server1]
hostname=192.168.10.216
port=3306
candidate_master=1
check_repl_delay=0
[server2]
hostname=192.168.10.217
port=3306
candidate_master=1
check_repl_delay=0
[server3]
hostname=192.168.10.218
port=3306
七. 检查SSH免密、复制状态
1. 检查SSH免密
masterha_check_ssh --conf=/etc/mha/mha.cnf
2. 检查复制
masterha_check_repl --conf=/etc/mha/mha.cnf
错误1
描述
Tue Mar 12 12:50:46 2019 - [info] Connecting to root@192.168.10.217(mysql_mha_02:22)..
Can't exec "mysqlbinlog": No such file or directory at /usr/share/perl5/vendor_perl/MHA/BinlogManager.pm line 106.
mysqlbinlog version command failed with rc 1:0, please verify PATH, LD_LIBRARY_PATH, and client options at /usr/bin/apply_diff_relay_logs line 493.
解决方案
mysql_mha_01/02/03
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
错误2
描述
Tue Mar 12 13:13:13 2019 - [info] Connecting to root@192.168.10.217(mysql_mha_02:22)..
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'
mysqlbinlog version command failed with rc 7:0, please verify PATH, LD_LIBRARY_PATH, and client options at /usr/bin/apply_diff_relay_logs line 493.
解决方案
mysql_mha_01/02/03
修改my.cnf
[client]
#default-character-set = utf8mb4
错误3
描述
Testing mysql connection and privileges..sh: mysql: command not found
mysql command failed with rc 127:0!
at /usr/bin/apply_diff_relay_logs line 375.
main::check() called at /usr/bin/apply_diff_relay_logs line 497
eval {...} called at /usr/bin/apply_diff_relay_logs line 475
main::main() called at /usr/bin/apply_diff_relay_logs line 120
Tue Mar 12 13:25:40 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln208] Slaves settings check failed!
Tue Mar 12 13:25:40 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln416] Slave configuration failed.
Tue Mar 12 13:25:40 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln427] Error happened on checking configurations. at /usr/bin/masterha_check_repl line 48.
Tue Mar 12 13:25:40 2019 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln525] Error happened on monitoring servers.
Tue Mar 12 13:25:40 2019 - [info] Got exit code 1 (Not master dead).
解决方案
mysql_mha_01/02/03
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
八. MHA启动与停止
1. 启动manager
nohup masterha_manager --conf=/etc/mha/mha.cnf< /dev/null > /etc/mha/manager.log 2>&1 &
2. 停止manager
masterha_stop --conf=/etc/mha/mha.cnf
3. 检查manager
masterha_check_status --conf=/etc/mha/mha.cnf
九. 测试
1. 查看状态
[root@mysql_mha_manager mha]# masterha_check_status --conf=/etc/mha/mha.cnf
mha (pid:27991) is running(0:PING_OK), master:mysql_mha_01
2. 关闭mysql_mha_01数据库
service mysqld stop
3. 查看日志和mha状态
日志
----- Failover Report -----
mha: MySQL Master failover mysql_mha_01(192.168.10.216:3306) to mysql_mha_02(192.168.10.217:3306) succeeded
Master mysql_mha_01(192.168.10.216:3306) is down!
Check MHA Manager logs at mysql_mha_manager:/etc/mha/manager.log for details.
Started automated(non-interactive) failover.
The latest slave mysql_mha_02(192.168.10.217:3306) has all relay logs for recovery.
Selected mysql_mha_02(192.168.10.217:3306) as a new master.
mysql_mha_02(192.168.10.217:3306): OK: Applying all logs succeeded.
mysql_mha_03(192.168.10.218:3306): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
mysql_mha_03(192.168.10.218:3306): OK: Applying all logs succeeded. Slave started, replicating from mysql_mha_02(192.168.10.217:3306)
mysql_mha_02(192.168.10.217:3306): Resetting slave info succeeded.
Master failover to mysql_mha_02(192.168.10.217:3306) completed successfully.
状态
[root@mysql_mha_manager mha]# masterha_check_status --conf=/etc/mha/mha.cnf
mha is stopped(2:NOT_RUNNING).
4. 查看 slave 状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.217
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_mha02-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql_mha_03-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql_mha02-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes