MySQL禁用binlog中的语句 - 数据库编程

MySQL复制是异步的，也就是说是非同步的过程，它不会校验数据库中数据的一致性，只要SQL语法正确并且没有错误就能成功执行

[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: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_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_SLAVE_MODE=0*/;

可以看到binlog里记录了这条语句，可以通过sql_log_bin参数来控制是否捕获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

MySQL禁用binlog中的语句(一)