MySQL-day16
MHA切换优先级
在数据量相同的情况下，MHA会往哪里切换？
[root@db02 ~]# cat /etc/mha/app1.cnf
[server default]
manager_log=/etc/mha/app1/manager.log
manager_workdir=/etc/mha/app1
master_binlog_dir=/application/mysql/data
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=rep
ssh_user=root
ssh_port=22
[server1]
hostname=10.0.0.51
port=3306
[server3]
hostname=10.0.0.53
port=3306
[server4]
hostname=10.0.0.54
port=3306
[server2]
#candidate_master=1
#check_repl_delay=0
hostname=10.0.0.52
port=3306
#master_ip_failover_script没有设置
master_ip_failover_script is not set. Skipping invalidating dead master IP address.
#候选的主库在配置文件中：
Candidate masters from the configuration file:
#没有候选主库
Non-candidate masters:
#候选的主库在配置文件中：10.0.0.54
Candidate masters from the configuration file:
10.0.0.54(10.0.0.54:3306) Version=5.6.40-log (oldest major version between slaves)
----- Failover Report -----
app1: MySQL Master failover 10.0.0.51(10.0.0.51:3306) to 10.0.0.52(10.0.0.52:3306) succeeded
Master 10.0.0.51(10.0.0.51:3306) is down!
Check MHA Manager logs at db02:/etc/mha/app1/manager.log for details.
Started automated(non-interactive) failover.
Selected 10.0.0.52(10.0.0.52:3306) as a new master.
10.0.0.52(10.0.0.52:3306): OK: Applying all logs succeeded.
10.0.0.53(10.0.0.53:3306): OK: Slave started, replicating from 10.0.0.52(10.0.0.52:3306)
10.0.0.54(10.0.0.54:3306): OK: Slave started, replicating from 10.0.0.52(10.0.0.52:3306)
10.0.0.52(10.0.0.52:3306): Resetting slave info succeeded.
Master failover to 10.0.0.52(10.0.0.52:3306) completed successfully.
## MHA切换在数据一致的情况下，server标签越小，优先级越高
MHA启动命令
#后台管理进程
nohup
#MHA启动命令脚本
masterha_manager
#指定配置文件
--conf=/etc/mha/app1.cnf
#从配置文件中，移除宕机的主库信息
--remove_dead_master_conf
#忽略最后一次切换
--ignore_last_failover
#MHA工作机制：在MHA做完一次切换后，会在工作目录（/etc/mha/app1）中，会生成`app1.failover.complete`,可以理解是锁文件，8小时之内，不会做第二次切换。
MHA集群恢复
1.修复宕机的主库，启动数据库
[root@db01 ~]# /etc/init.d/mysqld start
2.在日志中，找到change master语句
[root@db02 ~]# grep -i 'change master to' /etc/mha/app1/manager.log
3.拿着change master语句在旧主库中执行
CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='123';
4.开启主从复制
start slave;
5.修复MHA配置文件
[server_1]
hostname=10.0.0.51
port=3306
[server_2]
hostname=10.0.0.52
port=3306
[server_3]
hostname=10.0.0.53
port=3306
[server_4]
hostname=10.0.0.54
port=3306
6.启动MHA
[root@db02 mha]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/app1/manager.log 2>&1 &
脚本
vim MHA.sh
#!/bin/bash
#old_master=`ssh 10.0.0.52 "sed -nr 's#^Master (.*)\(.*!$#\2#gp' /etc/mha/app1/manager.log"`
change=`ssh 10.0.0.52 "grep -i 'change master to' /etc/mha/app1/manager.log"|awk -F: '{print $NF}'|sed 's#xxx#123#g'`
/etc/init.d/mysqld start
mysql -e "$change start slave;"
ssh 10.0.0.52 '\cp /etc/mha/app1.cnf.ori /etc/mha/app1.cnf'
传统主从复制MHA
1.停止MHA
[root@db02 ~]# masterha_stop --conf=/etc/mha/app1.cnf
2.取消所有从库的身份
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)
3.注释GTID相关参数
[mysqld]
#gtid_mode=ON
#enforce_gtid_consistency
#log-slave-updates
4.重启数据库
[root@db04 ~]# /etc/init.d/mysqld restart
5.记录主库的binlog和position
mysql> show master status;
+------------------+----------+
| File | Position |
+------------------+----------+
| mysql-bin.000008 | 120 |
+------------------+----------+
6.在从库上执行，change master语句
change master to
master_host='10.0.0.51',
master_user='rep',
master_password='123',
master_log_file='mysql-bin.000008',
master_log_pos=120;
7.开启主从复制
mysql> start slave;
8.查看主从复制状态
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
9.启动MHA
[root@db02 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/app1/manager.log 2>&1 &
10.查看MHA状态
[root@db02 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:33304) is running(0:PING_OK), master:10.0.0.51
MHA优先级参数
#开启候选主库参数
candidate_master=1
如果从库落后主库数据100M以上，即便是设置`candidate_master=1`也不会切换为主库
#关闭检测主从复制中从库落后主库数据状态
check_repl_delay=0
MHA自动找到数据最新的slave
#db04 10.0.0.54 是 master
#db01 10.0.0.51 停止IO线程
mysql> stop slave io_thread;
#db02 10.0.0.52 停止IO线程
mysql> stop slave io_thread;
Latest slaves (Slaves that received relay log files to the latest):
Tue Dec 17 11:48:06 2019 - [info] 10.0.0.53(10.0.0.53:3306) Version=5.6.40-log (oldest major version between slaves) log-bin:enabled
网友评论