SQL statements mysteriously not replicating with MySQL replication

If you’re using MySQL replication there’s a feature/bug that you should be aware of. The following SQL statement would not replicate to the slave servers:

mysql -uuser -ppass
> insert into dbname.tablename values (1, 2, 3, 4);

whereas this one would:

mysql -uuser -ppass dbname
> insert into tablename values (1, 2, 3, 4)”

so we ended up with inconsistent data between the master and slave servers. Turns out this only happens if you don’t specify a default database (either on the command line or via a use dbname statement) AND you have a binlog-ignore-db statement in your my.cnf such as:

binlog-ignore-db=mysql

The logic for determining whether to replicate an SQL statement can be found here in the MySQL online manual. Personally I lean more on the side of this being a bug than a feature because the logic for determining what gets written to the binlog being based on the default database is flawed if you can override the database name in an SQL statement. In any case we’re going to be removing the binlog-ignore-db from our my.cnf files, however, not everyone can do that as easily if you want to have different permissions in mysql.user between master and slave databases.

This entry was posted in Database, MySQL. Bookmark the permalink.

2 Responses to SQL statements mysteriously not replicating with MySQL replication

  1. sandeep says:

    Thanks a lot ….i really struggle a lot to solve this on my server.

  2. phazzi says:

    Yeah! very nice! it really solved my alot of time !

    thank you man!

Leave a Reply

Your email address will not be published. Required fields are marked *