Full-service Internet Marketing & Web Development
Recent Posts
Sponsors
 |
Fixing MySQL ReplicationKyle Deneen, 07-29-2007 |
Recently we had a situation where a client's MySQL replication system had a severe problem with missing records on the slave database. The master had some 90,000 more records in an important table than the slave did. Customers were clamoring because the UI is driven by the slave database, and their data was not showing up.
We researched this problem and were unable to come up with a conclusive 'reason' for the issue other than to say that replication is not perfect. Somehow due to network latency or some other interference, records sometimes do not get executed on the slave database. Apparently the best thing to do is just monitor the record counts on tables between master and slave, and fix this issue as it arises.
At that point, there were only two options to choose from. Either take another snapshot of the master and restart the slave, hoping the problem wouldn't return. The other option was to write a PHP utility script that would take care of the problem. We chose the utility script.
The script was written in such a way that you can use it for any MySQL replication system with a single master and slave. It is executed on the shell and will perform a number of operations to even the table out. It accepts the master database login, slave database login, the database where your table lives, the table you are fixing, the primary key of the table, and an optional secondary key as arguments. The secondary key can be any field containing relevant data which is unique per record.
The basic functionality of this script is taking the entire recordset from the the table you specify on the master and for each record, finding out if that record exists on the same table in the slave, based on the primary key. If it doesn't exist, the script creates the record with the values obtained from the master table.
If a secondary key is specified, the script decides whether to insert or update a record based on that secondary key. So for instance, the script finds a record missing on the slave. However since that unique value for the secondary key may have been created since the record count mismatch occurred, we want to check and see if a different record exists with the same secondary key value. If we find such a record, then we update the record's primary key to match the master rather than adding a new one. Otherwise, it creates the record.
Here is the source:
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 = @mysql_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 = mysql_query($q) or die (mysql_error()."rn".$q."rnrn");
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 = mysql_query($q, $slave_link) or die (mysql_error()."rn".$q."rnrn");
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 = mysql_query($q, $slave_link) or die (mysql_error());
// record exists
if ($row = mysql_fetch_array($r))
return 1;
// record doesnt exist
else
return 0;
}
We researched this problem and were unable to come up with a conclusive 'reason' for the issue other than to say that replication is not perfect. Somehow due to network latency or some other interference, records sometimes do not get executed on the slave database. Apparently the best thing to do is just monitor the record counts on tables between master and slave, and fix this issue as it arises.
At that point, there were only two options to choose from. Either take another snapshot of the master and restart the slave, hoping the problem wouldn't return. The other option was to write a PHP utility script that would take care of the problem. We chose the utility script.
The script was written in such a way that you can use it for any MySQL replication system with a single master and slave. It is executed on the shell and will perform a number of operations to even the table out. It accepts the master database login, slave database login, the database where your table lives, the table you are fixing, the primary key of the table, and an optional secondary key as arguments. The secondary key can be any field containing relevant data which is unique per record.
The basic functionality of this script is taking the entire recordset from the the table you specify on the master and for each record, finding out if that record exists on the same table in the slave, based on the primary key. If it doesn't exist, the script creates the record with the values obtained from the master table.
If a secondary key is specified, the script decides whether to insert or update a record based on that secondary key. So for instance, the script finds a record missing on the slave. However since that unique value for the secondary key may have been created since the record count mismatch occurred, we want to check and see if a different record exists with the same secondary key value. If we find such a record, then we update the record's primary key to match the master rather than adding a new one. Otherwise, it creates the record.
Here is the source:
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 = @mysql_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 = mysql_query($q) or die (mysql_error()."rn".$q."rnrn");
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 = mysql_query($q, $slave_link) or die (mysql_error()."rn".$q."rnrn");
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 = mysql_query($q, $slave_link) or die (mysql_error());
// record exists
if ($row = mysql_fetch_array($r))
return 1;
// record doesnt exist
else
return 0;
}
|
|
Subscribe Now to receive new posts via Email as soon as they come out.
Comments
Post your comments