使用mysqlbinlog时出错

聪明笨小孩

看看字符集

Coolriver

原帖由 lsl4437 于 2009-7-1 17:51 发表

我用mysqlbinlog fc10-base-bin.000015 >/tmp/15.sql这个命令生成的15.sql.它里面就有这些

试一下：

mysqlbinlog  --base64-output＝AUTO  fc10-base-bin.000015 >/tmp/151.sql

这样查看一下。

[ 本帖最后由 Coolriver 于 2009-7-2 14:22 编辑 ]

bitterness

原帖由 lsl4437 于 2009-7-1 15:35 发表
同一版本，就是同一个数据库

同一DB，不应该是charset问题

lsl4437

原帖由 Coolriver 于 2009-7-2 14:01 发表

试一下：

mysqlbinlog  --base64-output＝AUTO  fc10-base-bin.000015 >/tmp/151.sql

这样查看一下。

还是有那些二进制的东西
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#090630  9:58:58 server id 9838  end_log_pos 106        Start: binlog v 4, server v 5.1.24-rc-log created 090630  9:58:58 at startup
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
ROLLBACK/*!*/;
BINLOG '
YnFJSg9uJgAAZgAAAGoAAAABAAQANS4xLjI0LXJjLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAABicUlKEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#090630  9:59:06 server id 9838  end_log_pos 174        Query   thread_id=5     exec_time=0     error_code=0
use orca/*!*/;
SET TIMESTAMP=1246327146/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=2097152/*!*/;
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/*!*/;
BEGIN
/*!*/;
# at 174
# at 300
#090630  9:59:06 server id 9838  end_log_pos 300        Table_map: `orca`.`ALARM_EVENT` mapped to number 19
#090630  9:59:06 server id 9838  end_log_pos 732        Update_rows: table id 19 flags: STMT_END_F

BINLOG '
anFJShNuJgAAfgAAACwBAAAAABMAAAAAAAAABG9yY2EAC0FMQVJNX0VWRU5UACAIDwcQDw8CAgcH
Dw8Q/PwQDw8QBw8PDw8PDw8PDwICCCr9AgAB/QL9AvAA8AAAAQICAAH9Av0CAAH9Av0C/QL9Av0C
/QL9Av0C/QL6////
anFJShhuJgAAsAEAANwCAAAQABMAAAAAAAEAIP//////////AC/rBwcAAAAAAAAABgAxMjAwMjBf
cUlKAQQAMTkuMB8AaG9zdGlkPWNsdXN0ZXIxLGlwPTE3Mi4zMS4wLjEyNAUA//8AJgBtZW1vcnlf
dXNhZ2U9OTczLzEwMDFNLGZyZWVfbWVtb3J5PTI4TQABAAAmADEyMDAyMDpob3N0aWQ9Y2x1c3Rl
cjEsaXA9MTcyLjMxLjAuMTI0HwBob3N0aWQ9Y2x1c3RlcjEsaXA9MTcyLjMxLjAuMTI0AgABAP//
////////AC/rBwcAAAAAAAAABgAxMjAwMjBqcUlKAQQAMjcuMB8AaG9zdGlkPWNsdXN0ZXIxLGlw
PTE3Mi4zMS4wLjEyNAUA//8AJgBtZW1vcnlfdXNhZ2U9OTczLzEwMDFNLGZyZWVfbWVtb3J5PTI4
TQABAAAmADEyMDAyMDpob3N0aWQ9Y2x1c3RlcjEsaXA9MTcyLjMxLjAuMTI0HwBob3N0aWQ9Y2x1
c3RlcjEsaXA9MTcyLjMxLjAuMTI0AgABAP//////////
'/*!*/;

phphp

看描述像是5.1的一个bug, 把 OPTION_NOT_AUTOCOMMIT 写进binlog了，但没更新相应Log_event，在5.1.32已修复.

确认是的话删除SET /*!*/行就行了如: mysqlbinlog mysql-bin.000001| sed '/^SET \/\*!\*\/;$/d' | mysql -uroot
复现方法：
flush logs;
use test;
drop table if exists demo1;
create table demo1(id int);
insert into demo1 values(1);
set autocommit=0;
insert into demo1 values(2);