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 ;