I have a fairly trivial master-master setup. When testing this setup I see that mysql users are binlogged and replicated on slaves. However on each of the masters the binlog-do-db* is for a specific database only:

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000001 |      694 | my_db        |                  |

Why are DDL queries like (CREATE USER) and inserts into mysql.user logged as well? I can't see any mechanisms/configs in the manual which explain this, seemingly, exception.

If it matters, the binlog format is:

mysql> show variables like 'binlog_format';
| Variable_name | Value     |
| binlog_format | STATEMENT |

* = Let's forget about the bad-practice of using binlog-do-db for now

  • No, let's not. The USER info is in the database mysql. – Rick James Mar 10 '16 at 5:48
  • Okay, so why are these mysql-database changes included in the binlog? – hbogert Mar 10 '16 at 12:59

You are replicating only my_db.

There are valid security reasons for having the option to not replicate mysql.

The quick solution is to add binlog_do_db=mysql and restart. However, any GRANTs and CREATE USERs have already been lost. Either re-issue them and hope they replicate, or replay them on the Slave(s).

  • But that's the thing.. It is replicating the mysql-db. See my second paragraph – hbogert Mar 11 '16 at 7:04
  • Sorry, I misread. What version are you running? (I wonder if this has changed over the years.) – Rick James Mar 11 '16 at 19:31

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.