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

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 Zeus Traffic Manager 5.1 or 6.0 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

The quick solution (might work )

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 audit and rewrite, as described by MySQL.

You will need to direct the 'Update' connections to the master database (or through a dedicated Zeus virtual server), and direct the 'Read' connections to a Zeus 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. In most cases, all connections are directed to a single IP:port. A load balancer will need to discriminate between different connection types and route them accordingly.

Designing a MySQL proxy

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

For more information, refer to the article "Comparing client-first and server-first 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:

sub encpassword( $user ) {
   # From the mysql users table - double-SHA1 of the password
   # Do not include the leading '*' in the long 40-byte encoded password
   if( $user == "mysqlmaster" ) return "B17453F89631AE57EFC1B401AD1C7A59EFD547E5";
   if( $user == "mysqlslave" )  return "14521EA7B4C66AE94E6CFF753453F89631AE57EF";
}

sub pool( $user ) {
   if( $user == "mysqlmaster" ) return "SQL Master";
   if( $user == "mysqlslave" )  return "SQL Slaves";
}


$state = connection.data.get( "state" );

if( !$state ) {
   # First time in; we've just recieved a fresh connection

   $salt1 = randomBytes( 8 );
   $salt2 = randomBytes( 12 );   

   connection.data.set( "salt", $salt1.$salt2 );

   $server_hs = "\0\0\0\0" .           # length - fill in below
       "\012" .                        # protocol version
       "Zeus Proxy v0.9\0" .           # server version
       "\01\0\0\0" .                   # thread 1
       $salt1."\0" .                   # salt(1)
       "\054\242" .                    # Capabilities
       "\010\02\0" .                   # Lang and status
       "\0\0\0\0\0\0\0\0\0\0\0\0\0" .  # Unused
       $salt2."\0";                    # salt(2)

   $l = string.length( $server_hs )-4; # Will be <= 255
   $server_hs = string.replaceBytes( $server_hs, string.intToBytes( $l, 1 ), 0 );

   connection.data.set( "state", "wait for clienths" );
   request.sendResponse( $server_hs );

   break;
}

if( $state == "wait for clienths" ) {
   # We've recieved the client handshake.

   $chs = request.get( 1 );
   $chs_len = string.bytesToInt( $chs );
   $chs = request.get( $chs_len + 4 );

   # user starts at byte 36; password follows after
   $i = string.find( $chs, "\0", 36 );
   $user = string.subString( $chs, 36, $i-1 );
   $encpasswd = string.subString( $chs, $i+2, $i+21 );

   $passwd2 = string.hexDecode( encpassword( $user ) );
   
   $salt = connection.data.get( "salt" );
   $passwd1 = string_xor( $encpasswd, string.hashSHA1( $salt.$passwd2 ) );

   if( string.hashSHA1( $passwd1 ) != $passwd2 ) {
      log.warn( "User '" . $user . "': authentication failure" );
      connection.data.set( "state", "authentication failed" );
      connection.discard();
   }

   connection.data.set( "user",     $user );
   connection.data.set( "passwd1",  $passwd1 );
   connection.data.set( "clienths", $chs );

   connection.data.set( "state", "wait for serverhs" );
   request.set( "" );

   # Select pool based on user
   pool.select( pool( $user ) );
   
   break;
}

if( $state == "wait for client data" ) {
   # Write the client handshake we remembered from earlier to the server,
   # and piggyback the request we've just recieved on the end

   $req = request.get();

   $chs     = connection.data.get( "clienths" );
   $passwd1 = connection.data.get( "passwd1" );   
   $salt    = connection.data.get( "salt" );

   $encpasswd = string_xor( $passwd1, 
       string.hashSHA1( $salt . string.hashSHA1( $passwd1 ) ) );

   $i = string.find( $chs, "\0", 36 );
   $chs = string.replaceBytes( $chs, $encpasswd, $i+2 ); 

   connection.data.set( "state", "do authentication" );
   request.set( $chs.$req );

   break;
}


# Helper function

sub string_xor( $a, $b ) {
   $r = "";
   while( string.length( $a ) ) {
      $a1 = string.left( $a, 1 ); $a = string.skip( $a, 1 );
      $b1 = string.left( $b, 1 ); $b = string.skip( $b, 1 );

      $r = $r . chr( ord( $a1 ) ^ ord ( $b1 ) );
   }
   return $r;
}

The response rule...

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

$state = connection.data.get( "state" );

$authok = "\07\0\0\2\0\0\0\02\0\0\0";
   
if( $state == "wait for serverhs" ) {
   # Read server handshake, remember the salt

   $shs = response.get( 1 );
   $shs_len = string.bytesToInt( $shs )+4;
   $shs = response.get( $shs_len );

   $salt1 = string.substring( $shs, $shs_len-40, $shs_len-33 );
   $salt2 = string.substring( $shs, $shs_len-13, $shs_len-2 );

   connection.data.set( "salt", $salt1.$salt2 );

   # Write an authentication confirmation now to provoke the client
   # to send us more data (the first query).  This will prepare the
   # state machine to write the authentication to the server
   connection.data.set( "state", "wait for client data" );
   response.set( $authok );

   break;
}

if( $state == "do authentication" ) {
   # We're expecting two responses. 
   # The first is the authentication confirmation which we discard.

   $res  = response.get();
   $res1 = string.left( $res, 11 );
   $res2 = string.skip( $res, 11 );

   if( $res1 != $authok ) {
      $user = connection.data.get( "user" );
      log.info( "Unexpected authentication failure for " . $user );
      connection.discard();
   }

   connection.data.set( "state", "complete" );
   response.set( $res2 );

   break;
}

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 Zeus UI to see how Zeus has connected each session to a back-end database server.

Zeus'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 Zeus-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, Zeus 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 Zeus proxy. You may need to GRANT... TO 'user'@'%' for example.

Owen Garrett [Zeus Dev Team] 07 July 2008 7 comments