Drupal 5 and above supports multiple databases connection but it does not distinguish the reads and writes among executed query. Although, this can be done by simple hacking within the Drupal core but you will have trouble when updating Drupal.

To do this without inside the application layer Drupal, I take a deeper look into MySQL Proxy. MySQL Proxy can be placed in between of Drupal and MySQL. This middleman is totally transparent to both parties. MySQL Proxy will forward the write queries to master database and redirect the read queries to slave databases. Imagine MySQL Proxy as a customer service operator who transfers your call to relevant department.

Now. Firstly, we need to install MySQL Proxy:

sudo apt-get install mysql-proxy

The good thing is there is a ready script, rw-splitting.lua, available with the MySQL Proxy to handle read or write queries splitting. So, we don’t really have to know how to write a lua script.

Then, we run the lua script with MySQL Proxy:

mysql-proxy --proxy-backend-addresses=192.168.0.100:3306 \
--proxy-read-only-backend-addresses=192.168.0.101:3306 \
--proxy-read-only-backend-addresses=192.168.0.102:3306 \
--proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua

Change the our Drupal database connection’s port to 4040 in sites/default/settings.php:

$db_url = 'mysqli://username:password@127.0.0.1:4040/drupal';

Notice that the default port of MySQL Proxy is 4040. You may change to other port as you wish with –proxy-address=127.0.0.1:8080.

All go well during the installation. But when second node of Drupal inserted I got “Duplicate key” error.

I am out of luck and Drupal seem not running well with MySQL Proxy. The auto increment ids are all messed up and the node and node revisions relationship are falling apart. I am ready to get my hand dirty and drill into the rw-splitting.lua script. I enable the debug mode of the script to observe each query that actually take place.

I noticed that the script redirects the read of LAST_INSERT_ID to read-only database . Supposing last insert id should be returned from no others but master database. The bug happened at line 225 where LAST_INSERT_ID() is treated as TK_LITERAL instead of TK_FUNCTION

if not is_in_select_calc_found_rows and token.token_name == "TK_SQL_SQL_CALC_FOUND_ROWS" then
	is_in_select_calc_found_rows = true
elseif not is_insert_id and token.token_name == "TK_LITERAL" then
	local utext = token.text:upper()
	if utext == "LAST_INSERT_ID" or
	   utext == "@@INSERT_ID" then
		is_insert_id = true
	end
end

The bug can be easily fixed by replacing TK_LITERAL with TK_FUNCTION will fixed. Save the lua script and restart the MySQL Proxy.

Crossing my figures. Hooray, it works!. Now, I got my Drupal running with reads and writes on separate machines. Again, this is actually run and tested on AWS. :3