Start Free Trial

asked on

THE PROXYSQL-ADMIN TOOL WITH PROXYSQL V2

hi,

I am following percona xtraDB cluster 8.0.19 installation guide and when I go to this section
THE PROXYSQL-ADMIN TOOL WITH PROXYSQL V2

when will we need proxySQL-admin tools ? I have install proxysQL and version is proxysql-2.0.13-1.x86_64, I can't run proxysql-admin from the command line as it is not found.
proxySQL has been installed and I am not sure why .

is proxysql-admin ONLY for proxysQL HA configuration and for standalone proxysql we don't need that ?

and we need separate command to run yum install proxysql2? which is not proxySQL at all ?

I install this one already: proxysql-2.0.13-1-centos8.x86_64.rpm and I expect it is all there, am I right ?

ASKER

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> '

Open in new window

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';

Open in new window

?
or actually I need this to add a proxySQL users?

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

ASKER

from here:

https://www.digitalocean.com/community/tutorials/how-to-use-proxysql-as-a-load-balancer-for-mysql-on-ubuntu-16-04

Also it seems that I can't root password is right as once proxySQL setup, I only know admin username and password ! never shown me the root password, I tried my linux root password, won't work !

This might have something to with it: --prompt='Admin> '
This instructs the mysql to use the promtp Admin ... so why would the prompt Admin be a surprise?
And no the prompt has NOTHING to do with the way the command works.

If you say --prompt='Marrowyung? '
Then mysql will call your name every time it needs a command...

if CREATE USER does not exist try:
GRANT USAGE ON *.* TO 'proxysql'@'%' IDENTIFIED BY 'ProxySQLPassword' ;

ASKER

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)

Open in new window

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

User generated image

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

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

Open in new window

it shown:

User generated image

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

So the manual you are using doesn't match the version of the software you are using?
Before the comment there is a column check_type
with optional one of the following values: read_only, innodb_read_only, super_read_only, read_only|innodb_read_only or read_only&innodb_read_only
not sure what need to choose though.

ASKER

"So the manual you are using doesn't match the version of the software you are using? "

there is many different version of ProxySQL and the following is from Percona and it also doens't work !
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

both wrong when creating monitoring user account!

e.g., from this: http://blog.muhrizal.com/2017/12/13/Configure-ProxySQL-on-Ubuntu-16-04/

no need to do this:

Add a monitoring user on ProxySQL:

1
2

mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'ProxySQLPassword';
mysql> GRANT USAGE ON *.* TO 'proxysql'@'%';

but only this is enough!

Admin> UPDATE global_variables SET variable_value='proxysql' WHERE variable_name='mysql-monitor_username';
Admin> UPDATE global_variables SET variable_value='ProxySQLPassword' WHERE variable_name='mysql-monitor_password';

Open in new window

and only the offical installation guide from proxySQL: https://proxysql.com/documentation/ProxySQL-Configuration/ is right!

I get all right until that steps I shown in this question!

"with optional one of the following values: read_only, innodb_read_only, super_read_only, read_only|innodb_read_only or read_only&innodb_read_only
not sure what need to choose though. "

yeah! what is different this time is, in this part: MySQL replication hostgroups

when I do :

Admin> SHOW CREATE TABLE mysql_replication_hostgroups\G

Open in new window

the manual's output is:

  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)

Open in new window

which is different from mine! please see my screenshot above,

so I can't run:

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

Open in new window

so I can't go further until I get this right ! what value should i input to

mysql_replication_hostgroups

Open in new window

table?

try:

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

Open in new window

or:

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

Open in new window

ASKER

man, what will be the effect of it? what worry me is , once insert this record the proxySQL doesn't work any more and hard to debug later.

ASKER

and I found it still don't behave as expected, as according the manual:

from manual:

User generated image

and me:

User generated image

ASKER

this one :

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

Open in new window

still fail the UNIQUE constraint check.

and when I tried this one to create users today , I found those user account can't login at all to the proxy .

Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','',1);
Query OK, 1 row affected (0.00 sec)
 
Admin> INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('msandbox','msandbox',1);
Query OK, 1 row affected (0.00 sec)

Open in new window

and when I connect using:

mysql -u msandbox -pmsandbox -h 127.0.0.1 -P6033 -e "SELECT 1"
mysql -u msandbox -pmsandbox -h 127.0.0.1 -P6033 -e "SELECT @@port"

Open in new window

it seems can't find the port to login:

User generated image

I just follow the menu, why so hard!

and also, it seems that I don't even need to assign permission in ProxySQL level but in mySQL level, and we just create the same username and password in mysQL and proxySQL level

If you insert the directory and it fails then delete the entry again. (or are you testing on a production system??? )

ASKER

" (or are you testing on a production system??? ) "
no ! absolutely no !

in my own lab !

"If you insert the directory and it fails then delete the entry again"

what is directory here means?

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

and only this works! and I just run this query only and nothing happened in the way the official manual said.

i ment to type entry and didn;t read back. (while typing the answer i was also on a phone call).

I never tried sqlproxy so i'll leave that for someone else.

ASKER

"

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

Open in new window

in other box I have this works........

and now this ticket can be close as what is left is query rerouting rules !

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial