PHP My SQL Connectror
Use it on you own risk
All master slave must have one user/password for connection
When you send array of servers to my_connect, at first position go master, then all slaves.
Example
include('My_DB.php');
$SEND_ERROR_TO = 'admin@example.com';
my_connect(array('db1','db2'),'USER','PASSWORD','DB');
...
my_query('select ...'); // go to slave (db2)
...
my_query('select ...'); // go to slave (db2)
...
my_query('insert ...'); // go to master (db1)
...
Source :
<?php
/**
* MySql DataBase Package
*
* <p>Base DataBase Access Package. Allow access to many SQL servers in master/slave mode</p>
* <code>
* include('My_DB.php');
* $SEND_ERROR_TO = 'admin@example.com';
* my_connect(array('db1','db2'),'USER','PASSWORD','DB');
* ...
* my_query('select ...');
* ...
* my_query('select ...');
* ...
* ...
* </code>
* @autor Ruslan Bondar
* @since 2005-08-28
* @version $Id: My_DB.php,v 1.7 2007/03/13 13:02:27 rus Exp $
* @package My_DB
* @link http://rus.leaping.net/Ruslan_Bondar/projekty/scripts/mysqlphpconnector
*/
/**
* Connect to one or many sql servers.
* @param string/array $mysql_host one or meny sql server host names.
* @param string $DB_USER sql server user name.
* @param string $DB_PASSWORD sql server passwod.
* @param string $DB_DB sql server database name.
* @param string $DB_ENGINE sql server engine (default mysql).
* @access public
*/
function my_connect($mysql_host,$DB_USER,$DB_PASSWORD,$DB_DB,$DB_ENGINE='mysql'){
global $SQL_CONNECTIONS,$SQL_DB;
if (count($SQL_CONNECTIONS)>0) return $SQL_CONNECTIONS[0];
$SQL_DB = new My_Db();
if (!is_array($mysql_host)){
return $SQL_DB->add_connection($mysql_host,$DB_USER,$DB_PASSWORD,$DB_DB,1);
}else{
$master = array_shift($mysql_host);
foreach($mysql_host as $k=>$v)
$SQL_DB->add_connection($v,$DB_USER,$DB_PASSWORD,$DB_DB,0);
return $SQL_DB->add_connection($master,$DB_USER,$DB_PASSWORD,$DB_DB,1);
}
}
/**
**
* Error notify
* @param string $sql sql that get error
* @param resourse $RESOURCE connection that get error
*/
function debug_send_sql_error($sql,$RESOURCE){
global $SEND_ERROR_TO,$SEND_DB_ERRORS,$SQL_DB;
$err = $SQL_DB->error($RESOURCE);
$send = 1;
if ($SEND_DB_ERRORS[$err] == 1 && preg_match('/^( |\()*select /i',$sql)) $send = 0;
if ($SEND_ERROR_TO && $send) {
$subj = "==".$_SERVER["SERVER_NAME"]."== PHP error in SQL query";
$message .= "$sql:\n\n";
$message .= $err."\n";
$message .= print_r(debug_backtrace(),true);
$message .= "SESSION:\n";
$message .= print_r($_SESSION,true)."\n";
$message .= "SERVER:\n";
$message .= print_r($_SERVER,true)."\n";
mail($SEND_ERROR_TO, $subj, $message);
$SEND_DB_ERRORS[$err] = 1;
}
}
/**
* Send sql query to one of sql servers.
* @param string $sql sql to execute.
* @param int $send_slave 1 - automatic 0 - never send this sql to slave 2 - always send this sql to slave (default 1)
* @param array $change_tables array of tables to cheak for master only request.
* @access public
*/
function my_query($sql,$send_slave=1,$change_tables=array(),$allow_recall=true){
global $DEBUG, $error, $SQL_CONNECTIONS,$SQL_DB;
$RESOURCE = $SQL_CONNECTIONS[0];
if ($SQL_CONNECTIONS){
if ($send_slave && $SQL_DB->check_sql_for_good_for_master($sql)){
if (($send_slave == 2) || ($send_slave && $SQL_DB->check_sql_for_table_update($sql)))
// $RESOURCE = $SQL_CONNECTIONS[rand(0,count($SQL_CONNECTIONS)-1)];
$RESOURCE = ($SQL_CONNECTIONS[1])?$SQL_CONNECTIONS[1]:$SQL_CONNECTIONS[0];
} else {
$SQL_DB->add_updated_table($sql,$change_tables);
}
}
$return = (is_object($DEBUG))?$DEBUG->debug_query($sql,$RESOURCE):$SQL_DB->query($sql,$RESOURCE);
if (!$return) {
if ($SQL_DB->errno($RESOURCE) == 2013 && $allow_recall){
$isHelpt = 0;
$isHelpt = 0;
if ($SQL_DB->ping($RESOURCE)){
my_query($sql,$send_slave,$change_tables,false);
$isHelpt = 1;
}
}
else
{
debug_send_sql_error($sql,$RESOURCE);
}
}
return $return;
}
/**
* Get inserted row ID.
* @access public
*/
function my_insert_id(){
global $SQL_DB;
return $SQL_DB->insert_id();
}
/**
* Abstract My DB class
*
* Default MY DB functions.
* @package My_DB
*/
class My_DB {
/**
* Update array of tables updated this script
*/
function add_updated_table($sql,$change_tables=array()){
if ($change_tables == 'none') return;
$s = str_replace("\n"," ",$sql);
$s = str_replace("\r"," ",$s);
$s = str_replace("\t"," ",$s);
if (preg_match('/^ *alter .*table *([ _a-zA-Z0-9.]+) /i',$s,$matches)) $this->add_updated_table_safe($matches[1]);
if (preg_match('/^ *update.* ([ _a-zA-Z0-9.]+) +set/i',$s,$matches)) $this->add_updated_table_safe($matches[1]);
if (preg_match('/^ *insert *into *([ _a-zA-Z0-9.]+)( |\()/i',$s,$matches)) $this->add_updated_table_safe($matches[1]);
if (preg_match('/^ *replace *(into)? +([ _.a-zA-Z0-9]+) +( |\(|values|set|select)/i',$s,$matches)) $this->add_updated_table_safe($matches[1]);
if (preg_match('/^ *delete .*from +([_a-zA-Z0-9.]+) +(where|using)/i',$s,$matches)) $this->add_updated_table_safe($matches[1]);
if (preg_match('/^ *create.*table +([_a-zA-Z0-9.]+) +(as|select|\()/i',$s,$matches)) $this->add_updated_table_safe($matches[1]);
}
/**
* Extract table name from str
*/
function add_updated_table_safe($tablename){
$s = trim($tablename);
if (strpos($s,'.'))
list($db,$s) = explode('.',$s);
if (!$s) return;
$this->SQL_CONNECTIONS_TABLE_UPDATED[$s] = 1;
}
/**
* Check sql for running on slave
*/
function check_sql_for_good_for_master($sql){
if (preg_match('/^( |\(|\n)*commit/i',$sql)) $this->alltomaster = 1;
if (preg_match('/^( |\(|\n)*set /i',$sql)) $this->alltomaster = 1;
if (!preg_match('/^( |\(|\n)*select (.|\n)*from/i',$sql)) return 0;
if (preg_match('/LAST_INSERT_ID/i',$sql)) return 0;
return 1;
}
/**
* Check sql for using updated tables
*/
function check_sql_for_table_update($sql){
if ($this->alltomaster) return 0;
if (!is_array($this->SQL_CONNECTIONS_TABLE_UPDATED)) return 1;
foreach ($this->SQL_CONNECTIONS_TABLE_UPDATED as $table=>$v )
if (strpos($sql,$table))
return 0;
return 1;
}
/**
* Execute sql
*/
function query($q,$res){
return mysql_query($q,$res);
}
/**
* Return affected rows
*/
function affected_rows($res){
return mysql_affected_rows($res);
}
/**
* Return errror
*/
function errno($res){
return mysql_errno($res);
}
/**
* Return errno
*/
function error($res){
return mysql_error($res);
}
/**
* Return ping
*/
function ping($res){
return mysql_ping($res);
}
/**
* Return insert_id
*/
function insert_id(){
global $SQL_CONNECTIONS;
return mysql_insert_id($SQL_CONNECTIONS[0]);
}
/**
* Add connection
*/
function add_connection($mysql_host,$DB_USER,$DB_PASSWORD,$DB_DB,$ismaster=0){
global $SQL_CONNECTIONS;
// print "$mysql_host,$DB_USER,$DB_PASSWORD<br>";
$conn=mysql_pconnect($mysql_host,$DB_USER,$DB_PASSWORD);
if (mysql_error()) { if ($ismaster) die(mysql_error()); return;}
$ret = mysql_select_db($DB_DB);
if (mysql_error()) { if ($ismaster) die(mysql_error()); return;}
$id = count($SQL_CONNECTIONS);
if ($ismaster) $id = 0;
elseif($id<2) $id++;
$SQL_CONNECTIONS[$id] = $conn;
return $conn;
}
}
?>
Released under the BSD license.
