in Challenge

Running mysql-proxy with cPanel remote mysql and proxy for localhost

Mysql-proxy is a neat tool that is badly needed to make remote MySQL server installations seem as if they were local (as in localhost). It is a convenience more than anything else. For example, if you are multi-tenant provider (a.k.a shared hosting provider), you would rather not ask your clients to set the db host to db.example.net instead of the widely used localhost. It becomes a maintenance challenge as you cluster more databases together with different web servers. By the way, the proposed workaround works well with cPanel too!

Before we get started, keep in mind that Mysql-proxy is much more capable than just making remote MySQL installations appear to be local. It can do caching, scripting, on-the-fly SQL manipulation, and much more.

Let’s get down to implementation! Once you get Mysql-proxy installed, you’ll need to set the init.d script so it runs on system startup. Luckily it already comes with a generic init.d script, which is good enough and only needs a slight change.

Replace the PROXY_OPTIONS variable to the following:

PROXY_OPTIONS="--daemon --admin-address=127.0.0.1:4402 --proxy-address=/var/lib/mysql/mysql.sock --proxy-backend-addresses=10.1.1.1:3306"

10.1.1.1 being your remote MySQL node. Once that’s done, go ahead and restart mysql-proxy:

/etc/init.d/mysql-proxy restart

If all goes well, you should be able to grep the process:

5 S root      5970     1  4  75   0 - 11330 -      Oct05 ?        03:20:35 /usr/local/sbin/mysql-proxy --daemon --admin-address=127.0.0.1:4402 --proxy-address=/var/lib/mysql/mysql.sock --proxy-backend-addresses=10.1.1.1:3306 --pid-file /var/run/mysql-proxy.pid

Be sure to set php.ini to the following:

mysql.allow_persistent = On
mysql.max_persistent = -1
mysql.protocol = "TCP"
mysql.max_links = -1
mysql.default_port = 3306
mysql.default_socket =
mysql.default_host =
mysql.default_user =
mysql.default_password =
mysql.connect_timeout = 60
mysql.trace_mode = Off

That’s all folks! We hope you enjoyed this short article.

Cheers!

Write a Comment

Comment

Comment moderation is enabled. Your comment may take some time to appear.

Webmentions

  • baidu

    baidu…

    baidu http://www.baidu.com