菜单

mysql复制过滤参数表达

2019年5月12日 - sqlite

参考文书档案:

manbetx网页手机登录版,mysql复制过滤参数表明,mysql过滤参数表明

参照他事他说加以考察文书档案: http://www.ywnds.com/?p=6945
https://stackoverflow.com/questions/23191160/whats-the-difference-in-replicate-wild-do-table-and-replicate-do-table
http://80888888.blog.51cto.com/2741630/1333249
http://keithlan.github.io/2015/11/02/mysql\_replicate\_rule/  
总共有伍个过滤规则: 1 
–replicate-do-db
2  –replicate-ignore-db
3  –replicate-do-table

–replicate-wild-do-table

–replicate-ignore-table

–replicate-wild-ignore-table
  以上四个规则的前一个是库品级的规则,后多少个是表级其他规则。使用注意点:
一:库等级的平整,只针对binlog_format=’STATEMENT’或’MIXED’。
2:如果是binlog_format=’ROW’,不受库等第规则限制,只受表等第规则限制。
叁:表级其余条条框框,针对富有格局。
四:binlog_format=’STATEMENT’恐怕’MIXED’时,记录use
语句,此时借使应用 –replicate-do-db,则不辅助跨库修改。
    如use test; update db壹 set ……
这里推断的是use
前边的test库,所以updte语句不试行。提出采取–replicate-wild-do-table
5:binlog_format=’ROW’时,不记录use语句。只受表等级规则的震慑。只可以布置后边4条规则。
 
测试分化方式下binlog记录景况:   实施sql: set global
binlog_format=’row’; use test; update db1.t2 set name=’a200′ where
id=105; use db1;   记录的binlog如下:

 1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 2 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 3 /*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 4 DELIMITER /*!*/;
 5 # at 4
 6 #170808  0:49:08 server id 1882073306  end_log_pos 120 CRC32 0xbddf73ce         Start: binlog v 4, server v 5.6.23-72.1-log created 170808  0:49:08
 7 # Warning: this binlog is either in use or was not closed properly.
 8 # at 120
 9 #170808  0:50:16 server id 1882073306  end_log_pos 191 CRC32 0xb2586cef         Query   thread_id=73    exec_time=0     error_code=0
10 SET TIMESTAMP=1502124616/*!*/;
11 SET @@session.pseudo_thread_id=73/*!*/;
12 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
13 SET @@session.sql_mode=1073741824/*!*/;
14 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
15 /*!\C utf8 *//*!*/;
16 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
17 SET @@session.lc_time_names=0/*!*/;
18 SET @@session.collation_database=DEFAULT/*!*/;
19 BEGIN
20 /*!*/;
21 # at 191
22 #170808  0:50:16 server id 1882073306  end_log_pos 245 CRC32 0xe7585ab8         Table_map: `db1`.`t2` mapped to number 395
23 # at 245
24 #170808  0:50:16 server id 1882073306  end_log_pos 365 CRC32 0x86dbbb24         Update_rows: table id 395 flags: STMT_END_F
25 ### UPDATE `db1`.`t2`
26 ### WHERE
27 ###   @1=105 /* INT meta=0 nullable=0 is_null=0 */
28 ###   @2='a100' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
29 ###   @3='57747ab889255af96b48d65e505382' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
30 ###   @4='' /* VARSTRING(72) meta=72 nullable=0 is_null=0 */
31 ###   @5=NULL /* VARSTRING(72) meta=0 nullable=1 is_null=1 */
32 ### SET
33 ###   @1=105 /* INT meta=0 nullable=0 is_null=0 */
34 ###   @2='a200' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
35 ###   @3='57747ab889255af96b48d65e505382' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
36 ###   @4='' /* VARSTRING(72) meta=72 nullable=0 is_null=0 */
37 ###   @5=NULL /* VARSTRING(72) meta=0 nullable=1 is_null=1 */
38 # at 365
39 #170808  0:50:16 server id 1882073306  end_log_pos 396 CRC32 0x99f0e5d3         Xid = 32212
40 COMMIT/*!*/;

  执行sql: set global binlog_format=’mixed’; use test; update db1.t2
set name=’a300′ where id=105; use db1;   记录的binlog如下:

 1 # at 396
 2 #170808  0:53:14 server id 1882073306  end_log_pos 474 CRC32 0x46f75d21         Query   thread_id=74    exec_time=0     error_code=0
 3 SET TIMESTAMP=1502124794/*!*/;
 4 BEGIN
 5 /*!*/;
 6 # at 474
 7 #170808  0:53:14 server id 1882073306  end_log_pos 589 CRC32 0x7193484c         Query   thread_id=74    exec_time=0     error_code=0
 8 use `test`/*!*/;
 9 SET TIMESTAMP=1502124794/*!*/;
10 update db1.t2 set name='a300' where id=105
11 /*!*/;
12 # at 589
13 #170808  0:53:14 server id 1882073306  end_log_pos 620 CRC32 0xc3e0c9e3         Xid = 32328
14 COMMIT/*!*/;
15 DELIMITER ;
16 # End of log file
17 ROLLBACK /* added by mysqlbinlog */;
18 /*!50003 SET [email protected]_COMPLETION_TYPE*/;
19 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

http://www.bkjia.com/Mysql/1222904.htmlwww.bkjia.comtruehttp://www.bkjia.com/Mysql/1222904.htmlTechArticlemysql复制过滤参数说明,mysql过滤参数说明
参谋文书档案:http://www.ywnds.com/?p=6945https://stackoverflow.com/questions/23191160/whats-the-difference-in-replicate-wi

http://www.ywnds.com/?p=6945

https://stackoverflow.com/questions/23191160/whats-the-difference-in-replicate-wild-do-table-and-replicate-do-table

http://80888888.blog.51cto.com/2741630/1333249

http://keithlan.github.io/2015/11/02/mysql_replicate_rule/

 

共计有五个过滤规则:

1  –replicate-do-db

2  –replicate-ignore-db

3  –replicate-do-table

4  –replicate-wild-do-table

5  –replicate-ignore-table

6  –replicate-wild-ignore-table

 

上述伍个规则的前一个是库等级的规则,后四个是表级其余平整。使用注意点:

1:库级其余规则,只针对binlog_format=’STATEMENT’或’MIXED’。
2:如果是binlog_format=’ROW’,不受库等第规则限制,只受表品级规则限制。

三:表等第的规则,针对全部情势。

4:binlog_format=’STATEMENT’可能’MIXED’时,记录use
语句,此时假设应用 –replicate-do-db,则不帮衬跨库修改。

    如use test; update db一 set ……
这里判定的是use
后边的test库,所以updte语句不施行。建议采用–replicate-wild-do-table

5:binlog_format=’ROW’时,不记录use语句。只受表等第规则的熏陶。只好配备后边4条规则。

 

测试不相同情势下binlog记录意况:

 

执行sql:

set
global binlog_format=’row’;

use
test;

update
db1.t2 set name=’a200′ where id=105;

use
db1;

 

记录的binlog如下:

 1 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
 2 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
 3 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
 4 DELIMITER /*!*/;
 5 # at 4
 6 #170808  0:49:08 server id 1882073306  end_log_pos 120 CRC32 0xbddf73ce         Start: binlog v 4, server v 5.6.23-72.1-log created 170808  0:49:08
 7 # Warning: this binlog is either in use or was not closed properly.
 8 # at 120
 9 #170808  0:50:16 server id 1882073306  end_log_pos 191 CRC32 0xb2586cef         Query   thread_id=73    exec_time=0     error_code=0
10 SET TIMESTAMP=1502124616/*!*/;
11 SET @@session.pseudo_thread_id=73/*!*/;
12 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
13 SET @@session.sql_mode=1073741824/*!*/;
14 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
15 /*!\C utf8 *//*!*/;
16 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
17 SET @@session.lc_time_names=0/*!*/;
18 SET @@session.collation_database=DEFAULT/*!*/;
19 BEGIN
20 /*!*/;
21 # at 191
22 #170808  0:50:16 server id 1882073306  end_log_pos 245 CRC32 0xe7585ab8         Table_map: `db1`.`t2` mapped to number 395
23 # at 245
24 #170808  0:50:16 server id 1882073306  end_log_pos 365 CRC32 0x86dbbb24         Update_rows: table id 395 flags: STMT_END_F
25 ### UPDATE `db1`.`t2`
26 ### WHERE
27 ###   @1=105 /* INT meta=0 nullable=0 is_null=0 */
28 ###   @2='a100' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
29 ###   @3='57747ab889255af96b48d65e505382' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
30 ###   @4='' /* VARSTRING(72) meta=72 nullable=0 is_null=0 */
31 ###   @5=NULL /* VARSTRING(72) meta=0 nullable=1 is_null=1 */
32 ### SET
33 ###   @1=105 /* INT meta=0 nullable=0 is_null=0 */
34 ###   @2='a200' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
35 ###   @3='57747ab889255af96b48d65e505382' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */
36 ###   @4='' /* VARSTRING(72) meta=72 nullable=0 is_null=0 */
37 ###   @5=NULL /* VARSTRING(72) meta=0 nullable=1 is_null=1 */
38 # at 365
39 #170808  0:50:16 server id 1882073306  end_log_pos 396 CRC32 0x99f0e5d3         Xid = 32212
40 COMMIT/*!*/;

 

执行sql:

set
global binlog_format=’mixed’;

use
test;

update
db1.t2 set name=’a300′ where id=105;

use
db1;

 

记录的binlog如下:

 1 # at 396
 2 #170808  0:53:14 server id 1882073306  end_log_pos 474 CRC32 0x46f75d21         Query   thread_id=74    exec_time=0     error_code=0
 3 SET TIMESTAMP=1502124794/*!*/;
 4 BEGIN
 5 /*!*/;
 6 # at 474
 7 #170808  0:53:14 server id 1882073306  end_log_pos 589 CRC32 0x7193484c         Query   thread_id=74    exec_time=0     error_code=0
 8 use `test`/*!*/;
 9 SET TIMESTAMP=1502124794/*!*/;
10 update db1.t2 set name='a300' where id=105
11 /*!*/;
12 # at 589
13 #170808  0:53:14 server id 1882073306  end_log_pos 620 CRC32 0xc3e0c9e3         Xid = 32328
14 COMMIT/*!*/;
15 DELIMITER ;
16 # End of log file
17 ROLLBACK /* added by mysqlbinlog */;
18 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
19 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图