mysql主从强制同步_MySQL主从同步架构中你不知道的“坑”（上）

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

binlog-format&＃61;ROW模式

查看主从的binlog模式

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send 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&＃xff1a;在忽略不同步的库不存在的情况下

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

主库创建数据库

mysql> create database test;

Query OK, 1 row affected (0.06 sec)

mysql> show databases;

&＃43;--------------------&＃43;

| Database |

&＃43;--------------------&＃43;

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

&＃43;--------------------&＃43;

5 rows in set (0.01 sec)

从库查看

mysql> show databases;

&＃43;--------------------&＃43;

| Database |

&＃43;--------------------&＃43;

| information_schema |

| mysql |

| performance_schema |

| sys |

&＃43;--------------------&＃43;

5 rows in set (0.01 sec)

结论&＃xff1a;发现建库的动作没有被从库同步过来

2&＃xff1a;测试主从服务器库存在数据同步情况

目前主从库都存在库test001

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

Enter password:

test001

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

Enter password:

test001

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

mysql> use test001;

Database changed

mysql> create table test (

-> id varchar(10) not null,

-> name varchar(10) not null

-> );

Query OK, 0 rows affected (0.06 sec)

mysql> insert into test values(&＃39;zhang&＃39;,&＃39;man&＃39;);

Query OK, 1 row affected (0.03 sec)

mysql> select * from test;

&＃43;-------&＃43;------&＃43;

| id | name |

&＃43;-------&＃43;------&＃43;

| zhang | man |

&＃43;-------&＃43;------&＃43;

1 row in set (0.00 sec)

mysql> select * from test001.test;

ERROR 1146 (42S02): Table &＃39;test001.test&＃39; doesn&＃39;t exist

mysql> use test001;

Database changed

mysql> show tables;

Empty set (0.00 sec)

结论&＃xff1a;从库没有表&＃xff0c;同样也有数据同步过来

3&＃xff1a;征对数据库默认的库同步(如mysql)

replicate-ignore-db &＃61; mysql

#忽略指定不同步的库

测试前的数据

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

Enter password:

&＃43;-----------&＃43;--------------&＃43;

| user | host |

&＃43;-----------&＃43;--------------&＃43;

| mysql.sys | localhost |

| root | localhost |

&＃43;-----------&＃43;--------------&＃43;

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

Enter password:

&＃43;-----------&＃43;--------------&＃43;

| user | host |

&＃43;-----------&＃43;--------------&＃43;

| mysql.sys | localhost |

| root | localhost |

&＃43;-----------&＃43;--------------&＃43;

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

Enter password:

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

Enter password:

&＃43;-----------&＃43;--------------&＃43;

| user | host |

&＃43;-----------&＃43;--------------&＃43;

| mysql.sys | localhost |

| root | localhost |

| test | localhost |

&＃43;-----------&＃43;--------------&＃43;

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

Enter password:

&＃43;-----------&＃43;--------------&＃43;

| user | host |

&＃43;-----------&＃43;--------------&＃43;

| mysql.sys | localhost |

| root | localhost |

| test | localhost |

&＃43;-----------&＃43;--------------&＃43;

结论&＃xff1a;此种模式下此配置对默认的库不生效&＃xff0c;因此调整配置

replicate-ignore-db &＃61; mysql

#忽略指定不同步的库

replicate-wild-ignore-table&＃61;mysql.%

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

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

Enter password:

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

Enter password:

&＃43;-----------&＃43;--------------&＃43;

| user | host |

&＃43;-----------&＃43;--------------&＃43;

| mysql.sys | localhost |

| root | localhost |

| test | localhost |

| testuser | localhost |

&＃43;-----------&＃43;--------------&＃43;

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

Enter password:

&＃43;-----------&＃43;--------------&＃43;

| user | host |

&＃43;-----------&＃43;--------------&＃43;

| mysql.sys | localhost |

| root | localhost |

| test | localhost |

&＃43;-----------&＃43;--------------&＃43;

结论&＃xff1a;此种模式下此配置对默认的库进行不同步数据库&＃xff0c;需要增加相关表的参数&＃xff0c;最后测试得出不加replicate-ignore-db &＃61; mysql参数&＃xff0c;同样对默认的库生效,

binlog-format&＃61;STATEMENT模式

修改主从库的binlog模式

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

replicate-ignore-db &＃61; test

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

replicate-ignore-db &＃61; test001

#忽略指定不同步的库

replicate-wild-ignore-table&＃61;mysql.%

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

1&＃xff1a;测试默认库数据同步

mysql> grant all privileges on *.* to user&＃64;&＃39;localhost&＃39; identified by &＃39;123456&＃39;;

Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> select user,host from mysql.user;

&＃43;-----------&＃43;--------------&＃43;

| user | host |

&＃43;-----------&＃43;--------------&＃43;

| rep | % |

| mysql.sys | localhost |

| root | localhost |

| test | localhost |

| user | localhost |

&＃43;-----------&＃43;--------------&＃43;

5 rows in set (0.01 sec)

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

Enter password:

&＃43;-----------&＃43;--------------&＃43;

| user | host |

&＃43;-----------&＃43;--------------&＃43;

| rep | % |

| mysql.sys | localhost |

| root | localhost |

| test | localhost |

&＃43;-----------&＃43;--------------&＃43;

结论&＃xff1a;在此种模式下&＃xff0c;对于默认的库&＃xff0c;配置replicate-ignore-db &＃61; mysql&＃xff0c;对默认的库与库中的表都生效

2&＃xff1a;测试主从服务器库已经同步的情况下数据同步(test001)

mysql> use test001;

Database changed

mysql> create table test001 (

-> id varchar(10) not null,

-> name varchar(10) not null

-> );

Query OK, 0 rows affected (0.06 sec)

mysql> insert into test001 values(&＃39;zhang&＃39;,&＃39;man&＃39;);

Query OK, 1 row affected (0.11 sec)

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

Enter password:

&＃43;-------&＃43;------&＃43;

| id | name |

&＃43;-------&＃43;------&＃43;

| zhang | man |

&＃43;-------&＃43;------&＃43;

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

Enter password:

ERROR 1146 (42S02) at line 1: Table &＃39;test001.test001&＃39; doesn&＃39;t exist

点击-->