Not the answer you need?
Register and ask your own question!

XtraDB replication of mysql.users not working in a 3-node cluster

wwagonerwwagoner EntrantInactive User Role Beginner
We have a 3-node Percona MySql cluster (mysql01, 02 and 03) that is replicating just fine for our various databases for over 2 years. We have just noticed that changes to users in mysql.user (password changes and user deletions) are not being replicated.

Logging in with mysql at the RHEL 6 from Oracle command line gives these versions:



If I run the following commands on mysql01 the password for a user is updated, but if I check the other 2 instances, it has not changed.

select host, user from mysql.user where user='mysql_wfwagoner';
+
+
+
+
| host | user | password
+
+
+
+
| % | mysql_wfwagoner | *TheHashedPasswordBEFORE... |
+
+
+
+

update mysql.user SET PASSWORD=password('[The New Password Here]') where user='mysql_wfwagoner';

flush privileges;

select host, user from mysql.user where user='mysql_wfwagoner';
+
+
+
+
| host | user | password




When I run the select command on mysql02 or 03, I see no changes to password. If I delete a user from mysql.user the deletion is not replicated either.

If I change a field in any other database in the cluster the replication is instantaneous.

My understanding is that Percona XtraDB has supported SET PASSWORD= since version 5.5.28-23.7, so given that we are at version 5.6.20-68.0-56, there must be something we need to do to get this working.

Comments

  • przemekprzemek Percona Support Engineer Percona Staff Role
    PXC supports SET PASSWORD, but you are using here an UPDATE statement against MyISAM table, which by default is not replicated in Galera. Use GRANT, SET PASSWORD FOR... etc, which are fully replicated.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.