LSPE @Yahoo mysql-proxy

1. LSPE @Yahoo mysql-proxy By Farhad Saberi - Overview and architecture - Making The case for a proxy - Lua - Lua examples - The admin interface - currently known issues

2. Overview and Architecture Same idea as any other proxy. Sits between the application and its Database:

3. Overview and Arhitecture Read only Load Balancing with multiple –proxy-read-only-backend-addresses option

4. Making The case for mysql-proxy Easier sites management. Hundreds of connection definitions use the same host and port. DB Host connection changes are done at one place only. No need to restart for configuration or behavioral changes. It all happens in real time.

5. Making the case for mysql-proxy Flexible, Powerful and invisible: intercept, tokenize and modify queries Deny/allow queries based on DB or DB User. Deny/allow based on a regex Construct your own results set and return it to the application without ever actually querying the backend database. LUA embedded

6. Making the case for a proxy It's fast and stable: Small daemon, about 35 MB in VSZ compared to a preforked apache child being 50 to 100 MB. Coded in C implements connection pooling Doesn't need to do NS lookups every time Has never “crashed” on me so far!

7. Making the case for mysql-proxy Open source.

8. LUA Simple scripting language Small: its whole distribution fits in a floppy disk. Extensible; it is easy to interface with C code.

9. LUA Widely used in games:

10. LUA Mysql-proxy is the application code and Lua is its extension language. You change a running mysql-proxy's behavior and variables in a single Lua script in real time (no restart required).

11. Lua examples Mysql-proxy starts with –proxy-backend-addresses=db1.host.net, db2.host.net, db3.host.net Choose your backend in connect_server() Function connect_server() proxy.connection.backend_ndx=2 End And you will always connect to db2.host.net. If you change 2 to 3, you will always connect to db3. Note: Lua's array starts at 1, !0

12. Lua examples Deny all queries to any backend DB: function read_query(packet) if string.byte(packet) == proxy.COM_QUERY then local fields = { } local rows = { } fields = { { name = “message from DBA”, type =proxy.MYSQL_TYPE_STRING}, } rows[1] = { “maintenance”} proxy.respose = { type = proxy.MYSQLD_PACKET_OK, resultset = { fields = fields, rows = rows } } return proxy.PROXY_SEND_RESULT –means bypass backend, return to client now end end

13. Lua examples For any query issued, this will be the result: mysql> show tables; # or anything else +--------------------------+ | message from DBA | +--------------------------+ | maintenance | +---------------------------+

14. Lua example denying access to just one table using a regex. Return mysql error msg to client. Function read_query(packet) if packet:byte() == proxy.COM_QUERY then local query= packet:sub(2) print (“query: “ .. query) – debugging purpose if string.match(query:lower(), “from%s+.*my_table”) then proxy.response.type = proxy.MYSQLD_PACKET_ERR proxy.response.errmsg = “access denied to table my_table” return proxy.PROXY_SEND_RESULT end end end

15. Lua example Running any query that would match “from%s+.* my_table” will return ERROR 1105 (07000): access denied to table my_table

16. Lua example Some other things you can do: Start/stop mysqld backend. Choose your backend DB based on which IP address, or which mysql user the client has. Modify result set by adding or removing a column from the returned table. Time your queries. Gather statistics. Your limits are that of the Lua language.

17. The admin interface You can monitor and show internal structures of the running mysql-proxy process by programming your own admin interface in Lua. Start mysql-proxy with admin options: --admin-address=127.0.0.1:10008 --admin-username=root –admin-password=password --admin-lua-script=/mysql-proxy/share/admin/admin.lua

18. The admin interface Function read_query(packet) If packet:byte() ~= proxy.COM_QUERY then return_error(“[admin] we only handle COM_QUERY”); --user defined end Local rows = { } local fields = { } – no need for a semicolon in Lua if packet:sub(2) == “ls” then – if query is an ls fields = { { name = “backend_ndx”, type = proxy.MYSQL_TYPE_LONG }, { name = “address” , type = proxy.MYSQL_TYPE_STRING } , { name = “connected_clients”, type = proxy.MYSQL_TYPE_LONG}, } for i = 1, #proxy.global.backends do Local b = proxy.global.backends[i] rows[#rows + 1] = { i, b.dst.name, b.connected_clients } end end … Now build proxy.response and return with proxy.PROXY_SEND_RESULT

19. The admin interface Querying the previously Lua coded interface will return: mysql>msyql -u root -ppassword -h 127.0.0.1 -P 10008 mysql> ls; +------------------+--------------------------+-------------------------+ | backend_ndx | address | connected_clients | +------------------+--------------------------+-------------------------+ | 1 | 192.168.1.9:3309 | 23 | +------------------+--------------------------+-------------------------+ | 2 | 192.168.1.10:3309 | 142 | +------------------+--------------------------+-------------------------+

20. Currently known issues Read/Write splitting does not work if used by more than one client on the same proxy process. This is a known issue and I'm assuming it is being worked on. A bit complicated. Everything has to be coded by you. You need to know Lua well. (not a bad thing though)