测试验证replicate-ignore-table参数作用

来源：互联网发布：java神经网络编辑：程序博客网时间：2024/06/11 08:06

1.基本信息

主库：
IP：10.16.24.107 port:3377
server-id = 1073377
data_dir:/data/MySQL/mysql3377/data/
base_dir:/usr/local/mysql
版本：mysql 5.6.29-log
gtid_mode =on

从库：
IP：10.16.24.108 port:3377
server-id = 1083377
data_dir:/data/mysql/mysql3377/data/
base_dir:/usr/local/mysql
版本：mysql 5.6.29-log
binlog_format:ROW
gtid_mode =on

2.从库上设置replicate-ignore-table参数
配置文件[mysqld]中有设置
replicate-ignore-table=lots.test
并有重启mysql后，登入查看：
(product)root@localhost [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.16.24.107
                  Master_User: repl
                  Master_Port: 3377
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000018
          Read_Master_Log_Pos: 191
               Relay_Log_File: relay-bin.000009
                Relay_Log_Pos: 401
        Relay_Master_Log_File: mysql-bin.000018
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: lots.test
      Replicate_Wild_Do_Table:

3.验证参数作用
场景一：binlog_format=ROW，不跨库更新test表的记录
主库查询test表记录：
(product)root@localhost [lots]> select * from test;
+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
+------+-------+
2 rows in set (0.00 sec)

从库查询test表记录：
(product)root@localhost [lots]> select * from test;
+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
+------+-------+
2 rows in set (0.00 sec)

主库上update操作：
use lots;
update test set name='zeng3' where id=2;
(product)root@localhost [lots]> update test set name='zeng3' where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(product)root@localhost [lots]> select * from test;
+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng3 |
+------+-------+
2 rows in set (0.00 sec)

(product)root@localhost [lots]>

从库查看test记录：
(product)root@localhost [lots]> select * from test;

+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
+------+-------+
2 rows in set (0.00 sec)

说明从库test表的记录没有同步，设置参数忽略了表test的复制。

场景二：binlog_format=ROW，跨库更新test表的记录
将主库的记录更改回去
update test set name='zeng2' where id=2;
(product)root@localhost [lots]> select * from test;
+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
+------+-------+
2 rows in set (0.00 sec)

use mysql;
update lots.test set name='zeng3' where id=2;

(product)root@localhost [lots]> use mysql
Database changed
(product)root@localhost [mysql]> update lots.test set name='zeng3' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

(product)root@localhost [mysql]> select * from lots.test;
+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng3 |
+------+-------+
2 rows in set (0.00 sec)

查看从库记录：

(product)root@localhost [lots]> select * from test;
+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
+------+-------+
2 rows in set (0.00 sec)

说明从库test表的记录没有同步，设置参数忽略了表test的复制。

场景三：binlog_format=statement，不跨库更新test表的记录
将主库的记录更改回去
update test set name='zeng2' where id=2;
(product)root@localhost [lots]> update test set name='zeng2' where id=2;
ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to

row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ-COMMITTED or READ-UNCOMMITTED.
主从库分别执行：
set global tx_isolation='Repeatable-Read'
并重新连接mysql:
查看参数设置：
(product)root@localhost [lots]> show variables like '%ISOLATION%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+----------------+
1 row in set (0.00 sec)

主库查询test表记录：
(product)root@localhost [lots]> select * from test;
+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
+------+-------+
2 rows in set (0.00 sec)

(product)root@localhost [lots]> select * from test;
+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng3 |
+------+-------+
2 rows in set (0.00 sec)

(product)root@localhost [lots]>

从库查看test记录：
(product)root@localhost [lots]> select * from test;

+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
+------+-------+
2 rows in set (0.00 sec)

说明从库test表的记录没有同步，设置参数忽略了表test的复制。

场景四：binlog_format=statement，跨库更新test表的记录

将主库的记录更改回去
update test set name='zeng2' where id=2;
(product)root@localhost [lots]> select * from test;
+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
+------+-------+
2 rows in set (0.00 sec)

use mysql;
update lots.test set name='zeng3' where id=2;

(product)root@localhost [mysql]> select * from lots.test;
+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng3 |
+------+-------+
2 rows in set (0.00 sec)

查看从库记录：

(product)root@localhost [lots]> select * from test;
+------+-------+
| id   | name |
+------+-------+
|    1 | zeng1 |
|    2 | zeng2 |
+------+-------+
2 rows in set (0.00 sec)

说明从库test表的记录没有同步，设置参数忽略了表test的复制。

0 0