<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"> <head> <meta http-equiv="Content-type" content="text/html; charset=utf-8" /> <meta http-equiv="Content-Language" content="en-us" /> <meta name="ROBOTS" content="ALL" /> <meta http-equiv="imagetoolbar" content="no" /> <meta name="MSSmartTagsPreventParsing" content="true" /> <meta name="Keywords" content="cherokee web server httpd http" /> <meta name="Description" content="Cherokee is a flexible, very fast, lightweight Web server. It is implemented entirely in C, and has no dependencies beyond a standard C library. It is embeddable and extensible with plug-ins. It supports on-the-fly configuration by reading files or strings, TLS/SSL (via GNUTLS or OpenSSL), virtual hosts, authentication, cache friendly features, PHP, custom error management, and much more." /> <link href="media/css/cherokee_doc.css" rel="stylesheet" type="text/css" media="all" /> </head> <body> <h2 id="_a_href_index_html_index_a_8594_a_href_cookbook_html_cookbook_a"><a href="index.html">Index</a> → <a href="cookbook.html">Cookbook</a></h2> <div class="sectionbody"> </div> <h2 id="_cookbook_how_to_set_up_dbslayer_mysql_balancing">Cookbook: How to set up DBSlayer MySQL balancing</h2> <div class="sectionbody"> <h3 id="introduction">Introduction:</h3><div style="clear:left"></div> <div class="paragraph"><p>This recipe is meant to help in the task of setting up and understanding DBSlayer. This is a handler used to balance the load among several MySQL databases acting as a convenient front end that simultaneously offers great scalability advantages. Refer to the <a href="modules_handlers_dbslayer.html">MySQL Bridge handler</a> documentation for the basic information about this subject.</p></div> <div class="paragraph"><p>After reading this document you should be able to properly configure and understand this handler. It acts as a proxy for a back end MySQL server (or cluster of server). This proxy can then be queried via JSON over HTTP, and the responses can be given in either one of the following supported consuming languages: JSON, Ruby, PHP and Python, which makes processing the database results almost trivial.</p></div> <div class="paragraph"><p>As more than one SQL request can be issued at once -a transaction, for instance-, the responses are returned as a list of results.</p></div> <h3 id="independence">Independence:</h3><div style="clear:left"></div> <div class="paragraph"><p>It is generally a good idea to separate the database balancer from your production web server, both for efficiency and security reasons. The DBSlayer handler can be configured to run under a directory in your current web server, but concentrating the work load on the same box is not recommended. Setting up an independent Cherokee server on another box or port using only the dbslayer handler in its Default rule is a good idea. This will allow having your database balancer fully isolated, which is by far much more secure and easy to monitor and manage.</p></div> <div class="paragraph"><p>Keep in mind that each DBSlayer handler can manage a single database (or a set of replicated slaves to balance among). Thus you will have to set up multiple rules in Cherokee, or even multiple instances in different ports, in case you need to use several databases.</p></div> <div class="paragraph"><p>The handler can be used to perform both read and write operations, although the balancing part only makes sense for SELECT operations. When performing INSERT operations, keep in mind that DB synchronization must be managed elsewhere. When you perform an INSERT, the Cherokee DB balancer will return a tuple indicating whether it succeeded or not, the number of rows affected, a rollback flag, etc. It will not, however, replicate the operation among each one of you MySQL servers. These arrangements must be made independently using mechanisms suitable for the task.</p></div> <div class="paragraph"><p>Remember that balancing database operations is a powerful scaling advantage, but requires some precautions. You must assume the system is stateless. It is not much of a limitation, and it enables the handler to do connection pooling and database access dispatching. MySQL is mostly stateless for the bulk of your queries (SELECT / UPDATE / INSERT / DELETE, etc.), but some commands are meant to affect future commands in the session or may only affect the server currently receiving the command in the balancing scheme. Most notably, these are setting global or session variables, configuring database access privileges and modifying the table structure on a master without replication.</p></div> <div class="paragraph"><p>Also, due to the balancing scheme in place, you should be very careful when locking tables or performing transactions. In these cases you <strong>MUST</strong> perform all the related operations contained within a single query, either using semicolon separators or within a stored procedure. Never dispatch them as separate queries, since your databases will rapidly become unstable.</p></div> <h3 id="security">Security:</h3><div style="clear:left"></div> <div class="paragraph"><p>As for security concerns, you must keep in mind that the handler is designed for internal secure networks. If you have concerns about exposing your database to the public you will have to properly secure your internal network by using firewalling, binding the running Cherokee instance to a particular interface, tunnelizing your connections and so forth.</p></div> <div class="paragraph"><p>We strongly recommend never exposing the MySQL bridge to the outside world. It is also encouraged to forbid the account used by the handler to access the MySQL database from executing dangerous operations like dropping tables or deleting rows. Ideally, the account would only be allowed to run selects and/or some stored procedures. This is totally unrelated to Cherokee and the Database Bridge handler. Using the mechanisms provided by MySQL to enforce the integrity of the database makes sense.</p></div> <h3 id="modularity">Modularity:</h3><div style="clear:left"></div> <div class="paragraph"><p>The MySQL bridge does not work alone. It is an integral part of the Cherokee web server infrastructure and, like any Cherokee module, it can make use of the rest of the features provided by the web server.</p></div> <div class="paragraph"><p>Thus, you can use the normal loggers to log database access, you can encode the contents served to save bandwidth, you can configure the bridge to use any balancing mechanism provided by Cherokee, etc.</p></div> <div class="paragraph"><p>This handler effectively proxies the connections to the databases, and thus Keep-Alive should always remain enabled in the Cherokee instance running the MySQL bridge. Persistence will be correctly handled, and the performance will be optimal.</p></div> <h3 id="replication">Replication:</h3><div style="clear:left"></div> <div class="paragraph"><p>Odds are you want to use a M-M set-up, although that’s entirely related to your database architecture and is something you’ll have to study carefully.</p></div> <div class="paragraph"><p>Remember what was said before: The MySQL bridge handler will not manage the replication among each one of you MySQL servers. These arrangements must be made independently using mechanisms suitable for the task.</p></div> <h3 id="configuration">Configuration:</h3><div style="clear:left"></div> <div class="paragraph"><p>We will configure a simple setup and show a very basic usage example so that you can go on from there. This example will use a dedicated box running the Cherokee server with the MySQL bridge handler, and will be balancing the load among a couple of independent MySQL servers that are replicating the database in a master-master scheme. You could use only one machine to do all the tasks, run the database and Cherokee managing web pages and the MySQL bridge, but keep in mind what was said above about isolating the boxes and the security measures that you should take into account when migrating to a production environment. Isolation is generally a good idea.</p></div> <div class="paragraph"><p>First, we edit the <tt>default</tt> virtual server. Since we will only be using it for database balancing, we can safely point it to an empty <tt>Document Root</tt>, in this case <tt>/dev/null</tt>.</p></div> <div class="imageblock"> <div class="content"> <img src="media/images/cookbook_dbslayer1.png" alt="media/images/cookbook_dbslayer1.png" /> </div> </div> <div class="paragraph"><p>The next step will be setting up the sources of the MySQL information. This means we will have to configure as many MySQL hosts as we like, and the handler will balance the load among them. In this case we will be providing a couple of hosts, each one located in our secure intranet.</p></div> <div class="paragraph"><p>Refer to the <a href="config_info_sources.html">information sources</a> section for details on how to do this. In this case, the MySQL servers are running on port 3306 of the hosts 10.0.0.100 and 10.0.0.101, so that is what we will use.</p></div> <div class="imageblock"> <div class="content"> <img src="media/images/cookbook_dbslayer2.png" alt="media/images/cookbook_dbslayer2.png" /> </div> </div> <div class="tableblock"> <table rules="all" width="100%" frame="border" cellspacing="0" cellpadding="4"> <caption class="title">Source #1</caption> <col width="50%" /> <col width="50%" /> <tbody> <tr> <td align="left" valign="top"><p class="table">Type</p></td> <td align="left" valign="top"><p class="table">Remote source</p></td> </tr> <tr> <td align="left" valign="top"><p class="table">Nick</p></td> <td align="left" valign="top"><p class="table">mysql_0</p></td> </tr> <tr> <td align="left" valign="top"><p class="table">Connection</p></td> <td align="left" valign="top"><p class="table">10.0.0.100:3306</p></td> </tr> </tbody> </table> </div> <div class="tableblock"> <table rules="all" width="100%" frame="border" cellspacing="0" cellpadding="4"> <caption class="title">Source #2</caption> <col width="50%" /> <col width="50%" /> <tbody> <tr> <td align="left" valign="top"><p class="table">Type</p></td> <td align="left" valign="top"><p class="table">Remote source</p></td> </tr> <tr> <td align="left" valign="top"><p class="table">Nick</p></td> <td align="left" valign="top"><p class="table">mysql_1</p></td> </tr> <tr> <td align="left" valign="top"><p class="table">Connection</p></td> <td align="left" valign="top"><p class="table">10.0.0.101:3306</p></td> </tr> </tbody> </table> </div> <div class="paragraph"><p>Next is removing every possible rule and configuring the default one to use the MySQL bridge handler. This is not required, but obeys the recommendations stated above.</p></div> <div class="imageblock"> <div class="content"> <img src="media/images/cookbook_dbslayer3.png" alt="media/images/cookbook_dbslayer3.png" /> </div> </div> <div class="paragraph"><p>To do so, simply click on the rule in the <tt>Behavior</tt> tab and proceed to the <tt>Handler</tt> section, where you can adjust all the settings.</p></div> <div class="paragraph"><p>After adding the information sources to be balanced, and setting up the information required to access an existing database, only the matter of selecting a language will remain. In this example we will be using Python because that is what the testing script will be written in. If your favorite language of choice is not available you can simply select JSON, which is widely supported in most programming languages. Using Python in this case will simply allow us to directly evaluate the results offered by the database, creating a native object that can be managed more comfortably.</p></div> <div class="tableblock"> <table rules="all" width="100%" frame="border" cellspacing="0" cellpadding="4"> <caption class="title">Sample configuration values</caption> <col width="50%" /> <col width="50%" /> <tbody> <tr> <td align="left" valign="top"><p class="table">Handler</p></td> <td align="left" valign="top"><p class="table"><tt>MySQL Bridge</tt></p></td> </tr> <tr> <td align="left" valign="top"><p class="table">Language</p></td> <td align="left" valign="top"><p class="table"><tt>Python</tt></p></td> </tr> <tr> <td align="left" valign="top"><p class="table">DB User</p></td> <td align="left" valign="top"><p class="table">my_user</p></td> </tr> <tr> <td align="left" valign="top"><p class="table">DB Password</p></td> <td align="left" valign="top"><p class="table">my_password</p></td> </tr> <tr> <td align="left" valign="top"><p class="table">Database</p></td> <td align="left" valign="top"><p class="table">my_database</p></td> </tr> <tr> <td align="left" valign="top"><p class="table">Balancer</p></td> <td align="left" valign="top"><p class="table"><tt>Round Robin</tt></p></td> </tr> <tr> <td align="left" valign="top"><p class="table">Information Sources</p></td> <td align="left" valign="top"><p class="table">mysql_0, mysql_1</p></td> </tr> </tbody> </table> </div> <div class="imageblock"> <div class="content"> <img src="media/images/cookbook_dbslayer4.png" alt="media/images/cookbook_dbslayer4.png" /> </div> </div> <div class="paragraph"><p>And these were all the steps required. You are ready to go!</p></div> <h3 id="example_python">Example Python script:</h3><div style="clear:left"></div> <div class="paragraph"><p>The following script is provided to show how you can use the database balancing. It is as simple as it gets. You will only have to change the server name and prepare a database accessible with the parameters detailed above (database name, user parameters and so on).</p></div> <div class="listingblock"> <div class="content"> <pre><tt>#!/usr/bin/env python import urllib sql="SELECT * FROM my_table;" query = urllib.quote(sql) print 'Original:', sql print 'Encoded:', query url = 'http://localhost/%s'%(query) response = urllib.urlopen(url) print 'RESPONSE:', response print 'URL :', response.geturl() headers = response.info() print 'DATE :', headers['date'] print 'HEADERS :' print '---------' print headers data = response.read() print 'LENGTH :', len(data) print 'DATA :' print '---------' obj = eval(data) print obj</tt></pre> </div></div> <h3 id="example_php">Example PHP script:</h3><div style="clear:left"></div> <div class="paragraph"><p>This is a simple fetch using cURL:</p></div> <div class="listingblock"> <div class="content"> <pre><tt><?php $query = "SELECT * FROM my_table;"; $host = "http://localhost"; $url = $host ."/". rawurlencode($query); $curl = curl_init(); curl_setopt ($curl, CURLOPT_URL, $url); curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1); $result = curl_exec ($curl); curl_close ($curl); eval("\$obj = $result;"); var_dump($obj); ?></tt></pre> </div></div> <div class="paragraph"><p>Please note the variable must be set in the evaluation string, and that a semicolon is needed in order for the string to be correctly converted. This behavior -not adding the semicolon in the generated string automatically- is a design decision taken to make the underlying infrastructure more flexible.</p></div> </div> <h2 id="_mysql_wrapper_for_php">MySQL Wrapper for PHP</h2> <div class="sectionbody"> <div class="paragraph"><p>To facilitate the use of the MySQL Database Bridge, the PHP-MySQL API has been replicated in a wrapper that can be found under <tt>dbslayer/mysql_wrap.php</tt> in Cherokee’s source tree. It should provide an easy to use method of benefiting from the advantages offered by Cherokee without having to modify your applications.</p></div> <div class="paragraph"><p>You can use it in any of two ways:</p></div> <div class="olist arabic"><ol class="arabic"> <li> <p> By directly overriding every <tt>mysql_*</tt> function call. </p> </li> <li> <p> By prepending <tt>cherokee_</tt> to every <tt>mysql_*</tt> function. </p> </li> </ol></div> <div class="paragraph"><p>This is controlled when you run the initialization function. By default it tries to override the built-in MySQL functions. If you cannot provide a suitable environment or it simply fails, try passing a <tt>FALSE</tt> value as the optional second parameter.</p></div> <div class="paragraph"><p>The requirements for the wrapper are basically the <a href="http://php.net/manual/en/book.curl.php">cURL library</a>.</p></div> <div class="paragraph"><p>To override the built-in function, you need to have <a href="http://php.net/manual/en/book.apd.php">APD</a> installed.</p></div> <div class="paragraph"><p>If you don not, it is as simple as providing the <tt>phpize</tt> program (bundled with package <tt>php5-dev</tt> on Debian based Linux distribution) and issuing:</p></div> <div class="listingblock"> <div class="content"> <pre><tt>pecl install apd</tt></pre> </div></div> <div class="paragraph"><p>To use the wrapper you simply have to include the file in your code and initialize it with the appropriate host:port of a running Cherokee MySQL Bridge instance.</p></div> <div class="listingblock"> <div class="content"> <pre><tt><?php $host="http://localhost:8888"; cherokee_init($host); /* Add a second parameter if you don't want to override the built-in functions. cherokee_init($host, FALSE); */ ?></tt></pre> </div></div> <div class="paragraph"><p>From there on the built-in functions should be extended to support Cherokee Bridged MySQL resources as well as the regular MySQL ones.</p></div> <div class="paragraph"><p>If you don’t care about mixing regular MySQL resources and Bridged ones, as will be the case in most occasions, you could even skip the manual initialization and simply modify the <tt>cherokee_mysql_connect()</tt> and <tt>cherokee_mysql_pconnect()</tt> functions to do the task for you on invocation.</p></div> </div> <div id="footer"> <div id="footer-text"> </div> </div> </body> </html>