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

MySQL MHA