If you follow my blog or twitter stream you might know I've recently been at Barcelona to attend the PHP Barcelona conference. Conferences are great for exchanging ideas one of the ideas I discussed with Combell's Thijs Feryn: They are a hosting company providing managed MySQL instances to their customers, as such they run multiple MySQL servers and each server serves a few of their customers. Now they have to provide every customer with database credentials, including a host name to connect to. The issue there is that a fixed hostname takes flexibility out of the setup. Say you have db1.example.com and db2.example.com over time you figure out that there are two high load customers on db1 while db2 is mostly idle. You might want to move the data from one customer over to db2 to share the load. This means you have to ask the customer to change his application configuration at the time you're moving the data. Quite annoying task.
Now there's a solution: MySQL Proxy. The proxy is a daemon sitting in between of the application/web servers and MySQL something like in the picture below.
The proxy can be scripted using lua so it is not too hard to implement a feature which chooses the database server to actually connect to. The customer is then told to connect to the proxy and depending on the username given he is redirected to a specific system. All magic happens transparent in the background. This is nice but not without issues: There is one more daemon to monitor, the proxy sitting in between adds latency, and so on.
In case you attended a recent talk by Ulf or me you certainly learned about mysqlnd plugins. We always compare mysqlnd plugins with the MySQL Proxy, so let's take a closer look: The plugins are PHP extensions, usually written in C, hooking into mysqlnd, the native driver for PHP, overriding parts of mysqlnd's internals. mysqlnd, introduced in PHP 5.3, is the implementation of the MySQL Client-Server-Protocol sitting invisible below the PHP extensions ext/mysql, mysqli and PDO_mysql. This means any plugin to mysqlnd can transparently change the behavior without an changes to the actual application.
Now with this plugin facility we can move the code for the server selection from the proxy directly in PHP. By doing this we will have almost no overhead and due to the deep integration less work for monitoring and no additional fault component.
So let's look in the implementation of such a simple plugin: The goal is having an extension which overrides the server name given by the user by one set in a special configuration file so the user is transparently redirected. The configuration file format used is a INI file. As said above a mysqlnd plugin is a regular PHP extension, even though we usually won't export functions to PHP userland. A quick note before we really start: I won't discuss all parts of the PHP API in detail, please see the resources linked below for more on that.
The first thing PHP looks at while loading an extension is a module entry. In our case there is one special thing: We add a dependency to mysqlnd, to make sure mysqlnd was initialised before this extension is initialised. You can also see that I have chosen the name mysqlnd_server_locator.
static const zend_module_dep mysqlnd_server_locator_deps[] = {
ZEND_MOD_REQUIRED("mysqlnd")
{NULL, NULL, NULL}
};
zend_module_entry mysqlnd_server_locator_module_entry = {
STANDARD_MODULE_HEADER_EX,
NULL,
mysqlnd_server_locator_deps,
"mysqlnd_server_locator",
NULL,
PHP_MINIT(mysqlnd_server_locator),
PHP_MSHUTDOWN(mysqlnd_server_locator),
NULL,
NULL,
NULL,
"0.1",
STANDARD_MODULE_PROPERTIES
};
On PHP startup the module initializer, MINIT, is being called. We want to override the connect method from mysqlnd's connection related functions. Additionally I initialize a HashTable which will hold the translation table.
static int plugin_id;
static func_mysqlnd_conn__connect orig_mysqlnd_conn_connect_method;
static HashTable server_list;
static int server_list_init = 0;
PHP_MINIT_FUNCTION(mysqlnd_server_locator)
{
struct st_mysqlnd_conn_methods *conn_methods;
plugin_id = mysqlnd_plugin_register();
conn_methods = mysqlnd_conn_get_methods();
orig_mysqlnd_conn_connect_method = conn_methods->connect;
conn_methods->connect = MYSQLND_METHOD(mysqlnd_server_locator, connect);
if (zend_hash_init(&server_list, 10, NULL, free, 1) == FAILURE) {
php_error_docref(NULL TSRMLS_CC, E_WARNING, "Failed to init server_list table");
return FAILURE;
}
return SUCCESS;
}
One thing to note here is that I don't actually load the translation table, yet. This is due to issues I had while using the ini scanner during PHP's initialization phase and having the mechanism to load it later has the benefit of being ale to update the table without having to restart PHP. Anyways the above function should be relatively clear. We tell mysqlnd that a plugin is around, store the connection method pointer in a safe place and set our own connection method and then init the HashTable.
During PHP shutdown we will free this table again:
PHP_MSHUTDOWN_FUNCTION(mysqlnd_server_locator)
{
zend_hash_destroy(&server_list);
return SUCCESS;
}
Now let's look at the implementation of the overridden connect method. At first this looks complex as it takes tons of parameters but we simply pass them through and don't have to care about them. All we care about are two things: Firstly we make sure the the translation table was initilised, then we look for the username in the table, if the user exists in the table we take the hostname given in the table, else we connect to the host requested by the user.
static enum_func_status MYSQLND_METHOD(mysqlnd_server_locator, connect)(MYSQLND * conn,
const char *host, const char *user,
const char *passwd, unsigned int passwd_len,
const char *db, unsigned int db_len,
unsigned int port,
const char * socket_or_pipe,
unsigned int mysql_flags
TSRMLS_DC)
{
char **new_host;
char *actual_host = host;
if (!server_list_init) {
mysqlnd_server_locator_init_server_list(TSRMLS_C);
server_list_init = 1;
}
if (zend_hash_find(&server_list, user, strlen(user) + 1, (void**)&new_host) == SUCCESS) {
actual_host = *new_host;
}
return orig_mysqlnd_conn_connect_method(conn, actual_host, user, passwd, passwd_len, db, db_len, port, socket_or_pipe, mysql_flags TSRMLS_CC);
}
Please note that this method is not thread-safe and should, in this form, only be used in non-threaded environments. This is fixed in a version linked below, which also does one more thing: It will always check whether the ini file was modified since we read it, but let's keep it simple here. As said the configuration is a ini file which simply consists of username=host pairs:
johannes=db1.example.com guybrush=db1.example.com sam=db2.example.com max=db2.example.com bernard=db1.example.com
Such files can be parsed by PHP, I won't go into the details of the implementation here.
static void mysqlnd_server_locator_ini_parser_cb(zval *arg1, zval *arg2, zval *arg3, int callback_type, void *list_v TSRMLS_DC)
{
HashTable *list = (HashTable*)list_v;
char *hostname;
if (!arg1 || !arg2) {
return;
}
switch (callback_type)
{
case ZEND_INI_PARSER_ENTRY:
hostname = pestrndup(Z_STRVAL_P(arg2), Z_STRLEN_P(arg2), 1);
zend_hash_update(list, Z_STRVAL_P(arg1), Z_STRLEN_P(arg1) + 1, &hostname, sizeof(char *), NULL);
break;
case ZEND_INI_PARSER_SECTION:
break;
case ZEND_INI_PARSER_POP_ENTRY:
php_error_docref(NULL TSRMLS_CC, E_NOTICE, "Array syntax not allowed in ini file");
break;
default:
php_error_docref(NULL TSRMLS_CC, E_NOTICE, "Unexpected callback_type while parsing server list ini file");
break;
}
}
static int mysqlnd_server_locator_init_server_list(TSRMLS_D)
{
zend_file_handle fh;
memset(&fh, 0, sizeof(fh));
fh.filename = "/tmp/server.ini";
fh.type = ZEND_HANDLE_FILENAME;
if (zend_parse_ini_file(&fh, 0, ZEND_INI_SCANNER_NORMAL, mysqlnd_server_locator_ini_parser_cb, &server_list TSRMLS_CC) == FAILURE) {
php_error_docref(NULL TSRMLS_CC, E_WARNING, "Failed to parse server list ini file");
return FAILURE;
}
return SUCCESS;
}
And that's it. Now let's have a look at some PHP code running while this extension is loaded:
$ php -r 'mysql_connect("loalhost", "johannes", "supersecretpasswordforthis");'
Warning: mysql_connect(): php_network_getaddresses: getaddrinfo failed: node name or
service name not known in Command line code on line 1
Warning: mysql_connect(): [2002] php_network_getaddresses: getaddrinfo failed: node
name or servi (trying to connect via tcp://db1.example.com:3306) in Command line code on line 1
Neat, isn't it? - I also packaged this code in an slightly improved version. This version uses a php.ini setting for configuring the location of the extension's ini file, solves the threading issue mentioned above and automatically reloads the configuration file in case it was changed. Note that this code comes for educational purpose as-is only and I take no responsibility of any form.
This won't solve all problem's in the case of Combell as they want to provide external access or access from other applications, too. But I could imagine a solution using such a plugin for PHP as the overhead is minimal (in the version above one hash lookup, in the download version one hash lookup and a, well cached, stat call during connect which both can be neglected) and a proxy-based solution for other systems.
Some more resources: