How to disable statements in Binlog in MySQL

MySQL replication is asynchronous, that is, a unsynchronized process that does not validate the consistency of data in the database, as long as the SQL syntax is correct and there is no error to execute successfully

Mastertest 12:20:40>create Table Tab01

-> (id int () primary key,

-> name varchar (20));

Query OK, 0 rows affected (0.03 sec)

Mastertest 12:21:32>

Mastertest 12:21:49>show Master status;

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

| File | Position | binlog_do_db | binlog_ignore_db |

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

|      binlog-master.000004 |              338 |                  | |

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

1 row in Set (0.00 sec)

Mastertest 12:21:52>

[Root@mynode1 mysql]#/service/mysql/bin/mysqlbinlog binlog-master.000004|tail-13

# at 213

#140130 12:21:32 Server ID 1 end_log_pos 338 Query thread_id=3 exec_time=0 error_code=0

Use ' Test '/*!*/;

SET timestamp=1391055692/*!*/;

CREATE TABLE Tab01

(id int () primary key,

Name varchar (20))

/*!*/;

DELIMITER;

# End of log file

ROLLBACK/* Added by Mysqlbinlog * *;

/*!50003 SET completion_type= @OLD_COMPLETION_TYPE * *;

/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;

You can see that this statement is recorded in the Binlog, and you can control whether the action in Binlog is captured by Sql_log_bin parameters.

Mastertest 12:25:32>set sql_log_bin=0;

Query OK, 0 rows Affected (0.00 sec)

Mastertest 12:25:37>alter Table tab01 Add index (name);

Query OK, 0 rows affected (0.05 sec)

records:0 duplicates:0 warnings:0

Mastertest 12:26:04>set sql_log_bin=1;

Query OK, 0 rows Affected (0.00 sec)

Mastertest 12:26:07>

Mastertest 12:26:08>show CREATE TABLE Tab01\g

1. Row ***************************

Table:tab01

Create table:create Table ' tab01 ' (

Id ' int ' not NULL,

Name ' varchar ' DEFAULT NULL,

PRIMARY KEY (' id '),

KEY ' name ' (' name ')

) Engine=innodb DEFAULT Charset=utf8

1 row in Set (0.00 sec)

[Root@mynode1 mysql]#/service/mysql/bin/mysqlbinlog binlog-master.000004

/*!50530 SET @ @SESSION. pseudo_slave_mode=1*/;

/*!40019 SET @ @session. max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE =@ @COMPLETION_TYPE, completion_type=0*/;

DELIMITER/*!*/;

# at 4

#140130 12:08:05 Server ID 1 end_log_pos start:binlog v 4, Server v 5.5.34-log created 140130-12:08:05 at startup

# Warning:this Binlog is either in-use or being not closed properly.

rollback/*!*/;

Binlog '

Jddpug8baaaazwaaagsaaaabaaqans41ljm0lwxvzwaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

aaaaaaaaaaaaaaaaaaal0olsezgnaagaegaebaqeegaavaaegggaaaaicagcaa==

'/*!*/;

# at 107

This column more highlights: http://www.bianceng.cn/database/MySQL/

#140130 12:13:45 Server ID 1 end_log_pos 213 Query thread_id=3 exec_time=0 error_code=0

Use ' tmp '/*!*/;

SET timestamp=1391055225/*!*/;

SET @ @session. pseudo_thread_id=3/*!*/;

SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/;

SET @ @session. sql_mode=0/*!*/;

SET @ @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;

/*!\c UTF8 *//*!*/;

SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=33/*!*/;

SET @ @session. lc_time_names=0/*!*/;

SET @ @session. collation_database=default/*!*/;

CREATE TABLE tab_02 as SELECT * from Tab_tmp

/*!*/;

# at 213

#140130 12:21:32 Server ID 1 end_log_pos 338 Query thread_id=3 exec_time=0 error_code=0

Use ' Test '/*!*/;

SET timestamp=1391055692/*!*/;

CREATE TABLE Tab01

(id int () primary key,

Name varchar (20))

/*!*/;

DELIMITER;

# End of log file

ROLLBACK/* Added by Mysqlbinlog * *;

/*!50003 SET completion_type= @OLD_COMPLETION_TYPE * *;

/*!50530 SET @ @SESSION. pseudo_slave_mode=0*/;

[Root@mynode1 mysql]#

This DDL statement can be executed in the slave library

Slavetest 12:26:39>alter Table tab01 Add index (name);

Query OK, 0 rows affected (0.04 sec)

records:0 duplicates:0 warnings:0

Slavetest 12:27:57>show CREATE TABLE Tab01\g

1. Row ***************************

Table:tab01

Create table:create Table ' tab01 ' (

Id ' int ' not NULL,

Name ' varchar ' DEFAULT NULL,

PRIMARY KEY (' id '),

KEY ' name ' (' name ')

) Engine=innodb DEFAULT Charset=utf8

1 row in Set (0.00 sec)

Slavetest 12:28:10>

When executing DDL statements in a large master-slave environment, manually executing DDL commands on each slave can bypass MySQL data replication single-threaded restrictions on certain commands