1

I'm thinking of switching from STATEMENT-based to ROW-based MySQL replication, but I'm having a hard time figuring out if ROW-based replication is available on my 5.1.41-3ubuntu12.10-log server/client.

Looking at the documentation here: http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#sysvar_binlog_format

...it looks like ROW isn't an option until 5.1.5. But MIXED is mentioned as an option earlier (and MIXED implies that ROW is available, I'd think). Maybe the doc is only talking about the command line startup option?

In short is ROW-based MySQL replication available to me (5.1.41)?

Darryl Shippy
  • 11
  • Something to consider before you switch : Row-Based Binary Logging causes Binary Log Files to grow faster the Statement-Based Logging variety. In some cases, it can be orders of magnitude. – RolandoMySQLDBA Aug 04 '11 at 18:12

1 Answers1

1

According to http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html :

Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based replication (often abbreviated as SBR), which corresponds to the standard statement-based binary logging format. In MySQL 5.1.4 and earlier, binary logging and replication used this format exclusively.

Row-based binary logging logs changes in individual table rows. When used with MySQL replication, this is known as row-based replication (often abbreviated as RBR). In row-based replication, the master writes events to the binary log that indicate how individual table rows are changed.

As of MySQL 5.1.8, the server can change the binary logging format in real time according to the type of event using mixed-format logging.

When the mixed format is in effect, statement-based logging is used by default, but automatically switches to row-based logging in particular cases as described later. Replication using the mixed format is often referred to as mixed-based replication or mixed-format replication. For more information, see Section 5.2.4.3, “Mixed Binary Logging Format”.

From MySQL 5.1.12 to MySQL 5.1.28, mixed format is the default. Beginning with MySQL 5.1.29, statement-based format is the default.

Based on this excerpt and the fact that MySQL 5.1.30 was the very first GA release of MySQL 5.1, MySQL 5.1.41 has to have row-based binary logging, and thus row-based replication.

RolandoMySQLDBA
  • 16,694
  • Yeah, I'm aware of the log file growth when ROW-based logging is used. I'm OK with that. I'm more interested in stability/reliability here, and haven't found the reliability I'm looking for with STATEMENT-based logging to date. – Darryl Shippy Aug 04 '11 at 18:21