centos7搭建主从集群rpm方式

1.环境软件版本

环境&软件	版本
虚拟机&VirtualBox	6.1.8 r137981 (Qt5.6.2)
服务器&CentOS	7.1
数据库&Mysql	5.7.28
远程连接&Xshell	6
远程文件传输&Xftp	6

2.环境架构介绍

机器名称	IP	角色	权限
master1	192.168.33.4	主库Master	可读写
slave1	192.168.55.4	从库Slave1	可读
slave2	192.168.66.4	从库Slave2	可读
master2	192.168.45.4	主库Master2	可读写
slave3	192.68.46.4	从库Slave3	可读
slave4	192.168.47.4	从库Slave4	可读

3. MySQL主从搭建

3.1 MySQL安装（6台）rpm方式

下载wget

yum -y install wget

备份本地yum源

mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup

下载新的（阿里yum源）CentOS-Base.repo 到/etc/yum.repos.d/

wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

清楚缓存并编译

yum clean all
yum makecache

查看yum源

yum repolist

下载Mysql压缩包并通过xftp上传****

wegt https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.28-1.el7.x86_64.rpm- bundle.tar

解压缩包
tar xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
mysql-community-embedded-5.7.28-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
mysql-community-devel-5.7.28-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.28-1.el7.x86_64.rpm
mysql-community-libs-5.7.28-1.el7.x86_64.rpm
mysql-community-test-5.7.28-1.el7.x86_64.rpm
mysql-community-common-5.7.28-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.28-1.el7.x86_64.rpm
mysql-community-client-5.7.28-1.el7.x86_64.rpm
mysql-community-server-5.7.28-1.el7.x86_64.rpm

安装

要移除CentOS自带的mariadb-libs，不然会提示冲突

rpm -qa|grep mariadb 
rpm -e mariadb-libs-5.5.35-3.el7.x86_64 --nodeps

下面就利用安装命令来安装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
rpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpm

[root@slave3 mysql]# rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
警告：mysql-community-server-5.7.28-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
错误：依赖检测失败：
	/usr/bin/perl 被 mysql-community-server-5.7.28-1.el7.x86_64 需要
	libaio.so.1()(64bit) 被 mysql-community-server-5.7.28-1.el7.x86_64 需要
	libaio.so.1(LIBAIO_0.1)(64bit) 被 mysql-community-server-5.7.28-1.el7.x86_64 需要
	libaio.so.1(LIBAIO_0.4)(64bit) 被 mysql-community-server-5.7.28-1.el7.x86_64 需要
	net-tools 被 mysql-community-server-5.7.28-1.el7.x86_64 需要
	perl(Getopt::Long) 被 mysql-community-server-5.7.28-1.el7.x86_64 需要
	perl(strict) 被 mysql-community-server-5.7.28-1.el7.x86_64 需要

server报错：安装对应包
yum -y install perl.x86_64
yum install -y libaio.x86_64
yum -y install net-tools.x86_64

启动

初始化用户

mysqld --initialize --user=mysql

查看初始密码

cat /var/log/mysqld.log | grep password  

启动mysql服务

systemctl start mysqld

配置为开机启动

systemctl enable mysqld

进入mysql

mysql -u root -p
输入初始密码

修改密码并授权

SET PASSWORD = PASSWORD('123456');
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
FLUSH PRIVILEGES;
use mysql                                            #访问mysql库
update user set host = '%' where user = 'root';      #使root能在任何host访问
FLUSH PRIVILEGES;                                    #刷新 PRIVILEGES; 

exit												 #退出mysql命令

重启Mysql

systemctl restart mysqld

关闭防火墙 或者永久禁用

systemctl stop firewalld  systemctl disable firewalld

3.2 MySQL主从配置

3.2.1 Master节点

修改Master配置文件

user=mysql
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
#禁用DNS主机名查找
skip-name-resolve
#bin_log配置 
log_bin=mysql-bin 
server-id=1 
#每次写入都同步到binlog
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在线程执行完后被自动删除
relay_log_purge=0 

重启服务

systemctl restart mysqld

主库给从库授权

grant replication slave on *.* to root@'%' identified by '123456'; 
grant all privileges on *.* to root@'%' identified by '123456'; 
flush privileges; 

//查看主库
show master status;
master_log_file='mysql- bin.000001',master_log_pos=154

3.2.2 Slave节点

修改Slave配置文件，两台Slave的server-id分别设置为2和3

user=mysql
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
#禁用DNS主机名查找
skip-name-resolve
#bin_log配置 
log_bin=mysql-bin 
#服务器ID,从库1是2,从库2是3
server-id=2
#每次写入都同步到binlog
sync-binlog=1 
binlog_format=ROW
#忽略系统数据库
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在线程执行完后被自动删除
relay_log_purge=0 
#只读
read_only=1

重启服务

systemctl restart mysqld

开启同步

在Master节点的Mysql命令行执行：

show master status;

在Slave节点的MySQL命令行执行如下命令：

change master to master_host='192.168.45.4',master_port=3306,master_user='root',master_password ='123456',master_log_file='mysql-bin.000001',master_log_pos=154;

#开启同步线程

STOP SLAVE ;

START SLAVE ;