Hi,

Could you please describe your test in more detail?

What is the exact statement that you use to create a user?
What binlog_format does your server use?
How exactly do you pass replicate-ignore-db values?
How do you check whether your statement was replicated?

Thanks

Hello,

– grant all privileges on database.* to user@'192.168.1.1' identified by 'password' require ssl;
– binlog_format = MIXED
– I noticed that replicate-ignore-db can be set while running and it works , but I tried both ways: by configuration and by set global ...
– show grants for user on slave

replicate_ignore_db = multitestrep
replicate_ignore_db = mysql

Hi Cristian,

Account management statements (GRANT, CREATE USER and such) are written into the binary log as they are, not as updates to mysql.user table; so, they are replicated, regardless replicate_ignore_db. Thus, when the value does not work as you expect it to, it is actually the correct behavior.
Now, it would be a miracle if any value of replicate_ignore_db stopped replication of GRANT statements. Could you please provide the exact steps and the checks that you performed to ensure that the statements were not replicated?

Thanks.

Hello,

we have this slave setting: Replicate_Ignore_DB: multitestrep,mysql

but the following behavior is interesting :

on master:
MariaDB [(none)]> grant all privileges on dbname.* to test88888@'192.168.1.25' identified by 'mypass' require ssl;
Query OK, 0 rows affected (0.01 sec)

– replication works and on slave we can see the new user created.

but the following env on master:
MariaDB [multitestrep]> grant all privileges on dbname.* to test88888@'192.168.1.26' identified by 'mypass' require ssl;
Query OK, 0 rows affected (0.00 sec)

– in this case the replication does not work , I cannot find the new user replicated on slave .

I believe that this behavior made me believe that this is a functional workaround ... but it is not .

But, is there a way to prevent mysql database replication if binlog_format=MIXED ? To my knowledge if we go on binlog_format=ROW and Replicate_Ignore_DB=mysql , then it should work . Am I correct ? I am sorry if this is the wrong place to ask .

Thank you .

I believe that this behavior made me believe that this is a functional workaround ... but it is not .

But, is there a way to prevent mysql database replication if binlog_format=MIXED ? To my knowledge if we go on binlog_format=ROW and Replicate_Ignore_DB=mysql , then it should work . Am I correct ? I am sorry if this is the wrong place to ask .

Sorry, I wasn't accurate enough in my previous comment. Besides, the fact that you run your GRANT statements with different default databases changes things.

The way replicate-ignore-db works is very much non-intuitive. Indeed, it differs dramatically for STATEMENT and ROW replication (MIXED is mostly STATEMENT unless it's forced into ROW mode); but in your case, you run GRANT statements, which are always replicated in STATEMENT mode, regardless the binlog format.

So, to understand why you get what you get, you should look at how replicate-ignore-db works for STATEMENT-based replication.
See this explanation for replicate-do-db: http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-do-db (for ignore-db, it's pretty much the same).

In your case, slave decides whether to replicate GRANT statement based on the default schema which was set on master when the statement was run. It does not take into account at all which tables get modified.
The default schema is set by USE <schema name>, or by providing it on the client command line.
With MariaDB command-line client, the schema is shown in the prompt.
In your example above, the first GRANT (192.168.1.25) was run without any default schema at all (none). Thus, nothing prevents replicating the statement.
The second GRANT (192.168.1.26) was run with multitestrep default schema, which is on the ignore list – this is why it was not replicated.

For GRANT statements, it will work the same way with any binlog_format, so switching to ROW is not a solution.
If you can make sure that GRANT statements are only executed lets say with 'mysql' default schema (or any other schema which is on the ignore list), it will be the simplest solution for your problem.

Another way is to explicitly issue SET SQL_LOG_BIN=0 before running GRANT (and SET SQL_LOG_BIN=1 afterwards); but it is not quite the same as skipping replication, it will cause the GRANT not being written into the master binary log at all.

Unfortunately I'm afraid there is no better way to prevent the replication.

As a variant of the second solution, instead of setting SQL_LOG_BIN, you can use the pair{{skip_replication}} (on master) and replicate_events_marked_for_skip (on slave). In this case, you'll need to set the variable on the slave once and forever, while on master you'll have to set skip_replication every time before issuing the state of statements that you don't want to replicate, and revert it afterwards. It should prevent replication much like SQL_LOG_BIN would, the difference is that the statements will still be written to the binary logs, which in some cases can be desirable. See https://mariadb.com/kb/en/selectively-skipping-replication-of-binlog-events/ for more details.

Thank you for you help , I used the second solution with skip_replication ...

Regards.

We use "replicate-wild-ignore-table = mysql.%" and seems to work without problems.

How interesting, you are right.. And I'm not even sure it's supposed to, the documentation is not so clear about it, but it is surely convenient.