MySQL主从复制（3）主从复制过滤配置与跨库更新

最新推荐文章于 2024-07-10 03:44:18 发布

weixin_42073629

最新推荐文章于 2024-07-10 03:44:18 发布

阅读量807

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_42073629/article/details/117175579

版权

数据库专栏收录该内容

52 篇文章 2 订阅

订阅专栏

本文介绍了MySQL的复制过滤功能，允许在主从复制过程中选择性同步特定数据库和表。通过binlog_do_db和binlog_ignore_db等配置实现主库过滤，而从库则使用replicate_do_db和replicate_ignore_db等选项进行过滤。当出现跨库更新问题时，由于过滤规则导致不同步，解决方案是改用表级别过滤。同时提供了MySQL5.6的主从配置文件示例。

摘要由CSDN通过智能技术生成

一、MySQL过滤复制介绍

当主库上存在多个database，但从库只需要同步一部分的话就需要用到MySQL的复制过滤功能。比如一个主库承载多个业务数据库，需要将不同业务数据库复制到不同的从库进行查询的场景。通过过滤复制可以灵活的指定哪些库和表需要复制，哪些库不需要同步。通常建议在从服务器上配置过滤复制，可以减轻主库的负载。

二、MySQL过滤复制配置介绍

1、主库静态配置复制过滤

· binlog_do_db：白名单，只对指定数据库进行binlog记录与复制，多个数据库设置可以写多行
· binlog_ignore_db：黑名单，此选项中指定的数据库将不进行binlog记录和复制，多个数据库设置可以写多行

[mysqld]
...
replicate_do_db=linuxedb
replicate_do_db=bbsdb
...

2、从库静态配置复制过滤

· replicate_do_db：数据库白名单列表，多个数据库用逗号分隔，该选项指定的数据库会执行主从复制操作
· replicate_ignore_db：数据库黑名单列表，该选项指定的数据库将不会被复制
· replicate_do_table：表级别的白名单
· replicate_ignore_table：表级别的黑名单
· replicate_wild_do_table：可以使用通配符进行指定表，如%代表所有
· replicate_wild_ignore_table：同上

3、从库在线配置复制过滤（生效后还需要写进配置文件）

mysql > stop slave sql_thread;  #必须先停止线程，否则后面语句会报错
mysql > change replication filter replicate_wild_do_table=('database1.%','database2.%')
mysql > start slave sql_thread;

三、MySQL跨库更新问题

如果Master服务器开启了binlog_do_db、binlog_ignore_db，Slave服务器开启了replicate_do_db和replicate_ignore_db选项会有跨库更新不同步的问题。原因是当MySQL执行SQL语句时对当前数据库做检查，如果当前库没有在白名单里指定，那么就不会写入到二进制日志。示例：

1、首先在Master服务器上执行SQL语句：

USE   mysql2;    #切换到另一个库
INSERT  INTO  mysql1.table1 VALUES(2.'zhangsan');   #对第一个库的表做操作
SELCET * FROM  mysql1.table1    #查看内容，已经成功插入

2、到从服务器上查看发现并没有同步

SELCET  *  FROM  mysql1.table1

3、查看下Master的日志情况

show  master  logs;    #查看日志记录情况
show  binlog  events  in  'master-bin.000003'    #查看第三个日志的内容，发现没有那条语句

解决办法：

修改Slave的my.cnf配置文件,把replicate_do_db和replicate_ignore_db选项修改为replicate_wild_do_table和replicate_wild_ignore_table，使用表级别的主从复制不会受到跨库影响

replicate_wild_do_table=mysql1.%
replicate_wild_do_table=mysql2.%
replicate_wild_ignore_table=mysql3.%

附：MySQL 5.6 主从复制配置文件示例

Master配置文件：

[mysql]
socket = /var/lib/mysql/mysql.sock
[mysqld]
user = mysql    #运行用户
port = 3306    #监听端口
binlog_format = mixed    #混合格式
log_bin = mysql-bin    #设置binlog文件的前缀，日志路径是在datadir下
datadir = /mysqldata
basedir = /usr/local/mysql
socket = /var/lib/mysql/mysql.sock
server_id = 1    #这里的数字不能和从库一样 
sync_binlog = 1    #事务提交后立即写入二进制日志，而不是放内存中，避免主服务器故障时事务没有写入日志
innodb_flush_logs_at_trx_commit = 1    #每次事务提交后立即将日志写入磁盘
log_error = /mysqldata/log    #错误日志的存放路径

Slave配置文件：

[mysql]
socket = /var/lib/mysql/mysql.sock
[mysqld]
user = mysql    #运行用户
port = 3306    #监听端口
binlog_format = mixed    #混合格式
log_bin = mysql-bin    #设置binlog文件的前缀，日志路径是在datadir下
datadir = /mysqldata
basedir = /usr/local/mysql
socket = /var/lib/mysql/mysql.sock
server_id = 2    #这里的数字不能和主库一样
replicate_do_db = mysql1    #需要复制的数据库，多个数据库写多行，这里设置的并不是binlog
replicate_do_db = mysql2
replicate_ignore_db = mysql3    #不需要复制的数据库，多个数据库写多行，不写的话默认复制所有库
log_error = /mysqldata/log    #错误日志的存放路径
read-only = 1    #从库需要开启只读，避免数据写入到从库，导致主从数据不一致