MySQL中如何禁用binlog中的语句
|
MySQL复制是异步的，也就是说是非同步的过程，它不会校验数据库中数据的一致性，只要SQL语法正确并且没有错误就能成功执行 MASTERtest 12:20:40>create table tab01 -> (id int(10) 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(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中的操作 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 tab01G *************************** 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) [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 本栏目更多精彩内容：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(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]# 可以将此DDL语句在slave库执行 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 tab01G *************************** 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) SLAVEtest 12:28:10> 在大型master-slave环境下执行DDL语句时，在每一台slave上手动去执行DDL命令能绕过MySQL数据复制单线程对某些命令的限制 （编辑：常州站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
