|
1 2 |
grant replication slave on mysql.* to rep@'172.16.1.%' identified by '123'; |
1.确认两台从库状态
2.过滤复制的两种方式
|
1 2 3 4 5 6 7 |
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000010 | 120 | 白名单 | 黑名单 | | +------------------+----------+--------------+------------------+-------------------+ |
1）黑名单
|
1 2 3 4 5 |
黑名单：不执行黑名单中列出的库或者表的中继日志 replicate-ignore-db=test replicate-ignore-table=test.t1 replicate-wild-ignore-table=test.t2 |
2）白名单
|
1 2 3 4 5 |
白名单：只执行白名单中列出的库或者表的中继日志 replicate-do-db=test replicate-do-table=test.t1 replicate-wild-do-table=test.t2 |
3.从库配置过滤复制
1）主库创建两个库
|
1 2 3 4 5 6 |
mysql> create database lol; Query OK, 1 row affected (0.00 sec) mysql> create database wzry; Query OK, 1 row affected (0.00 sec) |
2）第一台从库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
1.配置数据库 [root@db02 ~]# vim /etc/my.cnf [mysqld] replicate-do-db=lol 2.重启数据库 [root@db02 ~]# systemctl restart mysqld 3.查看主从状态 mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: lol #从库的执行白名单 |
3）第二台从库
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
1.配置数据库 [root@db03 ~]# vim /etc/my.cnf [mysqld] replicate-do-db=wzry 2.重启数据库 [root@db03 ~]# systemctl restart mysqld 3.查看主从状态 mysql> show slave status\G Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: wzry #从库的执行白名单 |
4）主库建表测试
|
1 2 3 4 5 6 |
mysql> use lol; mysql> create table bierjiwote(id int); mysql> use wzry; mysql> create table cikexintiao(id int); |
5）从库查看
第一台从库查看
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> use lol; mysql> show tables; +---------------+ | Tables_in_lol | +---------------+ | bierjiwote | +---------------+ 1 row in set (0.00 sec) mysql> use wzry mysql> show tables; Empty set (0.00 sec) |
第二台从库查看
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> use lol; mysql> show tables; Empty set (0.00 sec) mysql> use wzry mysql> show tables; +----------------+ | Tables_in_wzry | +----------------+ | cikexintiao | +----------------+ 1 row in set (0.00 sec) |
4.扩展
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
#一台机器配置多个白名单 1.方式一： [root@db02 ~]# vim /etc/my.cnf [mysqld] server_id=2 replicate-do-db=lol replicate-do-db=dnf 2.方式二： [root@db02 ~]# vim /etc/my.cnf [mysqld] server_id=2 replicate-do-db=lol,dnf |
5.主库配置白名单
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
1.配置白名单 [root@db01 ~]# vim /etc/my.cnf [mysqld] server_id=1 log_bin=mysql-bin binlog-do-db=dnf #主库配置 2.主库查看白名单 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000011 | 120 | dnf | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) 3.主库建表测试 mysql> use lol; mysql> create table heisemeigui(id int); Query OK, 0 rows affected (0.01 sec) mysql> use wzry mysql> create table weilaizhanshi(id int); Query OK, 0 rows affected (0.01 sec) mysql> use dnf mysql> create table shanghai1qu(id int); Query OK, 0 rows affected (0.00 sec) 4.从库查看 mysql> use lol; mysql> showtables; mysql> show tables; +---------------+ | Tables_in_lol | +---------------+ | bierjiwote | +---------------+ 1 row in set (0.00 sec) mysql> use wzry mysql> show tables; Empty set (0.00 sec) mysql> use dnf mysql> show tables; +-----------------+ | Tables_in_dnf | +-----------------+ | heilongjiang1qu | | shanghai1qu | +-----------------+ 2 rows in set (0.00 sec) |
6.过滤复制总结
|
1 2 3 4 5 6 7 8 |
#从库配置： 1.白名单：IO线程会将主库所有的binlog都取过来，存储在relay-log中，但是sql线程根据我们配置的白名单，只执行了跟白名单有关的数据库语句 2.黑名单：IO线程会将主库所有的binlog都取过来，存储在relay-log中，但是sql线程根据我们配置的给名单，不执行跟黑名单有关的数据库语句 #主库配置： 1.白名单：主库只记录对白名单中的库操作的sql语句到binlog 2.黑名单：主库不记录对黑名单中的库操作的sql语句到binlog |