1

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

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

1 Answer 1

-1

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).

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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