mysql 复制过滤_MySQL 5.7 复制的过滤参数

1、库级过滤参数

--binlog-do-db=db_nameMaster节点记录二进制日志的库

--binlog-ignore-db=db_nameMaster节点不记录二进制日志的库

--replicate-do-db=db_nameSlave节点应用日志的库

--replicate-ignore-db=db_nameSlave节点不应用日志的库

2、表级过滤参数

--replicate-do-table=db_name.tbl_nameSlave节点执行指定表的修改事件

--replicate-wild-do-table=db_name.tbl_nameSlave节点执行正则中含有指定表的修改事件

--replicate-ignore-table=db_name.tbl_nameSlave节点不执行指定表的修改事件

--replicate-wild-ignore-table=db_name.tbl_nameSlave节点不执行正则中含有指定表的修改事件

MySQL里面有启动参数、系统参数，启动参数通过show variables无法查询到，可以在mysqld的选项中查到

例如关于二进制日志有Startup Options Used with Binary Logging、System Variables Used with Binary Logging两种参数

上面所说的复制过滤参数均为启动参数

设置参数

[root@localhost log]# mysqld --verbose --help > 02.txt

[root@localhost log]# vim 02.txt

..

--binlog-do-db=name Tells the master it should log updates for the specified

database, and exclude all others not explicitly

mentioned.

..

--binlog-ignore-db=name

Tells the master that updates to the given database

should not be logged to the binary log.

..

--replicate-do-table=name

Tells the slave thread to restrict replication to the

specified table. To specify more than one table, use the

directive multiple times, once for each table. This will

work for cross-database updates, in contrast to

replicate-do-db.

[root@localhost log]# vim /etc/my.cnf

..

# log

server-id = 10000

log-bin = /log/binlog/mysql-bin

binlog_format = MIXED

binlog-do-db = test

启动数据库后，查看参数

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position |Binlog_Do_DB|Binlog_Ignore_DB| Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000009 |      120 |test|                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.03 sec)