MySQL主从同步架构中你不知道的“坑”

数据库 MySQL
本文主要分享MySQL主从同步架构中你不知道的“坑”,其中包括binlog-format=ROW模式和binlog-format=STATEMENT模式的情况,快来看看吧。

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

1、binlog-format=ROW模式

查看主从的binlog模

 

  1. mysql> show slave statusG 
  2.  
  3. *********************** 1. row ************************* 
  4.  
  5. Slave_IO_State: Waiting for master to send event 
  6.  
  7.   Master_Host: 192.168.22.171 
  8.  
  9.   Master_User: rep 
  10.  
  11.   Master_Port: 3306 
  12.  
  13. Connect_Retry: 60 
  14.  
  15. Master_Log_File: master-bin.000004 
  16.  
  17. Read_Master_Log_Pos: 463 
  18.  
  19. Relay_Log_File: s1.000011 
  20.  
  21. Relay_Log_Pos: 630 
  22.  
  23. Relay_Master_Log_File: master-bin.000004 
  24.  
  25. Slave_IO_Running: Yes 
  26.  
  27. Slave_SQL_Running: Yes 
  28.  
  29. Replicate_Do_DB: 
  30.  
  31. Replicate_Ignore_DB: test  

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

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

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

主库创建数据库 

  1. mysql> create database test; 
  2.  
  3. Query OK, 1 row affected (0.06 sec)  
  4.  
  5. mysql> show databases; 
  6.  
  7. +--------------------+ 
  8.  
  9. Database     | 
  10.  
  11. +--------------------+ 
  12.  
  13. | information_schema | 
  14.  
  15. | mysql       | 
  16.  
  17. | performance_schema | 
  18.  
  19. | sys        | 
  20.  
  21. | test        | 
  22.  
  23. +--------------------+ 
  24.  
  25. rows in set (0.01 sec)  

从库查看

  1. mysql> show databases; 
  2.  
  3. +--------------------+ 
  4.  
  5. Database      | 
  6.  
  7. +--------------------+ 
  8.  
  9. | information_schema | 
  10.  
  11. | mysql       | 
  12.  
  13. | performance_schema | 
  14.  
  15. | sys        | 
  16.  
  17. +--------------------+ 
  18.  
  19. rows in set (0.01 sec)  

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

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

目前主从库都存在库test001 

  1. [root@mysql-m ~]# mysql -uroot -p -e "show databases;"|grep test001 
  2.  
  3. Enter password
  4.  
  5. test001 
  6.  
  7. [root@mysql-s ~]# mysql -uroot -p -e "show databases;"|grep test001 
  8.  
  9. Enter password
  10.  
  11. test001  

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

  1. mysql> use test001; 
  2.  
  3. Database changed 
  4.  
  5. mysql> create table test ( 
  6.  
  7.     -> id varchar(10) not null
  8.  
  9.     -> name varchar(10) not null 
  10.  
  11.     -> ); 
  12.  
  13. Query OK, 0 rows affected (0.06 sec) 
  14.  
  15. mysql> insert into test values('zhang','man'); 
  16.  
  17. Query OK, 1 row affected (0.03 sec) 
  18.  
  19. mysql> select * from test; 
  20.  
  21. +-------+------+ 
  22.  
  23. | id  | name | 
  24.  
  25. +-------+------+ 
  26.  
  27. | zhang | man | 
  28.  
  29. +-------+------+ 
  30.  
  31. 1 row in set (0.00 sec) 
  32.  
  33. mysql> select * from test001.test; 
  34.  
  35. ERROR 1146 (42S02): Table 'test001.test' doesn't exist 
  36.  
  37. mysql> use test001; 
  38.  
  39. Database changed 
  40.  
  41. mysql> show tables; 
  42.  
  43. Empty set (0.00 sec)  

结论:从库没有表,同样也有数据同步过来

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

  1. replicate-ignore-db = mysql   
  2.  
  3. #忽略指定不同步的库  

测试前的数据 

  1. [root@mysql-m ~]# mysql -uroot -p -e "select user,host from mysql.user;" 
  2.  
  3. Enter password
  4.  
  5. +-----------+--------------+ 
  6.  
  7. user    | host     | 
  8.  
  9. +-----------+--------------+ 
  10.  
  11. | mysql.sys | localhost   | 
  12.  
  13. | root   | localhost  | 
  14.  
  15. +-----------+--------------+ 
  16.  
  17. [root@mysql-s ~]# mysql -uroot -p -e "select user,host from mysql.user;" 
  18.  
  19. Enter password
  20.  
  21. +-----------+--------------+ 
  22.  
  23. user   | host     | 
  24.  
  25. +-----------+--------------+ 
  26.  
  27. | mysql.sys | localhost  | 
  28.  
  29. | root   | localhost  | 
  30.  
  31. +-----------+--------------+ 
  32.  
  33. [root@mysql-m ~]# mysql -uroot -p -e "grant all privileges on *.* to test@localhost identified by '123456';" 
  34.  
  35. Enter password
  36.  
  37. [root@mysql-m ~]# mysql -uroot -p -e "select user,host from mysql.user;" 
  38.  
  39. Enter password
  40.  
  41. +-----------+--------------+ 
  42.  
  43. user   | host    | 
  44.  
  45. +-----------+--------------+ 
  46.  
  47. | mysql.sys | localhost  | 
  48.  
  49. | root   | localhost  | 
  50.  
  51. | test   | localhost  | 
  52.  
  53. +-----------+--------------+ 
  54.  
  55. [root@mysql-s ~]# mysql -uroot -p -e "select user,host from mysql.user;" 
  56.  
  57. Enter password
  58.  
  59. +-----------+--------------+ 
  60.  
  61. user   | host     | 
  62.  
  63. +-----------+--------------+ 
  64.  
  65. | mysql.sys | localhost  | 
  66.  
  67. | root   | localhost  | 
  68.  
  69. | test   | localhost  | 
  70.  
  71. +-----------+--------------+  

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

  1. replicate-ignore-db = mysql      
  2.  
  3. #忽略指定不同步的库 
  4.  
  5. replicate-wild-ignore-table=mysql.% 
  6.  
  7. #忽略指定不同步的库的所有表 
  8.  
  9. [root@mysql-m ~]# mysql -uroot -p -e "grant all privileges on *.* to testuser@localhost identified by '123456';" 
  10.  
  11. Enter password
  12.  
  13. [root@mysql-m ~]# mysql -uroot -p -e "select user,host from mysql.user;" 
  14.  
  15. Enter password
  16.  
  17. +-----------+--------------+ 
  18.  
  19. user   | host    | 
  20.  
  21. +-----------+--------------+ 
  22.  
  23. | mysql.sys | localhost  | 
  24.  
  25. | root   | localhost  | 
  26.  
  27. | test   | localhost  | 
  28.  
  29. | testuser | localhost  | 
  30.  
  31. +-----------+--------------+ 
  32.  
  33. [root@mysql-s ~]# mysql -uroot -p -e "select user,host from mysql.user;" 
  34.  
  35. Enter password
  36.  
  37. +-----------+--------------+ 
  38.  
  39. user   | host     | 
  40.  
  41. +-----------+--------------+ 
  42.  
  43. | mysql.sys | localhost   | 
  44.  
  45. | root   | localhost   | 
  46.  
  47. | test   | localhost   | 
  48.  
  49. +-----------+--------------+  

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

2、binlog-format=STATEMENT模式

修改主从库的binlog模式

 

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

  1. replicate-ignore-db = test  
  2.  
  3. #忽略指定不同步的库(主从不存在) 
  4.  
  5. replicate-ignore-db = test001  
  6.  
  7. #忽略指定不同步的库 
  8.  
  9. replicate-wild-ignore-table=mysql.%  
  10.  
  11. #忽略指定不同步的库的所有表 

1:测试默认库数据同步 

  1. mysql> grant all privileges on *.* to user@'localhost' identified by '123456'
  2.  
  3. Query OK, 0 rows affected, 1 warning (0.03 sec) 
  4.  
  5. mysql> select user,host from mysql.user
  6.  
  7. +-----------+--------------+ 
  8.  
  9. user   | host     | 
  10.  
  11. +-----------+--------------+ 
  12.  
  13. | rep   | %       | 
  14.  
  15. | mysql.sys | localhost  | 
  16.  
  17. | root   | localhost  | 
  18.  
  19. | test   | localhost  | 
  20.  
  21. user   | localhost  | 
  22.  
  23. +-----------+--------------+ 
  24.  
  25. rows in set (0.01 sec) 
  26.  
  27. [root@mysql-s ~]# mysql -uroot -p -e "select user,host from mysql.user;" 
  28.  
  29. Enter password
  30.  
  31. +-----------+--------------+ 
  32.  
  33. user   | host     | 
  34.  
  35. +-----------+--------------+ 
  36.  
  37. | rep    | %       | 
  38.  
  39. | mysql.sys | localhost  | 
  40.  
  41. | root   | localhost  | 
  42.  
  43. | test   | localhost  | 
  44.  
  45. +-----------+--------------+  

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

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

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

 

  1. mysql> use test001; 
  2.  
  3. Database changed 
  4.  
  5. mysql> create table test001 ( 
  6.  
  7.     -> id varchar(10) not null
  8.  
  9.     -> name varchar(10) not null 
  10.  
  11.     -> ); 
  12.  
  13. Query OK, 0 rows affected (0.06 sec) 
  14.  
  15. mysql> insert into test001 values('zhang','man'); 
  16.  
  17. Query OK, 1 row affected (0.11 sec)  
  18.  
  19. [root@mysql-m ~]# mysql -uroot -p -e "select * from test001.test001;" 
  20.  
  21. Enter password
  22.  
  23. +-------+------+ 
  24.  
  25. | id  | name | 
  26.  
  27. +-------+------+ 
  28.  
  29. | zhang | man  | 
  30.  
  31. +-------+------+ 
  32.  
  33. [root@mysql-s ~]# mysql -uroot -p -e "select * from test001.test001;" 
  34.  
  35. Enter password
  36.  
  37. ERROR 1146 (42S02) at line 1: Table 'test001.test001' doesn't exist  

 

 

责任编辑:庞桂玉 来源: 数据库开发
相关推荐

2024-01-09 07:39:20

maven特性版本

2020-06-12 09:20:33

前端Blob字符串

2020-07-28 08:26:34

WebSocket浏览器

2011-09-15 17:10:41

2009-12-10 09:37:43

2022-10-13 11:48:37

Web共享机制操作系统

2021-02-01 23:23:39

FiddlerCharlesWeb

2010-08-23 09:56:09

Java性能监控

2022-11-04 08:19:18

gRPC框架项目

2020-09-15 08:35:57

TypeScript JavaScript类型

2020-08-11 11:20:49

Linux命令使用技巧

2012-11-23 10:57:44

Shell

2015-06-19 13:54:49

2021-10-17 13:10:56

函数TypeScript泛型

2021-12-29 11:38:59

JS前端沙箱

2021-12-22 09:08:39

JSON.stringJavaScript字符串

2011-08-17 15:51:59

windows7Shift键

2019-11-04 15:57:29

MySQLInnoDB内存

2022-05-05 12:02:45

SCSS函数开发

2022-12-12 08:35:51

Map容器接口
点赞
收藏

51CTO技术栈公众号