Archive for December 18th, 2010

Script to generate command to push MySQL database changes between enviornments

Got sick of manually doing database dumps between our production, test, and development environments, so I just wrote a very handy script using Zend Framework to generate a command to push changes between environments as you make them.

You will have to tweak ssh_to to suit your needs (our test and production dbs are not separated at the moment, so that removes a layer of complexity for us), but if you have everything setup in application.ini like most people do, this should work almost out of box. You’ll need to setup the autoloader on your own.

usage: php push_db.php --to=dev --from=prod

Enjoy!

#!/usr/bin/env php5
<?php
require_once(getcwd() . '/autoload.php');

$opts = new Zend_Console_Getopt(
   array(
      'from=s'    => 'From environment, with required string parameter',
      'to=s'        => 'To environment, with required string parameter',
   )
);

$from = $opts->getOption('from');
$to = $opts->getOption('to');

foreach (array('from','to') as $env) {
      if (!in_array($$env, array('production','prod','testing','test','development','dev'))) {
         die('Error: invalid parameter ' . $env . " = '{$$env}'" . PHP_EOL);
      }
}

$prod_env = new Zend_Config_Ini(APPLICATION_PATH . '/configs/application.ini', 'production');
$test_env = new Zend_Config_Ini(APPLICATION_PATH . '/configs/application.ini', 'testing');
$dev_env = new Zend_Config_Ini(APPLICATION_PATH . '/configs/application.ini', 'development');

$to_server = array();
$from_server = array();
foreach (array('from','to') as $serv) {
   $server = $serv . '_server';
   switch ($$serv) {
      case 'production':
      case 'prod':
         ${$server}['host'] =                   $prod_env->database->params->host;
         ${$server}['username'] =                   $prod_env->database->params->username;
         ${$server}['password'] =                   $prod_env->database->params->password;
         ${$server}['dbname'] =                   $prod_env->database->params->dbname;
         ${$server}['ssh_to'] = 'me@foobar';
         break;
      case 'testing':
      case 'test':
         ${$server}['host'] = $test_env->database->params->host;
         ${$server}['username'] =                   $test_env->database->params->username;
         ${$server}['password'] =                   $test_env->database->params->password;
         ${$server}['dbname'] =                   $test_env->database->params->dbname;
         ${$server}['ssh_to'] = 'me@foobar';
         break;
      case 'development':
      case 'dev':
         ${$server}['host'] = $dev_env->database->params->host;
         ${$server}['username'] =                   $dev_env->database->params->username;
         ${$server}['password'] =                   $dev_env->database->params->password;
         ${$server}['dbname'] =                   $dev_env->database->params->dbname;
         ${$server}['ssh_to'] = 'me@foobar';
         break;
      }
}

print "Run this command on " . $from . ':' . PHP_EOL;
echo 'mysqldump -u ' . $from_server['username'] . ' -p' . $from_server['password'] . ' ' . $from_server['dbname']
. ' | ssh ' . $from_server['ssh_to'] . ' "mysql -u ' . $to_server['username'] . ' -p' . $to_server['password'] . ' ' . $to_server['dbname'] . '"' . PHP_EOL;