binlog还原遇到的问题

gxg353

1、在端口3307数据库里的 操作：
mysql> show binlog events in 'binlog3307.000006';
+-------------------+-----+-------------+-----------+-------------+------------------------------------------+
| Log_name          | Pos | Event_type  | Server_id | End_log_pos | Info                                    |
+-------------------+-----+-------------+-----------+-------------+------------------------------------------+
| binlog3307.000006 |  4 | Format_desc |        2 |        106 | Server ver: 5.1.26-rc-log, Binlog ver: 4 |
| binlog3307.000006 | 106 | Query      |        2 |        189 | create database fang                    |
| binlog3307.000006 | 189 | Query      |        2 |        279 | use `fang`; create table m (age int(4))  |
| binlog3307.000006 | 279 | Query      |        2 |        367 | use `fang`; insert into m values(555)    |
+-------------------+-----+-------------+-----------+-------------+------------------------------------------+
4 rows in set (0.00 sec)
2、我还原在端口为3308的数据库里：
/opt/mysql3308/bin/mysqlbinlog  /opt/mysql3307/var/binlog3307.000005 | mysql -uroot -p -P3308 -h127.0.0.1
提示错误：
ERROR 1065 (42000) at line 6: Query was empty
3、导出binlog
mysqlbinlog  /opt/mysql3307/var/binlog3307.000005 >1.sql
cat 1.sql
[root@localhost mysql3308]# vi 1.sql
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#081107 18:05:27 server id 2  end_log_pos 106  Start: binlog v 4, server v 5.1.26-rc-log created 081107 18:05:27 at startup
ROLLBACK/*!*/;
BINLOG '
5xIUSQ8CAAAAZgAAAGoAAAAAAAQANS4xLI2LXJjLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADnEhRJEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#081107 18:08:13 server id 2  end_log_pos 195  Query  thread_id=1    exec_time=0    error_code=0
use restore/*!*/;
SET TIMESTAMP=1226052493/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into b values(6)
/*!*/;
# at 195
#081107 18:08:16 server id 2  end_log_pos 284  Query  thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1226052496/*!*/;
insert into b values(7)
/*!*/;
# at 284
#081107 18:08:20 server id 2  end_log_pos 373  Query  thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1226052500/*!*/;
insert into b values(
/*!*/;
# at 373
#081107 18:22:45 server id 2  end_log_pos 417  Rotate to binlog3307.000006  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
"1.sql" 40L, 1619C

另外我用source 也不成：
mysql> source /opt/mysql3308/1.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
Charset changed
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty
ERROR 1065 (42000): Query was empty

yueliangdao0608

记得以前有人问过这个问题。

yueliangdao0608

http://bbs.chinaunix.net/archiver/tid-1165511.html

gxg353

我也看到过这个帖子！可是，没有解决方法呀 ？

zhubowen_636

原帖由 gxg353 于 2008-11-9 12:59 发表
我也看到过这个帖子！可是，没有解决方法呀 ？

很简单你这个问题 只要把路径输全就能解决问题.把路径打全 具体原因不想多说了 以前我也遇到过这个问题

showsa

上次那个帖子是错误的 binlog输入之后是sql语句 再用mysqlbinglog去输入当然那是错误的用法

你这上面的log是基于行级或者混合的复制模式
需要使用参数 --base64-output=DECODE-ROWS

yueliangdao0608

不错。

gxg353

原帖由 showsa 于 2008-11-10 12:27 发表
上次那个帖子是错误的 binlog输入之后是sql语句 再用mysqlbinglog去输入当然那是错误的用法

你这上面的log是基于行级或者混合的复制模式
需要使用参数 --base64-output=DECODE-ROWS

我看mysqlBINGLOG帮助里没有你说的这个参数呀！能不能详细说下呀！非常感谢！我在线等你的回答。。。

gxg353

原帖由 zhubowen_636 于 2008-11-10 11:50 发表

很简单你这个问题 只要把路径输全就能解决问题.把路径打全 具体原因不想多说了 以前我也遇到过这个问题

大哥我的路径不全？我已经写全了呀，请指出那个路径没写全，如何写！谢谢

sinboy2002

原帖由 gxg353 于 2008-11-11 08:51 发表


我看mysqlBINGLOG帮助里没有你说的这个参数呀！能不能详细说下呀！非常感谢！我在线等你的回答。。。

The --base64-output=DECODE-ROWS and --verbose options may be used to affect row event output. These options are available as of MySQL 5.1.28.