I have deployed pxc xtradb cluster with proxysql and both have 3 instances each on k8s.
When I try to get into proxysql admin interface through below command it was not accepting me to go in.
alakma pxc-db % kubectl run -i --tty --rm percona-client --image=percona --restart=Never -n pxc
– mysql -h my-db-pxc-db-proxysql.pxc.svc.cluster.local -uproxyadmin -padmin_password
If you don’t see a command prompt, try pressing enter.
ERROR 1045 (28000): ProxySQL Error: Access denied for user ‘proxyadmin’@‘xxx.115.xxx.10’ (using password: YES)
pod “percona-client” deleted
pod pxc/percona-client terminated (Error)
But when I exec into one of my pod
kubectl exec -it my-db-pxc-db-proxysql-0 -n pxc – /bin/bash
mysql -h127.0.0.1 -P6032 -uproxyadmin -padmin_password --prompt='ProxySQLAdmin> ’
It was letting me in and setup configuration for Adding cluster nodes to ProxySQL, mysql_galera_hostgroups, mysql_query_rules. But here when exec into my other pods of proxysql my-db-pxc-db-proxysql-1, my-db-pxc-db-proxysql-2 respectively the configuration that I set in my-db-pxc-db-proxysql-0 was not there.
1) So should I have to configure all three instances of proxysql separately for HA?
2) Or if I make use of this command (
kubectl run -i --tty --rm percona-client --image=percona --restart=Never -n pxc \
-- mysql -h my-db-pxc-db-proxysql.pxc.svc.cluster.local -uproxyadmin -padmin_password) will replicate the configuration in all 3 instances of proxysql?
3) And let us assume that I configured proxysql for HA (based on solutions of above 2) and setup pxcdb for galera replication then I want to deploy this configured package in different cluster and can I expect a out of the box configured whole package in a new cluster? (I know the node Ip address are different in new cluster but can they be taken into config dynamically? is there any solution for that or any documentation of out of the configuration for my whole stack of pxc-operator, pxcxtradb cluster and proxysql)? Is there any post deployment script that could solve this issue?
Your first attempt did not specify the admin port -P6032 but your second attempt did. So that’s probably why it worked. Add the port to your first attempt.
the configuration that I set in my-db-pxc-db-proxysql-0 was not there
It should have been replicated once you LOAD TO RUNTIME. Have a look at this blog post which shows an example of this.
Hi @matthewb,
Thank you so much for your response, sure i will go though it and replicate same stuff which is there in the attached documentation.
And one more thing related to 3rd point, for suppose if i configure proxysql with pxc (basically with all the configuration that i need) in my ops cluster and then is there any way to deploy the pxc stack with the same configuration that i did in ops out of the box in my dev and qa clusters? any documentation or scripts related to it available? and is it a possible thing to workout with some post deployment scripts? because i went through so many blogs but everywhere they configured proxysql with mysql after deployment only through admin interface. I did not find much information regarding this, please suggest me here any help would be much much appreciated.
The below combinations I tired with including the P6032 and namespace in one and not in other:
pxc-db % kubectl run -i --tty --rm percona-client --image=percona --restart=Never -n pxc
– mysql -h my-db-pxc-db-proxysql.pxc.svc.cluster.local -P6032 -uproxyadmin -padmin_password
If you don’t see a command prompt, try pressing enter.
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘my-db-pxc-db-proxysql.pxc.svc.cluster.local’ (110)
pod “percona-client” deleted
pod pxc/percona-client terminated (Error)
pxc-db % kubectl run -i --tty --rm percona-client --image=percona --restart=Never
– mysql -h my-db-pxc-db-proxysql.pxc.svc.cluster.local -P6032 -uproxyadmin -padmin_password
Error from server (AlreadyExists): pods “percona-client” already exists
pxc-db % kubectl run -i --tty --rm percona-client --image=percona --restart=Never -n pxc
– mysql -h my-db-pxc-db-proxysql.pxc.svc.cluster.local -P6032 -uroot -pinsecure-root-password
If you don’t see a command prompt, try pressing enter.
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘my-db-pxc-db-proxysql.pxc.svc.cluster.local’ (110)
pod “percona-client” deleted
pod pxc/percona-client terminated (Error)
pxc-db % kubectl run -i --tty --rm percona-client --image=percona --restart=Never \
– mysql -h my-db-pxc-db-proxysql.pxc.svc.cluster.local -P6032 -uroot -pinsecure-root-password
Error from server (AlreadyExists): pods “percona-client” already exists
It was stating above errors.
And I followed the documentation mentioned but it was not replicating mysql_query_rules that I added I proxysql-0 into proxysql-1 even though I load them to runtime. Is there any parameter that I have to turn on to maintain all the PODs in sync? As per following statement in the documentation “ProxySQL Cluster is enabled in order to maintain all the PODs in sync”.
ProxySQLAdmin> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum,
→ FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at,
→ diff_check, DATETIME(‘NOW’) FROM stats_proxysql_servers_checksums
→ ORDER BY hostname, name;
Empty set (0.00 sec).
found this statement " At the moment this feature is EXPERIMENTAL and subject to change. Think very carefully before installing it in production, in fact I strongly recommend you wait. However, if you would like to start testing this feature, you need to install ProxySQL 1.4.2, or better." at ProxySQL Experimental Feature: Native ProxySQL Clustering - Percona Database Performance Blog that is why did not configure proxysql_servers. should i go ahead do it? please suggest.
The clustering should have been enabled/configured automatically by the K8S operator. I would try an re-deploy your ProxySQL pods and see if the operator correctly configures them. If not, then this is probably some bug in the operator.
Hi @matthewb - i redeployed it with helm chart (pxc-db-1.10.0 and pxc-operator 1.10.0 Releases · percona/percona-helm-charts · GitHub). I could see all tables are empty notably mysql_Servers, proxysql_servers and mysql_galera_hostgroups. and when i try to access mysql from percona client at (kubectl run -i --rm --tty percona-client --image=percona:8.0 --restart=Never – bash -il)
with mysql -h cluster1-proxysql -uroot -proot_password it was returning ERROR 1045 (28000): ProxySQL Error: Access denied for user ‘proxyadmin’@‘x.x.x.x’ (using password: YES)
and logs
{“level”:“info”,“ts”:1642737376.4732475,“caller”:“pxc/controller.go:465”,“msg”:“reconcile replication error”,“err”:“get primary pxc pod: failed to get proxySQL db: Error 1045: ProxySQL Error: Access denied for user ‘proxyadmin’@‘1x.x.x.x’ (using password: YES)”}
{“level”:“error”,“ts”:1642737381.6907582,“caller”:“pxc/controller.go:1139”,“msg”:“sync users”,“error”:“exec syncusers: command terminated with exit code 1 / / ERROR 1045 (28000): ProxySQL Error: Access denied for user ‘proxyadmin’@‘127.0.0.1’ (using password: YES)\nERROR (line:568) : ProxySQL connection check failed. \n– Could not connect to ProxySQL at localhost:6032 \n– Please check the ProxySQL connection parameters and status.\n”,“errorVerbose”:“exec syncusers: command terminated with exit code 1 / / ERROR 1045 (28000): ProxySQL Error: Access denied for user ‘proxyadmin’@‘127.0.0.1’ (using password: YES)\nERROR (line:568) : ProxySQL connection check failed. \n– Could not connect to ProxySQL at localhost:6032 \n– Please check the ProxySQL connection parameters and status.\n\ngithub.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).syncPXCUsersWithProxySQL\n\t/go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/users.go:491\ngithub.com/percona/percona-xtradb-cluster-
operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).resyncPXCUsersWithProxySQL.func1\n\t/go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/controller.go:1137\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1581”,“stacktrace”:“github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc.(*ReconcilePerconaXtraDBCluster).resyncPXCUsersWithProxySQL.func1\n\t/go/src/github.com/percona/percona-xtradb-cluster-operator/pkg/controller/pxc/controller.go:1139”}
{“level”:“info”,“ts”:1642737385.5778792,“caller”:“pxc/controller.go:465”,“msg”:“reconcile replication error”,“err”:“get primary pxc pod: failed to get proxySQL db: Error 1045: ProxySQL Error: Access denied for user ‘proxyadmin’@‘100.11x.x38.xx’ (using password: YES)”}