Monday, May 24, 2010

mysqlbinlog Ver 3.3和3.1版本之间的输出差异

mysqlbinlog Ver 3.3中多了一个--base64-output参数,默认值为auto:


--base64-output[=name]
Determine when the output statements should be
base64-encoded BINLOG statements: 'never' disables it and
works only for binlogs without row-based events; 'auto'
is the default and prints base64 only when necessary
(i.e., for row-based events and format description
events); 'always' prints base64 whenever possible.
'always' is for debugging only and should not be used in
a production system. The default is 'auto'.
--base64-output is a short form for
--base64-output=always.

在原来的Ver3.1版本中没有此参数,对比mysql5.0生成的日志文件的输出结果:
Ver3.1版本中使用mysqlbinlog输出结果:

shell> mysqlbinlog mysql-bin.000182|less
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#100515 1:38:58 server id 25 end_log_pos 98 Start: binlog v 4, server v 5.0.37-log created 100515 1:38:58
# at 98

Ver3.3版本中使用mysqlbinlog输出结果:

shell> mysqlbinlog --base64-output=never mysql-bin.000182|less
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#100515 1:38:58 server id 25 end_log_pos 98 Start: binlog v 4, server v 5.0.37-log created 100515 1:38:58
# at 98

shell> mysqlbinlog --base64-output=auto mysql-bin.000182|less
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#100515 1:38:58 server id 25 end_log_pos 98 Start: binlog v 4, server v 5.0.37-log created 100515 1:38:58
BINLOG '
sortSw8ZAAAAXgAAAGIAAAAAAAQANS4wLjM3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAASwAEGg==
'/*!*/;
# at 98

从上面结果比较而言,相当于3.1版本的mysqlbinlog的base64-output参数默认为never。
在mysql 5.0中带的mysqlbinlog一般为3.1版本的,而mysql5.1版本中带的mysqlbinlog则为3.3版本,如果需要将mysql5.0生成的日志文件导入mysql5.1时,需要将mysqlbinlog的base64-output值设置为never。如果不设置此参数,导入日志时,会在
BINLOG 'sortSw8ZAAAAXgAAAGIAAAAAAAQANS4wLjM3LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAASwAEGg=='

处报SQL语法错误。

No comments :