架构
使用一台MHA manager、一台MySQL master节点、两台MySQL slave节点
软件版本
虚拟机:Ubuntu 18
MySQL:5.7.32
MHA:0.54
环境检查
安装ssh组件
# 安装ssh
sudo apt-get install openssh-server
# 开启ssh服务
sudo service ssh start
配置ssh 免密码登录生成密钥
shell ssh-keygen -t rsa将公钥写入到 authorized_keys 文件中
可以使用scp进行将公钥复制到一个机器上,然后一起写入到authorized_keys中将authorized_keys文件分发到各个节点的 /root/.ssh/目录下
MySQL安装
# 执行更新
sudo apt-get update
# 安装 mysql
sudo apt-get install mysql-server
# 初始化MySQL
sudo mysql_secure_installation
# 检查mysql状态
systemctl status mysql.service
MySQL初始化
修改配置文件,注释掉 bind-address = 127.0.0.1
vi /etc/mysql/mysql.conf.d/mysqld.cnf
主库配置(Master)MySQL配置文件配置
vim /etc/mysql/my.cnf
mysql
[mysqld]
character-set-server=utf8mb4
default-time-zone='+8:00'
innodb_rollback_on_timeout='ON'
max_connections=500
innodb_lock_wait_timeout=500
# 开启binlog功能
log_bin=mysql-bin
# 服务唯一id,不能重复
server-id=1
# 写入日志始终同步磁盘
sync-binlog=1
# 需要忽略同步binlog的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
重启mysql服务
shell sudo service mysql restart开始授权开启主从同步
# 每个slave使用标准的MySQL用户名和密码连接master。对进行复制操作的用户授予REPLICATION SLAVE权限
mysql> grant replication slave on *.* to 'root'@'%' identified by 'root';
# 授于远程登录访问权限
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
# 刷新 privileges(使privileges生效)
mysql> flush privileges;安装semi
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';开启semi(半同步复制)
# 查看是否开启了esmi
mysql> show variables like '%semi%';
# 是否开启半同步复制,需要开启
mysql> set global rpl_semi_sync_master_enabled=1;
# 配置同步延迟时间(毫秒)
mysql> set global rpl_semi_sync_master_trace_level=1000;
从库配置(Slave)MySQL配置文件配置
vim /etc/mysql/my.cnf
[mysqld]
character-set-server=utf8mb4
default-time-zone='+8:00'
innodb_rollback_on_timeout='ON'
max_connections=500
innodb_lock_wait_timeout=500
# 服务唯一id,不能重复
server-id=2
# 配置relay log
relay_log=mysql-relay-bin
# 配置数据库只读
read-only=1
#--------------备用master开启binlog-------------------#
# 写入日志始终同步磁盘
sync-binlog=1
# 需要忽略同步binlog的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
重启mysql服务
shell sudo service mysql restart授权
# 授于远程登录访问权限
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
# 刷新 privileges(使privileges生效)
mysql> flush privileges;配置同步日志
mysql> change master to master_host='172.22.0.3', master_port=3306, master_user='root', master_password='root', master_log_file='mysql-bin.000006', master_log_pos=302;安装semi
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';开启semi
mysql mysql> set global rpl_semi_sync_slave_enabled=1;查看是否开启
mysql mysql> show variables like '%semi%';启动slave
mysql> start slave;
# 查看slave状态
mysql> show slave status;
MHA环境准备
组件介绍Manager节点
masterha_check_ssh:MHA依赖的SSH环境检测工具
masterha_check_repl:MySQL复制环境检测工具
masterha_manager:MHA服务主程序
masterha_check_status:MHA运行状态检测工具
masterha_master_monitor:MySQL master节点可用性检测工具
masterha_master_switch:master节点切换工具
masterha_conf_host:添加或删除配置节点
masterha_stop:关闭MHA服务的工具
Node节点
save_binary_logs:保存和复制master的二进制日志
apply_diff_relay_logs:识别差异的中继日志事件并应用于其他slave
filter_mysqlbinlog:去除不必要的ROLLBACK事件(MHA已不在使用这个工具)
purge_relay_logs:清除中继日志(不会阻塞SQL线程)
自定义扩展
sccondary_check_script:通过多条网络路由检测master的可用性
master_ip_failover_script:更新application使用的masterrip
shutdown_script:强制关闭master节点
report_script:发送报告
init_conf_load_script:加载初始化配置参数
master_ip_online_change_script:更新master节点ip地址
安装HMA node
所有节点都需要安装mha node
# install perl
apt-get install libdbd-mysql-perl
# 下载 mha node
wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysql-master-ha/mha4mysql-node_0.54-0_all.deb
# 安装 mha node
dpkg -i mha4mysql-node_0.54-0_all.deb
安装MHA manager
mha manager 节点安装
# install perl
apt-get install libdbd-mysql-perl
# 下载mha manager
wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysql-master-ha/mha4mysql-manager_0.54-0_all.deb
# 安装mha manager
dpkg -i mha4mysql-manager_0.54-0_all.deb
配置MHA manager
vim /etc/masterha_default.cnf
[server default]
# mysql user and password
user=root
password=root
#mysql复制帐号,用来在主从机之间同步二进制日志等
repl_user=root
repl_password=root
#ping间隔,用来检测master是否正常
ping_interval=1
# working directory on the manager
manager_workdir=/masterha/app1
# manager log file
manager_log=/masterha/app1/app1.log
# working directory on MySQL servers
remote_workdir=/masterha/app1
[server1]
hostname=10.6.130.42
#候选人,master挂掉时候优先让它顶
candidate_master=1
#用防止master故障时,切换时slave有延迟,卡在那里切不过来
check_repl_delay=0
[server2]
hostname=10.6.130.43
#候选人,master挂掉时候优先让它顶
candidate_master=1
#用防止master故障时,切换时slave有延迟,卡在那里切不过来
check_repl_delay=0
[server3]
hostname=10.6.130.44
#不能成为master
no_master=1
#用防止master故障时,切换时slave有延迟,卡在那里切不过来
check_repl_delay=0
检测 ssh 通讯
masterha_check_ssh --conf=/etc/masterha_default.cnf
检测主从节点
masterha_check_repl --conf=/etc/masterha_default.cnf
启动 MHA
masterha_manager --conf=/etc/masterha_default.cnf --ignore_last_failover &
检测 MHA 状态
masterha_check_status --conf=/etc/masterha_default.cnf
检验MHA服务
service mysql stop
观察mha mannager 日志
Wed Dec 2 15:41:33 2020 - [info] Resetting slave info on the new master..
Wed Dec 2 15:41:33 2020 - [info] 10.6.130.43: Resetting slave info succeeded.
Wed Dec 2 15:41:33 2020 - [info] Master failover to 10.6.130.43(10.6.130.43:3306) completed successfully.
Wed Dec 2 15:41:33 2020 - [info]
----- Failover Report -----
masterha_default: MySQL Master failover 10.6.130.42 to 10.6.130.43 succeeded
Master 10.6.130.42 is down!
Check MHA Manager logs at NXG-AUTO-BASE:/masterha/app1/app1.log for details.
Started automated(non-interactive) failover.
The latest slave 10.6.130.43(10.6.130.43:3306) has all relay logs for recovery.
Selected 10.6.130.43 as a new master.
10.6.130.43: OK: Applying all logs succeeded.
10.6.130.44: This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
10.6.130.44: OK: Applying all logs succeeded. Slave started, replicating from 10.6.130.43.
10.6.130.43: Resetting slave info succeeded.
Master failover to 10.6.130.43(10.6.130.43:3306) completed successfully.
表创建
position:id(int)、name(varchar)、salary(varchar)、city(varchar)
position_detail:id(int)、pid(int)、description(text)
mysql> create database lagou;
mysql> use lagou;
# 创建表positionCREATE TABLE position (
`id` int(11) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`salary` decimal(10,2) DEFAULT NULL,
`city` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
# 插入数据mysql> insert into position values(2, 'tengfei', '30000', 'beijing');
# 创建表position_detailCREATE TABLE position_detail (
`id` int(0) NOT NULL,
`pid` int(0) NULL,
`description` text NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
注意事项虚拟机要安装openssh组件,不然无法外部访问
各个节点之间需要将各个节点的公钥放到.ssh/authorized_keys 下
MySQL 配置文件中记得注释掉 bind-address = 127.0.0.1
修改mysql 配置文件要重启
安装hma组件之前需要安装libdbd-mysql-perl
参考文章
使用的docker compose文件
version:'3.3'services:mysql-master1:image:mysql:5.7container_name:mysql-master1ports:- "3316:3306"restart:alwaysvolumes:- ./master1/data:/var/lib/mysql- ./master1/config:/etc/mysql/conf.denvironment:MYSQL_ROOT_PASSWORD:"root"mysql-master2:image:mysql:5.7container_name:mysql-master2ports:- "3326:3306"restart:alwaysvolumes:- ./master2/data:/var/lib/mysql- ./master2/config:/etc/mysql/conf.denvironment:MYSQL_ROOT_PASSWORD:"root"mysql-slave1:image:mysql:5.7container_name:mysql-slave1ports:- "3317:3306"restart:alwaysvolumes:- ./slave1/data:/var/lib/mysql- ./slave1/config:/etc/mysql/conf.denvironment:MYSQL_ROOT_PASSWORD:"root"mysql-slave2:image:mysql:5.7container_name:mysql-slave2ports:- "3318:3306"restart:alwaysvolumes:- ./slave2/data:/var/lib/mysql- ./slave2/config:/etc/mysql/conf.denvironment:MYSQL_ROOT_PASSWORD:"root"mysql-slave3:image:mysql:5.7container_name:mysql-slave3ports:- "3327:3306"restart:alwaysvolumes:- ./slave3/data:/var/lib/mysql- ./slave3/config:/etc/mysql/conf.denvironment:MYSQL_ROOT_PASSWORD:"root"mysql-slave4:image:mysql:5.7container_name:mysql-slave4ports:- "3328:3306"restart:alwaysvolumes:- ./slave4/data:/var/lib/mysql- ./slave4/config:/etc/mysql/conf.denvironment:MYSQL_ROOT_PASSWORD:"root"
master 配置文件
[mysqld]
character-set-server=utf8mb4
default-time-zone='+8:00'
innodb_rollback_on_timeout='ON'
max_connections=500
innodb_lock_wait_timeout=500
# enable binary log
log_bin=mysql-bin
server-id=1
# write log always sync disk
sync-binlog=1
# ignore sync database
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
slave配置文件
[mysqld]
character-set-server=utf8mb4
default-time-zone='+8:00'
innodb_rollback_on_timeout='ON'
max_connections=500
innodb_lock_wait_timeout=500
server-id=11
relay_log=mysql-relay-log
read-only=1
机器对应角色&作用&ip地址
mysql-master1
作用:mysql 主节点1,主要做写入操作
IP地址:172.17.0.5
mysql-master2
作用:mysql 主节点2,主要做写入操作
IP地址:172.17.0.5
mysql-slave1
作用:mysql 从节点1,隶属于master1的从节点
IP地址:172.17.0.5
mysql-slave2
作用:mysql 从节点2,隶属于master1的从节点
IP地址:172.17.0.5
mysql-slave3
作用:mysql 从节点3,隶属于master2的从节点
IP地址:172.17.0.5
mysql-slave4
作用:mysql 从节点4,隶属于master2的从节点
IP地址:172.17.0.5