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:
- sub encpassword( $user ) {
-
-
- 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 ) {
-
- $salt1 = randomBytes( 8 );
- $salt2 = randomBytes( 12 );
- connection.data.set( "salt", $salt1.$salt2 );
-
-
- $server_hs = "\0\0\0\0" .
- "\012" .
- "Stingray Proxy v0.9\0" .
- "\01\0\0\0" .
- $salt1."\0" .
- "\054\242" .
- "\010\02\0" .
- "\0\0\0\0\0\0\0\0\0\0\0\0\0" .
- $salt2."\0";
-
- $l = string.length( $server_hs )-4;
- $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" ) {
-
- $chs = request.get( 1 );
- $chs_len = string.bytesToInt( $chs );
- $chs = request.get( $chs_len + 4 );
-
-
- $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( "" );
-
-
- pool.select( pool( $user ) );
-
- break;
- }
-
- if( $state == "wait for client data" ) {
-
-
- $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;
- }
-
-
-
- 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" ) {
-
- $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 );
-
-
-
-
- connection.data.set( "state", "wait for client data" );
- response.set( $authok );
-
- break;
- }
-
- if( $state == "do authentication" ) {
-
-
- $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 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.
