I run a shared hosting provider and we're looking to move to a High Availability (replicated across multiple datacenters) setup for our hosting. We have created a replicated MySQL setup with failover that works wonderfully, and we'd like to move all of our clients' databases to it.

The only trouble is that we have many many customers, all of whom have configured their Wordpress, Drupal, etc. installations to connect to MySQL via a local socket, not to the address of the remove server.

I would hate to have to go through manually and change the connection statement in all of our clients' sites.

What I'd ideally love to see is a program that listens on /tmp/mysql.sock and forwards connections there to the remote server I specify. I've seen SQL Relay, but it seems to require that I hardcode all of the database names and usernames and passwords into its configuration file. This is not going to work for me because our users add new databases dynamically all of the time, and I'd rather not have to write code to updated SQLRelay's config file every time.

Does anyone have an idea on how to do this?

Alternatively, I'd accept idea on how to handle this at the PHP level. (i.e. redirect any attempted calls to mysql_connect() to use that hostname rather than localhost)

Thanks, Kevin


You probably want MySQL Proxy to proxy the connection to the real server(s).

  • This looks like exactly what I need. – Kevin Mar 12 '11 at 20:10

In the /etc/wordpress directory there should be a config file per site. They are in the form "config-sitename.php". They should have line starting define('DB_HOST',. Edit the defined value to be the name of the mysql host.

Verify the permissions in mysql on the remotehost to ensure they allow the user to connect from the webserver. The table mysql.user has a hostname column which can be used to restrict access to a host. Setting the hostname to % allows any host to connect. It is more secure to set it to the name of the host that will be connecting.

Drupal should use a similar mechanism.

You can start the transition by enabling the mysql on the server to listen on localhost and getting usres to transition to this from the socket.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.