Today I'm tasked with replicating a database table from one server to another. But, because of permissions issues as well as flexibility, I'm required to do it via script.
So why would anyone want to replicate one table to another on a single server, non-ecommerce, medium-traffic website?
In our case, the master table (the one being copied from) is updated from another data store – an object database that is extremely slow, and hence, writes to the master table very slowly. This presents issues, as queries to the database often return incomplete record sets.
Query Caching
This is not a common issue though, the bigger reason would be caching. Most databases are setup to cache query results (to a certain maximum number of rows or amount of memory). But, that cache is invalidated (cleared) whenever an update is made to the table.
If your table is updated often, queries will rarely pull from cache. Depending on your situation, performance may be more important than up-to-the-second data accuracy.
Table replication solves this issue by copying records over from a master table to another table at set intervals (say, once every hour). Then, instead of querying the master table, you would query the new, replicated table. This would allow you to pull results from the query cache consistently.
Explaining the Script
The script is surprisingly simple, primarily because the heavy lifting is done by your database server.
- Drop the target table
Typically faster than deleting all rows, plus it sets us up nicely for the next command, which involves creating the table. - Create the target table
- Copy all records from the source table to the target table
- Create the primary key on the target table
This is not done automatically by the create statement above
Note: The database username you use in the script must have DROP and CREATE privileges on the database.
Running the Script
I'd strongly recommend placing this script in a web accessible directory. It will be run by a cron job, not a human. Also, it can be accessed directly with the PHP binary on your server, there's no need to pass it through your web server first.
Once you have the script where you want it, create a cron job:
crontab -e
[ press "i" to start inserting ]
#database table replication at 4AM daily
0 4 * * * php /path/to/script/replicate-table.php source_table target_table primary_key
[press "esc", then "wq" to save and quit]
The Script
<?php
$conn = new mysqli("localhost", "myusername", "mypassword", "mydatabase_name");
if (sizeof($argv) < 3) {
echo "\nUsage: php replicate-table.php SOURCETABLE TARGETTABLE [PRIMARYKEY]\n\n";
echo "Drop (remove) target table, copy source table to target table.\n\n";
echo "Primary keys are not copied during replication, create manually with supplied primary key field.";
exit();
}
$source = $argv[1];
$target = $argv[2];
echo "\n\nDropping target table...\n";
echo "[DROP TABLE " . $target . "]";
$query = "DROP TABLE " . $target;
$result = $conn->query($query);
echo "\n\nCreating target table, copying records from source table...\n";
echo "[CREATE TABLE " . $target . " SELECT * FROM " . $source . "]";
$query = "CREATE TABLE " . $target . " SELECT * FROM " . $source;
$result = $conn->query($query);
if (sizeof($argv) > 3) {
$primaryKey = $argv[3];
echo "\n\nAdding primary key / index to new table.\n";
echo "[ALTER TABLE " . $target . " ADD PRIMARY KEY (" . $primaryKey . ")]";
$query = "ALTER TABLE " . $target . " ADD PRIMARY KEY (" . $primaryKey . ")";
$result = $conn->query($query);
}
echo "\n\nDone!\n\n";
?>