基于MySQL5.6实现的同城多IDC间的MySQL部分库表数据复制方案

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

近期刚刚实施了一套同城多 IDC 间的 MySQL 主从同步方案，主要功能是实现的一主多从数据复制，但真正实施起来确又并不是如此简单。
最主要的制约因素无外乎就是通信带宽和数据量负载大小。
方案实施的背景：

4 个集群的 IDC 机房间通过 20Mb 专线相互连通；
选取 IDC A 的 mysql 数据库作为 master；
该 master 角色的 mysql 数据库，会有一部分表的数据量异常大，单表过亿；
该 master 数据库的 binlog 日志量平均每天 60GB；
数据同步使用需求是，只需对数据库中的部分库表做同步即可，这部分小表的数据量分别在个位数到百万之间；
业务使用需求是，主从之间数据同步，同步延时只要控制在 2 分钟以内，就可以满足上层的业务使用需求。

基于以上情况的分析：
专线带宽有限，不可能复制全部库表数据，也不可能达到实时复制；
IDC 间有库表复制需求的表只是一部分，这些表的存量数据在几条到一百万条之间；
复制策略设计为 mysql master–>middle slave–>slave，master 和 middle slave 位于同一 IDC 机房私网内；
第一阶段 mysql 主从，用于实现从全部库表中过滤出需要使用的部分表；
第二阶段 mysql middle alve –>slave，用于实现指定的表在集群间的数据复制；

一、IDC 内部 mysql 部分表主 - 从复制
IDC A mysql ha 双机部署在 server1 和 server2 上面，使用共享存储，双机软件为 heartbeat。主、备机的硬件配置相同。对外服务使用的 vip。在该方案中，正常情况下 mysql 服务运行在主机上，而备机 server2 一直处在热备待机状态，资源大部分时间内是没有被利用到的。

这次我们把 mysql 从部署在数据库备机 server2 上面。因为在 mysql ha 进行主备切换时，要使用备机上的 mysql 用户、3306 端口以及其它资源文件，所以在新增一个 mysql 实例作为 slave 时，需要完全得避开与这些资源的使用冲突。

1、mysql 从的数据复制策略说明：
为减少对应用程序层面的干扰，暂不考虑在 mysql 主机上进行分库处理，因此也就无法使用 binlog-do-db 功能来控制仅复制指定的数据库（比如把待复制的表放入一个新的库中，然后基于这个库进行 mysql 主从间的数据复制）。
注：使用 binlog-do-db 参数并不安全，因为它会仅让指定的库打印 binlog 日志。这在发生意外故障，就无法满足进行全部库的日志回滚的要求了。

我们选择使用在 mysql 从上通过 Replicate-do-table 功能，来控制哪些表的数据会被写入到 mysql slave 的数据库中，而这一操作的背景条件是在 mysql 主、从之间是进行的全部的数据库表复制（仅在从机上决定写入数据库时再按表做判断和过滤操作）。
这个数据复制操作，是在 IDC A 局域网内主、备机网卡 2 通过网线直接，使用 mysql 主从复制功能中的 mysql IO thread 进行。主机的网卡 1 是用于业务生产使用。

每天的 binlog 日志数据量大约有 60GB，这些全部要复制到从机。从机只有先把 binlog 复制过来后，才能根据表过滤规则判断是否需要入库。从机会把复制来的 binlog 存放在 relaylog 中，从中仅过滤出与过滤条件相符的库表事件，然后执行并记录到自己的 binlog 中。
通过以上设计，在 IDC A 的 mysql slave 上只保留了我们需要在 IDC 间进行数据复制的那些小表，slave 本身基于这些小表而产生的 binlog 日志量成功降低到每天 100MB。通过 IDC 间的专线，甚至是直接使用互联网带宽实施 IDC 间的这部分数据表的数据复制同步，可行性都是很高的。

mysql 间的主从复制本来就是异步的，任何形式的实时热备的要求，都是在耍流氓。不同的技术满足不同的场景，绝大数企业是承受不了同城热备的成本的。

2、mysql 从的部署信息说明：
安装目录：/data/mysql-middle-slave/mysql
数据目录：/data/mysql-middle-slave/mysql_datadir
sock 文件：/data/mysql-middle-slave/mysql.sock
pid 文件：/data/mysql-middle-slave/mysql_datadir/server2_HA.pid
配置文件：/data/mysql-middle-slave/mysql_conf/my.cnf
监听端口：9000
错误日志：/var/log/mysqld-slave.log
启动脚本：/etc/init.d/mysqld-slave 可以使用 service mysqld-slave start/stop/restart 管理
在 server2 本机登录 middle-slave 的 mysql 方法：mysql –port=9000–socket=/data/mysql-middle-slave/mysql.sock -uroot -p

3、mysql 主从复制使用的网段
在 IDC A 的 mysql 主、备 HA 双机之间是通过主机的网卡 2 传输的双机心跳监测数据。网卡 1 是对外提供 mysql 服务的网卡，目前平均流量在 100Mbps。
为减少对生产环境的影响，我们使用网卡 2 作为 mysql 主从间数据复制使用的网卡。主机为 10.0.0.1，备机为 10.0.0.2。

4、主从部分表数据复制方案实施步骤
（1）在主机上执行授权配置
设置 mysql 数据复制账户
mysql> GRANT REPLICATION SLAVE ON *.* TO repl@’10.0.0.2′ IDENTIFIED BY ‘repl’;
mysql> flush privileges;
在 mysql 主机上对 slave 开放 3306 端口的授权
ACCEPT tcp — 10.0.0.2 0.0.0.0/0 state NEW tcp dpt:3306

（2）从库配置 my.cnf
server-id = 2
log-bin=/data/mysql-middle-slave/mysql_datadir/mysql-bin
binlog_format=mixed
binlog_cache_size = 4M
max_binlog_size = 1024M
expire-logs-days = 10
slow_query_log = 0
long_query_time = 10
log-error = /var/log/mysqld-slave.log
slave-skip-errors = all
log_slave_updates = 1
skip-slave-start

#mysql replication policy
replicate_wild_do_table=mydatabase.code
replicate_wild_do_table=mydatabase.info
replicate_wild_do_table=mydatabase.location
replicate_wild_do_table=mydatabase.controller
replicate_wild_do_table=mydatabase.user_level
replicate_wild_do_table=mydatabase.check_type
replicate_wild_do_table=mydatabase.code_price
replicate_wild_do_table=mydatabase.service_info
replicate_wild_do_table=mydatabase.user_param
replicate_wild_do_table=mydatabase.method_info
replicate_wild_do_table=mydatabase.thread_code

（3）从主库导出一份数据快照
使用 mysqldump 来得到一个数据快照可分为以下几步：
因为主库设置的是 transaction_isolation = REPEATABLE-READ，所以支持以下方法导出一致性的数据。仅导出需要实现主从复制的表。
mysqldump -uroot -p –single_transaction –master-data=2 mydatabase code info location controller user_level check_type code_price service_info user_param method_info thread_code > master_partial.sql

（4）拷贝备份数据至从库并导入
先确认从库上已经按正确字符集要求创建了数据库，再执行数据导入。
CREATE DATABASE `mydatabase` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

mysql –port=9000 –socket=/data/mysql-middle-slave/mysql.sock -uroot -p mydatabase < master_partial.sql

（5）在备份文件 master_partial.sql 查看 binlog 和 pos 值

head -25 master_partial.sql
— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=122; #举例，大概 22 行

（6）从库设置从这个日志点同步并启动

mysql> change master to master_host=’10.0.0.1′,
-> master_user=’repl’,
-> master_password=’repl’,
-> master_port=3306,
-> master_log_file=’mysql-bin.000001′,
-> master_log_pos=122;

mysql> start slave;

mysql> show slave status\G;

观察 mysql 主从同步线程的状态。

如果因故需要暂停主从复制，可以从机上执行：mysql>stop slave;

mysql 从上的数据复制功能在重启 mysqld 服务后，需要手工登录 mysql，并执行 start slave 进行开启。

（7）临时中止与恢复主从数据复制
从 mysql5.6 版本开始，在 mysql slave 上执行了 stop slave 命令后，虽然中止了主从间的数据复制服务，但主从复制的配置信息仍然会被保存在缓存中。所以如果此时继续执行 start slave 命令，则会正常得继续前面中断的主从复制工作。
如果在执行了 stop slave 命令后，又重启了 mysql slave 的服务，那么就需要重新做一遍主、从间数据复制的配置了。先前的主从配置信息，在重启后不复存在。

（8）主从间数据库表状态不一致时的处理办法
可以有各种情况会造成 mysql slave 的库表数据状态与 master 不一致。
这时可以先停止主从复制：在从机上登录 mysql 并执行 stop slave；
在 mysql 从上执行 reset slave；
重新按前面的操作方法，从 master 上导出一份库表数据并传输到从机上，导入 mysql 从；
在 mysql 从上，根据上一步中得到的备份文件大约第 25 行注明的 master binlog 信息，重新设置 change master to 主从复制配置；
在 mysql 从上，执行 start slave，重新开启主从复制；
使用 show slave status\G 查看主从复制状态；

注：在执行 reset slave 命令时，mysql slave 会清除本地的一些日志文件（it clears the master info and relay log info repositories, deletes all the relay log files, and starts a new relay log file）。

（9）怎么在现有的库表之外，按需增减主从间进行数据复制的表
停从机的 mysql 数据复制服务，stop slave，reset slave；
修改从机的 my.cnf 文件，更新要过滤的库表信息；
重启从机的 mysqld 服务；
重新配置主、从间的数据同步、主从复制；

二、IDC 间的 mysql 数据复制
按照 mysql master–>middle slave–>slave 的实施策略，其它 3 个 IDC 的 mysql 都去和 IDC A 的 mysql slave 进行数据复制同步。
需要明确的是其它 3 个 IDC 的 mysql 都并不是一个空的数据库，而是原本就和 IDC A 的库表结构、规模相同、相当的。我们在实施另外 3 个 IDC mysql 与 IDC A 的 mysql slave 进行数据复制时，仍然需要使用 Replicate-do-table 功能来设置过滤条件，仅允许指定的部分表可以同步数据。
与此同时，其它 3 个 IDC 的数据库中未参与到主从复制中的那些库表，仍然需要继续被自己集群内的业务应用进行读、写。

这里就不再重复主从配置方法了，请参照前面即可。该方案实施后，在现有网络条件下、现有数据量条件下，运行良好。

在对以上方案进行反复测试的过程中，我也发现 mysql 服务并不会去检查 slave 上表的数据是否真得与 master 上在各个方面都是完全一致的。你完全可以在不影响到主从复制间发生表主键冲突的条件下，向 slave 上的表中插入数据。
而且，在仅同步 mysql 主从间的一部分表的条件下，无论是 master 上还是 slave 上的那些没参与到主从数据复制任务中的库表，仍然可以像往常一样得读写，而不会相互影响或制约。

本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-11/137091.htm