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!

A feature, a bug, a new feature and a bug fix

After I wrote about reading a master.info file using the MySQL Proxy, I went ahead and added the missing piece. Creating a master.info file using the MySQL Proxy.

A bug?
As I went back to lib/mysql-proto.c looking for a function that I could duplicate and modify to add the to_masterinfo_string() function, I realized that I missed a few master_ssl_* fields. It turned out that I was not exporting all the fields from the master.info file.

This time, the bug fix was easy enough. After modifying the test case to account for the missing fields, I added a few lines of code and I was ready to add the new feature.

New feature
You can now use the function to_masterinfo_string(), which takes as a parameter a table. You can create one using from_masterinfo_string()

You could read this example to get an idea of how it could be used.

Next?
I'll continue my work on Mock Load(*) and wait for the merge proposal to be accepted.

(*)More about what it does once I finish some legal paperwork here at Sun.

Reading master.info files using the MySQL Proxy

After I wrote about a new feature on MySQL Proxy that helps you read master.info files, I thought that showing an example could come handy.

You can find the complete file on the MySQL Forge and once I have a test case for this script, it will be available on Launchpad.

Explaining the code.
You can see at the top I have

local proto = assert(require("mysql.proto"))

This is important, as it makes the from_masterinfo_string() function available for use.

I also included a function called get_command(), which is a modification of code found here.
It basically does a simple parsing of the statement you send through the proxy and returns two variables.

read_query()
Here, I look for the query "read masterinfo;" and convert it into

SHOW GLOBAL VARIABLES LIKE "datadir"

I do this so that I could get the location of the master.info file. You can just hardcode it, but that's not fun :)

read_query_result()
Here, I read the value for datadir, and use it to load the content of master.info into a lua variable, convert the content into a mysql result set and send it back to the client.

How to use.
You need to connect to a mysql proxy that is in front of a slave mysql server; (and tell the proxy to use this lua script)(*). Then execute

mysql> read masterinfo;

And you would get something like this:

mysql> read masterinfo;
+----------------------+----------------+
| Variable_name        | Value          |
+----------------------+----------------+
| master_user          | slave          |
| master_ssl           | 0              |
| master_port          | 43309          |
| master_log_file      | dv1-bin.000005 |
| master_connect_retry | 60             |
| master_password      | slave          |
| master_log_pos       | 272            |
| master_host          | 192.168.2.1    |
+----------------------+----------------+
8 rows in set (0.00 sec)

And you are done.


(*)You can find more information about the MySQL Proxy on the MySQL Forge site.

MySQL Proxy and master.info (contribution 2 of N)

This time I'll write about a nice featured we now have on the MySQL Proxy.

Parsing master.info
The master.info is a file that the MySQL server creates when you are running a slave server, you can read the manual for more details.
And now you can use the MySQL Proxy to read that file and parse the data from it.

Background
It all started when Jan emailed the proxy discuss mailing with the Changelog from 0.6.1 to 0.7.0. The part that got my attention was:
"...
Core
[skip]
* added parser for master.info files"
... "
As I was working on a Lua script that did some simulation, I asked Jan how that worked, and I found out that it was only implemented on C-land, and only compatible with MySQL server 5.1. That meant you could not write a Lua script and use the parser we had.



Nothing like motivation
So I went ahead and asked how much work it would take to implement this feature and I was pointed to the lib/mysql-proto.c file. As I really wanted to have this implemented, I couldn't resist and made a local branch to work on it.

I first looked at that whole file, trying to figure out how things worked and after reading lib/mysql-proto.c I found out I also needed to look at src/network-mysqld-masterinfo.c. After some more reading, I was ready to write some code.

Thought process
Well, I was ready to modify what was there. The way I usually add code when I'm not very familiar with it is by modifying the current code, little by little, compile, test, fix what I broke and continue. I do this a few times until I have something new working :).

This time I took this function:

static int lua_proto_get_ok_packet (lua_State *L) {
 size_t packet_len;
 const char *packet_str = luaL_checklstring(L, 1, &packet_len);
 network_mysqld_ok_packet_t *ok_packet;
 network_packet packet;
 GString s;
 int err = 0;

 s.str = (char *)packet_str;
 s.len = packet_len;

 packet.data = &s;
 packet.offset = 0;

 ok_packet = network_mysqld_ok_packet_new();

 err = err || network_mysqld_proto_get_ok_packet(&packet, ok_packet);
 if (err) {
  network_mysqld_ok_packet_free(ok_packet);

  luaL_error(L, "%s: network_mysqld_proto_get_ok_packet() failed", G_STRLOC);
  return 0;
 }

 lua_newtable(L);
 LUA_EXPORT_INT(ok_packet, server_status);
 LUA_EXPORT_INT(ok_packet, insert_id);
 LUA_EXPORT_INT(ok_packet, warnings);
 LUA_EXPORT_INT(ok_packet, affected_rows);

 network_mysqld_ok_packet_free(ok_packet);

 return 1;
}

I duplicated it and renamed it "lua_proto_get_masterinfo_string". I chose that function as it had some "LUA_EXPORT_INT" lines which sounded like what I needed.

As you can see form here I started by commenting out some lines and renaming some function calls and other lines of code.

I also added a line to "luaL_reg mysql_protolib" which is where things really get exposed to Lua (I found this out by searching the whole branch for "from_ok_packet" which I found on test/unit/lua/mysql-proto.lua)

I then compiled the new code and wrote a simple Lua script to test this (which was not the best way, but more on that later).
At this point all I wanted to test was that I could call "get_masterinfo()" from Lua, and it worked!

I then removed the lines that I comentd out and after showing Jan where I was, he suggested renaming the function, so I did.

Where I should've started.
We all now that we should first have a test, and then code, well, I wasn't sure what I was doing so I started the other way, but eventualy I got to writing the test for this piece of code.
There was already a lua unit test for the mysql-proto.c file, so I added some tests for the master.info parsing.

At that point, it only worked for master.info files from MySQL 5.1, so the next step was to add support for 4.1 and 5.0.

The way I implemnted the suport for 4.1 and 5.0 master.info files took me a few days, I really wanted to return a NULL (nil in Lua) value for "master_ssl_verify_server_cert" if it was not present, but I really did not know how to do it. Either Jan or Kay came to the rescue there and we ended up with this.

Basically, if you don't export it, it will be NULL. And having the unit test made it so much easier to test the changes.

And as I was done with this, I went ahead and proposed a merge and a few days later it was part of the Main Proxy code.

Next?
So far we can parse a master.info file, but we cannot "create" one, so this is next, I want to have a function that would create the master.info file, I know I could just use the io library from Lua, but it will be more fun to add a "to_masterinfo_string()" function on Lua land :)

Thanks and enjoy!

Contributing to MySQL Proxy (1 of N)

Thanks to Kay, it is now much easier to contribute to the MySQL Proxy project. And it turns out you don't have to be a super C developer to help out.
Yes, you may think, you can report bugs, help answering questions, even submit Lua scripts. But there is another way you can contribute.

Blueprints
You can check out the blueprints for the Proxy and pick one that seems "simple" to implement. That's what I did. I went there and picked "Chassis/Proxy should have short names for often used options". It sounded simple enough for me, and I thought it would help me get more familiar with the source code.
After a few emails on the mailing list, I gather all the info I needed and created a branch to work on it.

It turned out not to be much work at all :), so after I pushed my changes to the branch I created on Launchpad, I proposed a merge and now I'm waiting for a code review and hopefully it will be part of the main Proxy branch.


Conclusion
No matter what your skill set is, there are plenty of ways to get involved on this project.