Hello , I’m approaching to manage for the first time a 3 nodes mysql cluster managed with proxysql.
Into mysql database i created a new user. At mysql level the user is correctly replicated among nodes.
Also In mysql are present 4 user root ( root@localhost , root@node1, root@node2, root@node3) correctly replicated in mysql database.
when I try to sync the percona database using “proxysql-admin --config-file=<.cnf file> --syncuser”
, new user is coorectly sync on all nodes but :we have the following situation for root :
on node 1 all users were correctly sync ( no error shown)
on node 2 and 3 i have the message
Cannot add the user (root). The user (root) already exists in ProxySQL database with different hostgroup…
In addition in percona DB on node 1 i saw one user root but in node 2 and 3 no user root are defined .
Delete all root users except root@localhost. Sync. Did it work? Ok. Then add 1 new root user. Sync. Did that work?
Users in mysql are different from users in proxysql. In mysql a “user” is defined as the unique combination of “username”@“hostname”. While in proxysql a user is just plain, simple username. From proxysql’s view, you are trying to create many users with the same name, ‘root’.
Best practice here is to not put you root users on proxysql. Root should only ever access mysql from localhost.
Hi , first of all thanks for reply. I understood what you explained but i can’t understand the difference between node1 and the others. I mean : on node 1 ( where we have the same root users as the other nodes ) the syncuser process work without error , why do we have the error only on node2 and node3?
Why are you syncing the users from node2 and node3? You don’t need to do that with PXC. All users exist on all nodes, so you only need to sync one time to proxysql.