MySQL主從同步架構(gòu)中你不知道的“坑”
作者：佚名
本文主要分享MySQL主從同步架構(gòu)中你不知道的“坑”，其中包括binlog-format=ROW模式和binlog-format=STATEMENT模式的情況，快來看看吧。
以下操作征對(duì)指定不同步庫
1、binlog-format=ROW模式
查看主從的binlog模
- mysql> show slave statusG
- *********************** 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
查看從庫狀態(tài)也顯示此項(xiàng)配置
1：在忽略不同步的庫不存在的情況下
在配置文件指定主從庫都不存在的庫，然后在主庫中創(chuàng)建這個(gè)庫，測試數(shù)據(jù)是否同步過去
主庫創(chuàng)建數(shù)據(jù)庫
- mysql> create database test;
- Query OK, 1 row affected (0.06 sec)
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | sys |
- | test |
- +--------------------+
- 5 rows in set (0.01 sec)
從庫查看
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | mysql |
- | performance_schema |
- | sys |
- +--------------------+
- 5 rows in set (0.01 sec)
結(jié)論：發(fā)現(xiàn)建庫的動(dòng)作沒有被從庫同步過來
2：測試主從服務(wù)器庫存在數(shù)據(jù)同步情況
目前主從庫都存在庫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
在主庫插入數(shù)據(jù)測試從庫同步情況
- 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('zhang','man');
- Query OK, 1 row affected (0.03 sec)
- mysql> select * from test;
- +-------+------+
- | id | name |
- +-------+------+
- | zhang | man |
- +-------+------+
- 1 row in set (0.00 sec)
- mysql> select * from test001.test;
- ERROR 1146 (42S02): Table 'test001.test' doesn't exist
- mysql> use test001;
- Database changed
- mysql> show tables;
- Empty set (0.00 sec)
結(jié)論：從庫沒有表，同樣也有數(shù)據(jù)同步過來
3：征對(duì)數(shù)據(jù)庫默認(rèn)的庫同步（如mysql）
- replicate-ignore-db = mysql
- #忽略指定不同步的庫
測試前的數(shù)據(jù)
- [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 |
- +-----------+--------------+
結(jié)論：此種模式下此配置對(duì)默認(rèn)的庫不生效，因此調(diào)整配置
- 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 |
- +-----------+--------------+
結(jié)論：此種模式下此配置對(duì)默認(rèn)的庫進(jìn)行不同步數(shù)據(jù)庫，需要增加相關(guān)表的參數(shù)，***測試得出不加replicate-ignore-db = mysql參數(shù)，同樣對(duì)默認(rèn)的庫生效,
2、binlog-format=STATEMENT模式
修改主從庫的binlog模式
一起測試不存在的庫、存在的庫與默認(rèn)庫
- replicate-ignore-db = test
- #忽略指定不同步的庫（主從不存在）
- replicate-ignore-db = test001
- #忽略指定不同步的庫
- replicate-wild-ignore-table=mysql.%
- #忽略指定不同步的庫的所有表
1：測試默認(rèn)庫數(shù)據(jù)同步
- mysql> grant all privileges on *.* to user@'localhost' identified by '123456';
- Query OK, 0 rows affected, 1 warning (0.03 sec)
- mysql> select user,host from mysql.user;
- +-----------+--------------+
- | user | host |
- +-----------+--------------+
- | rep | % |
- | mysql.sys | localhost |
- | root | localhost |
- | test | localhost |
- | user | localhost |
- +-----------+--------------+
- 5 rows in set (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 |
- +-----------+--------------+
結(jié)論：在此種模式下，對(duì)于默認(rèn)的庫，配置replicate-ignore-db = mysql，對(duì)默認(rèn)的庫與庫中的表都生效
2：測試主從都不存在的庫同步
3：測試主從服務(wù)器庫已經(jīng)同步的情況下數(shù)據(jù)同步（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('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) at line 1: Table 'test001.test001' doesn't exist
責(zé)任編輯：龐桂玉
來源：
數(shù)據(jù)庫開發(fā)
