mysql 主从 过滤_MySQL主从复制（5）主从复制过滤配置与跨库更新

一、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：同上

三、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 #从库需要开启只读，避免数据写入到从库，导致主从数据不一致