CentOs 7.1

MarkP,

Note: since you did not mention in your description anything about Galera, I will assume you are using traditional server and replication, even though you specified 10.0.21-galera as an affected version; if I am wrong and you do actually use Galera, my reply below might be not relevant, since the galera cluster might have special logic to deal with updates to system tables.

Your remark about a password being binary logged in clear text is valid – it's the old MySQL way (up to and including MySQL 5.5); it's changed upstream now, and should be changed in MariaDB as well.

But could you please elaborate on the rest of your complaint, I don't quite understand what you meant there?

"consuming a bin log sequence number on a box that is not expected to produce any" – which box is it, the one where CREATE USER is executed, or the one where it is replicated?
If you do not want something to be binary logged on the server where it is executed, why can't you set SQL_LOG_BIN=0 before running it?
If you do not want anything that was replicated to be written to the binary log, why don't you run the server with log-slave-updates=0?

Obviously, binary logging cannot be forbidden unconditionally for these statements – it would break data consistency for anything more traditional than your specific Master<->Master setup. Or maybe I misunderstood you – what was your suggestion here?

I also don't understand the question about the statement being or not being evaluated. The statement is evaluated, and binlog format does not count here, because it's a DDL statement which is always logged in statement format.

To your points/questions:

1) You are correct, my database is not Galera. I didn't see the non-galera flavor in the list so I went with what seemed closest. Perhaps I just missed it.
2) Replication of admin statements (DDL), specificaly CREATE USER
It has been my experience that such admin statements do not replicate. We have several clusters setup and I don't recall seeing users created on one box get replicated to another, but I could be mistaken
3) Master - Master setup
Our Master - Master setup uses the same default GTID domain id (0) for both servers with different server ids. Only one is supposed to be hot at a time, making the second server a server used primarily for reading/hot spare. We have processes that manipulate that number to effect multi-threaded replication, but lets set that aside for a moment. The "hot" master has a fairly active pace of record creation/modification (~1000/sec) so any activity on the hot spare creating a binlog entry causes replication on both boxes to fail with a binlog sequence out of order error, as the sequence number for that GTID (Server number apparently ignored) will have invariably already have been consumed on the other master. Replication stops, and strict mode must be momentarily turned off to get it going again.

I suppose the issue stems from my surprise that Admin DDL statements like create user were actually hitting the binlog, especially when I have binlog-do-db specified to log only DDL/DML statements to a particular database. I was under the impression that I could run admin DDL, DML, and DDL on non-binlogged schemas without having those statements/rows being written to the binlog and therefore risking binlogging sequence collision. Am I simply misunderstanding how this works?

MarkP,

) Replication of admin statements (DDL), specificaly CREATE USER
It has been my experience that such admin statements do not replicate. We have several clusters setup and I don't recall seeing users created on one box get replicated to another, but I could be mistaken

What kind of clusters are you talking about? If you mean standard MySQL/MariaDB replication, then these statements are replicated as any other DDL. I can't say whether they have always been replicated, but at the very least they were replicated in MySQL 5.1, so there is nothing new in here.
If you mean some other clusters – Galera, other DBMS, anything – it might be not the case for them of course.

suppose the issue stems from my surprise that Admin DDL statements like create user were actually hitting the binlog, especially when I have binlog-do-db specified to log only DDL/DML statements to a particular database. I was under the impression that I could run admin DDL, DML, and DDL on non-binlogged schemas without having those statements/rows being written to the binlog and therefore risking binlogging sequence collision. Am I simply misunderstanding how this works?

It's possible that you are not taking into account the very non-intuitive logic of binlog-do-db option.
In your case, we are only interested in the part of the logic related to statement-based replication – even though your server is configured for row-based, DDL is always binary logged in statement format.
So,

Only those statements are written to the binary log where the default database (that is, the one selected by USE) is db_name.

It means that if you have binlog-do-db=positioning and do the following

USE db1;

CREATE USER foo;

USE positioning;

CREATE USER bar;

then CREATE USER bar will be binary-logged (and replicated), while CREATE USER foo will not.
Does it explain the confusion?

It does, and it was what I was suspecting was happening. It leaves me with really only one remaining question.

Why don't those statements actually get replicated on the slaves? Is the expectation that a create user statement issued on a master should also get executed on a slave? If so I don't believe that is happening.

Not something I'm really interested in chasing down, just curious as to why it would get logged if no slave will ever execute it. (Standard Replication, not Galera)

Did you actually see that they are not replicated, or do you just think they are not?
In general, they are replicated. There are some stipulations though which might apply in your case.

The config you quoted above has both binlog-do-db and replicate-do-db set to 'positioning'. If it's the same on master and slave, CREATE USER events which made it to the binary log shoud be replicated on the slave; but if it's different on the slave, they will not.

The config also has replicate-events-marked-for-skip option, which suggests you might be using the logic. In this config it's set to FILTER_ON_MASTER, which means if you actually set @@skip_replication variable, the events might not make it to the binary log. If it's different or overridden somewhere, it might happen that the events hit the binary log but are not executed on the slave;

Maybe there are some other parts of configuration which did not seem relevant but actually are.

Did you actually see that they are not replicated, or do you just think they are not?

Thus my surprise to see the create user statements appearing in the binlog at all, and the explanation of why they are there.

As far as my replication configuration is concerned, both masters are setup identically, but I think I understand your explanation as to under what circumstances a logged statement might not properly be executed on the slave.

Thinking back to when I was witnessing users not being replicated, it is quite possible, and even likely, that my default database was not set to one of the databases specified for replication, so the create user statement was likely never even written to the binlog.

Thanks for the help.

Re-opening because in the discussion around statements being written into the binlog I forgot the part about passwords being written in a clear text.
It's quite different from the main idea of this report, so I don't want to re-use this JIRA entry, but please feel free to file a separate one.