mysql架构 三级主从同步_MySQL主从同步架构中你不知道的“坑”

以下操作征对指定不同步库

1、binlog-format=ROW模式

查看主从的binlog模

mysql> show slave statusG

*********************** 1. row *************************

Slave_IO_State: Waiting formastertosend event

Master_Host: 192.168.22.171

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000004

Read_Master_Log_Pos: 463

Relay_Log_File: s1.000011

Relay_Log_Pos: 630

Relay_Master_Log_File: master-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: test

查看从库状态也显示此项配置

1：在忽略不同步的库不存在的情况下

在配置文件指定主从库都不存在的库，然后在主库中创建这个库，测试数据是否同步过去

主库创建数据库

mysql>createdatabasetest;

Query OK, 1 row affected (0.06 sec)

mysql> show databases;

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

| Database|

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

| information_schema |

| mysql       |

| performance_schema |

| sys        |

| test        |

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

5 rowsinset(0.01 sec)

从库查看

mysql> show databases;

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

| Database|

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

| information_schema |

| mysql       |

| performance_schema |

| sys        |

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

5 rowsinset(0.01 sec)

结论：发现建库的动作没有被从库同步过来

2：测试主从服务器库存在数据同步情况

目前主从库都存在库test001

[root@mysql-m ~]# mysql -uroot -p -e"show databases;"|grep test001

Enter password:

test001

[root@mysql-s ~]# mysql -uroot -p -e "show databases;"|grep test001

Enter password:

test001

在主库插入数据测试从库同步情况

mysql> use test001;

Databasechanged

mysql> createtabletest (

-> id varchar(10)notnull,

-> namevarchar(10)notnull

-> );

Query OK, 0 rowsaffected (0.06 sec)

mysql> insertintotestvalues('zhang','man');

Query OK, 1 row affected (0.03 sec)

mysql> select*fromtest;

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

| id  | name|

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

| zhang | man |

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

1 row inset(0.00 sec)

mysql> select*fromtest001.test;

ERROR 1146 (42S02): Table'test001.test'doesn't exist

mysql> use test001;

Databasechanged

mysql> show tables;

Empty set(0.00 sec)

结论：从库没有表，同样也有数据同步过来

3：征对数据库默认的库同步(如mysql)

replicate-ignore-db = mysql

#忽略指定不同步的库

测试前的数据

[root@mysql-m ~]# mysql -uroot -p -e"select user,host from mysql.user;"

Enter password:

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

| user| host     |

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

| mysql.sys | localhost   |

| root   | localhost  |

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

[root@mysql-s ~]# mysql -uroot -p -e "select user,host from mysql.user;"

Enter password:

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

| user| host     |

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

| mysql.sys | localhost  |

| root   | localhost  |

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

[root@mysql-m ~]# mysql -uroot -p -e "grant all privileges on *.* to test@localhost identified by '123456';"

Enter password:

[root@mysql-m ~]# mysql -uroot -p -e "select user,host from mysql.user;"

Enter password:

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

| user| host    |

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

| mysql.sys | localhost  |

| root   | localhost  |

| test   | localhost  |

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

[root@mysql-s ~]# mysql -uroot -p -e "select user,host from mysql.user;"

Enter password:

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

| user| host     |

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

| mysql.sys | localhost  |

| root   | localhost  |

| test   | localhost  |

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

结论：此种模式下此配置对默认的库不生效，因此调整配置

replicate-ignore-db = mysql

#忽略指定不同步的库

replicate-wild-ignore-table=mysql.%

#忽略指定不同步的库的所有表

[root@mysql-m ~]# mysql -uroot -p -e "grant all privileges on *.* to testuser@localhost identified by '123456';"

Enter password:

[root@mysql-m ~]# mysql -uroot -p -e "select user,host from mysql.user;"

Enter password:

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

| user| host    |

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

| mysql.sys | localhost  |

| root   | localhost  |

| test   | localhost  |

| testuser | localhost  |

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

[root@mysql-s ~]# mysql -uroot -p -e "select user,host from mysql.user;"

Enter password:

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

| user| host     |

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

| mysql.sys | localhost   |

| root   | localhost   |

| test   | localhost   |

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

结论：此种模式下此配置对默认的库进行不同步数据库，需要增加相关表的参数，***测试得出不加replicate-ignore-db = mysql参数，同样对默认的库生效,

2、binlog-format=STATEMENT模式

修改主从库的binlog模式

一起测试不存在的库、存在的库与默认库

replicate-ignore-db = test

#忽略指定不同步的库(主从不存在)

replicate-ignore-db = test001

#忽略指定不同步的库

replicate-wild-ignore-table=mysql.%

#忽略指定不同步的库的所有表

1：测试默认库数据同步

mysql>grantallprivilegeson*.*touser@'localhost'identifiedby'123456';

Query OK, 0 rowsaffected, 1 warning (0.03 sec)

mysql> selectuser,hostfrommysql.user;

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

| user| host     |

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

| rep   | %       |

| mysql.sys | localhost  |

| root   | localhost  |

| test   | localhost  |

| user| localhost  |

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

5 rowsinset(0.01 sec)

[root@mysql-s ~]# mysql -uroot -p -e "select user,host from mysql.user;"

Enter password:

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

| user| host     |

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

| rep    | %       |

| mysql.sys | localhost  |

| root   | localhost  |

| test   | localhost  |

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

结论：在此种模式下，对于默认的库，配置replicate-ignore-db = mysql，对默认的库与库中的表都生效

2：测试主从都不存在的库同步

3：测试主从服务器库已经同步的情况下数据同步(test001)

mysql> use test001;

Databasechanged

mysql> createtabletest001 (

-> id varchar(10)notnull,

-> namevarchar(10)notnull

-> );

Query OK, 0 rowsaffected (0.06 sec)

mysql> insertintotest001values('zhang','man');

Query OK, 1 row affected (0.11 sec)

[root@mysql-m ~]# mysql -uroot -p -e "select * from test001.test001;"

Enter password:

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

| id  | name|

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

| zhang | man  |

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

[root@mysql-s ~]# mysql -uroot -p -e "select * from test001.test001;"

Enter password:

ERROR 1146 (42S02) atline 1:Table'test001.test001'doesn't exist

【编辑推荐】

【责任编辑：庞桂玉 TEL：(010)68476606】

点赞 0