mysqlbinlog 恢复报错ERROR at line 24826643: Unknown command '\"'


有两个binlog日志文件mysql-bin.000001,mysql-bin.000002,大小都是1G左右。现在要在另外一个库上做完全恢复。想到通常的恢复方法:


 mysqlbinlog /usr/local/var/mysql1/mysql-bin.000001 | mysql -uroot -S /usr/local/var/mysql2/mysql2.sock 


mysqlbinlog /usr/local/var/mysql1/mysql-bin.000002 | mysql -uroot -S /usr/local/var/mysql2/mysql2.sock

在恢复mysql-bin.000001的时候很顺利,但在恢复mysql-bin.000002时,有一个报错:
ERROR at line 24826643: Unknown command '\"'

搜了一圈,大部分定位为binlog日志导出时的字符集与恢复到新库时的字符集不一致。查看了老库的character_set。
clipboard.png

然后再次尝试了几次恢复


 mysqlbinlog --set-charset=latin1 /usr/local/var/mysql1/mysql-bin.000001 | mysql -uroot -S /usr/local/var/mysql2/mysql2.sock --default-character-set=latin1

mysqlbinlog --set-charset=utf8 /usr/local/var/mysql1/mysql-bin.000001 | mysql -uroot -S /usr/local/var/mysql2/mysql2.sock --default-character-set=utf8

mysqlbinlog /usr/local/var/mysql1/mysql-bin.000001 | mysql -uroot -S /usr/local/var/mysql2/mysql2.sock --default-character-set=utf8

mysqlbinlog /usr/local/var/mysql1/mysql-bin.000001 | mysql -uroot -S /usr/local/var/mysql2/mysql2.sock --default-character-set=latin1

又或者像这样先到处文件,再做恢复
mysqlbinlog /usr/local/var/mysql1/mysql-bin.000001 > 1.log
mysql -uroot -S /usr/local/var/mysql2/mysql2.sock --default-character-set=latin1 < 1.log

还是有如题的报错。然后想是不是可以找到报错的position,然后跳过。但发现这报错似乎不是发生在事物中,所以也不太好找position。
由于文件太大,打开文件找到错误行似乎也不太可能。

实在无力,求大神赐教!谢谢!

Linux 数据库 mysql

啦啦啦啦啦啦啦 11 years, 6 months ago

Your Answer