mysql-MHA架构实践记录

前提条件，系统安装为centos7，mysql5.7未提供centos8的版本。

一、规划

角色	ip	权限
master	113.31.153.7	可读写、主库
slave1	113.31.153.1	只读、从库
slave2	106.75.210.2	只读、从库
MHA	8.131.93.1	高可用监控

二、下载安装

mysql安装(https://blog.csdn.net/wudinaniya/article/details/81094578)

https://downloads.mysql.com/archives/community/

1、清理环境

（mariadb是从mysql来的，是它的一个分支，需要清理掉）

centos7会自带一个mariadb

1、查看是否已安装

rpm -qa |grep -i mysql
rpm -qa |grep -i mariadb（centos7默认系统自带的）

2、清理

rpm -e mariadb --nodeps
rpm -e mariadb-libs --nodeps

3、按照依赖关系依次安装rpm包 依赖关系依次为common→libs→client→server

rpm -ivh mysql-community-common-5.7.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.22-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.22-1.el7.x86_64.rpm

注：ivh中， i-install安装；v-verbose进度条；h-hash哈希校验**

4、启动mysql服务并重置密码

初始化用户并启动服务，设置为开机启动

mysqld --initialize --user=mysql
systemctl start mysqld
systemctl enable mysqld

查看初始密码

tail -500f /var/log/mysqld.log |grep password

用默认密码登录，修改密码，登录mysql，执行

mysql -uroot -p
set password=password('root');

2、关闭防火墙

centos7用firewalld，要先关闭iptables，同时设置firewalld开机禁用

systemctl stop iptables
systemctl stop firewalld
systemctl disabled firewalld.service

检查下，ecs外网防火墙是否开放了3306端口，否则下面执行ssh是不通的

3、设置主从配置

1、修改master

#bin_log配置
log_bin=mysql-bin
server-id=1
sync-binlog=1
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#relay_log配置
relay_log=mysql-relay-bin
log_slave_updates=1
relay_log_purge=0

2、修改slave配置，用server-id做区分

#bin_log配置
log_bin=mysql-bin
#服务器ID,从库1是2,从库2是3
server-id=2
sync-binlog=1
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#relay_log配置
relay_log=mysql-relay-bin
log_slave_updates=1
relay_log_purge=0

read_only=1

修改 完都要重新启动

systemctl restart mysqld

3、master授权

// 登录主库进行授权
mysql -uroot -proot
grant replication slave on *.* to root@'%' identified by 'your_password'; 
grant all privileges on *.* to root@'%' identified by 'your_password';
flush privileges;

show master status;

4、登录mysql slave节点，开启同步

stop slave;
change master to master_host='113.31.153.7',master_port=3306,master_user='root',master_password ='root',master_log_file='mysql-bin.000021',master_log_pos=1872;
start slave;

show slave status \G;

4、开启半同步复制

1、master需要一个插件

install plugin rpl_semi_sync_master soname 'semisync_master.so';
show variables like '%semi%';

修改my.cnf

# 自动开启半同步复制
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=1000

2、slave需要一个插件

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
show variables like '%semi%';

修改my.cnf

# 自动开启半同步复制
rpl_semi_sync_slave_enabled=ON

5、MHA

1、环境准备

四台机器ssh互通，设置完成后通过ssh访问无需密码

1、四台机器分别执行

ssh-keygen -t rsa

2、三台mysql分别执行命令将公钥拷贝到mha

ssh-copy-id mha_ip

3、mha上查看秘钥

cat /root/.ssh/authorized_keys

4、执行命令将mha的秘钥也拷贝进来

cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys

5、将mha的秘钥拷贝到三台mysql

scp /root/.ssh/authorized_keys root@113.31.153.7:/root/.ssh/authorized_keys
scp /root/.ssh/authorized_keys root@113.31.153.1:/root/.ssh/authorized_keys
scp /root/.ssh/authorized_keys root@106.75.210.245:/root/.ssh/authorized_keys

6、通过ssh命令测试互联（无需密码）

2、下载安装

MySQL5.7对应的MHA版本是0.5.8，github上分别下载MHA manager和 node的安装包

https://github.com/yoshinorim/mha4mysql-manager/releases/tag/v0.58
https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58

1、mha4台节点安装mha4mysql-node

yum install perl-DBD-MySQL -y
wget https://github.com/yoshinorim/mha4mysqlnode/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

2、mha上还需要安装mha4mysql-manager。

MHA的manager又依赖了perl-Config-Tiny、perl-Log-Dispatch、perl-Parallel-ForkManager

wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm rpm -ivh epel-release-latest-7.noarch.rpm 

yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-ParallelForkManager -y

rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

3、mha监控实例的日志文件

mkdir -p /var/log/mha/app1
touch /var/log/mha/app1/manager.log

4、监控全局配置文件修改

vim /etc/masterha_default.cnf

[server default]
#主库用户名，在master mysql的主库执行下列命令建一个新用户
#create user 'mha'@'%' identified by '123123';
#grant all on *.* to mha@'%' identified by '123123';
#flush privileges;
user=mha
password=123123
port=3306
#ssh登录账号
ssh_user=root
#从库复制账号和密码
repl_user=root
repl_password=123456
port=3306
#ping次数
ping_interval=1
#二次检查的主机
secondary_check_script=masterha_secondary_check -s 106.75.210.2 -s
113.31.153.7 -s 113.31.153.1

5、修改监控实例配置文件

mkdir -p /etc/mha
vim /etc/mha/app1.cnf

[server default]
#MHA监控实例根目录
manager_workdir=/var/log/mha/app1
#MHA监控实例日志文件
manager_log=/var/log/mha/app1/manager.log
#[serverx] 服务器编号
#hostname 主机名
#candidate_master 可以做主库
#master_binlog_dir binlog日志文件目录
[server1]
hostname=113.31.153.7
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server2]
hostname=113.31.153.1
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server3]
hostname=106.75.210.2
candidate_master=1
master_binlog_dir="/var/lib/mysql"

3、mha配置检测

mha manager上执行以下两个检测

1、ssh通信检测

masterha_check_ssh --conf=/etc/mha/app1.cnf

2、mysql主从复制检测

masterha_check_repl --conf=/etc/mha/app1.cnf

3、mha监控启动

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

4、查看监控日志命令

tail -500f /var/log/mha/app1/manager.log

4、模拟mha故障转移

1、停掉master节点

systemctl stop mysqld

2、查看mha日志

tail -500f /var/log/mha/app1/manager.log