1

This is a really dumb question, but here goes.

  1. I've got one web server, running a simple PHP app.
  2. 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.

simon
  • 13
  • 3

4 Answers4

1

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.

Sven
  • 98,649
  • 14
  • 180
  • 226
  • This is what we used in our environment -- a simple PRNG to pseudo-randomly choose a database to connect to for each request. The only hitch we ran into in this regard is that there is a delay in the replication, and if subsequent script requests come fast enough data written to one server may not be available yet on the other! Sticking your selection into the session, though, to make all requests for a particular user use the same database, could solve this issue. – Kromey May 12 '11 at 16:39
  • great - this is a nice simple solution and i can trade up to haproxy/pound if i need to :) – simon May 12 '11 at 17:04
0

You want to be very careful what you are doing here. MySQL replication is a master-slave process. Any updates written to the slave will not make it back to the master, and you will end up with inconsistent databases. In fact, sooner or later you will probably end up with broken replication and then the fun starts for real. You could try and set up some sort of circular replication, but last I read about this nobody has managed to get this to work in a production environment.

In any case I can tell you that a single MySQL database server, properly configured and optimised, can easily handle the traffic for several web servers (unless the web servers are real animals and the db server is a midget).

If you need a proper multimaster database cluster, you will have to look into other solutions. AFAIK there is only one proper multimaster database solution on the market, and that is oracle.

wolfgangsz
  • 8,847
  • 3
  • 30
  • 34
  • In point of fact, MySQL master-master and MySQL circular replication both work quite well, and both see much action in production environments. While the *most basic* form of replication in MySQL is master-slave, with the others built up from that, it's hardly all there is to it. – Kromey May 12 '11 at 16:35
  • Thanks for the advice, but I'm using master-master replication already, sorry if that wasn't clear :) – simon May 12 '11 at 17:03
  • what is called "master-master" replication in MySQL is in fact a hack - it is just a circular master/slave setup. Obviously it breaks nearly every ACID principle and would work only in the special cases where ACID is not required (which might be true for MyISAM databases where you would not expect transaction consistency or isolation anyway). I would not recommend enabling it without thorough testing and extensive simulation. – the-wabbit May 12 '11 at 17:48
0

MySQL uses the NBCLuster group. Not all databases support the NBCluster DB Type. You might try migrating your data in a test environment to see what happens? You can then use a clustered environment.

lilott8
  • 496
  • 5
  • 14
0

Check out the new mysql driver (mysqlnd) in 5.3.

Or you could use MySQL Proxy instead of doing it in PHP.

Cheers

HTTP500
  • 4,833
  • 4
  • 23
  • 31