MySQL5.6主从复制搭建基于日志（binlog）

198次阅读

共计 4260 个字符，预计需要花费 11 分钟才能阅读完成。

简单来说就是保证主 SQL（Master）和从 SQL（Slave）的数据是一致性的，向 Master 插入数据后，Slave 会自动从 Master 把修改的数据同步过来（有一定的延迟），通过这种方式来保证数据的一致性，就是主从复制。

因为数据都是相同的，所以当 Master 挂掉后，可以指定一台 Slave 充当 Master 继续保证服务运行，因为数据是一致性的（如果当插入 Master 就挂掉，可能不一致，因为同步也需要时间），当然这种配置不是简单的把一台 Slave 充当 Master，毕竟还要考虑后续的 Salve 同步 Master，当然本文并不是将高可用的配置，所以这里就不多讲了

因为读写分离也算是负载均衡的一种，所以就不单独写了，因为一般都是有多台 Slave 的，所以可以将读操作指定到 Slave 服务器上（需要代码控制），然后再用负载均衡来选择那台 Slave 来提供服务，同时也可以吧一些大量计算的查询指定到某台 Slave，这样就不会影响 Master 的写入以及其他查询

一般我们都会做数据备份，可能是写定时任务，一些特殊行业可能还需要手动备份，有些行业要求备份和原数据不能在同一个地方，所以主从就能很好的解决这个问题，不仅备份及时，而且还可以多地备份，保证数据的安全

可以一个业务模块读取一个 Slave，再针对不同的业务场景进行数据库的索引创建和根据业务选择 MySQL 存储引擎

主从复制支持 2 种扩展方式

向上扩展或者纵向扩展，主要是提供比现在服务器更好性能的服务器，比如增加 CPU 和内存以及磁盘阵列等，因为有多台服务器，所以可扩展性比单台更大

向外扩展或者横向扩展，是指增加服务器数量的扩展，这样主要能分散各个服务器的压力

无可厚非的是搭建主从肯定会增加成本，毕竟一台服务器和两台服务器的成本完全不同，另外由于主从必须要开启二进制日志，所以也会造成额外的性能消耗

Slave 从 Master 复制过来肯定是会有一定的数据延迟的，所以当刚插入就出现查询的情况，可能查询不出来，当然如果是插入者自己查询，那么可以直接从 Master 中查询出来，当然这个也是需要用代码来控制的

主从复制主要是针对读远大于写或者对数据备份实时性要求较高的系统中，因为 Master 在写中需要更多操作，而且只有一台写入的 Master（因为我目前只会配置一台写入 Master，最多就是有从 Master 的 Slave，用来在 Master 挂掉后替换成 Master，平时不对外进行服务），所以写入的压力并不能被分散，当然如果直接怎么解决这个问题的话，欢迎留言指教

MySQL5.6 开始主从复制有两种方式：基于日志（binlog）、基于 GTID（全局事务标示符）。

本文只涉及基于日志 binlog 的主从配置

1、Master 将数据改变记录到二进制日志 (binary log) 中，也就是配置文件 log-bin 指定的文件，这些记录叫做二进制日志事件(binary log events)

2、Slave 通过 I / O 线程读取 Master 中的 binary log events 并写入到它的中继日志(relay log)

3、Slave 重做中继日志中的事件，把中继日志中的事件信息一条一条的在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据(数据重放)

1、主从服务器操作系统版本和位数一致

2、Master 和 Slave 数据库的版本要一致

3、Master 和 Slave 数据库中的数据要一致

4、Master 开启二进制日志，Master 和 Slave 的 server_id 在局域网内必须唯一

两台或两台以上安装了相同版本的 MySQL（我没有试过不同版本会不会有问题，有兴趣的可以试试），当然这个可以用虚拟机或者 Docker 代替，个人推荐用 Docker，比虚拟机消耗少太多了，当然用起来可能没有虚拟机那么方便，但是却不用挨个环境配置了

不同的系统 my.cnf 路径不同，所以我们只讲解牵扯修改的地方。添加配置

[mysqld]
## 设置 server_id，一般设置为 IP, 注意要唯一
server_id=100  
## 复制过滤：也就是指定哪个数据库不用同步（mysql 库一般不同步）
binlog-ignore-db=mysql  
## 开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）
log-bin=edu-mysql-bin  
## 为每个 session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M  
## 主从复制的格式（mixed,statement,row，默认格式是 statement）
binlog_format=mixed  
## 二进制日志自动删除 / 过期的天数。默认值为 0，表示不自动删除。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免 slave 端复制中断。
## 如：1062 错误是指一些主键重复，1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062

配置完成后重启 mysql

复制过滤可以让你只复制服务器中的一部分数据，有两种复制过滤：

1、在 Master 上过滤二进制日志中的事件

2、在 Slave 上过滤中继日志中的事件。

在 Master 上执行的 SQL 语句，在 Slave 上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。一旦发现没法精确复制时，会自动选着基于行的复制

把改变的内容复制到 Slave，而不是把命令在 Slave 上执行一遍。从 MySQL5.0 开始支持

默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

这里主要是要授予用户 REPLICATION SLAVE 权限和 REPLICATION CLIENT 权限

[mysqld]
## 设置 server_id，一般设置为 IP, 注意要唯一
server_id=101  
## 复制过滤：也就是指定哪个数据库不用同步（mysql 库一般不同步）
binlog-ignore-db=mysql  
## 开启二进制日志功能，以备 Slave 作为其它 Slave 的 Master 时使用
log-bin=edu-mysql-slave1-bin  
## 为每个 session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M  
## 主从复制的格式（mixed,statement,row，默认格式是 statement）
binlog_format=mixed  
## 二进制日志自动删除 / 过期的天数。默认值为 0，表示不自动删除。
expire_logs_days=7  
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免 slave 端复制中断。
## 如：1062 错误是指一些主键重复，1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062  
## relay_log 配置中继日志
relay_log=edu-mysql-relay-bin  
## log_slave_updates 表示 slave 将复制事件写进自己的二进制日志
log_slave_updates=1  
## 防止改变数据(除了特殊的线程)
read_only=1

如果 Slave 为其它 Slave 的 Master 时，必须设置 bin_log。配置完成后重启 mysql

保证 Master 和 Slave 除不同步的数据库，其他库的数据一致

在 Master 中执行

show master status;

记录下返回结果的 File 列和 Position 列的值

在 Slave 中执行

change master to master_host='192.168.1.100', master_user='slave', master_password='123456', master_port=3306, master_log_file='edu-mysql-bin.000001', master_log_pos=1389, master_connect_retry=30;

上面执行的命令的解释：

master_host=’192.168.1.100′ ## Master 的 IP 地址

master_user=’slave’ ## 用于同步数据的用户（在 Master 中授权的用户）

master_password=’123456′ ## 同步数据用户的密码

master_port=3306 ## Master 数据库服务的端口

master log file=’edu-mysql-bin.000001′ ## 指定 Slave 从哪个日志文件开始读复制数据（Master 上执行命令的结果的 File 字段）

master log pos=429 ## 从哪个 POSITION 号开始读（Master 上执行命令的结果的 Position 字段）

master connect retry=30 ## 当重新建立主从连接时，如果连接建立失败，间隔多久后重试。单位为秒，默认设置为 60 秒，同步延迟调优参数。

在 Slave 中执行命令