proxysql - lefred/mysqlshell-plugins GitHub Wiki

proxysql

This ProxySQL module is different than other modules, it creates a proxysql object that has itself some extra methods.

NAME
      proxysql - ProxySQL Object

DESCRIPTION
      ProxySQL Object.

For more information check:

methods

create

Create the ProxySQL Object

 JS  proxysql=proxysql.create('radmin@mysql1:6032')
Password: ****
Connected to ProxySQL (2.0.13-107-g91737e08)
{
    "configure": <Function:<lambda>>, 
    "getUsers": <Function:<lambda>>, 
    "hostgroups": <Function:<lambda>>, 
    "hosts": <Function:<lambda>>, 
    "importUsers": <Function:<lambda>>, 
    "setUser": <Function:<lambda>>, 
    "setUserHostgroup": <Function:<lambda>>, 
    "status": <Function:<lambda>>, 
    "version": <Function:<lambda>>
}

proxysql object

methods

configure

This method needs to be called with a user that has privileges to create and drop routines in sys. The user created by Shell when configuring an instance doesn't have such grants.

To add it please use something like GRANT CREATE ROUTINE, ALTER ROUTINE, DROP, CREATE, CREATE VIEW ON sys.* TO clusteradmin;

 JS  proxysql.configure()
ERROR: you need to be connected to a InnoDB Cluster

 JS  proxysql.configure()
There is already a monitor user
ProxySQL is configured to use MySQL InnoDB Cluster which mysql1 is part

getUsers

Prints users in ProxySQL.

Note that users added manually with setUser have their password in clear-text:

 JS  proxysql.getUsers()
+----------+-------------------------------------------+-------------------+
| username | password                                  | default_hostgroup |
+----------+-------------------------------------------+-------------------+
| myapp2   | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 | 2                 |
| myapp    | fred                                      | 3                 |
+----------+-------------------------------------------+-------------------+

hostgroups

Prints the hostsgroups configured in ProxySQL

 JS  proxysql.hostgroups()
ProxySQL Hostgroups:
===================:
PRIMARY   | R/W : 2
SECONDARY | R/O : 3
BACKUP PRIMARY  : 4
OFFLINE         : 1

hosts

Prints the hosts in ProxySQL

 JS  proxysql.hosts()
{'host': 'mysql1', 'port': '3306'}
{'host': 'mysql2', 'port': '3306'}
{'host': 'mysql3', 'port': '3306'}

importUsers

Import users from MySQL to ProxySQL

  • hostgroup: the hosgroup for the user
  • search_pattern: user search pattern
 JS  proxysql.importUsers()
Importing users from MySQL requires <hostgroup> and <user search pattern> as mandatory parameters

 JS  proxysql.importUsers(2, 'myapp')
No user found! Only users using mysql_native_password can be loaded.
 MySQL   mysql1:33060+   2020-08-01 10:53:24  
 JS  proxysql.importUsers(2, 'myapp2')
myapp2 added or updated

The user myapp exists on MySQL but uses a secure authentication plugin. Such user must be entered manually (see setUser).

mysql> select User,plugin from mysql.user where user like 'myapp%';
+--------+-----------------------+
| User   | plugin                |
+--------+-----------------------+
| myapp  | caching_sha2_password |
| myapp2 | mysql_native_password |
+--------+-----------------------+
2 rows in set (0.00 sec)

No need to change the authentication plugin in MySQL, but the password cannot be automatically imported in ProxySQL.

setUser

Create a user in ProxySQL and assigned it to a hostgroup.

 JS  proxysql.setUser()
Adding a user requires <hostgroup> and <user> as mandatory parameters
 MySQL   mysql1:33060+   2020-08-01 10:58:39  

 JS  proxysql.setUser(2,'myapp')
Password: ****

setUserHostgroup

Change the hostgroup for a user.

 JS  proxysql.setUserHostgroup(3,'myapp')
User [myapp] changed from hostgroup [2] to [3]

status

Prints a connection pool status for ProxySQL usage

 JS  proxysql.status()
ProxySQL Connection Pool Status
===============================

Hostgroups:
-----------
1 : OFFLINE     2 : PRIMARY    3 : SECONDARY
+-------------+----+--------+------+----------+----------+--------+---------+-------------+---------+-----------+-----------+-------------+
| HostGrpRole | HG | Host   | Port | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | BytesSent | BytesRecv | Latency(us) |
+-------------+----+--------+------+----------+----------+--------+---------+-------------+---------+-----------+-----------+-------------+
| PRIMARY     | 2  | mysql1 | 3306 | 0        | 1        | 1      | 0       | 1           | 1       | 14        | 23        | 151         |
| SECONDARY   | 3  | mysql2 | 3306 | 0        | 0        | 0      | 8       | 1           | 0       | 0         | 0         | 880         |
| SECONDARY   | 3  | mysql3 | 3306 | 0        | 0        | 0      | 14      | 1           | 0       | 0         | 0         | 764         |
+-------------+----+--------+------+----------+----------+--------+---------+-------------+---------+-----------+-----------+-------------+
Commands:
---------
+---------+---------------+-----------+
| Command | Total_Time_us | Total_cnt |
+---------+---------------+-----------+
| SELECT  | 0             | 3         |
| SHOW    | 361673        | 2         |
+---------+---------------+-----------+

version

Prints the ProxySQL Version

 JS  proxysql.version()
Connected to ProxySQL (2.0.13-107-g91737e08)
⚠️ **GitHub.com Fallback** ⚠️