Dviejų Mysql bazių naudojimas

Dviejų (ar daugiau) duomenų bazių (esančių skirtinguose serveriuose) naudojimas. Jei serveryje A esanti bazė neveikia (nepavyksta prie jos prisijungti), automatiškai jungiamasi prie atsarginės bazės B serveryje. Lankytojai nieko nepastebi (nerodoma jokia klaida).

Imagine, you're connecting to a mySQL database with PHP, but hell, you want a bit more robustness. So why would you want just one database? You'd use a failover right? So if the first database is down or just doesn't connect, it can connect to the next one. Here's how to do that (I'm assuming you're already replicating between databases so that they're roughly in sync).
Here's how you do it. Firstly, you'll have to define some things...
$host=""; //Database host.
$user=""; //Database username.
$pass=""; //Database password.
$dbase=""; //Database.
$host2=""; //Database host two... This is optional: in case the other database cannot connect.
$user2=""; //Database username two... This is optional: in case the other database cannot connect.
$pass2=""; //Database password two... This is optional: in case the other database cannot connect.
$dbase2=""; //Database two... This is optional: in case the other database cannot connect.
?>
View the comments, they are simple yet... informative. Now, the one thing that can make all of this come true:
$connect=mysql_connect($host,$user,$pass); //Connect to the database.
?>

That will connect to the mySQL database with the currently defined variables. So, obviously, if $connect does that, what would $connect2 do?
$connect2=mysql_connect($host2,$user2,$pass2); //Connect to the database.
?>
It connects to the database! But, oh, it's the second one. Strange, isn't it? Now, you can check if the database connected or not by checking if its true, or false.
if (!$connect) {
mysql_close($connect); //Ends the current connection to the database.
echo "Cannot connect to the first database!"; //Display the text inside the quotes.
}
else {
echo "Connected to the first database!"; //Display the text inside the quotes.
}
?>
Of course, how does it connect to the second database? Simple!
if (!$connect) {
mysql_close($connect); //Ends the current connection to the database.
echo "Cannot connect to the first database!
"; //Display text inside the quotes.
if (!$connect2) {
mysql_close($connect2); //Ends the current connection to the second database.
echo "Cannot connect to second database!"; //Display text inside the quotes.
}
else {
echo "Connected to the second database!"; //Display text inside the quotes.
}
}
else {
echo "Connected to the first database!
Did not connect to the second database!"; //Display text in quotes
}
?>
Now, that's all this really does. It has some mysql_error() stuff in it too, and checks if the url is connection.php?type=1 before executing the code. I basically combined all of that into one script, all you have to do is edit the variables, and do:
include "connection.php";
?>
Here's the full script:
/////////////////////////////////////////////////////////
//Name --- Connection.php //
/////////////////////////////////////////////////////////
//Description --- Connects to a mySQL database easily //
/////////////////////////////////////////////////////////
//Author --- [SJB]Dude7 //
/////////////////////////////////////////////////////////

$host=""; //Database host.
$user=""; //Database username.
$pass=""; //Database password.
$dbase=""; //Database.
$host2=""; //Database host two... This is optional: in case the other database cannot connect.
$user2=""; //Database username two... This is optional: in case the other database cannot connect.
$pass2=""; //Database password two... This is optional: in case the other database cannot connect.
$dbase2=""; //Database two... This is optional: in case the other database cannot connect.

$connect=mysql_connect($host,$user,$pass); //Connect to the database.

if ($_GET["type"]=="1") {
if (!$connect) {
mysql_close($connect); //End the connection to possibly prevent SQL injection or just for safety.
$connect2=mysql_connect($host2,$user2,$pass2); //Connect to the host.
if (!$connect2) {
die("ERROR: " . mysql_error()); //Receive an error if it cannot connect.
mysql_close($connect2); //End the connection to possibly prevent SQL injection or just for safety.
}
else {
$dbconnect2=mysql_select_db($dbase2, $connect2); //Select the database.
if (!$dbconnect2) {
die("ERROR: " . mysql_error()); //Receive an error if it cannot connect.
mysql_close($connect2); //End the connection to possibly prevent SQL injection or just for safety.
}
}
}
else {
$dbconnect=mysql_select_db($dbase, $connect); //Select the database.
if (!$dbconnect) {
die("ERROR: " . mysql_error()); //Receive an error if it cannot connect.
mysql_close($connect); //End the connection to possibly prevent SQL injection or just for safety.
}
}
}
else {
echo "ERROR: Invalid argument '" . $_GET["type"] . "'."; //If the connect.php?type doesn't equal one, it will display this error.
}
?>
I was born in Philly. I started to code. I read some books. Yeah.
4 comments on this article. Log in to add your comment | Rate this article:

Thanks, but..
Submitted by danielpunt on September 13, 2007 - 17:37.

.. I really don't get the $_GET['type'] thing, but apart from that it can be done with far less code :
// Connect with MySQL database
// by danielpunt;

// Login for both users
$db1['host'] = 'localhost';
$db1['user'] = 'user';
$db1['pass'] = 'pass';
$db1['dbName'] = 'database';

$db2['host'] = 'localhost';
$db2['user'] = 'user2';
$db2['pass'] = 'user2';
$db2['dbName'] = 'database';

$conn = @mysql_connect($db1['host'], $db1['user'], $db1['pass']);
if($conn != false) {
$db = @mysql_select_db($db1['dbName']);
} else {
$conn = @mysql_connect($db2['host'], $db2['user'], $db2['pass']);
$db = @mysql_select_db($db2['dbName']);
}

if($conn == false OR $db == false) {
die("Couldn't connect.
#" . mysql_errno() . ' : ' . mysql_error());
} else {
echo 'Whoo! Connected!';
}
?>
login or register to post comments

Switch instead of If
Submitted by Original Sin on September 24, 2007 - 13:32.

I often find that using switch() instead of if() produces cleaner and shorter code. It becomes particularly beneficial if you for instance were to add another 3 dbs to connect to.
See below for a code example using switch(). You can read more about it at php.net/switch.


$db1['host'] = 'localhost';
$db1['user'] = 'user';
$db1['pass'] = 'pass';
$db1['dbName'] = 'database';

$db2['host'] = 'localhost';
$db2['user'] = 'user2';
$db2['pass'] = 'user2';
$db2['dbName'] = 'database';

switch (true) {
case @mysql_select_db($db1['dbName'],mysql_connect($db1['host'],$db1['user'],$db1['pass'])): break;
case @mysql_select_db($db2['dbName'],mysql_connect($db2['host'],$db2['user'],$db2['pass'])): break;
default: echo 'Unable to connect to the database. God save us all!'; exit();
}
?>
login or register to post comments

IF for the shortest code
Submitted by atherix on September 29, 2007 - 05:26.

A one-liner IF statement is the most efficent. Also, why echo() and exit() when you can just die()?

if (!mysql_select_db($db1['dbName'],mysql_connect($db1['host'],$db1['user'],$db1['pass'])) && !mysql_select_db($db2['dbName'],mysql_connect($db2['host'],$db2['user'],$db2['pass']))) die("Unable to connect");
?>

Šaltinis: http://www.evolt.org/failover-database-connection-with-php-mysql