MySQL replication in PHP – on the same machine
I just realized I needed to replicate some data from one database to another, but only certain tables.
After reading up on MySQL replication for a bit I realized that it would go quicker to simply write something in PHP that would sync a subset of tables in one database to exact copies of the same tables in another.
Note that the code/SQL below only works if you replicate from one database to another on the same machine since the main thing here are SQL queries that contain operations/look ups on two databases in the same query.
Let’s start off with tables that contain an auto incrementing primary key.
$to_db = $sql->getSetting('database');
$tables = array('table1' => 'id', 'table2' => 'partner_id');
foreach($tables as $tbl => $pkey){
$sql->query("DELETE FROM `$tbl` WHERE $pkey NOT IN(SELECT $pkey FROM `masterdb`.`$tbl`)");
$to_data = $sql->loadArray("SELECT * FROM $tbl", 'ASSOC', $pkey);
$from_data = $sql->loadArray("SELECT * FROM `masterdb`.`$tbl`", 'ASSOC', $pkey);
foreach($to_data as $id => $row){
if($from_data[$id] != $row)
$sql->updateArray($tbl, $from_data[$id], array($pkey => $id));
}
$sql->query("INSERT INTO `$tbl` SELECT * FROM `masterdb`.`$tbl` WHERE $pkey NOT IN (SELECT $pkey FROM `$to_db`.`$tbl`) ");
}In the above the $sql variable contains a database connector object with some wrapping around mysql_*. You get the picture because you probably have one yourself.
1.) We begin by deleting all rows in the “slave” ($to_db) database which can not be found in the master database anymore.
2.) Next we update all rows that are different in the master from the corresponding row in the slave, this is done with PHP’s == operator using two arrays. This is possible due to the fact that we use the primary keys as keys in the PHP arrays too.
3.) Finally we copy all new rows from the master to the slave.
That was easy but what do we do if we have tables that don’t have auto incrementing primary keys? Well in my case I can utilize the fact that these tables contain a lot of data that is never deleted or updated after it has been inserted, hence the problem breaks down into simply having to fetch all rows from the master that are not yet present in the slave.
$limit = 1000000000;
foreach( array( 'table3', 'table4 ) as $tbl ){
$to_count = $sql->getValue("SELECT COUNT(*) FROM $tbl");
$sql->query("INSERT INTO `$tbl` SELECT * FROM `masterdb`.`$tbl` LIMIT $to_count, $limit");
}That took care of that, note that I’m forced to use an arbitrary number as the limit in the LIMIT clause since there is no way of simply having a LIMIT with an offset and a limit that automatically fetches everything above the offset. Here I’m pretty sure the tables in question will never have more than 1000000000 rows so I use that number.
Finally we have a table that we can be sure never contains a large amount of rows that might be the subject of deletions, inserts and updates in the master. This problem we simply solve by truncating the slave table followed by copying the whole table from the master to the slave:
$sql->query("START TRANSACTION; TRUNCATE TABLE `table5`; INSERT INTO `table5` SELECT * FROM `masterdb`.`table5`; COMMIT;");We wrap the whole thing in a transaction so that no other read/write will encounter an empty table during the sync process.
Related Posts
Tags: Database, mysql, PHP, replication