Home / server / Questions / 340089
In Process
Aha
Asked: 2011-12-13 05:09:30 +0800 CST

Replication issue with mysql 5.5.8 while restoring the dump on master server.

  • 772

While restoring the Mysql dump file it throws the following error.

ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging.
Query OK, 0 rows affected (0.00 sec)

The replication configuration is as below.

my.cnf

Replication Configuration:

       server-id    = 33
       max_binlog_size=40M
       log_bin=/MySql_5.5.8/log/my-bin
       log-bin-index=/MySql_5.5.8/log/log/mysql_rpllg/my-bin.index
mysql5.5

3 Answers

  1. You most likely need to set it up for mixed format so the server can decide.

    binlog-format=MIXED

    Statement format does not always work, especially if a statement contains variable such as CURRENT_TIMESTAMP. You can read about this here: https://mariadb.com/kb/en/mariadb/mariadb-documentation/log-files/binary-log/binary-log-formats/ - Clark Vera

    • 1

  2. First, you gotta understand what each format gives, so you can choose which one fits you better.

    Row-based: Records events that affect individual table rows.

    Statement-based: Records all SQL statements that make changes to data or structure of a table.

    Mixed will bring both worlds together, but MariaDB or MySQL will prefer STATEMENT over ROW.

    @Clark Vera answer's have the documentation for more details.

    Row-based is for structures that have few queries that need to be logged and replicated, so each change in every row can be logged and replicated.

    Statement-based log, in other hand, brings you a more concise log and a more efficient replication of high-volume Database, but may lose some data that wasn't considered important or didn't change the structure.

    After that, you gotta keep both of your replication servers with the same log method, and you'll be good to go.

    I recommend you MIXED log, as @Clark Vera said, or you can just set it as ROW in your session, and when you restart your bd, it'll be STATEMENT-based log automatically.

    • 0

  3. Try setting this in my.cnf then re-trying

    binlog-format=STATEMENT
    • -1