【讨论中】使用mysqlbinlog恢复数据时报错ERROR 1032 Can't find record

pu2182_cn

各位兄台
      我在使用mysqlbinlog 想测试一下是否可以用binlog正常恢复数据时，遇到一个错误
命令：
mysqlbinlog --no-defaults --start-datetime="2013-07-17 10:30:00" /var/lib/mysql/mysql-bin.000112 |mysql -uroot -ppassword
错误：ERROR 1032 (HY000) at line 324: Can't find record in ‘tablename'

没办法，使用如下命令导出数据：
mysqlbinlog --no-defaults --start-datetime="2013-07-17 10:30:00" /var/lib/mysql/mysql-bin.000112 >recovery.sql

然后看more recovery.sql
324行大致是如下这个样子：

323：#130717 10:39:43 server id 1  end_log_pos 145385        Delete_rows: table id 35 flags: STMT_END_F

324：BINLOG '
7wPmURMBAAAAbAAAACgBAAAAACMAAAAAAAEADG1tc19zZG10djR0aAAPYnVzaW5lc3NfcmVjb3Jk
ABED9g/2/v4DBw8PDw8PDw8PDxwKAB4ACgD+Av4CBAAIAAwAJAAcABQAkAGQAZABAPQB

谁能给提供点帮助，不胜感谢！

pu2182_cn

给大家普及一下，这个问题老外回答了：
应该用bin-log = MIXED，但是这样会跟使用ROW级别的场景冲突，因此我感觉使用Row级别的场景不能使用binlog来恢复数据。

MySQL binlog format dilemma?

up vote
3
down vote
favorite
MySQL version: 5.5.13

If I set binlog format to STATEMENT, I got the following warnings on the Master:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave...

I also read the limitations of replicate stores procedures and functions: http://dev.mysql.com/doc/refman/ ... ograms-logging.html

But if I switch to MIXED, mysqld.log on the Slave shows:

[Warning] Slave SQL: Could not execute Update_rows event on table hdcn.sessions; Can't find record in 'sessions', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.003834, end_log_pos 602692401, Error_code: 1032

[Warning] Slave SQL: Could not execute Delete_rows event on table reportingdb.102_rpt_clickview; Can't find record in '102_rpt_clickview', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.003834, end_log_pos 725203511, Error_code: 1032

Looks like the MIXED binlog format causes the Master doesn't replicate completely to Slave.

I switched back to STATEMENT format. Can I ignore the unsafe warnings?

mysql-replication
share|improve this question
edited Sep 29 '11 at 4:09

asked Sep 29 '11 at 3:47

quanta
26.7k22687
2 Answers activeoldestvotes
up vote
2
down vote
accepted
I assume you did this on one setup without cleaning up the slave and setting it up from scratch.

As the error message for the statement based replication tells you your application used some commands which can't be replicated using statement based replication. An example is a query like

INSERT INTO t (t) VALUES(NOW())
where NOW() will return different values when executed on the master and on the slave.

By doing this you have different data on the master and slave. Which is bad as depending on your slave your clients will read different data and subsequent writes will change other data, so you end up with data which is even more different.

Now you switch over to mixed replication, which might use row-based replication for some statements. With RBR you really need the exact same data as it has a hard time to identify the rows which were changed and update them.

So what should you do? - Configure your master to use mixed logging and then setup the slave using a consistent snapshot.

share|improve this answer
answered Sep 29 '11 at 9:03

johannes
33019
I have a question: what is the safest way to switch replication format at runtime? How do I know if there is any temporary tables exist? – quanta Sep 29 '11 at 9:11
The safest way is to configure the master accordingly, create a consistent dump and create the slaves from that. – johannes Sep 29 '11 at 9:13

up vote
1
down vote
You must eliminate all chance of Inserts, Updates, open temp tables, etc...

on Slave(s):
stop slave;
flush tables with read lock;
on Master:
flush tables with read lock;
set global binlog_format='MIXED';
unlock tables;
on Slave(s):
unlock tables;
start slave;

pu2182_cn

更正一下，是这个配置项
binlog_format=mixed

RogerZhuo

@pu2182_cn
不能说基本row的binlog_format不适合作恢复操作。
出错的原因： 基于row 的binlog event, 此处为Delete_rows event,  你delete的这行，在此时的mysql表中已经不存在了，所以抛出 Can't find record in。
如果你做恢复操作，你此时的数据已经和之前不一样了，严格来说已经是不一致。