CODEWITHSUNDEEP
mysqlreplication
kayn
kayn

Reputation: 683

mysql replication works only if I choose database by USE database

I have mysql master-slave replication.

When I use following to update, it doesn't change slave:

UPDATE database.table SET key = 'value' WHERE ID = 1;

But when use following query, slave works correctly and update database:

USE database;
UPDATE table SET key = 'value' WHERE ID = 1;

What is causing problem?

Upvotes: 2

Views: 764

Answers (2)

kayn
kayn

Reputation: 683

You are right. More is in manual (http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html):

Statement-based logging. Only those statements are written to the binary log where the default database (that is, the one selected by USE) is db_name. To specify more than one database, use this option multiple times, once for each database; however, doing so does not cause cross-database statements such as UPDATE some_db.some_table SET foo='bar' to be logged while a different database (or no database) is selected.

Warning To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, the list will be treated as the name of a single database if you supply a comma-separated list.

An example of what does not work as you might expect when using statement-based logging: If the server is started with --binlog-do-db=sales and you issue the following statements, the UPDATE statement is not logged:

USE prices;
UPDATE sales.january SET amount=amount+1000;

Upvotes: 0

Vatev
Vatev

Reputation: 7590

It's probably a MySQL bug with statement replication and replicate-ignore-db/replicate-do-db, which causes it to ignore the wrong statements.

Switching to row-based replication format should resolve this issue (and several others...).

You can also 'fix' it by using a ignore/do-table instead of db in the configudation. configuration

Upvotes: 1

Related Questions