运维日记|MySQL数据单向同步方案对比 – 作者:database

MySQL数据同步是目前数据库使用中比较常见的场景，其技术成熟，应用广泛。

本文介绍两种基础的数据单向同步方式，以应对不同的场景和需求，对比两种方案的优缺点，总结应用场景。

一、简单的库内表同步场景

此场景的主要特点是数据量不大，结构比较简单，而且没有跨数据库实例的情况，适合使用触发器实现。

这里先对触发器做一个简单的介绍：触发器是存储在数据库目录中的一组SQL语句。每当与表相关联的事件发生时，就会触发SQL触发器语句，从而实现对数据的同步。

下面以简单的实例展示如何使用触发器同步数据。

首先，新建两个需要同步的数据库以及相同结构的表：

在需要同步的数据库和表上新建一个insert动作之后的触发器:

使用 show triggers 查看新建的触发器:

新增数据并测试：

这里对temp_db数据库的 temp_for_sync 表新增数据，而temp_db1中的temp_for_sync表也有新增的记录同步过来了，说明insert触发器生效了。

update和delete触发器实例如下：

注意：触发器同步来的数据，只能做到单向同步，所以同步来的数据必须是只读的，否则会引发数据一致性问题，此外，对于复杂的业务场景，

触发器可能会造成一定性能问题，所以，这个方案仅适合于简单的同步场景，对于有性能要求或者较为复杂的场景并不适用。

 

二、使用MySQL的主从同步方法

binlog简介：MySQL的binlog日志作用是用来记录MySQL内部增删改查等对MySQL数据库有更新的内容的记录（对数据库的改动），对数据库的查询select或show等不会被binlog日志记录。主要用于数据库的主从复制以及增量恢复。

基于binlog的主从同步流程如下：

1.主服务器(master)将变更事件(更新、删除、表结构改变等等)写入二进制日志(master log)。

2.从服务器(slave)的IO线程从主服务器(binlog dump线程)获取二进制日志，并在本地保存一份自己的二进制日志(relay log)

3.从服务器的SQL线程读取本地日志(relay log)，并重演变更事件。

下面简要介绍主从同步的配置方法：

主库，本地MySQL服务器 IP: 192.168.99.215

从库，本地MySQL服务器 IP: 192.168.98.159

第一步，配置主库信息：

主库配置，新增以下配置项，完成bin_log的配置：

这里注意 server-id要唯一。

重启服务并查看主库状态：

给从库赋权，以读取binlog从而同步数据：

主库配置完成。

第二步，配置从库信息：

配置从库信息server_id并重启从库：

在主库配置从库关于主库的信息，并开启同步主库：

验证同步效果，在主库新建库，表，记录，删除：

至此，完成了单向主从同步的基本配置。

 

三、两种方案的对比

这两种方法都可以实现需求，而且各有优劣：

触发器的实现比较简单，不需要另外新启动一个MySQL实例，对资源的消耗比较小，适合简单的业务场景，缺点在于字段有变更时，需要及时修改表结构，以及触发器的实现。

主从不需要关系表结构的修改，只需要配置，缺点在于需要新启MySQL服务实例，会消耗一定资源，适合业务比较复杂的场景。

 

---

---

美创运维中心数据库服务团队拥有Oracle ACE 1人、OCM 10余人、数十名Oracle OCP、MySQL OCP、红帽RHCA、中间件weblogic、tuxedo认证、达梦工程师 ，著有《Oracle DBA实战攻略》，《Oracle数据库性能优化方法和最佳实践》，《Oracle内核技术揭秘》等多本数据运维优化书籍。目前运维各类数据库合计2000余套，精通Oracle、MySQL、SQLServer、DB2、PostgreSQL、达梦等主流商业和开源数据库。并成为首批国内达梦战略合作伙伴之一，拥有海量经验和完善的人员培养体系。并同时提供超融合，私有云整体解决方案。

 

来源：freebuf.com 2020-06-04 11:48:57 by: database