Not the answer you need?
Register and ask your own question!

when work with latest edition of proxySQL

DBA100DBA100 Current User Role Patron
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 [email protected]> SHOW DATABASES;
[email protected]> SHOW TABLES;
3) I can add nodes to the proxysQL:
[email protected]> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,
˓→'192.168.70.61',3306);
[email protected]> INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,
˓→'192.168.70.62',3306);
[email protected]> 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.

[email protected]> CREATE USER 'proxysql'@'%' IDENTIFIED WITH mysql_native_password by '<password>';

[email protected]> GRANT USAGE ON *.* TO 'proxysql'@'%';

5) configure this user on the ProxySQL node:

[email protected]> UPDATE global_variables SET variable_value='proxysql'

WHERE variable_name='mysql-monitor_username';

[email protected]> UPDATE global_variables SET variable_value='<password>'

WHERE variable_name='mysql-monitor_password';

6) 

[email protected]> LOAD MYSQL VARIABLES TO RUNTIME;

[email protected]> SAVE MYSQL VARIABLES TO DISK;

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

[email protected]> 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:

[email protected]> LOAD MYSQL SERVERS TO RUNTIME;

8) Creating ProxySQL Monitoring User

[email protected]> CREATE USER 'proxysql'@'%' IDENTIFIED WITH mysql_native_password by '$3Kr

˓→$t';

[email protected]> 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 ?



Answers

  • YodaYoda Current User Role Supporter
    you must have on ProxySQL all the same User like in DB with the same Passwords
  • DBA100DBA100 Current User Role Patron
    wait, this mean in proxySQL I have to create the same user in Percona DB as well and their passwrod the same ?


  • YodaYoda Current User Role Supporter
    Yes!
    But you dont neet to use the same Password in plain text, you kann take the hash from user table.
  • DBA100DBA100 Current User Role Patron
    "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 ?

  • YodaYoda Current User Role Supporter
    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:  geheim
    hash in user table: *EC575F87BA2EC345EAE587C23C33B0353D4DBA59
    You 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.

  • DBA100DBA100 Current User Role Patron
    let me check and come back later if I need.
  • DBA100DBA100 Current User Role Patron
    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
    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');
    ?

  • DBA100DBA100 Current User Role Patron
    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

           table: mysql_replication_hostgroups
    Create Table: CREATE TABLE mysql_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),
    comment VARCHAR,
    UNIQUE (reader_hostgroup))
    1 row in set (0.00 sec)
     
    Admin> INSERT INTO mysql_replication_hostgroups VALUES (1,2,'cluster1');
    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?
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.