How do I use the MySQL Proxy Admin plugin?

We have an Admin plugin for the MySQL Proxy, but people started asking how to use it. I only found one example, thanks to Giuseppe, but people wanted more.

While the admin plugin is somehow limited, it already provides some nice features. One of the use cases is to give access to information to only authorized users. The Admin plugin uses its own username and password to authenticate users. This is not related to any user on your MySQL server.

The example.

I went ahead and put two scripts together in about an hour. They are basic, but should give you more of an idea of what you can do.

Number of queries processed.

Connecting to the admin port 4041, you can execute this query

mysql> show querycounter;
+---------------+
| query_counter |
+---------------+
|            15 |
+---------------+
1 row in set (0.00 sec)

And you can see how many queries went through the proxy (does not count the queries to the admin plugin)


Backends list.

You can see some information about the backends. Type 1 means master and type 2 means slave

mysql> SELECT * FROM backends;
+-------------+-----------------+-------+------+
| backend_ndx | address         | state | type |
+-------------+-----------------+-------+------+
|           1 | 127.0.0.1:22547 | 1     | 1    |
|           2 | 127.0.0.1:22548 | 0     | 2    |
|           3 | 127.0.0.1:22549 | 0     | 2    |
+-------------+-----------------+-------+------+
3 rows in set (0.00 sec)

Client connections list

mysql> SHOW PROXY PROCESSLIST;
+------+--------------------+--------------------------+
| Id   | IP Address         | Time                     |
+------+--------------------+--------------------------+
| 249  | 127.0.0.1:53830    | Fri May  1 00:13:28 2009 |
| 248  | 127.0.0.1:53827    | Fri May  1 00:13:27 2009 |
| 247  | 127.0.0.1:53825    | Fri May  1 00:13:26 2009 |
| 250  | 192.168.0.99:53836 | Fri May  1 00:13:38 2009 |
+------+--------------------+--------------------------+
4 rows in set (0.00 sec)

The way it works is basically by collecting data using the proxy plugin, making that information available using proxy.global.* variables, and then the admin plugin reads those global variables and returns the data on a pretty format :)

You can start the proxy with a command like this:

./sbin/mysql-proxy --admin-lua-script=/Users/wizard/etools/mysql-lua-scripts/trunk/scripts/admin-1.lua \
--proxy-backendddresses=127.0.0.1:22547 \
--proxy-read-only-backend-addresses=127.0.0.1:22548 \
--proxy-read-only-backend-addresses=127.0.0.1:22549 \
--proxy-lua-script=/Users/wizard/etools/mysql-lua-scripts/trunk/scripts/reporter.lua \

--plugins=admin

Where are the Lua scripts?

You can find them on the MySQL Forge site here and here.

And remember that the default username and password for the admin plugin is root / secret, which you can change by using

--admin-username=proxy \
--admin-password=guesswhat

Enjoy!