mysql+禁用binlog_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]#

本文原创发布php中文网，转载请注明出处，感谢您的尊重！