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

Question

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?

Answer 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.