CentOS7 Linux 搭建 MySQL 主从复制

发表于 更新于 分类于 阅读次数： 14 本文字数： 2.5k 阅读时长 ≈ 9 分钟

前言

MySQL 主从复制实战。以前总听说集群之类的，从来没有好好思考过。如今有空余时间，终于亲自搭建环境得以实现 MySQL 主从复制。

从搭建虚拟机环境，以及安装 Redhat 系列的 centos7.5 服务器，部署 MySQL5.6 环境。嘿嘿，测试使用虚拟机搭建美滋滋。当然你也可以花 money，多建几台云服务器实现集群环境。

正文

MySQL 主从复制实战

主要使用到的环境说明

在这里插入图片描述

数据库版本

1
2
#使用centos7.3初始安装就可选择安装所需基本环境
5.6.51-log MySQL Community Server (GPL)

操作系统

1
linux for centos7.3  or centos7.5

测试环境：VMware12 虚拟机搭建三台 centos7.3 服务器
服务器 IP 地址：

1
2
3
IP_MASTER：192.168.245.131
IP_SLAVE：192.168.245.132
IP_SLAVE：192.168.245.132

搭建服务器环境参考文章：VMware12 配合使用 Centos7 搭建 Linux 开发环境

测试模型：一主两从，即（master、slave、slave）
知识宝库：VMware 的简单使用，Linux 基本命令、MySQL 数据库 CRUD
tips：VMware8 亦可搭建，解决内存过小，这个小伙子有点东西啊！
目标实现：MySQL 主从复制

一、binlog 与 relay-log

1.1 binlog

作用：记录数据库改变的数据，在第四步可以进行验证。

查看 binlog 日志 

1
2
/** 查看binlog日志 **/
SHOW BINLOG EVENTS IN 'mysql-bin.000004'

mysqlbinlog 查看 binlog 日志 

1
2
/** mysqlbinlog查看binlog日志 **/
mysqlbinlog --short-form --force-if-open --base64-output=never /var/lib/mysql/mysql-bin.000004 //对应你MySQL的安装目录

1.2 relay-log

作用：连接 master 与 slave 节点的核心

1
2
01 master.info
02 relay-log.info

二、master 节点配置

2.1 master 节点配置

MySQL 数据库配置文件 my.cnf

1
vim /etc/my.cnf

加入配置

1
2
3
4
5
6
7
8
9
10
11
12
13
[mysqld]
#开启binlog
log-bin=mysql-bin
#不与其它从节点重复即可
server-id=101
#需要同步的数据，如果不配置则同步全部
binlog-do-db=test_db
#binlog日志保留的天数，清除超过10天的日志；防止日志文件过大，导致磁盘空间不足
expire-logs-days=10
#开启混合模式
binlog_format=mixed
innodb_file_per_table=ON
skip_name_resolve=ON

2.2 重启 mysql

1
2
#readhat7系列使用方式
systemctl restart mysql

2.3 通过命令查看主节点状态

1
show master status\G;

注意查看：master_log_pos 的值，这点很重要，master 与搭建集群的两个 slave 节点保持一致。

2.4 授权 slave 节点账号

关联授权搭建的两个 slave 子节点

1
2
#关联授权搭建的两个slave子节点
GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.245.132' IDENTIFIED BY '123456';
1
2
#关联授权搭建的两个slave子节点
GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.245.133' IDENTIFIED BY '123456';

刷新权限

1
2
#刷新权限
flush privileges;

2.5 查看验证

显示查看日志

1
2
#显示查看日志
show global variables like '%log%';

显示查看服务

1
2
#显示查看服务
show global variables like '%server%';

三、slave 节点配置

3.1 slave 节点配置

MySQL 数据库配置文件 my.cnf

1
2
#slave01（从节点配置）
vim /etc/my.cnf
1
2
3
[mysqld]
#不与主节点以及其它从节点重复即可
server-id=102

3.2 登录 mysql 设置

1
mysql -uroot -p

输入命令如下

1
2
3
4
5
6
7
CHANGE MASTER TO
MASTER_HOST='192.168.245.131',//MASTER主节点IP
MASTER_USER='root',//创建的用户
MASTER_PASSWORD='123456',//用户密码
MASTER_LOG_FILE='mysql-bin.000001',//MASTER主机binlog日志名称，必须是最新的一个
MASTER_LOG_POS=120,//binlog日志偏移量，对应master节点的
master_port=3306;/端口号

3.3 开启中继日志

1
2
3
4
5
6
[mysqld]
server-id=102 #必须
relay-log=relay-log #必须
relay-log-index=relay-log.index #必须
innodb_file_per_table=ON #非必须
skip_name_resolve=ON #非必须

3.4 辅助操作

查看 slave 子节点状态

1
2
#查看slave子节点状态
show slave status\G;

启动 slave 节点

1
2
#启动slave节点
start slave;

关闭 slave 节点

1
2
#关闭slave节点
stop slave;

3.5 slave02（从节点配置）

修改 MySQL 从节点配置文件

1
vim /etc/my.cnf
1
2
3
[mysqld]
#不与主节点以及其它从节点重复即可
server-id=103

查看 slave 子节点状态

1
2
#查看slave子节点状态
show slave status\G;

启动 slave 节点

1
2
#启动slave节点
start slave;

四、创建表以及新增数据进行验证

4.1 创建数据库用户

建议使用 SQLyog 工具创建用户，比较方便。

链接： https://pan.baidu.com/s/11gIlZKxoTG5BCCcoXdVJRg 提取码: ntu7

4.2 创建表

1
2
3
4
5
6
7
8
/** 创建测试表GIRL **/
CREATE TABLE `GIRL` (
  `ID` varchar(64) CHARACTER SET utf8 NOT NULL,
  `GIRE_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `GIRL_AGE` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `CUP_SIZE` varchar(2) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

4.3 生成测试数据

1
2
/** 插入测试数据 **/
insert into GIRL values('tolove16B','梦梦','16','B');

4.4 采用 SQLylog 工具验证

master 节点插入测试数据
在这里插入图片描述

master 节点 binlog 日志验证
在这里插入图片描述
slave_check_RELAYLOG（133 服务器）
在这里插入图片描述

132_slave_insert_data_girl 同步数据
在这里插入图片描述

133_slave_check_RELAYLOG 日志
在这里插入图片描述

133_slave_同步数据
在这里插入图片描述

五、排查问题

5.1 error 1236

master_log_pos 与从节点不一致导致 error 1236
在这里插入图片描述
分析日志：position 日志偏移量与 master 节点不一致
设置的 master_log_pos 发生改变，导致 position 不一致，出现无法同步（报：error 1236）
在这里插入图片描述

slave 节点正常状态参考

注意：我截图上的是后期优化处理过的，所以看到的 pos 和上面的不一致
在这里插入图片描述

5.2 解决问题

通过如下设置保持与 master 节点一致
首先关闭 slave 节点

1
stop slave

查看 master 节点日志偏移量

1
show master status\G;

在 slave 节点修改日志偏移量与 master 一致

1
change master to master_log_file ='mysql-bin.000005',master_log_pos=950;

最后启动 slave 节点

1
start slave

5.3 数据库授权远程登录

5.3.1 授权命令

1
GRANT ALL PRIVILEGES ON *.* TO '你的数据库用户名'@'单个ip或者%表示所有' IDENTIFIED BY '你的数据库连接密码' WITH GRANT OPTION;
1
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

5.3.2 修改用户密码：

1
set password for root@localhost=password('123456');

5.4 防火墙问题

5.4.1 本次实战中使用到的是 centos7，默认安装的是 firewalld
5.4.2 临时关闭防火墙：

1
systemctl stop firewalld.service

5.4.3 实际工作中推荐需要使用哪些端口采取开放，比如开放 MySQL 默认的 3306：
公共区域设置开放 3306 端口永久生效（–permanent）：

1
firewall-cmd --zone=public --add-port=3306/tcp --permanent

操作完记住重启或重载：

1
firewalld-cmd --reload

移除：

1
firewall-cmd --zone=public --remove-port=3306/tcp --permanent

查看是否开放：

1
firewall-cmd --zone=public --query-port=3306/tcp

5.5 连接工具

推荐使用 Navicat 和 SQLyog 可视化工具。
链接: https://pan.baidu.com/s/11gIlZKxoTG5BCCcoXdVJRg 提取码: ntu7
SQLyog 官网下载
https://sqlyog.en.softonic.com/

六、实现 MySQL 主从复制

6.1 分析模型

分析模型：一主一从，一主多从，多主一从

6.1.1 一主一从模型：master->slave

在这里插入图片描述

6.1.2 一主多从

在 slave 节点再配置 slave 缓解 master 服务器压力
在这里插入图片描述

6.1.3 多主一从

在这里插入图片描述

6.1.4 特例：主 - 主复制

（每一个主既是从又是主）
在这里插入图片描述

6.1.5 使用 SQLylog 操作验证

分别登陆三台数据库服务器（推荐 SQLylog 工具）
同时登陆 xshell 工具连接查看三台服务器状态（推荐使用 xshell 或者 SecureCRT）

1
ip:192.168.245.131 //master

数据库：test_db // 与 master 节点配置单一用户对应
数据库表：GIRL

1
ip:192.168.245.132 //slave

master 节点创建 GIRL 表，slave 同步创建

1
ip:192.168.245.133 //slave

master 节点创建 GIRL 表，slave 同步创建

tips：如果想实现主 - 主同步，则需要在其它两台配置数据库用户关联授权，参考第二步。
我并没有验证主 - 主同步，嘿嘿，当然你可以私下验证哟！

七、附件（搭建环境）

7.1 虚拟机搭建环境

搭建服务器环境参考文章：VMware12 配合使用 Centos7 搭建 Linux 开发环境

7.1.1 搭建虚拟机环境
在这里插入图片描述

7.1.2 虚拟机服务器配置
在这里插入图片描述

7.2 xshell 远程连接

7.2.1 使用 xshell 远程连接
在这里插入图片描述

7.3 搭建 linux 环境

准备三台 MySQL 服务器配置主从复制（master：131，slave：132，slave：133）
在这里插入图片描述

131 master 主节点 my.cnf 配置
在这里插入图片描述

131 验证 master 节点 log_bin 开启
在这里插入图片描述

131 show_master_status 查看状态（注意：此时 master 的 pos 为 120）
在这里插入图片描述

131 查看主节点 master 的 server 服务（131 服务器）
在这里插入图片描述

131 查看主节点 binlog 日志所在目录（131 服务器）
在这里插入图片描述

start_slave 启动从节点 132（此时 master 的 pos 为 120，没对应上导致 slave 无法读取）
在这里插入图片描述
在这里插入图片描述

参考步骤五排查问题进行解决
在 slave 节点修改日志偏移量与 master 一致，master_log_file 对应先查出主节点最新的：

1
show master status\G;

查看此时 master 的 pos 为 120

在这里插入图片描述

此时设置 master_log_pos=120

1
change master to master_log_file ='mysql-bin.000001',master_log_pos=120;

正常 slave 节点读取_IO_SQL_Running 为 YES，如下图
在这里插入图片描述

slave_mysql_log（132 服务器对应的 slave 节点）
在这里插入图片描述

启动从节点 slave（对应 slave 节点 133 服务器）

1
2
3
4
/**（此时master的pos为120，没对应上导致slave无法读取）
参考132服务器正常状态，修改pos对应上master节点 **/

start slave

slave 节点 MySQL 日志（slave 节点对应 133 服务器）
在这里插入图片描述

132 开启从节点中继日志
在这里插入图片描述

7.4 采用 SQLylog 工具进行测试

131_master_插入测试数据
在这里插入图片描述

131_master_binlog_日志验证
在这里插入图片描述
132_slave_check_RELAYLOG
在这里插入图片描述

132_slave_insert_data_girl 同步数据
在这里插入图片描述

133_slave_check_RELAYLOG 日志
在这里插入图片描述

133_slave_同步数据
在这里插入图片描述

—END—