工具准备
vmware15.5
CentOS-7-x86_64-DVD-1503-01.iso
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm
mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
mysql-installer-community-5.7.28.0.msi
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
xshell
安装3台(一主二从)虚拟机
出现的问题:ifconfig没有出现ip地址
vim /etc/sysconfig/network-scripts/ifcfg-eno16777736
将ONBOOT=NO 改成ONBOOT=yes
service network restart
ifconfig
开始
解压mysql安装包
tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
查看是否有mariadb
rpm -qa|grep mariadb
有得话移除
rpm -e mariadb-libs-5.5.41-2.el7_0.x86_64 --nodeps
再查看有没有
然后执行
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
rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm
初始化
mysqld --initialize --user=mysql
查看日志
cat /var/log/mysqld.log
末尾密码:,u&klPrid5BF iKyPs6+wKAMK
启动服务
systemctl start mysqld.service
查看有没有启动
systemctl status mysqld.service
mysql -uroot -p
密码就是上面那个末尾密码
重设密码
set password=password('root');
关闭防火墙
systemctl stop firewalld
关闭开机启动
systemctl disable firewalld.service
修改配置
主库
cd /etc/
vi my.cnf
log_bin=mysql-bin
server-id=1
sync-binlog=1
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=sys
保存退出重启:systemctl restart mysqld
授权:mysql -uroot -p
grant replication slave on *.* to 'root'@'%' identified by 'root';
grant all privileges on *.* to 'root'@'%' identified by 'root';
刷新权限:
flush privileges;
从库
cd /etc/
vi my.cnf
配置修改:
server-id=2
relay_log=mysql-relay-bin
read_only=1
重启:systemctl restart mysqld;
mysql -uroot -p
看一下从库:show slave status;
根据master信息:
change master to master_host='192.168.242.129',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=869;
启动:start slave;
在主库建库建表插数据,从库看;
加从库
如果主库已有大量数据时,依赖主从复制很慢,这时可以使用工具mysqldump
mysqldump --all-databases > mysql_backup_all.sql -uroot -p
先到从库恢复,再修改从库配置;
主从库安装半同步复制插件
进入mysql
install plugin rpl_semi_sync_master soname 'semisync_master.so';
查看配置
show variables like '%semi%';
修改配置
set global rpl_semi_sync_master_enabled=1;
set global rpl_semi_sync_master_timeout=1000;
从库同样步骤
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=1;
可以在主库操作数据库
然后查看日志看是不是半同步
cd /
cd /var
cd log
cat mysqld.log
MHA 故障切换,主从提升
ssh
4台服务器都执行,一直回车
ssh-keygen -t rsa
在三台MySQL服务器分别执行下面命令
ssh-copy-id 192.168.242.136
这里如果报错看下面解决方法
在MHA Manager服务器上检查
cat /root/.ssh/authorized_keys
将MHA Manager的公钥添加到authorized_keys文件中
cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys
从MHA Manager服务器执行下面命令,向其他三台MySQL服务器分发公钥信息。
scp /root/.ssh/authorized_keys root@192.168.242.163:/root/.ssh/authorized_keys
scp /root/.ssh/authorized_keys root@192.168.242.164:/root/.ssh/authorized_keys
scp /root/.ssh/authorized_keys root@192.168.242.165:/root/.ssh/authorized_keys
检测是否互通
ssh 192.168.31.163
exit
vi /etc/ssh/ssh_config
在最后添加
StrictHostKeyChecking no
UserKnownHostsFile /dev/null
MHA安装
分别导入安装node到4台服务器,以及manager到mha服务器
yum install perl-DBD-MySQL -y
这里报错看下面解决
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm --no-check-certificate
编辑此文件vi /etc/yum.repos.d/epel.repo
第一二行
baseurl=http://download.fedoraproject.org/pub/epel/6/$basearch
#mirrorlist=https://mirrors.fedoraproject.org/metalink?repo=epel-6&arch=$basearch
安装manager
wget http://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm rpm -ivh epel-release-latest-7.noarch.rpm --no-check-certificate
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel- ForkManager -y
wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm --no-check-certificate
rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm --no-check-certificate
rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
问题:没有可用软件包 perl-Parallel-
yum -y update
yum install perl-Parallel-ForkManager
配置文件
mkdir -p /var/log/mha/app1
touch /var/log/mha/app1/manager.log
配置监控全局配置文件
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 192.168.242.133 -s 192.168.242.134 -s 192.168.242.135
配置监控实例配置文件
先使用 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=192.168.242.133
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server2]
hostname=192.168.242.134
candidate_master=1
master_binlog_dir="/var/lib/mysql"
[server3]
hostname=192.168.242.135
candidate_master=1
master_binlog_dir="/var/lib/mysql"
MHA 配置检测
执行ssh通信检测
masterha_check_ssh --conf=/etc/mha/app1.cnf
检测MySQL主从复制
masterha_check_repl --conf=/etc/mha/app1.cnf
出现“MySQL Replication Health is OK.”证明MySQL复制集群没有问题。
MHA Manager启动
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 &
查看监控日志命令如下:
tail -f /var/log/mha/app1/manager.log
测试MHA故障转移
关闭Master MySQL服务器服务,模拟主节点崩溃
systemctl stop mysqld
查看日志