MySQL数据复制原理及实践

1.数据复制概述

1.1数据复制定义

数据复制使一个服务上的数据与另一个服务上数据保持同步

1.2复制用途

• 数据分布
• 负载均衡
• 备份
• 高可用和故障切换
• MySQL升级测试

2.数据复制工作原理

2.1复制工作流程介绍（以主从架构为例）

MySQL复制原理比较简单，其核心工作示意图如下：

• 主库把更新操作记录在二进制日志文件中
• 备库上的I/O线程收到主库的更新事件后，读取新增的二进制日志并写入自己的中继日志中
• 备库读取中继日志，将事件在数据库上重放，更新自身数据库数据

2.2对主从复制架构的思考

看完图2.1中MySQL主从架构，读者会不会感觉如果去掉中继日志，整个复制流程会更简单？中继日志究竟在复制架构中产生了什么作用？

如果备库去掉写中继日志这个流程，那么备库每次只需要读取binlog事件数据，紧接着直接重放sql事件即可。而增加了中继日志，不仅增加了流程的复杂性，而且多了一次写文件I/O的操作消耗了系统性能

其实中继日志对备机而言就是缓存事件的buffer而已，其作用就是对备机的俩个核心操作(读主机二进制日志事件、重放事件)进行异步解耦。试想如果主机执行了一个比较耗时的修改(alter修改表字段等)，如果没有buffer存在的话，备机只能在执行完这个耗时操作之后才能继续同步主机上的其他新增二进制事件，这样可能会造成很多麻烦(比如在执行耗时操作的过程中主机永久宕机无法恢复，那么这段时间里主机新增的大量二进制日志，备机将无法重放，造成大量数据丢失，后果严重)。

备机写中继日志会有大量I/O操作，会不会影响同步性能呢？正常情况下，中继日志的内容都会存储在系统缓存中，所以使用者大可不必担心

3.数据复制实操

假设如下:

• 主机IP: 192.168.1.1 备机IP: 192.168.1.2
• 主机上MySQL有大量实时数据更新操作(MySQL复制的就是更新操作)

3.1为备机开通复制权限账号

GRANT REPLICATION SLAVE ON . TO repluser@‘192.168.1.2’ IDENTIFIED BY ‘replpwd’;```
在主机上执行上述命令便可为备机创建一个专门用于复制的用户账号，账号名为: repluser、密码为: replpwd

3.2主机开启binlog功能

默认的mysql是没有写binlog的，需要我们修改配置文件
在/etc/my.cnf的mysqld配置项中增加如下俩行配置

log_bin=/data/mysql/mysql-bin
server_id=100

配置说明：

• log_bin指明bin_log文件名称以及路径(/data/mysql目录必须存在)
• server_id根据mysql实例不同，给一个其他实例不重复的值即可

配置完成后重启MySQL服务，此时在MySQL终端执行如下命令查看开启bin_log是否生效

show master status;

如图3.1此时主机bin_log已启动，当前bin_log文件名为: mysql-bin.000001 Pos为: 154

备机想要复制主机的数据，就必须要知道主机的bin_log文件名是啥，以及主机写binlog的pos在哪里

此时如果你在主机上插入一条数据，再次查看状态就会发现bin_log pos会发生移动了

3.3配置备机中继日志

默认的MySQL是没中继日志的，需要我们修改配置文件
在/etc/my.cnf的mysqld配置项中增加如下配置

server_id=101
relay_log=/data/mysql/mysql-relay-bin
read_only=1

配置说明：

• server_id根据mysql实例不同，给一个其他实例不重复的值即可(注意100已经被主机用了)
• relay_log指明中继日志的名称(/data/mysql目录必须存在)
• read_only备机设置为只读，防止备机写操作将数据写脏
  配置完成后重启MySQL服务即可

3.4同步克隆主机当前数据快照

MySQL同步数据的核心便是保证俩台机器上主机bin_log任一位置pos之前的数据完全一致

由于主机在开启bin_log之前已经有好多数据了(这些数据没有记录在binlog里，所以备机通过重放binlog日志是无法同步到这些数据的)，最常用的方法便是：

• 设置主机为只读(主机之后便不会有数据更新了)
• 通过mysqldump等工具将当前MySQL数据复制并导入备机
• 记录下当前主机的bin_log文件和pos位置(通过在主机执行show master status获取)
• 关闭主机的只读设置

此时备机便有了和主机某个bin_log位置一样的快照数据

本文将使用一种更为简单的方式：
只需执行如下命令便可:

mysqldump --single-transaction  --all-databases  --master-data=2 --host 192.168.1.1 -uroot -p > all_data.sql

该方法相较前一种方法省掉了设置只读，和记录bin_log文件及位置等操作，那么我们如何获取bin_log的位置信息呢，其实打开mysqldump的输出文件all_data.sql，我们发现如下注释内容:

如图3.2中所示，在从主机dump数据的那个时刻的主机bin_log信息都记录在dump的输出文件中了，此时我们只需记录下红框框住的这俩个值即可

最后将dump输出文件导入数据库

source all_data.sql;

3.5为备机配置和主机的连接通道以及bin_log信息

在sql终端执行如下命令

change master to master_host='192.168.1.1',
master_user='repluser',
master_password='replpwd',
master_log_file='mysql-bin.000002',
master_log_pos=698;

变量说明：

• master_host: 主机IP
• master_user: 复制用户名(前文中通过主机分配)
• master_password: 复制的密码
• master_log_file: bin_log文件名(mysqldump输出文件中的注释内容)
• master_log_pos: 当前bin_log位置(mysqldump输出文件中的注释内容)

此时以为备机配置好了master复制相关参数，接下来只需要在备机运行如下命令，便可跑起来主备复制

start slave;

此时在备机上执行如下命令检查备机运行情况

show slave status\G;

其中如下俩个指标为Yes，则备机运行正常

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

4.主从一致性

MySQL提供的复制功能并不能保证100%的可靠，一些特殊情况下可能会导致MySQL主从数据发生不一致的情况，此时，我们可以通过一些第三方工具来高效的协助我们应对这样的Case

• pt-table-checksum: 检查主从数据库是否一致
• pt-table-sync: 修复主从数据库不一致现象

5.总结

MySQL复制功能强大，我们在生产环境中可以依据实际需求，灵活搭建：主从、主主、一主多从等多种复制模式来满足业务需求