Created
October 1, 2012 13:47
-
-
Save karser/3811883 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
set_time_limit(0); | |
// set input args | |
$master_host = $argv[1]; | |
$master_user = $argv[2]; | |
$master_pass = $argv[3]; | |
$slave_host = $argv[4]; | |
$slave_user = $argv[5]; | |
$slave_pass = $argv[6]; | |
$database = $argv[7]; | |
$tablename = $argv[8]; | |
$primary_key = $argv[9]; | |
$secondary_key = $argv[10]; | |
// check for required data | |
if (empty($master_host) || empty($master_user) || empty($master_pass)) | |
die ("Incorrect Input: Master server login data missing!rn"); | |
if (empty($slave_host) || empty($slave_user) || empty($slave_pass)) | |
die ("Incorrect Input: Slave server login data missing!rn"); | |
if (empty($database)) | |
die ("You didn't specify a database!rn"); | |
if (empty($tablename)) | |
die ("You didn't specify a table!rn"); | |
if (empty($primary_key)) | |
die ("You need to specify the primary key/ID field!rn"); | |
// first, connect to the master | |
$master_link = mysql_connect($master_host, $master_user, $master_pass); | |
// check for link failure | |
if (!$master_link) | |
die ("unable to connect to master!rn"); | |
// select database on the master | |
@mysql_select_db($database, $master_link); | |
// connect to slave | |
$slave_link = mysql_connect($slave_host, $slave_user, $slave_pass); | |
// check for failure | |
if (!$slave_link) | |
die ("Unable to connect to slave!rn"); | |
// select database on the slave | |
@mysql_select_db($database, $slave_link); | |
$processed = 0; | |
$inserted = 0; | |
$updated = 0; | |
// get a result set from the table on master | |
$master_query = "select * from $tablename"; | |
$master_result = @safe_query($master_query, $master_link); | |
while ($master_row = @mysql_fetch_array($master_result, MYSQL_ASSOC)) | |
{ | |
$processed++; | |
// we are going to check the primary key field and see if its in the same db/table as the slave | |
$master_record_id = $master_row[$primary_key]; | |
// find out if this record exists on the slave | |
$add_record = LookupSlaveRecord($slave_link, $master_record_id, $tablename, $primary_key); | |
// check to see if we should add this record to slave, or continue | |
if (empty($add_record)) | |
{ | |
// now find out if we have the secondary key to check | |
if (!empty($secondary_key)) | |
{ | |
$secondary_data = $master_row[$secondary_key]; | |
// check to see if this key already has an entry | |
$update_record = LookupSlaveRecord($slave_link, $secondary_data, $tablename, $secondary_key); | |
// ok if the secondary key doesnt exist already, insert | |
if (empty($update_record)) | |
{ | |
InsertSlaveRecord($slave_link, $master_row, $tablename); | |
$inserted++; | |
} | |
else | |
{ | |
// the secondary key exists on this table, update | |
UpdateSlaveRecord($slave_link, $master_record_id, $secondary_data, $tablename, $primary_key, $secondary_key); | |
$updated++; | |
} | |
} | |
// we don't, just try to add | |
else | |
{ | |
// add this record to slave | |
InsertSlaveRecord($slave_link, $master_row, $tablename); | |
$inserted++; | |
} | |
} | |
else | |
// we already have this record, lets move on | |
continue; | |
} | |
echo "processed: ".$processed."rn"; | |
echo "inserted: ".$inserted."rn"; | |
echo "updated: ".$updated."rn"; | |
function UpdateSlaveRecord($slave_link, $master_record_id, $secondary_data, $tablename, $primary_key, $secondary_key) | |
{ | |
$q = "update $tablename set $primary_key='$master_record_id' where $secondary_key='".addslashes($secondary_data)."'"; | |
$r = safe_query($q); | |
if ($r) | |
return 1; | |
else | |
return 0; | |
} | |
// insert a record into the slave based on the current row item from the master | |
// make sure that the result set being passed is mysql_fetch_array with a type of | |
// MYSQL_ASSOC. otherwise foreach doesnt work correctly. | |
function InsertSlaveRecord($slave_link, $master_row, $tablename) | |
{ | |
// build the query | |
$q = "insert into $tablename ("; | |
$values = " values("; | |
foreach ($master_row as $name => $data) | |
{ | |
// update the query | |
$q .= " ".$name.","; | |
// update values string | |
$values .= " '".addslashes($data)."',"; | |
} | |
// remove last comma in the query and values strings and close parenthesis | |
$pos = strrpos($q, ","); | |
$q = substr($q, 0, $pos); | |
$q .= ")"; | |
$pos = strrpos($values, ","); | |
$values = substr($values, 0, $pos); | |
$values .= ")"; | |
// append values | |
$q = $q . $values; | |
// execute | |
$r = safe_query($q, $slave_link); | |
if ($r) | |
return 1; | |
else | |
return 0; | |
} | |
// find out if a record on the slave exists | |
function LookupSlaveRecord($slave_link, $master_record_id, $tablename, $primary_key) | |
{ | |
// look up the record | |
$q = "select $primary_key from $tablename where $primary_key='".addslashes($master_record_id)."'"; | |
$r = safe_query($q, $slave_link); | |
if (empty($r)) | |
{ | |
return false; | |
} | |
// record exists | |
if ($row = mysql_fetch_array($r)) | |
return 1; | |
// record doesnt exist | |
else | |
return 0; | |
} | |
function safe_query($q, $slave_link = null) | |
{ | |
$res = @mysql_query($q, $slave_link); | |
if (empty($res)) | |
{ | |
echo mysql_error(), PHP_EOL, $q, PHP_EOL; | |
} | |
return $res; | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment