Description:
When specified "replicate_wild_ignore_table=mysql.%" into replica's my.cnf, it filters out any CREATE USER and GRANT and other Privilege-management statements.
The other hand, MySQL Reference manual describes
> Table-level replication filters are only applied to tables that are explicitly mentioned and operated on in the query. They do not apply to tables that are implicitly updated by the query. For example, a GRANT statement, which updates the mysql.user system table but does not mention that table, is not affected by a filter that specifies mysql.% as the wildcard pattern.
https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#option_mysqld_rep...
and
> However, statements that would normally update this information indirectly—such GRANT, REVOKE, and statements manipulating triggers, stored routines, and views—are replicated to replicas using statement-based replication.
https://dev.mysql.com/doc/refman/8.0/en/replication-features-mysqldb.html
Because of these description, CREATE USER and GRANT have to be replicated.
---
But I seem this "filter-out" mechanism is expected behavior, in this comment in the source code for many years ago.
> GRANT and REVOKE are applied the slave in/exclusion rules as they are
> some kind of updates to the mysql.% tables.
https://github.com/mysql/mysql-server/blob/mysql-8.0.36/sql/auth/sql_user_table.cc#L1975-L...
Hence I think this is bug of Documentation.
How to repeat:
### Startup replication-source
mysqlsh -- dba deploySandboxInstance 3306 --password=testpass
### Startup replica without any filters
mysqlsh -- dba deploySandboxInstance 3307 --password=testpass
### Startup replica with replicate_wild_ignore_table=mysql.%
mysqlsh -- dba deploySandboxInstance 3308 --password=testpass --mysqldOptions="replicate-wild-ignore-table=mysql.%"
### Create user on replication-source
mysqlsh root:testpass@localhost:3306 --sql
CREATE USER replicate IDENTIFIED BY 'replication';
GRANT REPLICATION SLAVE ON *.* TO replicate;
### Start replicas 3307 and 3308 with same Statements.
mysqlsh root:testpass@localhost:3307 --sql
CHANGE REPLICATION SOURCE TO source_host = '127.0.0.1', source_port = 3306, source_user = 'replicate', source_password = 'replication', source_ssl = 1;
START REPLICA;
---
mysqlsh root:testpass@localhost:3308 --sql
CHANGE REPLICATION SOURCE TO source_host = '127.0.0.1', source_port = 3306, source_user = 'replicate', source_password = 'replication', source_ssl = 1;
START REPLICA;
### There's no CREATE USER on 3308 (with replicate_wild_ignore_table=mysql.% )
for port in 3306 3307 3308 ; do
mysqlsh root:testpass@localhost:$port --sql -e "SHOW GRANTS FOR replicate"
done
Grants for replicate@%
GRANT REPLICATION SLAVE ON *.* TO `replicate`@`%` <--- Of cource, there's replication-source
Grants for replicate@%
GRANT REPLICATION SLAVE ON *.* TO `replicate`@`%` <--- No replication filter, there is.
ERROR: 1141 (42000) at line 1: There is no such grant defined for user 'replicate' on host '%' <--- No user on "replicate_wild_ignore_table=mysql.%"
Suggested fix:
Fix documentation
