Bug #16486 Prevent GRANT and REVOKE statements from being replicated
Submitted: 13 Jan 2006 16:10 Modified: 2 Feb 2006 9:44
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: Documentation Severity:S4 (Feature request)
Version:4.1, 5.0 OS:Windows (windows, linux)
Assigned to: Stefan Hinz CPU Architecture:Any

[13 Jan 2006 16:10] Shane Bester
There exist workarounds to prevent GRANT/REVOKE from being replicated, but these rely on the user remembering to implement them.  

Some include disabling the binary log for the current connection, disabling grant tables on the slaves, and setting a current database.

However, it would be nice if there was some parameter to mysqld on the master to prevent writing GRANT/REVOKE to the binlogs.  Some setting such as "binlog-skip-grants" would be convenient.

How to repeat:
try globally disabling GRANT/REVOKE statements from appearing in the binlogs.

Suggested fix:
feature request
[28 Jan 2006 19:53] Guilhem Bichot
Hi Shane,
in fact there is a direct way to ask the slave to not replicate GRANT, REVOKE and SET PASSWORD:
--replicate-wild-ignore-table=mysql.% .
It is in the changelog of 4.0.13
" If the slave is configured (using for example --replicate-wild-ignore-table=mysql.%) to exclude mysql.user, mysql.host, mysql.db, mysql.tables_priv and mysql.columns_priv from replication, then GRANT and REVOKE are not replicated."
It was WL#846, a customer's request.
But unfortunately I apparently never wrote it into the "Replication" or "GRANT" section.
I have just verified that CREATE USER and DROP USER behave the same with regard to --replicate-wild-ignore-table=mysql.%.
So the task is for the documentation team: could you please write the info in a visible way, about GRANT, REVOKE, CREATE USER, DROP USER, SET PASSWORD? Thank you!
Shane: however, if what you want is really exclude the GRANT/REVOKE from the *binlog* (not from the slave's execution) then there is no direct way and then it's indeed a feature request.
[28 Jan 2006 20:00] Paul DuBois
Changing status to Verified and setting category to Documentation.
The "Documenting" status is only for fixed bugs that are ready
to be entered into the changelog.
[29 Jan 2006 19:26] Andrei Elkin
Just a remark. When I was reading the report
I thought that the meaning of this bug is actually the feature request spelled
in this quote

` it would be nice if there was some parameter to mysqld on the master 
prevent writing GRANT/REVOKE to the binlogs '

And currently such feature does not exist.
[2 Feb 2006 9:44] Stefan Hinz
We've put the suggested workaround in the Replication Features and Known Problems section. Should show up in the next couple of hours.