RESTAPI endpoint

Restapi endpoint feature allows users to create new RESTAPI endpoints and execute scripts on behalf of ProxySQL. It is disabled by default.

At the moment there are two examples of scripts that are used for querying memory metrics and exporting users from MySQL database to ProxySQL.

RESTAPI configuration

The RESTAPI is configured with two variables: admin-restapi_enabled and admin-restapi_port. To enable the RESTAPI you need to set the admin variable:

ProxySQL Admin> select * from global_variables where variable_name like '%rest%';
+-----------------------+----------------+
| variable_name         | variable_value |
+-----------------------+----------------+
| admin-restapi_enabled | false          |
| admin-restapi_port    | 6070           |
+-----------------------+----------------+
2 rows in set (0.00 sec)

ProxySQL Admin> set admin-restapi_enabled='true';
Query OK, 1 row affected (0.01 sec)

ProxySQL Admin> select * from global_variables where variable_name like '%rest%';
+-----------------------+----------------+
| variable_name         | variable_value |
+-----------------------+----------------+
| admin-restapi_enabled | true           |
| admin-restapi_port    | 6070           |
+-----------------------+----------------+
2 rows in set (0.00 sec)

ProxySQL Admin> load admin variables to runtime;
Query OK, 0 rows affected (0.00 sec)

ProxySQL Admin> load admin variables to runtime;
Query OK, 35 rows affected (0.02 sec)

ProxySQL Admin> select * from runtime_global_variables where variable_name like '%rest%';
+-----------------------+----------------+
| variable_name         | variable_value |
+-----------------------+----------------+
| admin-restapi_enabled | true           |
| admin-restapi_port    | 6070           |
+-----------------------+----------------+
2 rows in set (0.01 sec)

Now the RESTAPI will be enabled and listening on port 6070.

Installing dependencies

The python package mysqlclient must be installed in order for the export_users package to work. This can be accomplished by the pip command:

$ pip install mysqlclient

or, if ProxySQL is running under sudo:

$ sudo pip install mysqlclient

Creating a new endpoint

In order to create a new RESTAPI endpoint, it must be defined in the restapi_routes table.

MySQL [(none)]> select * from restapi_routes;
Empty set (0.00 sec)

ProxySQL Admin> insert into restapi_routes (active, timeout_ms, method, uri, script, comment) values (1,1000,'POST','export_users','./scripts/export_users.py','comm');
Query OK, 1 row affected (0.00 sec)

ProxySQL Admin> insert into restapi_routes (active, timeout_ms, method, uri, script, comment) values (1,1000,'POST','metrics','./scripts/metrics.py','comm');
Query OK, 1 row affected (0.00 sec)

ProxySQL Admin> insert into restapi_routes (active, timeout_ms, method, uri, script, comment) values (1,1000,'GET','metrics','./scripts/metrics.py','comm');
Query OK, 1 row affected (0.00 sec)

ProxySQL Admin> select * from restapi_routes\G
*************************** 1. row ***************************
         id: 1
     active: 1
 timeout_ms: 1000
     method: POST
        uri: export_users
     script: ./scripts/export_users.py
    comment: comm
*************************** 2. row ***************************
         id: 2
     active: 1
 timeout_ms: 1000
     method: POST
        uri: metrics
     script: ./scripts/metrics.py
    comment: comm
*************************** 3. row ***************************
         id: 3
     active: 1
 timeout_ms: 1000
     method: GET
        uri: metrics
     script: ./scripts/metrics.py
    comment: comm
3 rows in set (0.00 sec)

ProxySQL Admin> load restapi to runtime;
Query OK, 0 rows affected (0.00 sec)

ProxySQL Admin> save restapi to disk;
Query OK, 0 rows affected (0.02 sec)

Note: Prior to ProxySQL v2.1, the timeout_ms column of the restapi_routes table was called interval_ms – if you’re using an earlier version, modify the INSERT queries accordingly.

Calling metrics example using method POST

Parameters to the script are passed using json -d '{"user":"root", "password":"a", "host":"127.0.0.1", "port":"6032”}'

$ curl -X POST -d '{"user":"root", "password":"a", "host":"127.0.0.1", "port":"6032"}' http://127.0.0.1:6070/sync/metrics

{"params":{"user":"root", "password":"a", "host":"127.0.0.1", "port":"6032"}, "result":"Variable_Name\tVariable_Value\nSQLite3_memory_bytes\t3553504\njemalloc_resident\t21569536\njemalloc_active\t14565376\njemalloc_allocated\t10626296\njemalloc_mapped\t84004864\njemalloc_metadata\t5241896\njemalloc_retained\t51785728\nAuth_memory\t4042\nquery_digest_memory\t11832\nmysql_query_rules_memory\t1380\nmysql_firewall_users_table\t0\nmysql_firewall_users_config\t0\nmysql_firewall_rules_table\t0\nmysql_firewall_rules_config\t329\nstack_memory_mysql_threads\t33554432\nstack_memory_admin_threads\t16777216\nstack_memory_cluster_threads\t0n"}

Calling metrics example using method GET

Parameters to the script are passed in the URL

[email protected]:~/workspace/val214_proxysql$ curl -X GET "http://127.0.0.1:6070/sync/metrics?user=root&password=a&host=127.0.0.1&port=6032"

{"params":{"host":"127.0.0.1","port":"6032","user":"root","password":"a"}, "result":"Variable_Name\tVariable_Value\nSQLite3_memory_bytes\t3157344\njemalloc_resident\t16977920\njemalloc_active\t12062720\njemalloc_allocated\t9356864\njemalloc_mapped\t67137536\njemalloc_metadata\t4779336\njemalloc_retained\t43487232\nAuth_memory\t2810\nquery_digest_memory\t0\nmysql_query_rules_memory\t1380\nmysql_firewall_users_table\t0\nmysql_firewall_users_config\t0\nmysql_firewall_rules_table\t0\nmysql_firewall_rules_config\t329\nstack_memory_mysql_threads\t33554432\nstack_memory_admin_threads\t16777216\nstack_memory_cluster_threads\t0n"}

Calling export_user example

The export_users script returns the number of records processed in the json response.

$ curl -X POST -d '{"db":{"user":"root", "password":"a", "port":"3306", "host":"127.0.0.1"},"admin":{"user":"admin","password":"admin","port":"6032","host":"127.0.0.1"}}' http://127.0.0.1:6070/sync/export_users

{"params":{"db":{"user":"root", "password":"a", "port":"3306", "host":"127.0.0.1"},"admin":{"user":"admin","password":"admin","port":"6032","host":"127.0.0.1"}}, "result":{"num_records":"8"}}

Config file

In addition to enabling the restapi variables via the config file, it is possible to configure the restapi endpoints. An example would look like:

restapi:
(
    {
        id=1
        active=1
        interval_ms=1000
        method=POST
        uri="export_users"
        script="./scripts/export_users.py"
        comment="comment"
    }
)

Note: The id value is required if done through the configuration file