1. 环境软件版本
环境&软件 | 版本 |
---|---|
虚拟机&VMware Workstation Pro | VMwareworkstation15.5.6 |
服务器&Centos | CentOS-7-x86_64-DVD-1503-01.iso |
数据库&mysql | mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar |
远程连接&Xshell | 5 |
远程文件传输&Xftp | 5 |
2. 环境架构设计
机器名称 | IP | 角色 | 权限 |
---|---|---|---|
mysql-master1 | 192.168.5.128 | mysql主库 | 读写 |
mysql-slave1 | 192.168.5.129 | mysql从库1 | 读 |
mysql-slave2 | 192.168.5.131 | mysql从库2 | 读 |
MHA | 192.168.5.132 | MHA manager | mysql高可用监控 |
3. mysql 主从复制搭建
3.1 环境准备
关闭4台机器的防火墙
关闭防火墙
systemctl stop firewalld
禁用防火墙
systemctl disable firewalld
查看防火墙状态
systemctl status firewalld
安装mysql到128,129,131三台服务器上
查看是否安装了mariadb,有安装则卸载
rpm -qa | grep mariadb
卸载
rpm -e mariadb-libs-5.5.60-1.el7_5.x86_64 --nodeps
解压安装包
tar mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
按顺序安装mysql文件
rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
初始化用户
mysqld --initialize --user=mysql
进入日志查看用户密码
cat /var/log/mysqld.log | grep password
配置mysql主从复制
在mysql主库192.168.5.128上配置,进去/etc/my.cnf,增加配置
#bin_log配置
log_bin=mysql-bin
server-id=1
sync-binlog=1
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performation_schema
binlog-ignore-db=sys
#relay_log配置
relay_log=mysql-relay-bin
log_slave_updates=1
relay_log_purge=0
在mysql主库192.168.5.129,192.168.5.131上配置,进去/etc/my.cnf,增加配置
#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=performation_schema
binlog-ignore-db=sys
#relay_log配置
relay_log=mysql-relay-bin
log_slave_updates=1
relay_log_purge=0
read_only=1
mysql命令
启动
systemctl start mysqld
停止
systemctl stop mysqld
重启
systemctl restart mysqld
启动mysql并进入,修改初始密码
SET PASSWORD = PASSWORD('123456');
192.168.5.128主库配置
开放权限
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
mysql> flush privileges;
查看主库信息
mysql> show master status;
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
| mysql-bin.000020 | 408 | | information_schema,mysql,performation_schema,sys | |
+------------------+----------+--------------+--------------------------------------------------+-------------------+
192.168.5.129,192.168.5.131从库信息配置
查看从库是否开启,如开启,则需要先关闭
查看从库状态
mysql> show slave status \G;
关闭从库
mysql> stop slave;
关联192.168.5.128主库
mysql> change master to master_host='192.168.5.128',master_port=3306,master_user='root',master_password ='root',master_log_file='mysql-bin.000020',master_log_pos=408;
master_log_file: 关联上面主库查看状态时的File
master_log_pos:关联上面主库查看状态时的Position
开启从库复制
mysql> start slave;
主从配置就到这里
4. 设置半同步复制
192.168.5.128主库配置
进入mysql安装插件
install plugin rpl_semi_sync_master soname 'semisync_master.so'; show variables like '%semi%'
设置全局参数
set global rpl_semi_sync_master_enabled=ON
set global rpl_semi_sync_master_timeout=1000
重启mysql
192.168.5.129,192.168.5.131从库配置
进入mysql安装插件
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
设置全局参数
set global rpl_semi_sync_slave_enabled=ON
重启mysql
通过查看主库日志确定半同步复制
cat /var/log/mysqld.log
日志出现semi复制描述
5. 搭建MHA高可用
安装软件
在4个服务器都安装node节点
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
在192.168.5.132的MHA管理端再安装一个manager
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
设置ssh免密通信,4台机器同样操作
编辑
vi /etc/hosts
增加机器配置
192.168.5.128 master1.com master1
192.168.5.129 slave1.com slave1
192.168.5.131 slave2.com slave2
192.168.5.132 mha.com mha
生成rsa密钥
ssh-keygen -t rsa
拷贝到另外机器上
ssh-copy-id -i .ssh/id_rsa.pub root@master1
ssh-copy-id -i .ssh/id_rsa.pub root@slave1
ssh-copy-id -i .ssh/id_rsa.pub root@slave2
ssh-copy-id -i .ssh/id_rsa.pub root@mha
MHA manager配置
mkdir -p /var/log/mha/app1
touch /var/log/mha/app1/manager.log
配置监控全局配置文件
vi /etc/masterha_default.cnf
[server default]
#用户名
user=root
#密码
password=root
#ssh登录账号
ssh_user=root
#主从复制账号
repl_user=root
#主从复制密码
repl_password=root
#ping次数
ping_interval=1
#二次检查的主机
secondary_check_script=masterha_secondary_check -s 192.168.5.128 -s 192.168.5.129 -s 192.168.5.131
配置监控实例配置文件
vi /etc/mha/app1.cnf
[server default]
manager_log=/var/log/mha/app1/manager.log
manager_workdir=/var/log/mha/app1
[server1]
candidate_master=1
hostname=192.168.5.128
master_binlog_dir="/var/lib/mysql"
[server2]
candidate_master=1
hostname=192.168.5.129
master_binlog_dir="/var/lib/mysql"
[server3]
candidate_master=1
hostname=192.168.5.131
master_binlog_dir="/var/lib/mysql"
在MHA-manager上
通信检查
masterha_check_ssh --conf=/etc/mha/app1.cnf
检测MySQL主从复制
masterha_check_repl --conf=/etc/mha/app1.cnf
以上都没问题后启动
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 &
查看MHA日志
tail -f -n 200 /var/log/mha/app1/manager.log
模拟主库192.168.5.128宕机,在MHA日志可以看到主库切换,主库切换到129机器上
重启192.168.5.128上,绑定192.168.5.129主库
change master to master_host='192.168.5.129',master_port=3306,master_user='root',master_password ='root',master_log_file='xxx',master_log_pos=xxx;
开启同步
start slave;
切换主库
masterha_master_switch --conf=/etc/mha/app1.cnf --master_state=alive --new_master_host=192.168.5.128 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000
6. 遇到的问题
6.1 MHA高可用模拟主库宕机时,MHA的/etc/mha/app1.cnf配置中会把宕机服务的节点信息给删除掉,所以想要重新将宕机节点作为主库,需要把节点信息重新配置回/etc/mha/app1.cnf中。