MySQL主从复制(3)主从复制过滤配置与跨库更新

tanglu 4366 2021-03-15

一、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    #从库需要开启只读,避免数据写入到从库,导致主从数据不一致
版权声明
本站所有文章均为原创,转载请注明出处!小站维护不易,如果对您有所帮助,希望能点击一下站内广告,谢谢!
上一篇:MySQL主从复制(2)半同步复制(无损复制)与异地容灾应用
下一篇:MySQL主从复制(4)GTID主从复制与运维
相关文章

 发表评论

暂时没有评论,来抢沙发吧~

微信二维码