Mysql – Incremental MySQL restore using binlogs

backupMySQLmysqlbinlogrestore

I've tried to restore a MySQL database using incremental binlogs and mysqlbinlog tool but I received the following message:

ERROR 1666 (HY000) at line 30: Cannot execute statement: impossible to write to binary log since statement is in row format and BINLOG_FORMAT = STATEMENT.

BINLOG_FORMAT parameter in my database is set to ROW, but is there a way to restore binglog with this parameter in ROW?

Best Answer

SHOW GLOBAL VARIABLES LIKE 'binlog_format';

I believe you will find the server will tell you that it is configured for STATEMENT since there's not another good reason why you'd get this error.

The error message is referring to the server where you're trying to restore the files as currently configured, not the format the server may have been in when the log was created.

MySQL has two logging formats for replication, ROW, which logs the actual changes made to individual rows, and STATEMENT, which logs the queries that made the changes. The MIXED format isn't a different format, but instead is a configuration setting that allows the server to select the format for each query based on internal rules.

When a server has binary logging enabled and executing queries from an incoming binlog, it has to write the queries to its own binlog, but it doesn't just copy the incoming log entry into the outgoing log... if the server is configured for ROW, it will always translate incoming STATEMENT events into ROW events. If it is configured for MIXED, then it may log incoming STATEMENT events as outgoing STATEMENT events or it may log them as ROW... but if it is configured for STATEMENT then it is unable to log incoming ROW events, and the error you're seeing would be what is returned.

 -- server receives  -- server global   -- server writes          
 -- incoming event   -- binlog_format   -- outgoing event

    STATEMENT           STATEMENT          STATEMENT
    STATEMENT           MIXED              STATEMENT or ROW
    STATEMENT           ROW                ROW
    ROW                 STATEMENT          XXX NOT SUPPORTED XXX
    ROW                 MIXED              ROW
    ROW                 ROW                ROW

If SHOW GLOBAL VARIABLES LIKE 'binlog_format'; really does say ROW I'll be surprised, but if not, then your fix should be this:

SET GLOBAL BINLOG_FORMAT = 'ROW';

Related Solutions

Mysql – the safest way to switch the binlog format at runtime

Since a binlog will have a specific format at the moment you do this, you may decide not to gamble with the two formats together although MySQL (eh Oracle [still can't roll off my tongue]) built this feature.

To play it totally safe without a mysql restart, try the following:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

This will leave the last binlog in the 'MIXED' format. The penultimiate (next to last) binlog exists merely bring closure the last binlog that was in the previous format.

All existing sessions prior to the first FLUSH LOGS; will start writing in the last binlog once UNLOCK TABLES; is executed.

Give it a Try !!!

CAVEAT

Giving credit where credit is due, my answer is really piggybacking off of @Jonathan's answer. I just close and open binlogs on top of that. He gets a +1 for bringing this out first.

UPDATE 2011-10-12 13:58 EDT

If you do this to an active Master and there are one or more Slaves replicating from that Master, you need to be concerned about the relay logs being in the new format as well. Here is what you can do:

On the Slave, run STOP SLAVE;

On the Master run these:

FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
SET GLOBAL binlog_format = 'MIXED';
FLUSH LOGS;
UNLOCK TABLES;

On the Slave, run START SLAVE;

Running STOP SLAVE; and START SLAVE; rotates the relay logs and causes the new entries to be replicated whichever format it comes. You may want to apply the binlog_format change in the slave as well.

Sql Anywhere 11: Restoring incremental backup failure

Summarizing the information linked to in the post above....

The command you have used is for a live backup but what you want is an incremental backup. The approach you want to use (per this blog) uses somewhat different switches. You want to use the -n -t -x switches, and the -o to specify a log file. So the command ends up something like:

dbbackup -n -t -x -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)" -o "c:\backup\backup_log.txt"

Best Answer

Related Solutions

Mysql – the safest way to switch the binlog format at runtime

Sql Anywhere 11: Restoring incremental backup failure

Related Question