Replication Solution:PHP, One Slave, many Masters

[Ramon Sosa]

Hi, I develop this script,it is my own contribution I check it and it is working for me...
I need that many people that have same problem to test it and help to debug...

The philosofy is simple just change the master every interval... You don´t need any more...just configure your servers as mentioned in mysql manual.
Run this script in console mode in slave server.
Put your masters in array $list_masters, and insert in replicate table the initial values.
There are many consecuences on this, so you need to ensure that you know what are you doing, or replicate may override your data.
Use it at your own risk.

I hope that any Mysql Ab Support Worker read this and post if see anything not considered.

<?
//Script Name: replica.php
//This program must run in console mode
//In windows
//replica2.php put in c:\php
//c:\php\php.exe -a -q replica2.php
/*
#
# Estructura de tabla para la tabla `replicate`
#

CREATE TABLE `replicate` (
`Master_Host` varchar(30) NOT NULL default '',
`Master_User` varchar(30) NOT NULL default '',
`Master_Port` varchar(5) NOT NULL default '',
`Connect_retry` bigint(5) unsigned NOT NULL default '0',
`Master_Log_File` varchar(40) NOT NULL default '',
`Read_Master_Log_Pos` bigint(20) unsigned NOT NULL default '0',
`Relay_Log_File` varchar(40) NOT NULL default '',
`Relay_Log_Pos` bigint(20) unsigned NOT NULL default '0',
`Relay_Master_Log_File` varchar(40) NOT NULL default '',
`Slave_IO_Running` char(3) NOT NULL default '',
`Slave_SQL_Running` char(3) NOT NULL default '',
`Replicate_do_db` varchar(50) NOT NULL default '',
`Replicate_ignore_db` varchar(60) NOT NULL default '',
`Last_errno` int(10) unsigned NOT NULL default '0',
`Last_error` text NOT NULL,
`Skip_counter` int(10) unsigned NOT NULL default '0',
`Exec_master_log_pos` bigint(20) unsigned NOT NULL default '0',
`Relay_log_space` bigint(20) unsigned NOT NULL default '0'
) TYPE=InnoDB;

#
# Volcar la base de datos para la tabla `replicate`
#

INSERT INTO `replicate` VALUES ('10.9.1.232', 'repl', '3306', 60, 'Ramon-bin.008', 785, 'chac-relay-bin.002', 4, 'Ramon-bin.008', 'No', 'No', 'encuesta', '', 0, '', 0, 785, 4);
INSERT INTO `replicate` VALUES ('10.9.1.13', 'repl', '3306', 60, 'desarrollo1-bin.002', 624, 'chac-relay-bin.002', 4, 'desarrollo1-bin.002', 'No', 'No', 'encuesta', '', 0, '', 0, 624, 4);

*/


echo "Press Ctrl-C to Cancel\n";
$db_server="localhost";
$user="root";
$password="";
$db='encuesta';
$Status=array();
$seconds_quota=15;
$list_masters=array('10.9.1.232',
'10.9.1.13');

$_link=mysql_pconnect($db_server,$user,$password) or die('It is not posible to conect to db server');
mysql_select_db($db) or die("No fue posible elegir la base de datos");


function Show_Slave_Status(){
global $_link;
$sql="SHOW SLAVE STATUS";
$result=mysql_query($sql,$_link);
if(mysql_errno($_link)!=0){

echo "Error show Slave Status: ".mysql_errno($_link).'-'.mysql_error($_link)."\n";
return false;
}
if($result && mysql_num_rows($result)==1){
$row=mysql_fetch_assoc($result);
return $row;
}
return false;
}

function Stop_Slave(){
global $_link;
echo "Trying to Stop Slave...\n";
$sql="STOP SLAVE";
$result=mysql_query($sql,$_link);
if(mysql_errno($_link)!=0){
if(mysql_errno($_link)==1199)
echo "SLAVE already Stoped\n";
else{
echo "Error Stop Slave: ".mysql_errno($_link).'-'.mysql_error($_link)."\n";
return false;
}
}
else echo "Stop Slave Succesful\n";
return true;
}
function Start_Slave(){
global $_link;
echo "Trying to Start Slave...\n";
$sql="START SLAVE";
$result=mysql_query($sql,$_link);
if(mysql_errno($_link)!=0){
echo "Error Start Slave: ".mysql_errno($_link).'-'.mysql_error($_link)."\n";
return false;
}
echo "Start Slave Succesful\n";
return true;
}
function Backup_Status($host,$status){
global $_link;
echo "Trying to Backup $host\n";
foreach($status as $k =>$v) $array_tmp[]="$k='$v'";
$sql='UPDATE replicate SET '.implode(',',$array_tmp). " WHERE Master_Host='$host'";
//echo $sql;
$result=mysql_query($sql,$_link);
if(mysql_errno($_link)!=0){
echo "Error Backup Status $host: ".mysql_errno($_link).'-'.mysql_error($_link)."\n";
return false;
}
echo "Backup $host succesful\n";
return true;
}
function Restore_Status($host){
global $_link;
echo "Trying to change master $host ...\n";
$sql="select * from replicate where Master_Host='$host'";
//echo $sql;
$result_search=mysql_query($sql,$_link);
if(mysql_errno($_link)!=0){
echo "Error Search $host: ".mysql_errno($_link).'-'.mysql_error($_link)."\n";
return false;
}
if($result_search && mysql_num_rows($result_search)==1){
$row=mysql_fetch_assoc($result_search);
$sql_master="CHANGE MASTER TO MASTER_HOST='{$row['Master_Host']}',MASTER_LOG_FILE='{$row['Master_Log_File']}',MASTER_LOG_POS={$row['Read_Master_Log_Pos']}";
//echo $sql_master;
$result_master=mysql_query($sql_master,$_link);
if(mysql_errno($_link)!=0){
echo "Error Change Master $host: ".mysql_errno($_link).'-'.mysql_error($_link)."\n";
return false;
}
echo "Change master $host sucessful\n";
return true;

}
echo "Change Master fail $host \n";
return false;
}

while(true){

foreach($list_masters as $key=>$master){
echo "Master: $master \n";
if(Stop_Slave()){;
$Status=Show_Slave_Status();

if ($Status && $master!=$Master_Host){
if($Status['Read_Master_Log_Pos']==$Status['Exec_master_log_pos']){

if ($Status['Slave_IO_Running']== 'No' && $Status['Slave_SQL_Running']=='No'){
if(Backup_Status($Status['Master_Host'],$Status) && Restore_Status($master) ){;
Start_Slave();
}
}


}
}

}
sleep($seconds_quota);
}

}

?>