Chaining Proxies

If you need to combine two scripts with MySQL Proxy you have three choices.

• You can manually rewrite the two scripts (good luck!)
• you can use the load-multi module to load scripts on demand;
• or you can use the proxy chaining technique.

To chain two proxies, you need to start one Proxy with the first Lua script, pointing at the real backend, listening to a non-standard port. Then you start the second Proxy with the second Lua script, having the first proxy as a backend, and listening to the standard proxy port.
It's a difficult and error prone procedure. You would forget about it, unless there were an easy workaround. And indeed you can have such a workaround. Just use the chain_proxy script from MySQL Forge and use it with this simple syntax:

$ chain_proxy first_script.lua second_script.lua

When I made this script, I had the problem of handling the standard output for each proxy instance. The brute force solution is to start each instance in a separate terminal window, but that is less than optimal. It would be almost as much work as doing things manually.
Considering that usually I need the output of a Proxy session only for debugging and that I am a frequent user of GNU screen, I made this arrangement:
The script starts each proxy inside a separate screen. At the end, it gives the list of screens being used, and creates a customized script in /tmp/ to kill the chained proxies and remove the screens.

$ chain_proxy digits.lua loop.lua
proxy started on screen "second_proxy" using script "loop.lua" - pid_file : /tmp/proxy1.pid
proxy started on screen "first_proxy" using script "digits.lua" - pid_file : /tmp/proxy_chain1.pid
stop script is /tmp/proxy_chain_stop
There are screens on:
 21257.first_proxy (Detached)
 21258.second_proxy (Detached)
2 Sockets in /tmp/uscreens/S-gmax.

In this example, the two proxies are started in sequence, and the script gives information on what is going on. The output of "loop.lua" is in the "first_proxy" screen. To see it, I only need to do

$ screen -r first_proxy

When I am finished using the chained Proxy, I type

$ /tmp/proxy_chain_stop
There are screens on:
 21257.first_proxy (Detached)
 21258.second_proxy (Detached)
2 Sockets in /tmp/uscreens/S-gmax.

No Sockets found in /tmp/uscreens/S-gmax.

This script can also be implemented by redirecting the output of each proxy to a different file. YMMV. I like the screen solution better.

I will be speaking about this feature (and more) during my MySQL University session on advanced Lua scripting.