mysql proxy. a powerful, flexible mysql toolbox
DESCRIPTION
MySQL-Proxy is a software application that, as the name suggests, sits between your client and MySQL server(s) allowing you to monitor, analyse or transform that communication. It communicates over the network using the MySQL network protocol and as so, in its the most basic configuration, Proxy simply interposes itself between the server and clients, passing queries from the client to the server and its responses on the other way around. This opens the possibility of changing the communication packets when needed, allowing thus being used for multiple purposes being the most remarkable query analysis, query filtering and modification, load balancing, failover, query injection and pooling. On this session I'll present and give you a global overview of MySQL-Proxy and the concepts behind it. Use-cases, technical overview and architecture will follow. And of course, everyone will want to see it working so that'll be included as well in parallel with detailed explanation on how you can use it to fulfill your needs.TRANSCRIPT
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. Insert Information Protection Policy Classification from Slide 121
MySQL ProxyA powerful, flexible MySQL toolbox
Miguel AraújoSoftware Developer @MySQL Enterprise Tools
3 April 2014
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.3
Agenda
1. What is MySQL Proxy?
2. Overview / Concepts
3. Architecture1. Usage
2. Plugins
4. Scenarios1. Examples / Demo!
5. How can we improve?
6. Q/A
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.4
What is MySQL Proxy?
Do you know it?
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.5
What is MySQL Proxy?
● Stands between the client and the server
● “Speaks” MySQL
● Can analyze, block, inject, rewrite, route queries...
● A good MitM!
Do you know it?
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.6
Overview / Concepts
Transparent to the client
Supports all major platforms
GPLv2 license
Plugins layer
Customized with Lua scripts
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.7
Overview / Concepts
Transparent to the client
Supports all major platforms
GPLv2 license
Plugins layer
Customized with Lua scripts
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.8
Overview / ConceptsUse-cases
Query analysis
Query manipulation
Load balancing
Fail over
Sharding
R/W splitting
Pooling
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.9
Architecture4 Layers
Chassis
La scripting
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.10
Architecture4 Layers
Chassis
Network Core
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.11
Architecture4 Layers
Chassis
Network Core
Plugins
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.12
Architecture4 Layers
Chassis
Network Core
Plugins
Lua scripting
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.13
ArchitectureDetail
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.14
How do I start proxy?
Which are the command-line options?
Can I have a configuration file?
How does it look like?
ArchitectureUsage
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.15
Demo!
ArchitectureUsage
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.16
ArchitecturePlugins: Proxy plugin
MySQL Proxy “is” actually, the Proxy plugin
Accepts connections on its “--proxy-address”
Forwards data to the “--proxy-backend-addresses”
We can provide a Lua script with “--proxy-lua-script”
Exposes hooks to the scripting layer:
➔ connect_server()
➔ read_query()
➔ read_query_result()
➔ ...
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.17
Demo!
ArchitecturePlugins: Proxy plugin
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.18
ArchitecturePlugins: Admin plugin
Administration interface
Only authenticated users can use it➔ --admin-username
➔ --admin-password
Provides information about the proxy server
We must use a Lua script...➔ --admin-lua-script
… to access the internal components of mysql-proxy
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.19
Demo!
ArchitecturePlugins: Admin plugin
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.20
Scenarios
Query manipulation
Let's intercept some user queries and rewrite them...
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.21
Scenarios
Query manipulation
Let's intercept some user queries and rewrite them...
rewrite.lua
function read_query (packet)
if packet:byte() == proxy.COM_QUERY then ...
if string.match(string.upper(query), 'foo') then proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "bar")
return proxy.PROXY_SEND_QUERY
end endend
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.22
Demo!
ScenariosQuery manipulation
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.23
Scenarios
Query injection
Let's inject some useful SQL for certain queries...
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.24
Scenarios
Query injection
Let's inject some useful SQL for certain queries...
inject.lua
function read_query (packet) ... proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "SELECT CURRENT_USER()", {resultset_is_needed = true}) ...end
function read_query_result(inj) ... if (inj.id == 2) then for row in inj.resultset.rows do ... end
return proxy.PROXY_IGNORE_RESULT endend
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.25
Demo!
ScenariosQuery injection
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.26
Scenarios
Load balancing
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.27
Scenarios
Load balancingro-pooling.lua
function connect_server (packet) --get a connection to a backend... ...end
function read_auth_result (auth) --successful connection? Put it on the pool ...end
function read_query (packet) --get the first idle server and choose it for backend connection ...end
function read_query_result (inj) --manage backends useend
function disconnect_client () --manage disconnected clientsend
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.28
Scenarios
R/W splitting
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.29
Scenarios
R/W splittingrw-splitting.lua
function connect_server (packet) --get a connection to a backend... ...end
function read_auth_result (auth) --successful connection? Put it on the pool ...end
function read_query (packet) --use the tokenizer to check if query is a SELECT. If so pick an idle slave if not pick the master ...end
function read_query_result (inj) --manage backends useend
function disconnect_client () --manage disconnected clientsend
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.30
Demo!
ScenariosR/W splitting
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.31
Used worldwide
Used in production
Scenarios
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.32
How can we improve?Problems / Solutions
Performance issues due to global plugin lock
Plugins loaded at start
Scripting restricted to Lua
Admin plugin limited
Code can be optimized
0.8.4 Next... (0.9.0)
Copyright © 2014, Oracle and/or its affiliates. All rights reserved.33
Any questions?
Download from: http://dev.mysql.com/downloads/mysql-proxy/
Code @ launchpad.net/mysql-proxy
mailto: [email protected]; [email protected]; [email protected]
IRC: miguelaraujo / weigon / leithal in #mysql-proxy on irc.freenode.net
Thanks!