From: Date: August 13 2007 6:37pm Subject: svn commit - mysqldoc@docsrva: r7447 - in trunk/refman-common: . images/published images/source List-Archive: http://lists.mysql.com/commits/32461 Message-Id: <200708131637.l7DGbsIG024072@docsrva.mysql.com> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Author: mcbrown Date: 2007-08-13 18:37:50 +0200 (Mon, 13 Aug 2007) New Revision: 7447 Log: Added more detail on functions and operation, and some further examples Started documenting 0.6.0 features Provided new hooks for an exampe usage section Added Query/result execution diagram Added: trunk/refman-common/images/published/proxy-architecture.png Modified: trunk/refman-common/images/source/mysql-proxy-set.graffle trunk/refman-common/mysql-proxy.xml Property changes on: trunk/refman-common/images/published/proxy-architecture.png ___________________________________________________________________ Name: svn:mime-type + application/octet-stream Added: trunk/refman-common/images/published/proxy-architecture.png =================================================================== Changed blocks: 0, Lines Added: 0, Lines Deleted: 0; 349 bytes Modified: trunk/refman-common/images/source/mysql-proxy-set.graffle =================================================================== --- trunk/refman-common/images/source/mysql-proxy-set.graffle 2007-08-13 13:40:53 UTC (rev 7446) +++ trunk/refman-common/images/source/mysql-proxy-set.graffle 2007-08-13 16:37:50 UTC (rev 7447) Changed blocks: 10, Lines Added: 639, Lines Deleted: 125; 23677 bytes @@ -30,32 +30,299 @@ GraphicsList + Bounds + {{170.382, 329.5}, {120, 14}} Class + ShapedGraphic + FitText + YES + FontInfo + + Color + + w + 0 + + Font + Helvetica + Size + 12 + + ID + 35 + Line + + ID + 25 + Position + 0.35825392603874207 + RotationType + 0 + + Shape + Rectangle + Style + + shadow + + Draws + NO + + stroke + + Draws + NO + + + Text + + Text + {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 +{\fonttbl\f0\fswiss\fcharset77 Helvetica;} +{\colortbl;\red255\green255\blue255;} +\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural + +\f0\fs24 \cf0 Return Query Result} + + + + Bounds + {{548.888, 329.5}, {135, 14}} + Class + ShapedGraphic + FitText + YES + FontInfo + + Color + + w + 0 + + Font + Helvetica + Size + 12 + + ID + 29 + Line + + ID + 24 + Position + 0.55271565914154053 + RotationType + 0 + + Shape + Rectangle + Style + + shadow + + Draws + NO + + stroke + + Draws + NO + + + Text + + Text + {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 +{\fonttbl\f0\fswiss\fcharset77 Helvetica;} +{\colortbl;\red255\green255\blue255;} +\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural + +\f0\fs24 \cf0 Return Multiple Results} + + + + Bounds + {{501.514, 233.5}, {84, 28}} + Class + ShapedGraphic + FitText + YES + FontInfo + + Color + + w + 0 + + Font + Helvetica + Size + 12 + + ID + 28 + Line + + ID + 23 + Position + 0.4049079418182373 + RotationType + 0 + + Shape + Rectangle + Style + + shadow + + Draws + NO + + stroke + + Draws + NO + + + Text + + Text + {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 +{\fonttbl\f0\fswiss\fcharset77 Helvetica;} +{\colortbl;\red255\green255\blue255;} +\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural + +\f0\fs24 \cf0 Submit Query\ +Queue} + + + + Bounds + {{234.444, 233.781}, {48, 28}} + Class + ShapedGraphic + FitText + YES + FontInfo + + Color + + w + 0 + + Font + Helvetica + Size + 12 + + ID + 27 + Line + + ID + 22 + Position + 0.44859817624092102 + RotationType + 0 + + Shape + Rectangle + Style + + shadow + + Draws + NO + + stroke + + Draws + NO + + + Text + + Text + {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 +{\fonttbl\f0\fswiss\fcharset77 Helvetica;} +{\colortbl;\red255\green255\blue255;} +\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural + +\f0\fs24 \cf0 Submit\ +Query} + + + + Class LineGraphic Head ID - 3 + 1 Info - 2 + 8 ID - 10 + 25 OrthogonalBarAutomatic OrthogonalBarPosition -1 Points - {251.5, 70} - {251.5, 130} + {333.28, 336.5} + {120.06, 262.5} Style stroke HeadArrow + FilledArrow + LineType + 2 + TailArrow 0 + + + Tail + + ID + 31 + Info + 4 + + + + Class + LineGraphic + Head + + ID + 31 + Info + 3 + + ID + 24 + OrthogonalBarAutomatic + + OrthogonalBarPosition + -1 + Points + + {699.5, 261.308} + {488.28, 336.5} + + Style + + stroke + + HeadArrow + FilledArrow LineType 2 TailArrow @@ -65,14 +332,92 @@ Tail ID + 6 + Info 1 + + + + Class + LineGraphic + Head + + ID + 6 + + ID + 23 + OrthogonalBarAutomatic + + OrthogonalBarPosition + 55.959991455078125 + Points + + {488.28, 247.5} + {624, 246.808} + + Style + + stroke + + HeadArrow + FilledArrow + LineType + 2 + TailArrow + 0 + + + Tail + + ID + 32 Info + 3 + + + + Class + LineGraphic + Head + + ID + 32 + + ID + 22 + OrthogonalBarAutomatic + + OrthogonalBarPosition + -1 + Points + + {197.56, 248} + {333.28, 247.5} + + Style + + stroke + + HeadArrow + FilledArrow + LineType + 2 + TailArrow + 0 + + + Tail + + ID 1 + Info + 5 Bounds - {{22.5, 141.854}, {99, 28}} + {{549.111, 151.5}, {55, 14}} Class ShapedGraphic FitText @@ -90,13 +435,13 @@ 12 ID - 9 + 21 Line ID - 8 + 20 Position - 0.46966013312339783 + 0.30990418791770935 RotationType 0 @@ -123,8 +468,7 @@ {\colortbl;\red255\green255\blue255;} \pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural -\f0\fs24 \cf0 Return \ -manual resultset} +\f0\fs24 \cf0 Connect} @@ -133,64 +477,84 @@ Head ID - 1 + 6 Info - 4 + 2 ID - 8 + 20 OrthogonalBarAutomatic OrthogonalBarPosition -1 Points - {174, 233.5} - {72, 55} - {174, 55.5} + {488.28, 158.5} + {699.5, 232.308} Style stroke HeadArrow - 0 + FilledArrow LineType 2 TailArrow - 0 + FilledArrow Tail ID - 4 + 33 Info - 4 + 3 Bounds - {{389, 264}, {151, 29}} + {{198.462, 151.5}, {55, 14}} Class ShapedGraphic + FitText + YES + FontInfo + + Color + + w + 0 + + Font + Helvetica + Size + 12 + ID - 7 - Magnets - - {0, 1} - {0, -1} - {1, 0} - {-1, 0} - + 19 + Line + + ID + 18 + Position + 0.62765359878540039 + RotationType + 0 + Shape Rectangle Style + shadow + + Draws + NO + stroke - CornerRadius - 5 + Draws + NO Text @@ -201,154 +565,299 @@ {\colortbl;\red255\green255\blue255;} \pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural -\f0\fs24 \cf0 Server} +\f0\fs24 \cf0 Connect} - Bounds - {{389, 175}, {151, 29}} Class - ShapedGraphic + LineGraphic + Head + + ID + 33 + Info + 4 + ID - 6 - Magnets + 18 + OrthogonalBarAutomatic + + OrthogonalBarPosition + -1 + Points - {0, 1} - {0, -1} - {1, 0} - {-1, 0} + {120.06, 233.5} + {333.28, 158.5} - Shape - Rectangle Style stroke - CornerRadius - 5 + HeadArrow + FilledArrow + LineType + 2 + TailArrow + FilledArrow - Text + Tail - Text - {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 -{\fonttbl\f0\fswiss\fcharset77 Helvetica;} -{\colortbl;\red255\green255\blue255;} -\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural - -\f0\fs24 \cf0 Server} + ID + 1 + Info + 2 Bounds - {{174, 308}, {155, 29}} + {{624, 232.308}, {151, 29}} Class ShapedGraphic ID - 5 + 6 Magnets {0, 1} {0, -1} {1, 0} {-1, 0} - {1, 1} - {1, -1} - {-1, 1} - {-1, -1} Shape Rectangle + Style + + stroke + + CornerRadius + 5 + + Text Text {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 -{\fonttbl\f0\fswiss\fcharset77 Helvetica;} +{\fonttbl\f0\fswiss\fcharset77 Helvetica-Bold;} {\colortbl;\red255\green255\blue255;} \pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural -\f0\fs24 \cf0 read_query_result()} +\f0\b\fs34 \cf0 Server} - Bounds - {{174, 219}, {155, 29}} Class - ShapedGraphic - ID - 4 - Magnets + Group + Graphics - {0, 1} - {0, -1} - {1, 0} - {-1, 0} - {1, 1} - {1, -1} - {-1, 1} - {-1, -1} - - Shape - Rectangle - Text - - Text - {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 + + Bounds + {{333.28, 322}, {155, 29}} + Class + ShapedGraphic + ID + 31 + Magnets + + {0, 1} + {0, -1} + {1, 0} + {-1, 0} + {1, 1} + {1, -1} + {-1, 1} + {-1, -1} + + Shape + Rectangle + Style + + stroke + + Color + + b + 0.666667 + g + 0.666667 + r + 0.666667 + + + + Text + + Text + {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 {\fonttbl\f0\fswiss\fcharset77 Helvetica;} {\colortbl;\red255\green255\blue255;} \pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural +\f0\fs24 \cf0 read_query_result()} + + + + Bounds + {{333.28, 233}, {155, 29}} + Class + ShapedGraphic + ID + 32 + Magnets + + {0, 1} + {0, -1} + {1, 0} + {-1, 0} + {1, 1} + {1, -1} + {-1, 1} + {-1, -1} + + Shape + Rectangle + Style + + stroke + + Color + + b + 0.666667 + g + 0.666667 + r + 0.666667 + + + + Text + + Text + {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 +{\fonttbl\f0\fswiss\fcharset77 Helvetica;} +{\colortbl;\red255\green255\blue255;} +\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural + \f0\fs24 \cf0 read_query()} - - - - Bounds - {{174, 130}, {155, 29}} - Class - ShapedGraphic - ID - 3 - Magnets - - {0, 1} - {0, -1} - {1, 0} - {-1, 0} - {1, 1} - {1, -1} - {-1, 1} - {-1, -1} - - Shape - Rectangle - Text - - Text - {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 + + + + Bounds + {{333.28, 144}, {155, 29}} + Class + ShapedGraphic + ID + 33 + Magnets + + {0, 1} + {0, -1} + {1, 0} + {-1, 0} + {1, 1} + {1, -1} + {-1, 1} + {-1, -1} + + Shape + Rectangle + Style + + stroke + + Color + + b + 0.666667 + g + 0.666667 + r + 0.666667 + + + + Text + + Text + {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 {\fonttbl\f0\fswiss\fcharset77 Helvetica;} {\colortbl;\red255\green255\blue255;} \pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural \f0\fs24 \cf0 connect_server()} - + + + + Bounds + {{269.28, 52}, {283, 373}} + Class + ShapedGraphic + ID + 34 + Magnets + + {-1, 0} + {-0.5, -1} + {0.5, -1} + {1, 0} + {0.5, 1} + {-0.5, 1} + + Shape + Hexagon + Style + + stroke + + Color + + b + 0.666667 + g + 0.666667 + r + 0.666667 + + + + Text + + Text + {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 +{\fonttbl\f0\fswiss\fcharset77 Helvetica-Bold;} +{\colortbl;\red255\green255\blue255;} +\pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural + +\f0\b\fs34 \cf0 mysql-proxy} + + TextPlacement + 0 + + + ID + 30 Bounds - {{174, 41}, {155, 29}} + {{42.56, 233.5}, {155, 29}} Class ShapedGraphic ID 1 Magnets - {0, 1} - {0, -1} - {1, 0} - {-1, 0} - {1, 1} - {1, -1} - {-1, 1} - {-1, -1} + {-0.596285, -1.19257} + {0, -1.33333} + {0.596285, -1.19257} + {1.19257, -0.596285} + {1.33333, 0} + {1.19257, 0.596285} + {0.596285, 1.19257} + {0, 1.33333} + {-0.596285, 1.19257} + {-1.19257, 0.596285} + {-1.33333, 0} + {-1.19257, -0.596285} Shape Rectangle @@ -356,11 +865,11 @@ Text {\rtf1\mac\ansicpg10000\cocoartf824\cocoasubrtf420 -{\fonttbl\f0\fswiss\fcharset77 Helvetica;} +{\fonttbl\f0\fswiss\fcharset77 Helvetica-Bold;} {\colortbl;\red255\green255\blue255;} \pard\tx560\tx1120\tx1680\tx2240\tx2800\tx3360\tx3920\tx4480\tx5040\tx5600\tx6160\tx6720\qc\pardirnatural -\f0\fs24 \cf0 Client} +\f0\b\fs32 \cf0 Client} @@ -463,7 +972,7 @@ ModificationDate - 2007-06-30 06:17:37 +0100 + 2007-08-13 11:46:01 +0100 Modifier Martin MC Brown, MySQL NotesVisible @@ -488,10 +997,15 @@ float 0 + NSOrientation + + int + 1 + NSPaperSize size - {595, 842} + {842, 595} NSRightMargin @@ -535,13 +1049,13 @@ FitInWindow Frame - {{566, 0}, {732, 996}} + {{354, 30}, {937, 965}} ShowRuler ShowStatusBar VisibleRegion - {{-72, -32}, {717, 882}} + {{-52, -139}, {922, 851}} Zoom 1 Modified: trunk/refman-common/mysql-proxy.xml =================================================================== --- trunk/refman-common/mysql-proxy.xml 2007-08-13 13:40:53 UTC (rev 7446) +++ trunk/refman-common/mysql-proxy.xml 2007-08-13 16:37:50 UTC (rev 7447) Changed blocks: 25, Lines Added: 624, Lines Deleted: 112; 36214 bytes @@ -19,6 +19,14 @@ + Because MySQL Proxy uses the MySQL network protocol, any MySQL + compatible client (include the command line client, any clients + using the MySQL client libraries, and any connector that supports + the MySQL network protocol) can connect to the proxy without + modification. + + + In addition to the basic pass-through configuration, the MySQL Proxy is also capable of monitoring and altering the communication between the client and the server. This interception of the queries enables @@ -27,57 +35,81 @@ - MySQL Proxy is currently available as a precompiled binary for the - following platforms: + By intercepting the queries from the client, the proxy can insert + additional queries into the list of queries sent to the server, and + remove the additional results when they are returned by the server. + Using this functionality you can add informational statements to + each query, for example to monitor their execution time or progress, + and separately log the results, while still returning the results + from the original query to the client. - + + The proxy allows you to perform additional monitoring, filtering or + manipulation on queries without you having to make any modifications + to the client and without the client even being aware that it is + communicating with anything but a genuine MySQL server. + - - - Linux (including RedHat, Fedora, Debia, SuSE) and derivatives. - - +
- - - Mac OS X - - + MySQL Proxy Supported Platforms - - - FreeBSD - - + + MySQL Proxy is currently available as a pre-compiled binary for + the following platforms: + - - - IBM AIX - - + - - - Sun Solaris - - + + + Linux (including RedHat, Fedora, Debian, SuSE) and + derivatives. + + - + + + Mac OS X + + - - Other Unix/Linux platforms not listed should be compatible by using - the source package and building MySQL Proxy locally. - + + + FreeBSD + + - - System requirements for the MySQL Proxy application are the same as - the main MySQL server. Currently MySQL Proxy is compatible only with - MySQL 5.0.1 and later. MySQL Proxy is provided as a standalone, - statically linked binary. You do not need to have MySQL or LUA - installed. - + + + IBM AIX + + + + + Sun Solaris + + + + + + + Other Unix/Linux platforms not listed should be compatible by + using the source package and building MySQL Proxy locally. + + + + System requirements for the MySQL Proxy application are the same + as the main MySQL server. Currently MySQL Proxy is compatible only + with MySQL 5.0.1 and later. MySQL Proxy is provided as a + standalone, statically linked binary. You do not need to have + MySQL or LUA installed. + + +
+
Installing MySQL Proxy @@ -123,12 +155,12 @@ If you download the binary packages then you need only to extract the package and then copy the - MySQL-proxy file to your desired location. + mysql-proxy file to your desired location. For example: -$ tar zxf MySQL-proxy-0.5.0.tar.gz -$ cp ./MySQL-proxy-0.5.0/sbin/MySQL-proxy /usr/local/sbin +$ tar zxf mysql-proxy-0.5.0.tar.gz +$ cp ./mysql-proxy-0.5.0/sbin/mysql-proxy /usr/local/sbin
@@ -146,7 +178,7 @@ - libevent 1.x or higher (1.3b or later is prefered) + libevent 1.x or higher (1.3b or later is preferred) @@ -181,15 +213,15 @@ then build: -$ tar zxf MySQL-proxy-0.5.0.tar.gz -$ cd MySQL-proxy-0.5.0 +$ tar zxf mysql-proxy-0.5.0.tar.gz +$ cd mysql-proxy-0.5.0 $ ./configure $ make $ make install - By default MySQL-proxy is installed into - /usr/local/sbin/MySQL-proxy + By default mysql-proxy is installed into + /usr/local/sbin/mysql-proxy @@ -267,15 +299,15 @@ svn: -$ svn co http://svn.MySQL.com/svnpublic/MySQL-proxy/ MySQL-proxy +$ svn co http://svn.MySQL.com/svnpublic/mysql-proxy/ mysql-proxy The above command will download a complete version of the - Subversion repository for MySQL-proxy. The + Subversion repository for mysql-proxy. The main source files are located within the trunk subdirectory. The configuration scripts need to be generated before you can configure and build - MySQL-proxy. The + mysql-proxy. The autogen.sh script will generate the configuration scripts for you: @@ -300,7 +332,7 @@ The above will create the file - MySQL-proxy-0.5.0.tar.gz + mysql-proxy-0.5.0.tar.gz within the current directory. @@ -313,7 +345,7 @@ MySQL Proxy Command Line Options - To start MySQL-proxy you can just run the + To start mysql-proxy you can just run the command directly. However, for most situations you will want to specify at the very least the address/hostname and port number of the backend MySQL server to which the MySQL Proxy should pass on @@ -325,9 +357,9 @@ --help-all: -$ MySQL-proxy --help-all +$ mysql-proxy --help-all Usage: - MySQL-proxy [OPTION...] - MySQL Proxy + mysql-proxy [OPTION...] - MySQL Proxy Help Options: -?, --help Show help options @@ -354,7 +386,7 @@ The majority of these options set up the environment, either in terms of the address/port number that - MySQL-proxy should listen on for connections, + mysql-proxy should listen on for connections, or the onward connection to a MySQL server. A full description of the options is shown below: @@ -441,7 +473,7 @@ not physically loaded and parsed until a connection is made. Also note that the specified LUA script is reloaded for each connection; if the content of the LUA script changes while - MySQL-proxy is running then the updated + mysql-proxy is running then the updated content will automatically be used when a new connection is made. @@ -470,19 +502,19 @@ - By default, MySQL-proxy will start up as a + By default, mysql-proxy will start up as a Daemon process and sets the home directory to root (/). The most common usage is as a simple proxy service (i.e. without - addition scripting). For basic proxy operation you must specify - at least one proxy-backend-addresses option to + addition scripting). For basic proxy operation you must specify at + least one proxy-backend-addresses option to specify the MySQL server to connect to by default: -$ MySQL-proxy +$ mysql-proxy --proxy-backend-addresses=MySQL.example.com:3306 @@ -496,7 +528,7 @@ If your server requires authentication information then this will be passed through natively without alteration by - MySQL-proxy, so you must also specify the + mysql-proxy, so you must also specify the authentication information if required: @@ -514,7 +546,7 @@ For more detailed information on how to use these command line - options, and MySQL-proxy in general in + options, and mysql-proxy in general in combination with LUA scripts, see . @@ -547,13 +579,41 @@ called each time a connection is made to MySQL Proxy from a client. You can use this function during load-balancing to intercept the original connection and decide which server the - connection to sent to, overriding the default round-robin - style distribution. + client should ultimately be attached to. If you don't define a + special solution, then a simple round-robin style distribution + is used by default. + read_handshake() — this function is + called when the initial handshake information is returned by + the server. You can capture the handshake information returned + and provide additional checks before the authorization + exchange takes place. + + + + + + read_auth() — this function is called + when the authorization packet (username, password, default + database) are submitted by the client to the server for + authentication. + + + + + + read_auth_result() — this funciton is + called when the server returns an authorization packet to the + client indicating whether the authorization succeeded. + + + + + read_query() — this function is called each time a query is sent by the client to the server. You can use this to edit and manipulate the original query, @@ -578,6 +638,58 @@ + The table below describes the direction of flow of information at + the point when the function is triggered. + + + + + + + + + + Function + Supplied Information + Direction + + + + + connect_server() + None + Client to Server + + + read_handshake() + Handshake packet + Server to Client + + + read_auth() + Authorization packet + Client to Server + + + read_auth_result() + Authorization response + Server to Client + + + read_query() + Query + Client to Server + + + read_query_result() + Query result + Server to Client + + + + + + In addition to these functions, a number of built-in structures provide control over how MySQL Proxy forwards on queries and returns the results by providing a simplified interface to @@ -586,6 +698,54 @@ + The figure below gives an example of how the proxy might be used. + Because the proxy sits between the client and MySQL server, what + the proxy sends to the server, and the information that the proxy + ultimately returns to the client do not have to match correlate. + Once the client has connected to the proxy, the following sequence + occurs for each individual query sent by the client. + + + + + + + + MySQL Proxy architecture + + + + + + + + The client submits one query to the proxy, the + read_query() function within the proxy is + triggered. The function adds the query to the query queue. + + + + + + Once manipulation by read_query() has + completed, the queries are submitted, sequentially, to the + MySQL server. + + + + + + The MySQL server returns the results from each query, one + result set for each query submitted. The + read_query_results() function is + triggered for each result set, and each invocation can decide + which result set to return to the client + + + + + + For example, you can queue additional queries into the global query queue to be processed by the server. This can be used to add statistical information by adding queries before and after the @@ -614,12 +774,28 @@ In both of these examples, the client would have received more - result sets than expected. You could alter your client to cope - with this fact, but it is easier to use the MySQL Proxy - read_query_results() to extract the additional - information and write the data to a separate log file. + result sets than expected. Regardless of how you manipulate the + incoming query and the returned result, the number of queries + returned by the proxy must match the number of original queries + sent by the client. + + You could adjust the client to handle the multiple result sets + sent by the proxy, but in most cases you will want the existence + of the proxy to remain transparent. To ensure that the number of + queries and result sets match, you can use the MySQL Proxy + read_query_results() to extract the + additional result set information and return only the result set + the client originally requested back to the client. You can + achieve this by giving each query that you add to the query queue + a unique ID, and then filter out queries that do not match the + original query ID when processing them with + read_query_results(). + + + +
Internal Structures @@ -793,24 +969,26 @@ append(id,packet) -Appends a query to the end of the query queue. The id is an - identifier that you can use to recognize the query results when they are - returned by the server. The packet should be a properly formatted query - packet. + Appends a query to the end of the query queue. The id + is an identifier that you can use to recognize the query + results when they are returned by the server. The packet + should be a properly formatted query packet. - prepend(id,packet) - Prepends a query to the query queue. The id is an - identifier that you can use to recognize the query results when they are - returned by the server. The packet should be a properly formatted query - packet. + prepend(id,packet) + Prepends a query to the query queue. The id is an + identifier that you can use to recognize the query + results when they are returned by the server. The packet + should be a properly formatted query packet. - reset() - Empties the query queue. + reset() + Empties the query queue. - len()Returns the - number of query packets in the queue. + + len() + Returns the number of query packets in the queue. + @@ -1228,12 +1406,15 @@
+
@@ -1242,53 +1423,131 @@ The read_query() function is called once for each query submitted by the client and accepts a single - argument, the query packet that was provided. - To access the content of the packet you must parse the packet manually. + argument, the query packet that was provided. To access the + content of the packet you must parse the packet manually. + -For example, you can intercept a query packet and print out the contents - using the following function definition: + + For example, you can intercept a query packet and print out the + contents using the following function definition: + function read_query( packet ) if string.byte(packet) == proxy.COM_QUERY then print("we got a normal query: " .. string.sub(packet, 2)) end -end - +end -This example checks the first byte of the packet to determine the type. If -the type is COM_QUERY (see ), then we extract - the query from the packet and print it out. The structure of the packet type - supplied is important. In the case of a COM_QUERY packet, - the remaining contents of the packet are the text of the query string. - - - You can also populate the query queue - (proxy.queries) and then execute the queries that you - have placed into the queue. The queue is empty by default; the packet - received from the client is not automatically added to the queue. + + This example checks the first byte of the packet to determine + the type. If the type is COM_QUERY (see + ), + then we extract the query from the packet and print it out. The + structure of the packet type supplied is important. In the case + of a COM_QUERY packet, the remaining contents + of the packet are the text of the query string. In this example, + no changes have been made to the query or the list of queries + that will ultimately be sent to the MySQL server. + -When adding queries to the queue, you should follow these guidelines: + + To modify a query, or add new queries, you must populate the + query queue (proxy.queries) and then execute + the queries that you have placed into the queue. If you do not + modify the original query or the queue, then the query received + from the client is sent to the MySQL server verbatim. + + + + When adding queries to the queue, you should follow these + guidelines: + + - The packets inserted into the queue must be valid query - packets. For each packet, you must - set the initial byte to the packet type. If you are appending a query, - you can append the query statement to the rest of the packet. + + + + The packets inserted into the queue must be valid query + packets. For each packet, you must set the initial byte to + the packet type. If you are appending a query, you can + append the query statement to the rest of the packet. + + + + + + Once you add a query to the queue, the queue is used as the + source for queries sent to the server. If you add a query to + the queue to add more information, you must also add the + original query to the queue or it will not be executed. + + + + + + Once the queue has been populated, you must set the return + value from read_query() to indicate + whether the query queue should be sent to the server. + + + + + + When you add queries to the queue, you should add a unique + ID. The unique ID you specify is returned with the result + set so that you identify which query is which. When + operating in a passive mode, during profiling for example, + you want to identify the original query and the + corresponding resultset so that the results expect by the + client can be returned correctly. + + + + + + Unless your client is designed to cope with more result sets + than queries, you should ensure that the number of queries + from the client match the number of results sets returned to + the client. Using the unique ID and removing result sets you + inserted will help. + + + - - + + Normally, the read_query() and + read_query_result() function are used in + conjunction with each other to inject additional queries and + remove the additional result sets. +
- Manipulating Results with <literal>read_query_results()</literal> + Manipulating Results with <function>read_query_results()</function> + + The reach_query_results() is called for + each result set returned by the server. The function supports a + single argument, the result packet, which provides a number of + properties: + + + id — the ID of the result set, + which corresponds to the ID that was set when the query + packet was submitted to the server. + + + + + query — the text of the original query. @@ -1319,6 +1578,100 @@ + + By accessing the result information from the MySQL server you + can extract the results that match the queries that you + injected, return different result sets (for example, from a + modified query), and even create your own result sets. + + + + The LUA script below, for example, will output the query, + followed by the query time and response time (i.e. the time to + execute the query and the time to return the data for the query) + for each query sent to the server: + + +function read_query( packet ) + if packet:byte() == proxy.COM_QUERY then + print("we got a normal query: " .. packet:sub(2)) + + proxy.queries:append(1, packet ) + + return proxy.PROXY_SEND_QUERY + end +end + +function read_query_result(inj) + print("query-time: " .. (inj.query_time / 1000) .. "ms") + print("response-time: " .. (inj.response_time / 1000) .. "ms") +end + + + You can access the rows of returned results from the resultset + by accessing the rows property of the resultset property of the + result that is exposed through + read_query_result(). For example, you can + iterate over the results showing the first column from each row + using this LUA fragment: + + +for row in inj.resultset.rows do + print("injected query returned: " .. row[0]) +end + + + Just like read_query(), + read_query_results() can return different + values for each result according to the result returned. If you + have injected additional queries into the query queue, for + example, then you will want to remove the results returned from + those additional queries and only return the results from the + query originally submitted by the client. + + + + The example below injects additional SELECT + NOW() statements into the query queue, given them a + different ID to the ID of the original query. Within + read_query_results(), if the ID for the + injected queries is identified, we display the result row, and + return the proxy.PROXY_IGNORE_RESULT from the + function so that the result is not returned to the client. If + the result is from any other query, we print out the query time + information for the query and return the default, which passes + on the result set unchanged. We could also have explicitly + returned proxy.PROXY_IGNORE_RESULT to the + MySQL client. + + +function read_query( packet ) + if packet:byte() == proxy.COM_QUERY then + proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "SELECT NOW()" ) + proxy.queries:append(1, packet ) + proxy.queries:append(2, string.char(proxy.COM_QUERY) .. "SELECT NOW()" ) + + return proxy.PROXY_SEND_QUERY + end +end + + +function read_query_result(inj) + if inj.id == 2 then + for row in inj.resultset.rows do + print("injected query returned: " .. row[0]) + end + return proxy.PROXY_IGNORE_RESULT + else + print("query-time: " .. (inj.query_time / 1000) .. "ms") + print("response-time: " .. (inj.response_time / 1000) .. "ms") + end +end + + + For further examples, see . + +
@@ -1327,12 +1680,124 @@ Using MySQL Proxy + + There are a number of different ways to use MySQL Proxy. At the + most basic level, you can allow MySQL Proxy to pass on queries + from clients to a single server. To use MySQL proxy in this mode, + you just have to specify the backend server that the proxy should + connect to on the command line: + + +$ mysql-proxy --proxy-backend-addresses=sakila:3306 + + + If you specify multiple backend MySQL servers then the proxy will + connect each client to each server in a round-robin fashion. For + example, imagine you have two MySQL servers, A and B. The first + client to connect will be connected to server A, the second to + server B, the third to server C. For example: + + +$ mysql-proxy \ + --proxy-backend-addresses=narcissus:3306 \ + --proxy-backend-addresses=nostromo:3306 + + + When you have specified multiple servers in this way, the proxy + will automatically identify when a MySQL server has become + unavailable and mark it accordingly. New connections will + automatically be attached to a server that is available, and a + warning will be reported to the standard output from + mysql-proxy: + + +network-mysqld.c.367: connect(nostromo:3306) failed: Connection refused +network-mysqld-proxy.c.2405: connecting to backend (nostromo:3306) failed, marking it as down for ... + + + + LUA scripts enable a finer level of control, both over the + connections and their distribution and how queries and result sets + are processed. When using an LUA script, you must specify the name + of the script on the command line using the + option: + + +$ mysql-proxy --proxy-lua-script=mc.lua --proxy-backend-addresses=sakila:3306 + + + When you specify a script, the script is not executed until a + connection a made. This means that faults with the script will not + be raised until the script is executed. Script faults will not + affect the distribution of queries to backend MySQL servers. + + + + + Because the script is not read until the connection is made, you + can modify the contents of the LUA script file while the proxy + is still running and the script will automatically be used for + the next connection. This ensures that MySQL Proxy remains + available because it does not have to be restarted for the + changes to take effect. + + + + + You can control how clients are distributed among backend servers, + for example, choosing slaves in a master/slave replication + environment, by using an LUA script to manage incoming + connections. See + . + + + + You can also use an LUA script to profile your SQL statements by + adding and/or monitoring the execution of the statements and + reporting extended information about their execution. For more + information, see . + + + + Client connections can be filtered automatically by capturing the + authorization handshake between the client and MySQL server and + automatically denying access before the client packet even reaches + the server. For an example of this in action, see + . + + + + Queries can be modified, either to fix common spelling mistakes or + to add or remove custom information from your queries + automatically. See + . + + + + Supporting alternative commands and queries can enable you to + provide aliases to common used queries and information. See + . + + + + Returning custom result sets can enable to combine or simplify + information, and even to return custom information information + directly from a query. To achieve this you must construct your own + resultset information to be returned to the client. For an example + of this in action, see + . + +
Using the Administration Interface - The MySQL-proxy administration interface can + The mysql-proxy administration interface can be accessed using any MySQL client using the standard protocols. You can use the administration interface to gain information about the proxy server as a whole - standard connections to the @@ -1375,7 +1840,7 @@ | admin.address | :4041 | | proxy.address | :4040 | | proxy.lua_script | mc.lua | -| proxy.backend_addresses[0] | mysql.mcslp.pri:3306 | +| proxy.backend_addresses[0] | mysql:3306 | | proxy.fix_bug_25371 | 0 | | proxy.profiling | 1 | +----------------------------+----------------------+ @@ -1383,19 +1848,66 @@
- +
+ + Using MySQL Proxy for Modifying Queries + + + Using MySQL Proxy for Modifying Queries + + +
+ +
+ + Using MySQL Proxy for Creating Custom Result Sets + + + Using MySQL Proxy for Creating Custom Result Sets + + +
+ +
+ + Using MySQL Proxy for Alternative Commands + + + Using MySQL Proxy as a solution creating alternative commands + + +
+ +