Changed sstuser password and now nodes will not come up and sync

1

While trying to bring up a failed node after an sstuser password change, I get:

Failed to connect to MySQL server: Access denied for user ‘sstuser’@‘localhost’ (using password: YES). on the donner node.

It has been set in the my.cnf file correctly on all nodes. Not sure if there is another config file it is set in that I am missing.

1 Like
2

You didn’t say if you actually changed the password for the user account within MySQL. Did you do that as well?

1 Like
3

Yes it was changed in MySQL on all 3 nodes.

1 Like
4

And did you restart each node?

1 Like
5

We did not but did flush privileges. Why would it require a restart? I can log in locally to the database as the sstuser on each of the active nodes…It is only when xtrabackup kicks off on the donner node that we get the sstuser access denied error…

1 Like
6

Hi @jim
Can you check if the sstuser account is consistent, please run this on each node, they should all return the same hash signature:

pager md5sum
SHOW GRANTS FOR sstuser@localhost;

For 5.7 please also ensure the same permissions are granted:

mysql@pxc1> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO
  'sstuser'@'localhost';
mysql@pxc1> FLUSH PRIVILEGES;
1 Like
7

Node 1:
mysql> SHOW GRANTS FOR sstuser@localhost;
±---------------------------------------------------------------------------------------+
| Grants for sstuser@localhost |
±---------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON . TO ‘sstuser’@‘localhost’ |
±---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Node 2 (donner node):
mysql> SHOW GRANTS FOR sstuser@localhost;
±---------------------------------------------------------------------------------------+
| Grants for sstuser@localhost |
±---------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON . TO ‘sstuser’@‘localhost’ |
±---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

1 Like
8

I recall one time in a training session, I had to restart mysql for the .cnf file to be re-read after changing the password for the sst user. Give it a try.

1 Like
9

As a side note, in PXC8, all of this “SST user management” is handled automatically. If an SST is needed, an account with a random password is created on-the-fly, used once to transfer the SST, then removed.

1 Like
10

What if we just did:

set global variable wsrep_sst_auth=“sstuser:newpass”;

On each of the active nodes?

1 Like
11

You still need to restart the node after changing the password. The config file is not re-read until node restart.

1 Like
12

set global wsrep_sst_auth=“sstuser:newpass”; fixed the problem without a reboot. The my.cnf files are updated, so it will come up with the current password moving forward. Thanks for taking the time to guide me along. I know it was a matter of the xtrabackup command being built and picking up the password from somewhere. Just didn’t realize it was that global variable until you mentioned the reboot to re-read it from the config file.

Again, appreciate the help!

1 Like