2

In Statement Based Replication the Default Database is of paramount importance.

e.g.

replicate-do-db=dbTwo

USE dbOne;
UPDATE dbTwo.tableTwo . . . .

will fail as dbOne has been set as the default database.

Whereas under Row Based Replication it should be fine as it looks at the database that is changed rather than the default.

https://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#option_mysqld_binlog-do-db

However, with Row Based Replication all DDL statements are replicated as Statements.

https://dev.mysql.com/doc/refman/5.5/en/binary-log-setting.html

With the binary log format set to ROW, many changes are written to the binary log using the row-based format. Some changes, however, still use the statement-based format. Examples include all DDL (data definition language) statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE.

So in that case does the default database come into play.

e.g.

replicate-do-db=dbTwo

USE dbOne;
ALTER TABLE dbTwo.tableTwo . . . .

will that fail to execute as dbOne is the default, or will it succeed as it is dbTwo that has been changed?

All the examples I can find refer to UPDATE statements, and are quite clear on what will and wont work.

But I can't find any examples that refer to DDL statements using Row Based Replication.

(n.b. I'm currently using MySQL 5.5 in case that makes a difference - upgrading is not an option any time soon)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.