I am not sure if it is yet since we are testing for a massive CMS based site.
But here are the steps, if it helps ??
Also check the reference FAQ
http://dev.mysql.com/doc/refman/5.0/en/mysql-proxy-faq.html#qandaitem-15-6-6-1-22
Step 1. configure mysql master and slave.
Step 2. Run the proxy with the argument
mysql-proxy --proxy-lua-script=share/rw_splitting.lua (of course with some redirection to log file as each and every request is kind of logged..)
(little investigation required here) My friend David in Linux Foundation pointed out an interesting flaw
Run the master and slave
Step 3 : mysql -u USERNAME -pPASSWORD -h 127.0.0.1 -P 4040
step 4: in joomla configuration.php - change the $host variable to <hostname>:4040
Note: 4040 is the proxy port.
Note: For drupal or any Mysql based application, modify the port to 4040 rather than 3306 (default mysql port)
Run apache..
When you visit a site -- the db requests are routed to proxy. then to master or slave..
We need to work through any issues we come across. but i am confident that this is something which will work.. share your thoughts too
David Ames - my friend at Linux Foundation suggested the following
David Says "
A couple of differences from Sudhi's setup.
mysql-proxy is running on each web server on port 3306. So configurations will use
localhost port 3306.
mysql-proxy has two backend servers one Read Write and one Read Only. The command line
options in the startup file look like this:
/usr/sbin/mysql-proxy \
--proxy-address=localhost:3306 \
--proxy-backend-addresses=<host>:3306 \
--proxy-read-only-backend-addresses=<host>:3306 \
--pid-file=/var/run/mysql-proxy/mysql-proxy.pid
--daemon
Regarding the rw_splitting. I am not as confident in this script. It attempts to have all writes go to one server and all reads go to the other. In the config above we accomplish almost the same thing. The only difference is reads will be load balanced between the two.
I am more confident in the this setup than using the rw_splitting lua script. The above scales better as well as we can just add slaves as Read Only backend servers.
Reference:
http://dev.mysql.com/doc/refman/5.0/en/mysql-proxy-faq.html#qandaitem-15-6-6-1-22
Edited 2 time(s). Last edit at 02/26/2009 12:06PM by sudhi seshachala.