I am currently developing a website that is driven by a MySQL database.

The database is the backbone of the site - a business directory.

The website is hosted on 3 separate servers, hosted by 3 different companies, in 3 different geographic locations. Users visiting the website are fed transparently through a reverse-proxy to one of the 3 servers.

Each of the 3 servers is synchronised nightly, so all three should always be identical.

However - as we are using shared servers, we can only use one of these web servers as our MySQL server - although all 3 have MySQL.

This means that if that one server goes down, the website (although it will still display static pages) is useless.

This has been bothering me for a while - and I can't find any way to get around it (without using MySQL clustering which is out of our budget!).

So I have developed a PHP script, which I am currently integrating into the site.

It is called Replexa, and the idea is that it replicates any modification queries (insert, update, delete etc...) across all databases, and distributes any other queries to single servers on either a Round Robin, Random or First-available basis according to parameters.

It is used in the same way as PHP MySQL so for example:

$result=mysql_query($query);
would become: $result=replexa_query($query);

mysql_num_rows($result);
would become: replexa_num_rows($result);

mysql_fetch_array($result);
would become: replexa_num_rows($result);


Database connection details, host selection type (round robin etc..) are all stored in a separate config file.

I am planning to make this open source, and I was just curious to see if anyone has had a similar problem - and if so, does this seem like a viable solution?

See

http://www.webmaster-talk.com/websit...ion-linke.html

Ta for linking,

Can i get source? and could it be done so dont have to re-write all scripts or not?

Thanks,

Dan, I can send you the source that's no problem.

One thing though - I read your original post- are you looking for a way of distributing your website across two servers? Or a way of synchronising two databases?

The replexa script is for the latter, however I could give you some advice on how I'm load balancing my site across 3 servers.

Nick

Originally Posted by dansgalaxy

Ta for linking,

Can i get source? and could it be done so dont have to re-write all scripts or not?

Thanks,

Unfortunately you can't really get away without rewriting some of your scripts.

I only use 3 functions in PHP MySQL:

mysql_query
mysql_num_rows
mysql_fetch_array

another possible one is mysql_affected_rows but I don't currently use it.

I have built the first three functions into Replexa (I could easily add affected_rows), and renamed them thus:

replexa_query
replexa_num_rows
replexa_fetch_array

That should be all you need to change.

mysql_query returns a resource pointer, whereas replexa_query returns an array:
db=>id of the database used
result=>resource pointer

the other two functions are designed to accept this array instead of just the resource pointer, so you can use them with exactly the same syntax as their equivalent mysql functions.

The other thing is the mysql_connect and mysql_select_db functions. These become unnecessary as the replexa functions connect and disconnect as and when required.

You simply configure the connection strings in rep_constants.php, and tell the replexa script where to find it.

Here is the source-code to replexa.php:

PHP Code:

<?php
# replexa.php
# Version 1.0 - 28th December 2007
# Author - Nick Cousins
#
# This is designed to replicate data insertion functions across multiple databases on multiple servers
# and to provide failover across those databases for data retrieval functions.
# Replacements are provided for major PHP-MySQL functions, using the prefix replexa_ instead of mysql_


// Change the following line to the path of the rep_constants.php file
require_once("/path/to/rep_constants.php");

# We need to decide if this query should be queued for replication, or attempted as a single query from the database pool.


# The do_replicate function decides if the given query should be sent to all servers. It returns true or false

function do_replicate($query){
$query=substr($query, 0, 10);
$rep=false;
global $replicaq;
foreach ($replicaq as $qtype){
if (strpos($query, $qtype)===false){

} else {
    $rep=true;
}
}
return($rep);

}

# The poll_server function tests a database server connection to check if it is responding, and is used in the selection procedure

function poll_server($dbid){
    global $database;
$db=$database[$dbid];
$result=true;
$rpxcon=mysql_connect($db[hostname], $db[username], $db[password]) or $result=false;
mysql_select_db($db[dbname]) or $result=false;
mysql_close($rpxcon);
return $result;
}

# The db_server function returns the next server to use for data retrieval based on the selection_type parameter

function db_server(){
global $selection_type, $database;
$server=-1;
if($selection_type==1){
    #first available
    foreach ($database as $id=>$db){
    if (poll_server($id)==true){
    $server=$id;
    break;
}
}
} elseif ($selection_type==2){
    #round-robin
} elseif ($selection_type==3) {
    #random
    $numm=(count($database)-1); 
    $server=(rand(0, $numm));
    while (poll_server($server)!=true){
    $server=(rand(0, $numm));
}
    
}
    return($server);
}

function replexa_query($query){
global $database;

if(do_replicate($query)==true){
    $results=array();
    $affected=array();
    foreach($database as $db){
    $rpxcon=mysql_connect($db[hostname], $db[username], $db[password]) or (mysql_error());
    mysql_select_db($db[dbname]) or (mysql_error());
    $result=mysql_query($query, $rpxcon);
    if ($result!=false){
    $af=mysql_affected_rows();
} else echo(mysql_error());
    array_push($results, $result);
    array_push($affected, $af);
    mysql_close($rpxcon);
}
return array($results, $affected);
} else {
    $dbid=db_server();
    if ($dbid!=-1){
    $db=$database[$dbid];
    $rpxcon=mysql_connect($db[hostname], $db[username], $db[password]) or (mysql_error());
    mysql_select_db($db[dbname]) or(mysql_error());
    $result=mysql_query($query, $rpxcon);
    $retval=array(db=>$dbid, result=>$result);
    return($retval);
    mysql_close($rpxcon);} else {
    return false;
}
}

}

function replexa_fetch_array($result_array){
global $database;
$dbid=$result_array[db];
$resource=$result_array[result];
$db=$database[$dbid];
$rpxcon=mysql_connect($db[hostname], $db[username], $db[password]) or die(mysql_error());
mysql_select_db($db[dbname]) or die(mysql_error());
return mysql_fetch_array($resource);
mysql_close($rpxcon);
}

function replexa_num_rows($result_array){

global $database;
$dbid=$result_array[db];
$resource=$result_array[result];
$db=$database[$dbid];
$rpxcon=mysql_connect($db[hostname], $db[username], $db[password]) or die(mysql_error());
mysql_select_db($db[dbname]) or die(mysql_error());
return mysql_num_rows($resource);
mysql_close($rpxcon);

}

function replexa_numrows($result_array){
    return(replexa_num_rows($result_array));
}


?>

And this is the source of rep_constants.php:

PHP Code:

<?php

# The pool of databases is a 2D array consisting of hostname, username, password, dbname and is stored as an array called $database. For simplicity, I have first compiled each database into its own array (db1, db2 etc...)


$db1=array(hostname=>"host1.domain.com", username=>"db_user1", password=>"pa55word", dbname=>"my_database");
$db2=array(hostname=>"host2.domain.com", username=>"db_user1", password=>"pa55word", dbname=>"my_database");
$db3=array(hostname=>"host3.domain.com", username=>"db_user1", password=>"pa55word", dbname=>"my_database");

$database=array($db1, $db2, $db3);

# queries that will return just true or false, and must be replicated across each database in the pool are stored in an array called $replicaq

$replicaq=array("insert","update", "delete", "drop", "empty");

# The selection_type parameter dictates whether data retrieval is attempted in first-available, round-robin, or random order.
# 1 = First-available server
# 2 = Round-robin - not yet implemented
# 3 = Random

$selection_type=1;


?>

I have tried it with 3 servers and had no issues yet.
Now I should point out that this is my first attempt at this script, so if anyone wants to pick holes in it feel free (but try to be nice!! )

thanks, i will have to look into it, at the moment im okay with manually sync files its just the DB which is a bummer. at the moment its all jsut getting it from one server using remote to keep em the same, i think i will have to get my head arround making a class for all my db functions then this kind of thing would be easy to implement.

good idea tho, when i have time i will download and have a better look.

Thanks for putting it out there

You did pick it up that this script is for synchronising the DB and not the files?

yes i fully understand that.

I was wondering if you had implemented round-robin by now.
was just curious to see how that was implemented, can you plz post the code

I havnt implemented this yet, infact i had forgot about it...

i have now converted (most) of the site to useing mu mysql class functions so this kind of thing would be easier...

Thanks found this stuff useful!

If you're still looking for way to sync files across servers and want to automate this you can use rsync (by the way php.net mirrors also use rsync to sync up their files).

If I understand you right, you are going to do quite the same actions as if there was the DB replication set up. And I guess that you mixed up DB replication (which can be done using native mysql functions) with mysql cluster (which is separate commercial product). If rtfm'ed a little you would not be reinventing the wheel right now.