MySQL禁用binlog中的語句

MySQL複製是非同步，也就是說是非同步的過程，它不會校正資料庫中資料的一致性，只要SQL文法正確並且沒有錯誤就能成功執行

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>

[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=0use `test`/*!*/;SET TIMESTAMP=1391055692/*!*/;create table tab01(id int(10) primary key ,name varchar(20))/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可以看到binlog裡記錄了這條語句，可以通過sql_log_bin參數來控制是否捕獲binlog中的操作

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: 0MASTER@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: tab01Create Table: CREATE TABLE `tab01` (  `id` int(10) NOT NULL,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 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 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 'JdDpUg8BAAAAZwAAAGsAAAABAAQANS41LjM0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAl0OlSEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA=='/*!*/;# at 107#140130 12:13:45 server id 1  end_log_pos 213   Query   thread_id=3     exec_time=0     error_code=0use `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=0use `test`/*!*/;SET TIMESTAMP=1391055692/*!*/;create table tab01(id int(10) primary key ,name varchar(20))/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[root@mynode1 mysql]# 

可以將此DDL語句在slave庫執行

SLAVE@root@test 12:26:39>alter table tab01 add index(name);Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0SLAVE@root@test 12:27:57>show create table tab01\G*************************** 1. row ***************************       Table: tab01Create Table: CREATE TABLE `tab01` (  `id` int(10) NOT NULL,  `name` varchar(20) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)SLAVE@root@test 12:28:10>

在大型master-slave環境下執行DDL語句時，在每一台slave上手動去執行DDL命令能繞過MySQL資料複製單線程對某些命令的限制