REST API

Restapi endpoint

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

At the moment there are two examples of the 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

MySQL [(none)]> 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)

Installing dependencies

The python package mysqlclient must be installed in order 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 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)

MySQL [(none)]> insert into restapi_routes (active, interval_ms, method, uri, script, comment) values (1,1000,'POST','export_users','./scripts/export_users.py','comm');
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into restapi_routes (active, interval_ms, method, uri, script, comment) values (1,1000,'POST','metrics','./scripts/metrics.py','comm');
Query OK, 1 row affected (0.00 sec)

MySQL [(none)]> insert into restapi_routes (active, interval_ms, method, uri, script, comment) values (1,1000,'GET','metrics','./scripts/metrics.py','comm');
Query OK, 1 row affected (0.00 sec)

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

Calling metrics example POST

Parameters to 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\t0\n"}

Calling metrics example using method GET

Parameters to script are passed in the URL

val@s89830:~/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\t0\n"}

Calling export_user example

The export_users script returns 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"}}