Home / server / Questions / 721116
Accepted
merlin
Asked: 2015-09-10 00:44:18 +0800 CST

Why does MySQL Galera not replicate the user table?

  • 772

I am running a 3 node MySQL 5.6 Galera Cluster. Everything is in sync and looks fine, but as I just found out the user table was not in sync.

One particular user was not present on the other nodes where I thought it might replicate it after issuing:

mysql -u root -p -e "INSERT INTO mysql.user (Host,User) values ('10.0.0.10','haproxy_check'); FLUSH PRIVILEGES;"

I double checked wsrep status and user tables on all nodes. After issuing the command on the other 2 nodes it is available and galera seems to be still in sync.

Am I missing something here? Why is galera not replicating the user table?

mysql

2 Answers

  1. Best Answer

    It is in the known limitations:

    Currently replication works only with the InnoDB storage engine. Any writes to tables of other types, including system (mysql.*) tables are not replicated (this limitation excludes DDL statements such as CREATE USER, which implicitly modify the mysql.* tables — those are replicated). There is however experimental support for MyISAM - see the wsrep_replicate_myisam system variable)

    So using proper CREATE USER statement will replicate, your INSERT INTO mysql.user statement will not.

    I can't quite find a suitable reference if GRANT statements are considered DDL statements and will replicate or not.

    • 6

  2. As above MyISAM isn't replicated. GRANT and CREATE USER and all other commands that manipulate system tables are replicated. Use them always and you'll have no troubles because of galera and you won't get stuck when/if an underlying implementation changes.

    You may want to use pt-show-grants from Percona Toolkit to extract your grants from one node and inject them back into the cluster.

    • 1