Mysql – Load-balance two MySQL dbs from one PHP page

load balancingMySQLPHP

This is a really dumb question, but here goes.

I've got one web server, running a simple PHP app.
I've got two MySQL servers, with one replicated database, which powers the PHP app. I'm using two MySQL databases to deal with demand.

How can I (and indeed, can I) get the PHP app to alternate which MySQL server it uses?

The PHP line where I connect to the database is currently this:

$con = mysql_connect('10.0.0.2:3306','name','password')
  or die('Could not connect to the server!');

Is there a way I can load-balance with 10.0.0.2 and my other server? Thanks.

Best Answer

One really simple way: You could just use a random number to select between two connection strings, something like the following (this is pseudo-code, I don't speak PHP):

  $ran = gen_random();    // assuming range from 0..1
  if (ran < 0.5) then 
     $con = database1; 
  else 
     $con = database2;

Real load balancing would be possible as well of course, you would have to check to load of your DB servers and select the one with the lower load.

Related Solutions

MySQL – Export and Import Privileges to a New Server

Do not mess with the mysql db. There is a lot more going on there than just the users table. Your best bet is the "SHOW GRANTS FOR" command. I have a lot of CLI maintenance aliases and functions in my .bashrc (actually my .bash_aliases that I source in my .bashrc). This function:

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

The first mysql command uses SQL to generate valid SQL which is piped to the second mysql command. The output is then piped through sed to add pretty comments.

The $@ in the command will allow you to call it as: mygrants --host=prod-db1 --user=admin --password=secret

You can use your full unix tool kit on this like so:

mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret

That is THE right way to move users. Your MySQL ACL is modified with pure SQL.

Mysql – PHP cannot connect to MySQL

I have this on my machine since upgrading. In my case, specifying 127.0.0.1 as the host instead of localhost does the trick.

One day it will bother me enough to really fix it :)

Edit regarding phpMyAdmin
Ah, phpMyAdmin... yes! I feel your pain. I HATE with a passion every time I am forced to re-install this app. It's a brilliant app when it works, but getting it into a working state has caused me a lot of grief over time.

In my personal experience, make sure all the required PHP modules are loaded and make sure your PHP sessions directory (as specified in php.ini) actually exists on the filesystem. If you change any of those, restart Apache.

Also in my experience, restarting Apache alone is not good enough - a browser restart is needed to properly clear the session.

Best Answer

Related Solutions

MySQL – Export and Import Privileges to a New Server

Mysql – PHP cannot connect to MySQL

Related Topic