Mysql – slave configuration that stops replication of thesql.thesql and grants

mariadbMySQL

I have a mariadb slave that cannot have the same permissions and users as the the mysql master. Is there a way to configure the slave so that it does not replicate changes to the mysql system tables and does not try to run GRANT statements?

I am currently using replicate_wild_do_table=mydb.%, and I tried using replicate_wild_ignore_table=mysql.% but that did not seem to ignore the GRANT statements.

I noticed replicate_wild_do_table=mydb.% seems to be sufficient on a mysql slave to not replicate changes to the mysql system table, but it is not sufficient on a mariadb slave.

Best Answer

Use binlog-ignore-db instead. This post explains the behaviour and answers your question. If not, comment.

Related Solutions

MySQL master/slave replication… structure not being replicated

Using replicate-wild-do-table=% is not the correct way to get complete replication. That would seem sensible, but here's why it isn't:

The replicate-* options are restrictive by their presence. The *-do-* options seem to be telling the server what to "do," but in fact they are telling the server what to only do.
The complete absence of any replicate-* configuration variables means "replicate everything."

In the simplest case, when there are no --replicate-* options, the slave executes all statements that it receives from the master. Otherwise, the result depends on the particular options given. -- http://dev.mysql.com/doc/refman/5.5/en/replication-rules.html

That, I think, is the point you are needing. It holds true for all MySQL 5.x.

Once you enable binary logging, set the server-id values on each machine, synchronize the data sets, CHANGE MASTER TO ..., and then START SLAVE, then you should have a working configuration where all DML and DDL will be replicated and your servers will be identical replicas of each other.

If you started out with identical data sets, everything should behave exactly as you would expect it to.

What MySQL replication does best and simplest is replicate entire data sets among servers without any restrictions. Trying to restrict replication to a subset of the data is a process that should carry a warning label that ends with the phrase "...unless you really know what you're doing."

When you use the --replicate-* options, the document cited above also offers this tip:

it is recommended that you avoid mixing “do” and “ignore” options, or wildcard and nonwildcard options

You are using a mix of these, which adds complexity and may explain why your DDL didn't replicate as you expected it to.

Mysql – Master/Slave MySQL error in replication

The slave status you posted says

Error 'Operation CREATE USER failed for 'fetchers'@'localhost'' on query. Default database: ''. Query: 'CREATE USER 'fetchers'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*A516E88562C2C5E0D1EA9875F2910B36584C217A''

SUGGESTION #1

Some have suggested running FLUSH PRIVILEGES. So, in your case, that would be

STOP SLAVE;
FLUSH PRIVILEGES;
START SLAVE;

If the error still comes back on the Slave, you will have to run

STOP SLAVE;
DROP USER 'fetchers'@'localhost'
START SLAVE;

on the Slave.

MySQL 5.7 may have generated a warning on the Master and passed it on the Slave. MySQL 5.7 is becoming a little more strict in its GRANT operations. In the future, please try creating the user first and then use ALTER USER to set the password.

SUGGESTION #2

The user 'fetchers'@'localhost' must already exist on the Slave

If MySQL 5.7 is on Master and Slave, you should run

CREATE USER IF NOT EXISTS 'fetchers'@'localhost'

This will do nothing if 'fetchers'@'localhost' already exists.

Replication would proceed from there.