Building a load-balancing MySQL proxy with TrafficScript

When you need to scale out your MySQL database, replication is a good way to proceed. Database writes (UPDATEs) go to a 'master' server and are replicated across a set of 'slave' servers. Reads (SELECTs) are load-balanced across the slaves.

Overview

MySQL's replication documentation describes how to configure replication:

MySQL Replication

A quick solution...

If you can modify your MySQL client application to direct 'Write' (i.e. 'UPDATE') connections to one IP address/port and 'Read' (i.e. 'SELECT') connections to another, then this problem is trivial to solve. This generally needs a code update (Using Replication for Scale-Out).

You will need to direct the 'Update' connections to the master database (or through a dedicated Stingray virtual server), and direct the 'Read' connections to a Stingray virtual server (in 'generic server first' mode) and load-balance the connections across the pool of MySQL slave servers using the 'least connections' load-balancing method:

Routing connections from the application

However, in most cases, you probably don't have that degree of control over how your client application issues MySQL connections; all connections are directed to a single IP:port. A load balancer will need to discriminate between different connection types and route them accordingly.

Routing MySQL traffic

A MySQL database connection is authenticated by a username and password. In most database designs, multiple users with different access rights are used; less privileged user accounts can only read data (issuing 'SELECT' statements), and more privileged users can also perform updates (issuing 'UPDATE' statements).

A well architected application with sound security boundaries will take advantage of these multiple user accounts, using the account with least privilege to perform each operation. This reduces the opportunities for attacks like SQL injection to subvert database transactions and perform undesired updates.

This article describes how to use Stingray Traffic Manager to inspect and manage MySQL connections, routing connections authenticated with privileged users to the master database and load-balancing other connects to the slaves:

Load-balancing MySQL connections

Designing a MySQL proxy

Stingray Traffic Manager functions as an application-level (layer-7) proxy. Most protocols are relatively easy for layer-7 proxies like Stingray to inspect and load-balance, and work 'out-of-the-box' or with relatively little configuration.

For more information, refer to the article Feature Brief: Server First, Client First and Generic Streaming Protocols.

Proxying MySQL connections

MySQL is much more complicated to proxy and load-balance.

When a MySQL client connects, the server immediately responds with a randomly generated challenge string (the 'salt'). The client then authenticates itself by responding with the username for the connection and a copy of the 'salt' encrypted using the corresponding password:

Connect and Authenticate in MySQL

If the proxy is to route and load-balance based on the username in the connection, it needs to correctly authenticate the client connection first. When it finally connects to the chosen MySQL server, it will then have to re-authenticate the connection with the back-end server using a different salt.

Implementing a MySQL proxy in TrafficScript

In this example, we're going to proxy MySQL connections from two users - 'mysqlmaster' and 'mysqlslave', directing connections to the 'SQL Master' and 'SQL Slaves' pools as appropriate.

The proxy is implemented using two TrafficScript rules ('mysql-request' and 'mysql-response') on a 'server-first' Virtual Server listening on port 3306 for MySQL client connections. Together, the rules implement a simple state machine that mediates between the client and server:

Implementing a MySQL proxy in TrafficScript

The state machine authenticates and inspects the client connection before deciding which pool to direct the connection to. The rule needs to know the encrypted password and desired pool for each user. The virtual server should be configured to send traffic to the built-in 'discard' pool by default.

The request rule:

Configure the following request rule on a 'server first' virtual server. Edit the values at the top to reflect the encrypted passwords (copied from the MySQL users table) and desired pools:

1. sub encpassword( $user ) {  
2.    # From the mysql users table - double-SHA1 of the password  
3.    # Do not include the leading '*' in the long 40-byte encoded password  
4.    if( $user == "mysqlmaster" ) return "B17453F89631AE57EFC1B401AD1C7A59EFD547E5";  
5.    if( $user == "mysqlslave" )  return "14521EA7B4C66AE94E6CFF753453F89631AE57EF";  
6. }  
7.   
8. sub pool( $user ) {  
9.    if( $user == "mysqlmaster" ) return "SQL Master";  
10.    if( $user == "mysqlslave" )  return "SQL Slaves";  
11. }  
12.   
13. $state = connection.data.get( "state" );  
14.   
15. if( !$state ) {  
16.    # First time in; we've just recieved a fresh connection  
17.    $salt1 = randomBytes( 8 );  
18.    $salt2 = randomBytes( 12 );    
19.    connection.data.set( "salt", $salt1.$salt2 );  
20.   
21.   
22.    $server_hs = "\0\0\0\0" .           # length - fill in below  
23.        "\012" .                        # protocol version  
24.        "Stingray Proxy v0.9\0" .           # server version  
25.        "\01\0\0\0" .                   # thread 1  
26.        $salt1."\0" .                   # salt(1)  
27.        "\054\242" .                    # Capabilities  
28.        "\010\02\0" .                   # Lang and status  
29.        "\0\0\0\0\0\0\0\0\0\0\0\0\0" .  # Unused  
30.        $salt2."\0";                    # salt(2)  
31.   
32.    $l = string.length( $server_hs )-4; # Will be <= 255  
33.    $server_hs = string.replaceBytes( $server_hs, string.intToBytes( $l, 1 ), 0 );  
34.   
35.    connection.data.set( "state", "wait for clienths" );  
36.    request.sendResponse( $server_hs );  
37.   
38.    break;  
39. }  
40.   
41. if( $state == "wait for clienths" ) {  
42.    # We've recieved the client handshake.  
43.    $chs = request.get( 1 );  
44.    $chs_len = string.bytesToInt( $chs );  
45.    $chs = request.get( $chs_len + 4 );  
46.   
47.    # user starts at byte 36; password follows after  
48.    $i = string.find( $chs, "\0", 36 );  
49.    $user = string.subString( $chs, 36, $i-1 );  
50.    $encpasswd = string.subString( $chs, $i+2, $i+21 );  
51.   
52.    $passwd2 = string.hexDecode( encpassword( $user ) );  
53.     
54.    $salt = connection.data.get( "salt" );  
55.    $passwd1 = string_xor( $encpasswd, string.hashSHA1( $salt.$passwd2 ) );  
56.   
57.    if( string.hashSHA1( $passwd1 ) != $passwd2 ) {  
58.       log.warn( "User '" . $user . "': authentication failure" );  
59.       connection.data.set( "state", "authentication failed" );  
60.       connection.discard();  
61.    }  
62.   
63.    connection.data.set( "user",     $user );  
64.    connection.data.set( "passwd1",  $passwd1 );  
65.    connection.data.set( "clienths", $chs );  
66.   
67.    connection.data.set( "state", "wait for serverhs" );  
68.    request.set( "" );  
69.   
70.    # Select pool based on user  
71.    pool.select( pool( $user ) );  
72.     
73.    break;  
74. }  
75.   
76. if( $state == "wait for client data" ) {  
77.    # Write the client handshake we remembered from earlier to the server,  
78.    # and piggyback the request we've just recieved on the end  
79.    $req = request.get();  
80.   
81.   
82.    $chs     = connection.data.get( "clienths" );  
83.    $passwd1 = connection.data.get( "passwd1" );    
84.    $salt    = connection.data.get( "salt" );  
85.   
86.    $encpasswd = string_xor( $passwd1,  
87.        string.hashSHA1( $salt . string.hashSHA1( $passwd1 ) ) );  
88.   
89.    $i = string.find( $chs, "\0", 36 );  
90.    $chs = string.replaceBytes( $chs, $encpasswd, $i+2 );  
91.   
92.    connection.data.set( "state", "do authentication" );  
93.    request.set( $chs.$req );  
94.   
95.    break;  
96. }  
97.   
98. # Helper function  
99.   
100. sub string_xor( $a, $b ) {  
101.    $r = "";  
102.    while( string.length( $a ) ) {  
103.       $a1 = string.left( $a, 1 ); $a = string.skip( $a, 1 );  
104.       $b1 = string.left( $b, 1 ); $b = string.skip( $b, 1 );  
105.   
106.       $r = $r . chr( ord( $a1 ) ^ ord ( $b1 ) );  
107.    }  
108.    return $r;  
109. }  

The response rule

Configure the following as a response rule, set to run every time, for the MySQL virtual server.

1. $state = connection.data.get( "state" );  
2.   
3. $authok = "\07\0\0\2\0\0\0\02\0\0\0";  
4.     
5. if( $state == "wait for serverhs" ) {  
6.    # Read server handshake, remember the salt  
7.    $shs = response.get( 1 );  
8.    $shs_len = string.bytesToInt( $shs )+4;  
9.    $shs = response.get( $shs_len );  
10.   
11.    $salt1 = string.substring( $shs, $shs_len-40, $shs_len-33 );  
12.    $salt2 = string.substring( $shs, $shs_len-13, $shs_len-2 );  
13.   
14.    connection.data.set( "salt", $salt1.$salt2 );  
15.   
16.    # Write an authentication confirmation now to provoke the client  
17.    # to send us more data (the first query).  This will prepare the  
18.    # state machine to write the authentication to the server  
19.    connection.data.set( "state", "wait for client data" );  
20.    response.set( $authok );  
21.   
22.    break;  
23. }  
24.   
25. if( $state == "do authentication" ) {  
26.    # We're expecting two responses.  
27.    # The first is the authentication confirmation which we discard.  
28.    $res  = response.get();  
29.    $res1 = string.left( $res, 11 );  
30.    $res2 = string.skip( $res, 11 );  
31.   
32.    if( $res1 != $authok ) {  
33.       $user = connection.data.get( "user" );  
34.       log.info( "Unexpected authentication failure for " . $user );  
35.       connection.discard();  
36.    }  
37.   
38.    connection.data.set( "state", "complete" );  
39.    response.set( $res2 );  
40.   
41.    break;  
42. }  

Testing your configuration

If you have several MySQL databases to test against, testing this configuration is straightforward. Edit the request rule to add the correct passwords and pools, and use the mysql command-line client to make connections:

$ mysql -h zeus -u username -p

Enter password: *******

Check the 'current connections' list in the Stingray UI to see how Stingray has connected each session to a back-end database server.

If you encounter problems, try the following steps:

• Ensure that trafficscript!variable_pool_use is set to 'Yes' in the Global Settings page on the UI. This setting allows you to use non-literal values in pool.use() and pool.select() TrafficScript functions.
• Turn on the log!client_connection_failures and log!server_connection_failures settings in the Virtual Server > Connection Management configuration page; these settings will configure the traffic manager to write detailed debug messages to the Event Log whenever a connection fails.

Then review your Traffic Manager Event Log and your mysql logs in the event of an error.

Stingray's access logging can be used to record every connection. You can use the special *{name}d log macro to record information stored using connection.data.set(), such as the username used in each connection.

Conclusion

This article has demonstrated how to build a fairly sophisticated protocol parser where the Stingray-based proxy performs full authentication and inspection before making a load-balancing decision. The protocol parser then performs the authentication again against the chosen back-end server.

Once the client-side and server-side handshakes are complete, Stingray will simply forward data back and fro between the client and the server.

This example addresses the problem of scaling out your MySQL database, giving load-balancing and redundancy for database reads ('SELECTs'). It does not address the problem of scaling out your master 'write' server - you need to address that by investing in a sufficiently powerful server, architecting your database and application to minimise the number and impact of write operations, or by selecting a full clustering solution.

The solution leaves a single point of failure, in the form of the master database. This problem could be effectively dealt with by creating a monitor that tests the master database for correct operation. If it detects a failure, the monitor could promote one of the slave databases to master status and reconfigure the 'SQLMaster' pool to direct write (UPDATE) traffic to the new MySQL master server.

Acknowledgements

Ian Redfern's MySQL protocol description was invaluable in developing the proxy code.

Appendix - Password Problems?

This example assumes that you are using MySQL 4.1.x or later (it was tested with MySQL 5 clients and servers), and that your database has passwords in the 'long' 41-byte MySQL 4.1 (and later) format (see http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html)

If you upgrade a pre-4.1 MySQL database to 4.1 or later, your passwords will remain in the pre-4.1 'short' format.

You can verify what password format your MySQL database is using as follows:

mysql> select password from mysql.user where user='username';

+------------------+

| password         |

+------------------+

| 6a4ba5f42d7d4f51 |

+------------------+

1 rows in set (0.00 sec)

mysql> update mysql.user set password=PASSWORD('password') where user='username';

Query OK, 1 rows affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select password from mysql.user where user='username';

+-------------------------------------------+

| password                                  |

+-------------------------------------------+

| *14521EA7B4C66AE94E6CFF753453F89631AE57EF |

+-------------------------------------------+

1 rows in set (0.00 sec)

If you can't create 'long' passwords, your database may be stuck in 'short' password mode. Run the following command to resize the password table if necessary:

$ mysql_fix_privilege_tables --password=admin password

Check that 'old_passwords' is not set to '1' (see here) in your my.cnf configuration file.

Check that the mysqld process isn't running with the --old-passwords option.

Finally, ensure that the privileges you have configured apply to connections from the Stingray proxy. You may need to GRANT... TO 'user'@'%' for example.