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 Traffic Manager virtual server), and direct the 'Read' connections to a Traffic Manager 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
ort. 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 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 Traffic Manager to inspect and load-balance, and work 'out-of-the-box' or with relatively little configuration.
For more information, refer to the article 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:
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
"Stingray 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 Traffic Manager UI to see how it 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.
Traffic Manager'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 Traffic Manager-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, Traffic Manager 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.
