Creating/dropping MySQL users using “IF NOT EXISTS” when row based replication is in place

All we need is an easy explanation of the problem, so here it is.

I’d like to know the affect of using "IF NOT EXISTS" when creating or dropping users when row based replication is being used to replicate data to a mysql slave.

I know that in the past I have broken replication by creating or dropping users that already existed (or didn’t exist) on the slave, even though row based replication was being used.

It seems that the user table isn’t replicated in a row based fashion even though the database itself is.

Would adding "if not exists" to my create or drop statements prevent me from breaking replication in the future?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You are correct in that row based replication is solely for table changes. CREATE USER etc are replicated as statements.

CREATE USER IF NOT EXISTS does prevent a failure overwiting an existing user and a replication failure if the user exists. It doesn’t guarantee that the new user definition is the same.

MariaDB’s CREATE OR REPLACE USER ensures the user created can always replicatoin and that the user becomes the same.

Another technique that will work for MySQL is DROP USER IF EXISTS followed by CREATE USER.

The more thorough solution for consistency is to be strictly on how replicas are created and don’t update replicas except through replication.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply