At Xobni, I’ve developed a simple testing and deployment platform that helps us ensure that new web code is good before it’s pushed out the door. Part of this means maintaining two separate databases, one for development and one for live mode (Ruby on Rails developers will find this concept familiar). That way, we can test our database-enabled pages without adversely affecting the live database.
But this means the development database gets stale over time and must be refreshed. Ideally it should contain data from the day before or some reasonable approximation. In the past, I relied on the “copy-database” command in phpMyAdmin to do this. But being a manual process, and being fairly intensive on the server to boot, it rarely got done.
So I set out to create a php script that would mirror the two databases on a regular schedule. Essentially to copy one database’s tables to another, erasing the old data in the development database.
It’s not rocket science, as it turns out. Here it goes:
$todb = “development”;
$fromdb = “live”;$sql = “SHOW TABLES”;
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
$table = $row[0];
$sql = “DROP TABLE IF EXISTS `$todb.$table`”;
mysql_query($sql);
$sql = “CREATE TABLE $todb.$table LIKE $fromdb.$table”;
mysql_query($sql);
$sql = “INSERT INTO $todb.$table SELECT * FROM $fromdb.$table”;
mysql_query($sql);
}
The script just loops through each table in your $fromdb and copies it to new tables in the $todb. I have it set to run every 24 hours via cron.
The LIKE syntax is fairly new in mysql (as of 4.1) and permits a fully copy of table, including all indexes and keys. The rest is all pretty standard stuff.
Be sure to fill in the appropriate mysql connection strings, and you’re good to go. And do a dry run or two before you run back crying that your deployment database got erased and you don’t have any backups.
I hope this was helpful.
Posted on June 16th, 2008 by plusbryan
Filed under: Development
Leave a Reply