when work with latest edition of proxySQL

MySQL & MariaDB Percona XtraDB Cluster 8.x
1

hi,

I follow the Percona xtraDB cluster installation menu to setup ProxySQL , but when I connect from workbench using an account to login to the proxySQL and I expect  I can select the backend MySQL data.  It is not !
the menu I follow is “LOAD BALANCING WITH PROXYSQL” section in “Percona XtraDB Cluster Documentation, Release 8.0.19-10”.

What I have done is:

1) I have install ProxySQL:
  Percona-XtraDB-Cluster-client-80.xxxx rpm
 proxysql2.xxxx rpm

once installed this commands is ok:
“mysql -u admin -padmin -h 127.0.0.1 -P 6032”
and "mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt=‘Admin> ’ " .
2) I can do mysql@proxysql> SHOW DATABASES;
mysql@proxysql> SHOW TABLES;
3) I can add nodes to the proxysQL:
mysql@proxysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,˓→’192.168.70.61’,3306);mysql@proxysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,˓→’192.168.70.62’,3306);mysql@proxysql> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,˓→’192.168.70.63’,3306);

and I do select * from mysql_Servers, it shown out what I inserted,

4)I create proxysql user and add to ono of the percona db nodes as the proxySQL to DB monitoring account.

mysql@pxc2> CREATE USER
‘proxysql’@‘%’ IDENTIFIED WITH mysql_native_password by ‘<password>’;

mysql@pxc2> GRANT USAGE ON *.* TO 'proxysql'@'%';

5) configure this user on the ProxySQL node:

mysql@proxysql> UPDATE global_variables SET variable_value='proxysql'

WHERE variable_name='mysql-monitor_username';

mysql@proxysql> UPDATE global_variables SET variable_value='<password>'

WHERE variable_name='mysql-monitor_password';

6) 

mysql@proxysql> LOAD MYSQL VARIABLES TO RUNTIME;

mysql@proxysql> SAVE MYSQL VARIABLES TO DISK;

7) however when doing this to ensure that monitoring is enabled,:

mysql@proxysql> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us

˓→DESC LIMIT 6;

it say monitor.mysql_server_connect_log doesn't exists ! why ?

7) to enabled mnnitorings of these nodes, load them at runtime:

mysql@proxysql> LOAD MYSQL SERVERS TO RUNTIME;

8) Creating ProxySQL Monitoring User

mysql@pxc2> CREATE USER 'proxysql'@'%' IDENTIFIED WITH mysql_native_password by '$3Kr

˓→$t';

mysql@pxc2> GRANT USAGE ON *.* TO 'proxysql'@'%';

9) Creating ProxySQL Client User

is this one create in proxySQL level or database level? which user account has to be create on both proxySQL side and DB size? 

is this one user connect to DB back bone via proxySQL ?

so this one must both in proxysql and DB level ? both must use the SAME password?

Is this one the application account application use to connect to DB via proxy ?


question:

1) once I connect using client user, it will be timeout message when connect to the proxySQL!

and what is the mapping so that proxySQL know which account used to connect to backed DB ? it is not the monitor account which we have to create in dB side, right?

2) what is missing in the above steps ?



2

you must have on ProxySQL all the same User like in DB with the same Passwords

3

wait, this mean in proxySQL I have to create the same user in Percona DB as well and their passwrod the same ?


4

Yes!But you dont neet to use the same Password in plain text, you kann take the hash from user table.

5

“But you dont neet to use the same Password in plain text,”

both password save in the database right? I am not sure what you mean plain text  ! as long as the same user with same password, user connected to the proxySQL will AUTOMATICALLY route to the backend percona cluster node?

how about the user account permission of that user account? must be the same again ?

6
DBA100 said: "But you dont neet to use the same Password in plain text,"

both password save in the database right? I am not sure what you mean plain text  ! as long as the same user with same
password, user connected to the proxySQL will AUTOMATICALLY route to the backend percona cluster node?

how about the user account permission of that user account? must be the same again ?

for example:
plain text password:  geheimhash in user table: *EC575F87BA2EC345EAE587C23C33B0353D4DBA59You are free to use in ProxySQL one of them.I recommend using the hash, then you can simplify the synchronization of the users between the DB and the ProxySQL with a script.
Yes, ProxySQL will automatically route to the DB.

7

please read this:http://blog.muhrizal.com/2017/12/13/Configure-ProxySQL-on-Ubuntu-16-04/
https://www.digitalocean.com/community/tutorials/how-to-use-proxysql-as-a-load-balancer-for-mysql-on-ubuntu-16-04



8

let me check and come back later if I need.

9

when I follow this to setup ProxySQL V2:

http://blog.muhrizal.com/2017/12/13/Configure-ProxySQL-on-Ubuntu-16-04/ 

in this steps:

Add a monitoring user on ProxySQL:

1<br>2
``` mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'ProxySQLPassword';
mysql> GRANT USAGE ON *.* TO 'proxysql'@'%'; ```

it seems proxysQL admin console do not allow me to do this and it say:

ERROR 1045 (28000): ProxySQL Admin Error: near "USER": syntax error.

when you login to the ProxySQL admin console by this:

``` mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' ```
the prompt is always:
Admin>

instead of

MySQL>

is it a mistake ? that's why I can't use this command to create a user in ProxySQL;

``` CREATE USER 'proxysql'@'%' IDENTIFIED BY 'ProxySQLPassword'; ``` ?
or actually I need this to add a proxySQL users?

INSERT INTO mysql_users (username,password) VALUES (' <name> ',' <password');
?

10

I checked and it seems that manual is wrong and we should follow the offical proxySQL guide:

https://proxysql.com/documentation/ProxySQL-Configuration/ 

and I found sth wong in other page, it said:

Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,‘127.0.0.1’,21891);
Query OK, 1 row affected (0.01 sec)
 
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,‘127.0.0.1’,21892);
Query OK, 1 row affected (0.01 sec)
 
Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,‘127.0.0.1’,21893);
Query OK, 1 row affected (0.00 sec)
 

but in section;

Admin> SHOW CREATE TABLE mysql_replication_hostgroups\G

&nbsp; &nbsp; &nbsp; &nbsp;table: mysql_replication_hostgroups<br>Create&nbsp;Table:&nbsp;CREATE&nbsp;TABLE&nbsp;mysql_replication_hostgroups&nbsp;(<br>writer_hostgroup&nbsp;INT&nbsp;CHECK&nbsp;(writer_hostgroup&gt;=0)&nbsp;NOT&nbsp;NULL&nbsp;PRIMARY&nbsp;KEY,<br>reader_hostgroup&nbsp;INT&nbsp;NOT&nbsp;NULL&nbsp;CHECK&nbsp;(reader_hostgroup&lt;&gt;writer_hostgroup&nbsp;AND&nbsp;reader_hostgroup&gt;0),<br>comment&nbsp;VARCHAR,<br>UNIQUE&nbsp;(reader_hostgroup))<br>1&nbsp;row&nbsp;in&nbsp;set&nbsp;(0.00&nbsp;sec)<br>&nbsp;<br>Admin&gt;&nbsp;INSERT&nbsp;INTO&nbsp;mysql_replication_hostgroups&nbsp;VALUES&nbsp;(1,2,'cluster1');<br>Query OK, 1 row affected (0.00 sec)


but on my end the SHOW CREATE TABLE mysql_replication_hostgroups\G  shows:


check my output (the schema) is not the same so when I do:

INSERT INTO mysql_replication_hostgroups VALUES (1,2,'cluster1');


it shown:

 as the schema is not the same! what should I input?