In the my.cnf for our PXC cluster nodes, we have been using root user for wsrep_sst_auth with all the admin privileges that comes with the root account. We know using the root user:password inside the config file might not be the most responsible and safe thing to do, so we are thinking to change this. So my question is, what are all the necessary permissions required for the wsrep_sst_auth user that would make the PXC work effectively without having any issue whatsoever?
It seems you are using Xtrabackup-v2 method for SST.
So if you are using that, you should set RELOAD, LOCK TABLES, REPLICATION CLIENT and SUPER privileges to work correctly.
If you do not want set directly user pass in my.cnf you cant start mysql server with this command:
service mysql start --wsrep-sst-auth=username:password
These privileges do not work. When you create a table on one of the nodes in the PXC, the table shows in all nodes. When you insert a record on the primary node (where the new user was created), the records are also replicated on all nodes. But if you try to insert a record from any of the secondary nodes in the PXC, the those records will not be replicated to other nodes. Sep 19, 2017 at 7:00
OK but I think your comment doesn't relate to the first question. Your question was about SST method. SST method is for transfer data from one node to joiner node (new node or any node needs to SST) and not relate to insert or delete. Sep 19, 2017 at 7:08
Yes. But we want the PXC to continue operating normally. So i guessing the user would basically need all privileges as root? Sep 19, 2017 at 7:21
Yes sure But I think this problem is not related to wsrep_sst_auth. Could you send output of show status like "wsrep%" ? Sep 19, 2017 at 7:29
I will accept you answer based on the second the alternative answer you gave, which is "service mysql start --wsrep-sst-auth=username:password" since the whole point of my original question was to avoid including root username and password with all its admin privileges in the config file. Sep 19, 2017 at 7:32