MySQL disables statements in binlog

MySQL replication is asynchronous, that is, non-synchronous. It does not verify data consistency in the database, as long as the SQL syntax is correct and there is no error, it can be successfully executed.

MySQL replication is asynchronous, that is, non-synchronous. It does not verify data consistency in the database, as long as the SQL syntax is correct and there is no error, it can be successfully executed.

MySQL replication is asynchronous, that is, non-synchronous. It does not verify data consistency in the database, as long as the SQL syntax is correct and there is no error, it can be successfully executed.

[Plain] view plaincopyprint?
MASTER @ root @ test 12:20:40> create table tab01
-> (Id int (10) primary key,
-> Name varchar (20 ));
Query OK, 0 rows affected (0.03 sec)

MASTER @ root @ test 12:21:32>
MASTER @ root @ test 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)

MASTER @ root @ test 12:21:52>

MASTER @ root @ test 12:20:40> create table tab01
-> (Id int (10) primary key,
-> Name varchar (20 ));
Query OK, 0 rows affected (0.03 sec)

MASTER @ root @ test 12:21:32>
MASTER @ root @ test 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)

MASTER @ root @ test 12:21:52> [plain] view plaincopyprint?
[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 (10) 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 do_slave_mode = 0 */;

[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 (10) 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 do_slave_mode = 0 */;

You can see that this statement is recorded in binlog. You can use the SQL _log_bin parameter to control whether to capture operations in binlog.

[Plain] view plaincopyprint?
MASTER @ root @ test 12:25:32> set SQL _log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

MASTER @ root @ test 12:25:37> alter table tab01 add index (name );
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

MASTER @ root @ test 12:26:04> set SQL _log_bin = 1;
Query OK, 0 rows affected (0.00 sec)

MASTER @ root @ test 12:26:07>
MASTER @ root @ test 12:26:08> show create table tab01 \ G
* *************************** 1. row ***************************
Table: tab01
Create Table: create table 'tab01 '(
'Id' int (10) not null,
'Name' varchar (20) default null,
Primary key ('id '),
KEY 'name' ('name ')
) ENGINE = InnoDB default charset = utf8
1 row in set (0.00 sec)

MASTER @ root @ test 12:25:32> set SQL _log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

MASTER @ root @ test 12:25:37> alter table tab01 add index (name );
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0

MASTER @ root @ test 12:26:04> set SQL _log_bin = 1;
Query OK, 0 rows affected (0.00 sec)

MASTER @ root @ test 12:26:07>
MASTER @ root @ test 12:26:08> show create table tab01 \ G
* *************************** 1. row ***************************
Table: tab01
Create Table: create table 'tab01 '(
'Id' int (10) not null,
'Name' varchar (20) default null,
Primary key ('id '),
KEY 'name' ('name ')
) ENGINE = InnoDB default charset = utf8
1 row in set (0.00 sec)
[Plain] view plaincopyprint?
[Root @ mynode1 mysql] #/service/mysql/bin/mysqlbinlog binlog-master.000004
/*! 50530 SET @ SESSION. pseudo do_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 107 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 was not closed properly.
ROLLBACK /*! */;
Binlog'
Bytes
AAAAAAAAAAAAAAAAAAAl0OlSEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA =
'/*! */;
# At 107
#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 do_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 (10) 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 do_slave_mode = 0 */;
[Root @ mynode1 mysql] #