MySQL 主從同步架構中你不知道的“坑”（上）

民工哥發表於2019-11-15
MySql主從同步架構

以下操作針對指定不同步庫

binlog-format=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：在忽略不同步的庫不存在的情況下

在配置檔案指定主從庫都不存在的庫，然後在主庫中建立這個庫，測試資料是否同步過去

主庫建立資料庫

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)

結論：發現建庫的動作沒有被從庫同步過來

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;

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)

結論：從庫沒有表，同樣也有資料同步過來

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引數，同樣對預設的庫生效,

binlog-format=STATEMENT模式

修改主從庫的binlog模式


一起測試不存在的庫、存在的庫與預設庫

replicate-ignore-db = test 

#忽略指定不同步的庫（主從不存在）

replicate-ignore-db = test001 

#忽略指定不同步的庫

replicate-wild-ignore-table=mysql.% 

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

1：測試預設庫資料同步

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  |

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

結論：在此種模式下，對於預設的庫，配置replicate-ignore-db = mysql，對預設的庫與庫中的表都生效

2：測試主從伺服器庫已經同步的情況下資料同步（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

點選--> 民工哥技術之路 關注微信公眾號，對話方塊回覆關鍵字：1024 可以獲取一份最新整理的 2048G 技術乾貨：包括系統運維、資料庫、redis、MogoDB、電子書、Java基礎課程、Java實戰專案、架構師綜合教程、架構師實戰專案、大資料、Docker容器、ELK Stack、機器學習、BAT面試精講視訊等。

相關文章