Monday, January 26, 2015

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:

mysql1.png
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:

mysql2.pngRouting 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:

mysql3.png
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:

mysql4.png
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:

mysql5.png
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( $l1 ), 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( $chs36$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( $a1 ); $a = string.skip( $a1 );  
  104.       $b1 = string.left( $b1 ); $b = string.skip( $b1 );  
  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( $res11 );  
  30.    $res2 = string.skip( $res11 );  
  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.