http://dev.mysql.com/doc/refman/5.1/en/binary-log-mixed.html
官方文档中说When a DML statement updates an NDBCLUSTER table.
意思是说insert、update、delete操作引起的数据库变更都会自动转化成row-based?自己测试的结果是mysql 5.1.50 insert update delete在binlog中都是以row-based的方式进行保存的。现在的疑问的是实际是否是这样?
官方文档中说When a DML statement updates an NDBCLUSTER table.
意思是说insert、update、delete操作引起的数据库变更都会自动转化成row-based?自己测试的结果是mysql 5.1.50 insert update delete在binlog中都是以row-based的方式进行保存的。现在的疑问的是实际是否是这样?
在运行时可以动态低改变binlog的格式,除了以下几种情况:
. 存储过程或者触发器中间
. 启用了NDB
. 当前会话试用 RBR 模式,并且已打开了临时表
如果binlog采用了 MIXED 模式,那么在以下几种情况下会自动将binlog的模式由 SBR 模式改成 RBR 模式。
. 当DML语句更新一个NDB表时
. 当函数中包含 UUID() 时
. 2个及以上包含 AUTO_INCREMENT 字段的表被更新时
. 行任何 INSERT DELAYED 语句时
当一个UPDATE、INSERT、DELETE发生在一个NDBCLUSTER存储引擎的表时,MYSQL将自动从statement-based 切换为 row-based 。你是如何做测试的? 测试结果的哪个部分与手册中的不一致?
手册中原文如下。
show variables like '%format%'
你的sql语句
然后sqlbinlog -vv XXXX
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 2097152 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| sync_binlog | 15 |
+-----------------------------------------+----------------------+
7 rows in set (0.00 sec)mysql> use test
Database changed
mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)mysql> show master status\G
*************************** 1. row ***************************
File: fb-bin.000006
Position: 280
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)mysql> exit
Bye
[var]# mysqlbinlog fb-bin.000006 | more
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#100925 11:23:00 server id 2270 end_log_pos 106 Start: binlog v 4, server v 5.1.50-community-log created 100925 11:23:00 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
FGudTA/eCAAAZgAAAGoAAAABAAQANS4xLjUwLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAUa51MEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#100925 11:23:55 server id 2270 end_log_pos 174 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1285385035/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=83/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 174
# at 215
#100925 11:23:55 server id 2270 end_log_pos 215 Table_map: `test`.`t1` mapped to number 15
#100925 11:23:55 server id 2270 end_log_pos 253 Write_rows: table id 15 flags: STMT_END_FBINLOG '
S2udTBPeCAAAKQAAANcAAAAAAA8AAAAAAAEABHRlc3QAAnQxAAEIAAA=
S2udTBfeCAAAJgAAAP0AAAAAAA8AAAAAAAEAAf/+JgAAAAAAAAA=
'/*!*/;
# at 253
#100925 11:23:55 server id 2270 end_log_pos 280 Xid = 12
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;这个时候可以看到是row_format格式。
mysql> show variables like '%binlog%'
-> ;
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 2097152 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | MIXED |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| sync_binlog | 15 |
+-----------------------------------------+----------------------+
7 rows in set (0.00 sec)mysql> show variables like '%isolation%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)mysql> insert into t1 values(),(),(),(),();
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql> show master logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| fb-bin.000001 | 106 |
| fb-bin.000002 | 19105 |
| fb-bin.000003 | 723139 |
| fb-bin.000004 | 4549 |
| fb-bin.000005 | 1649 |
| fb-bin.000006 | 482 |
| fb-bin.000007 | 327 |
+---------------+-----------+
7 rows in set (0.00 sec)mysql> show master status\G
*************************** 1. row ***************************
File: fb-bin.000007
Position: 327
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)mysql> exit
Bye
[var]# mysqlbinlog fb-bin.000007 | more
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#100925 11:27:23 server id 2270 end_log_pos 106 Start: binlog v 4, server v 5.1.50-community-log created 100925 11:27:23 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
G2ydTA/eCAAAZgAAAGoAAAABAAQANS4xLjUwLWNvbW11bml0eS1sb2cAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAbbJ1MEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#100925 11:28:06 server id 2270 end_log_pos 174 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1285385286/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=83/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 174
#100925 11:28:06 server id 2270 end_log_pos 202 Intvar
SET INSERT_ID=41/*!*/;
# at 202
#100925 11:28:06 server id 2270 end_log_pos 300 Query thread_id=1 exec_time=0 error_code=0
use test/*!*/;
SET TIMESTAMP=1285385286/*!*/;
insert into t1 values(),(),(),(),()
/*!*/;
# at 300
#100925 11:28:06 server id 2270 end_log_pos 327 Xid = 10
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
另外我试验了一下,将tx_isolation改成REPEATABLE-READ之后,我再插入的时候,binlog中显示的是statement