MySQL禁用binlog中的语句

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

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: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_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 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_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 ' 
JdDpUg8BAAAAZwAAAGsAAAABAAQANS41LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 
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_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_SLAVE_MODE=0*/; 
[root@mynode1 mysql]#