wsrep_sst_auth is no more exist ( Percona Xtradb Cluster 8.x )

Before PXC 8.x , wsrep_sst_auth is the variable which was used to assign the SST ( State Snapshot Transfer ) user and password . From , PXC 8 the variable is deprecated and completely removed as it is causing the security concern because the user and password was saved in the .txt file and it is easily visible for the OS users .

So, if anyone using the variable wsrep_sst_auth on PXC 8 + will experience the below error .

2019-12-12T11:20:08.606323Z 0 [ERROR] [MY-000067] [Server] unknown variable ‘wsrep_sst_auth=root:Jesus@7sakthI’. 2019-12-12T11:20:08.606494Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you’re executing mysql_upgrade to correct the issue. 2019-12-12T11:20:08.608377Z 0 [ERROR] [MY-010119] [Server] Aborting 2019-12-12T11:20:10.612273Z 3 [ERROR] [MY-000000] [Galera] Exception: State wait was interrupted 2019-12-12T11:20:10.612530Z 3 [ERROR] [MY-000000] [Galera] View callback failed. This is unrecoverable, restart required. (FATAL)

Then How the SST was implemented in PXC 8 ?

The complete details has been provided in the Percona blog . From the Percona blog, there are three major user accounts which participating in the SST .

From Percona Blog,

mysql.pxc.internal.session

The mysql.pxc.internal.session user account provides the appropriate security context to create and set up the other PXC accounts. This account has a limited set of privileges, enough needed to create the mysql.pxc.sst.user

.

This account is locked and cannot be used to login (the password field will not allow login).

mysql.pxc.sst.user

The mysql.pxc.sst.user is used by XtraBackup to perform the backup. This account has the full set of privileges needed by XtraBackup.

 This account is created for an SST and is dropped at the end of an SST and also when the PXC node is shutdown. The creation/provisioning of this user account is not written to the binlog and is not replicated to other nodes. However, this account is sent with the backup to the joiner node. So the joiner node also has to drop this user after the SST has finished.

mysql.pxc.sst.role

The mysql.pxc.sst.role is the MySQL role that provides the privileges needed for XtraBackup. This allows for easy addition/removal of privileges needed for an SST.

The experimental release of PXC is based on MySQL 8.0.15, and we have not implemented the role-based support due to issues found with MySQL 8.0.15. This will be revisited in future versions of PXC 8.0.

Okay, Now experimentally ,

With wsrep_sst_auth , the error will occur as I showed above .

2019-12-12T11:20:08.606323Z 0 [ERROR] [MY-000067] [Server] unknown variable 'wsrep_sst_auth=root:Jesus@7sakthI'.
2019-12-12T11:20:08.606494Z 0 [Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue.
2019-12-12T11:20:08.608377Z 0 [ERROR] [MY-010119] [Server] Aborting

Without wsrep_sst_auth,

  • Internally, it will create the user mysql.pxc.sst.user for SST .
  • The user will not be written in the binary logs .
  • The user will be drop at the end of the SST process .
from file innobackup.backup.log,

xtrabackup: recognized client arguments: --user=mysql.pxc.sst.user --password=* --socket=/8.0/mysql.sock --lock-ddl=1 --backup=1 --galera-info=1 --stream=xbstream --target-dir=/tmp/pxc_sst_E1qV/donor_xb_cl6N

from MySQL process list ,

from MySQL.user table ,

as I mentioned earlier, the mysql.pxc.sst.user will be dropped once the SST has completed .

At the end of the SST, the below logs will be appear on the joiner node data directory .

ALL GOOD !!

I just experimented and experienced with this issue today . So, I just got curious to write about this . Honestly the Percona blog ( I already shared the link in this blog ) has more details .

Thank you !!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website at WordPress.com
Get started
%d bloggers like this: