mysql proxy tutorial
DESCRIPTION
The complete tutorial on MySQL Proxy at the MySQL Users Conference 2008TRANSCRIPT
![Page 1: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/1.jpg)
Presented by,
MySQL AB® & O’Reilly Media, Inc.
MySQL ProxyThe complete tutorialJan Kneschke,
Senior software Engineer,MySQL
Giuseppe Maxia, Community Team Leader, MySQL
This work is licensed under the Creative Commons Attribution-Share Alike 3.0 Unported License.
![Page 2: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/2.jpg)
Raise your hand Who has heard of MySQL Proxy ? Who has tried out MySQL Proxy ? Who runs MySQL Proxy in production ? Who wrote scripts for MySQL Proxy ? Who runs MySQL Proxy from svn ? Who runs another MySQL Proxy like
application ?
![Page 3: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/3.jpg)
Raise your hand
Who has read the "getting started" article?
Who has read the datacharmer blog posts?
![Page 4: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/4.jpg)
Agenda Overview Basic principles Lua scripts Proxy for a single backend Proxy for multiple backends Wizardry (all over)
![Page 5: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/5.jpg)
Proxy (< latin "procuratio")
= Someone taking care of someone else's interests
A server proxy is something acting on behalf of another server
![Page 6: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/6.jpg)
Proxies
Content• Caching (increase TTL)• Off Loading (SSL, compression)
Backends• Load Balancing• Fail Over
Auditing
![Page 7: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/7.jpg)
Proxies for http
Squid, Varnish, ... SSL Off-Loading in hardware perlbal DNS round robin
![Page 8: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/8.jpg)
And for MySQL?
Load Balancing for Scale Out Fail Over on Maintenance, Host Failure, ... Tracing of Evil Queries Patching Evil Queries Connection Pooling
![Page 9: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/9.jpg)
Proxies for MySQL
no MySQL awareness• tcp-level solutions
not transparent• Continuent(tm) uni/cluster• sqlrelay
around 2007• home grown solutions
![Page 10: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/10.jpg)
Grow your own
REST to SQL (nytimes)• uses HTTP features
MySQL transparent• Protocol in the forge• implement server and client side• MySQL Proxy, dormando, DBI::MySQLServer
![Page 11: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/11.jpg)
Transparency
programming language connectors authentication add/remove w/o huge changes flexibility
![Page 12: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/12.jpg)
Database problems
• broken?• missing feature?• not flexible?
![Page 13: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/13.jpg)
Solving database problems
traditional way
1. file a bug report2. wait
![Page 14: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/14.jpg)
Solving database problems
source code
modify
new source code
compile
Open source way
![Page 15: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/15.jpg)
Solving database problems
bring the logic at application level
creative (shortsighted) way
![Page 16: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/16.jpg)
Solving database problems
set the logic at server level (stored routines)
creative (enlightened) way
![Page 17: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/17.jpg)
Solving database problems
set the logic at protocol level (proxy)
creative (more enlightened) way
![Page 18: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/18.jpg)
what can you do with MySQL Proxy
create new commands filter queries (deny specific queries) collect statistics on usage implement usage quotas execute shell commands create customized logs implement server-side pivot tables start/stop a MySQL server remotely
![Page 19: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/19.jpg)
what can you do with MySQL Proxy
play movies (seriously!) make coffee (really?) sharding load balancing servers
![Page 20: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/20.jpg)
what can you do with MySQL Proxy
Let us show you ...
![Page 21: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/21.jpg)
basic principles
![Page 22: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/22.jpg)
basic principles
PROXY CORE
connection hook
read query hook
read result hook
function
Lua script
function
functionfunctionfunction
![Page 23: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/23.jpg)
Lua
??Why not ...{Perl ?
PHP?Javascript?[whatever]?
![Page 24: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/24.jpg)
Lua
• SMALL ( < 200 KB)• DESIGNED for
EMBEDDED systems• Widely used (lighttpd)
lighttpd, like MySQL Proxy, was created by Jan Kneschke
![Page 25: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/25.jpg)
Lua
Very popular among game writers
![Page 26: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/26.jpg)
Proxy - Lua overview
connect_server
Lua script
read_auth
read_auth_result
read_handshake
read_query
read_query_result
disconnect_client
global context
session contextsession
contextsession contextsession
contextsession contextsession
contextsession context
![Page 27: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/27.jpg)
Using Lua Files/usr/local/sbin/mysql-proxy \ --proxy-lua-script=/path/name.lua
IMPORTANT!THE SCRIPT DOES NOT START UNTIL THE FIRST
CLIENT CONNECTION
![Page 28: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/28.jpg)
intercepting
function read_query(packet) if packet:byte() == proxy.COM_QUERY then local query = packet:sub(2) print("Hello world! Seen query: " .. query ) endend
![Page 29: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/29.jpg)
injecting queries
![Page 30: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/30.jpg)
injecting queries
![Page 31: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/31.jpg)
injecting queries
function read_query(packet) -- ... proxy.queries:append(2, query1 ) proxy.queries:append(1, packet ) proxy.queries:append(3, query2 )
return proxy.PROXY_SEND_QUERY
end
![Page 32: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/32.jpg)
injecting queries
function read_query_result(inj) if inj.id == 1 then return -- default result else -- do something
return proxy.PROXY_IGNORE_RESULTend
![Page 33: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/33.jpg)
working with results
• return the original result
• return a fake result
• return an error
• alter the original result
• return something different (affected/retrieved)
![Page 34: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/34.jpg)
debugging
•Put a Proxy in between
•use a sensible script to see what's going on (e.g. tutorial-packets.lua or tutorial-states.lua)
![Page 35: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/35.jpg)
debugging
server
client
proxy
diagnosticstext
![Page 36: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/36.jpg)
debugging scripts
server
client
proxy
diagnosticstext
proxy
diagnosticstext
![Page 37: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/37.jpg)
chained proxy: double features
server
proxy
pivot tablesproxy
loops
![Page 38: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/38.jpg)
testing
server
client
proxy
fake packetse.g.
connectors
![Page 39: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/39.jpg)
DOWNLOAD MATERIAL
Slides and example scriptshttp://datacharmer.org/tutorial_uc2008/
![Page 40: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/40.jpg)
Lua basics
![Page 41: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/41.jpg)
Lua types
• nil• number• string• table• function• userdata
a = nilb = 1c = 'abc't = { a,b,c }f = printu = some_C_struct
![Page 42: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/42.jpg)
Lua comments
-- simple comment
print(1)--[[ print(2) print('hello')--]]print(3)
![Page 43: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/43.jpg)
Lua comments-- simple comment
--[=[ print(1) --[[ print(2) print('hello') --]] print(3)--]=]
![Page 44: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/44.jpg)
Numbers and strings• nil = no value (false)• number = floating point• '5' =/= 5 a = nil
b = 5; c = '5'print (b == c)falseprint (b == c +0)true
![Page 45: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/45.jpg)
Numbers and strings• conversion on demand
a = 5 ; b = '5'
print(type(a), type(b))number stringprint(type(b+0))numberprint(type(a .. ""))string
![Page 46: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/46.jpg)
Numbers and strings• conversion on demand
a = 5 ; b = '5'
print(type(tostring(a)))stringprint(type(tonumber(b)))number
![Page 47: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/47.jpg)
strings
• smart quoting
a = 'Hello' b = "World"c = "Can't"d = [[Don't say "Hello"]]e = [=["d'oh" [[braces]]!]=]
![Page 48: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/48.jpg)
tables• associative arrays• can be used as arrays• can create complex structures
t1 = {10, 20, 30 }t2 = { a = 'abc', b = 2, c = { 3, 4}}
![Page 49: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/49.jpg)
functions
• can be assigned to variables• new functions can override existing
ones
function f (x) print(x)endg = fg(10)
![Page 50: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/50.jpg)
userdata
• containers to exchange data between Lua and host language
• can have "tag methods"
![Page 51: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/51.jpg)
statements
• normal assignmentsa = 3b = get_num() -- func return
• multiple assignmentsa,b = 3,2
![Page 52: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/52.jpg)
statements
• multiple return valuesfunction x () return 1, 'OK'end
a, b, c = x() -- a = 1, b = 'OK', c = nil
![Page 53: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/53.jpg)
statement blocks
• ifif condition then statementsend
• whilewhile condition do statementsend
![Page 54: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/54.jpg)
statement blocks
• forfor var = 1, 10 [,step] do statementsend
• forfor n,v in pairs(table_var) do statementsend
![Page 55: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/55.jpg)
sample function• read_query
1 function read_query(packet) 2 if packet:byte() ~= 3 proxy.PROXY_COM_QUERY 4 then 5 return 6 end 7 local query = packet:sub(2) 8 print('received ' .. query) 9 end
![Page 56: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/56.jpg)
some details
== equals ~= not equal string.byte(packet) packet:byte()
string.sub(packet,2) packet:sub(2) 'abc' .. '123' == 'abc123'
![Page 57: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/57.jpg)
using tables
t = {}t[1] = 'a' --First element 1, ! 0t[2] = 'b'table.insert(t, 'c') -- or t[ #t +1 ] = 'c'
t = {'a', 'b', 'c' }t = {1 = 'a', 2 = 'b', 3 = 'c'}print (t[2])b
![Page 58: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/58.jpg)
using tables
sizes = {}sizes['john'] = 'XL'sizes['paul'] = 'M'sizes['fred'] = 'L'
sizes = { john = 'XL', paul = 'M', fred = 'L',}
![Page 59: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/59.jpg)
using tables
sizes = { john = 'XL', paul = 'M', fred = 'L',} print(sizes['john'])XLprint(sizes.paul)M
![Page 60: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/60.jpg)
using tables
for i, v in ipairs(t) do print (i ..' -> ' .. v)end
for name,size in pairs(sizes) do print(name .. ' ' .. 'wears' .. ' ' .. size)end
![Page 61: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/61.jpg)
WATCH OUT!
/* C / C++ */ int a = 0;printf("%s\n", a ? "true" : "false");false
-- Luaa = 0print ( a and "true" or "false")true
![Page 62: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/62.jpg)
WATCH OUT!
-- Luaa = falseprint ( a and "true" or "false")false
a = nilprint ( a and "true" or "false")false
![Page 63: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/63.jpg)
Finding text
query = 'SELECT id FROM t1'
local cmd, column = query:match("(SELECT)%s+(%w+)")
if cmd then -- do something with queryend
![Page 64: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/64.jpg)
finding text
• Regular expressions• similar to Perl/PHP, but simpler
• % instead of \• (captures)• [character classes]• ^ $ + - ? * • no alternation (a|b)• no modifiers /i
![Page 65: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/65.jpg)
finding text (Proxy way)
local tk = require('proxy.tokenizer')
local tokens = tk.tokenize(query)
if tokens[1].token_name == 'TK_SQL_SELECT' then -- do something with queryend
![Page 66: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/66.jpg)
finding text (Proxy way)
-- each token is a table
token = { token_name = 'TK_SQL_SELECT', text = 'select', token_id = 204}
![Page 67: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/67.jpg)
I/O
-- files are objects
local fname = '/tmp/test.txt'assert(fh = io.open(fname,'r'))
for line in fh:lines() do print(line)endfh:close()
![Page 68: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/68.jpg)
I/O
-- files are objects
local fname = '/tmp/test.txt'
assert(fh = io.open(fname,'w'), 'oops!')for x = 1, 100 do fh:write('new row ' .. x) fh:flush()endfh:close()
![Page 69: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/69.jpg)
MySQL Proxy recipes
![Page 70: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/70.jpg)
cookbook: returning a simple dataset
![Page 71: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/71.jpg)
cookbook: returning a simple datasetfunction simple_dataset (header, message)proxy.response.type = proxy.MYSQLD_PACKET_OKproxy.response.resultset = { fields = { {type = proxy.MYSQL_TYPE_STRING, name = header} }, rows = { { message} }}return proxy.PROXY_SEND_RESULTend
![Page 72: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/72.jpg)
cookbook: returning a full dataset
![Page 73: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/73.jpg)
cookbook: returning a full datasetfunction make_dataset (header, dataset) proxy.response.type = proxy.MYSQLD_PACKET_OK
proxy.response.resultset = { fields = {}, rows = {}} for i,v in pairs (header) do table.insert( proxy.response.resultset.fields, {type = proxy.MYSQL_TYPE_STRING, name = v}) end for i,v in pairs (dataset) do table.insert(proxy.response.resultset.rows, v ) end return proxy.PROXY_SEND_RESULTend
![Page 74: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/74.jpg)
cookbook: returning a full dataset
return make_dataset( {'command', 'description' }, -- the header { -- the rows {'FOO', 'removes the database'}, {'BAR', 'drops all tables'}, {'FOOBAR', 'makes the server explode'}, })
![Page 75: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/75.jpg)
cookbook: returning an error
![Page 76: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/76.jpg)
cookbook: returning an error
function error_result (msg, code,state) proxy.response = { type = proxy.MYSQLD_PACKET_ERR, errmsg = msg, errcode = code, sqlstate = state, } return proxy.PROXY_SEND_RESULTend
![Page 77: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/77.jpg)
cookbook: returning a non dataset result
ok. you changed 42 lines
![Page 78: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/78.jpg)
cookbook: returning a non dataset result
function affected_rows (rows,id) proxy.response = { type = proxy.MYSQLD_PACKET_OK, affected_rows = rows, insert_id = id, } return proxy.PROXY_SEND_RESULTend
![Page 79: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/79.jpg)
cookbook: debug messages
got that query, blah, blah
proxy terminal screen
![Page 80: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/80.jpg)
cookbook: debug messageslocal DEBUG = os.getenv('DEBUG') or 0DEBUG = DEBUG + 0
function read_query (packet ) if packet:byte() ~= proxy.COM_QUERY then return end print_debug(packet:sub(2),1) print_debug('inside read_query', 2)end
function print_debug(msg, level) level = level or 1 if DEBUG >= level then print (msg) endend
![Page 81: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/81.jpg)
cookbook: verbose level at run timelocal DEBUG = os.getenv('DEBUG') or 0DEBUG = DEBUG + 0
function read_query (packet ) if packet:byte() ~= proxy.COM_QUERY then return end local vlevel=query:match('^VERBOSE=(%d)$') if vlevel then DEBUG = vlevel+0 return simple_dataset('verbose',vlevel) endend
![Page 82: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/82.jpg)
cookbook: keep info inside a session
my variable value
proxy script
my variable valuemy variable value
![Page 83: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/83.jpg)
cookbook: keep info inside a session
nothing to do :) Proxy scripts have session scope by defaultlocal tot_q = 0
function read_query (packet ) if packet:byte() ~= proxy.COM_QUERY then return end tot_q = tot_q + 1 print('queries ' .. tot_q)end
![Page 84: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/84.jpg)
cookbook: share info among sessions
my variable value
proxy script
![Page 85: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/85.jpg)
cookbook: share info among sessions
proxy.global.tot_q = proxy.global.tot_q or 0
function read_query (packet ) if packet:byte() ~= proxy.COM_QUERY then return end proxy.global.tot_q = proxy.global.tot_q + 1 print('queries ' .. proxy.global.tot_q)end
![Page 86: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/86.jpg)
cookbook: rerouting traffic
![Page 87: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/87.jpg)
cookbook: rerouting traffic(1) do
sudo iptables -t nat \ -I PREROUTING \ -s ! 127.0.0.1 -p tcp \ --dport 3306 -j \ REDIRECT --to-ports 4040
![Page 88: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/88.jpg)
cookbook: rerouting traffic(1) undo
sudo iptables -t nat \ -D PREROUTING \ -s ! 127.0.0.1 -p tcp \ --dport 3306 -j \ REDIRECT --to-ports 4040
![Page 89: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/89.jpg)
Examples
http://datacharmer.org/tutorial_uc2008
• all hooks• session bandwidth• user bandwidth• blocking commands
![Page 90: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/90.jpg)
all_hooks.luasource: 010_all-hooks.lua
function read_query (packet) print_access('inside read_query \t' .. packet:sub(2)) proxy.queries:append(1, packet) return proxy.PROXY_SEND_QUERYend
function read_query_result (inj) print_access('inside read_query_result \t' .. inj.query)end
![Page 91: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/91.jpg)
read_query and read_query_resultclient
MySQL Proxy
SERVER
query
functionread_query
function
read_query_result
query
result
result
if a query is passed directly to the server, its result is NOT evaluated by read_query_result
![Page 92: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/92.jpg)
read_query and read_query_resultclient
MySQL Proxy
SERVER
query
function read_query
function
read_query_result
query queue
query
result
result
only if a query is added to the query queue, its result is evaluated by read_query_result
queryquery
![Page 93: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/93.jpg)
all_hooks.luasource: 010_all-hooks.lua
sample output/usr/local/sbin/mysql-proxy --proxy-lua-script=all-hooks.lua 1 inside connect_server 2 inside read_handshake 3 inside read_auth 4 inside read_auth_result 5 inside read_query 6 inside read_query_result 7 inside read_query 8 inside disconnect_client
![Page 94: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/94.jpg)
more examples
live
![Page 95: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/95.jpg)
read morehttp://www.lua.org/docs.html
online Lua documentation
![Page 96: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/96.jpg)
read morehttp://www.inf.puc-rio.br/~roberto/pil2/
![Page 97: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/97.jpg)
read morehttp://www.wrox.com/WileyCDA/WroxTitle/productCd-0470069171.html
![Page 98: MySQL Proxy tutorial](https://reader033.vdocuments.us/reader033/viewer/2022052900/5560cbc3d8b42a0d088b4b8d/html5/thumbnails/98.jpg)
Q&A
This work is licensed under the Creative Commons Attribution-Share Alike 3.0 Unported License. To view a copy of this license, visit http://creativecommons.org/licenses/by-sa/3.0/ or send a letter to Creative Commons, 171 Second Street, Suite 300, San Francisco, California, 94105, USA.
Let's talk!
Slides and example scriptshttp://datacharmer.org/tutorial_uc2008/