We're managing a PHP (zend framework) application that produces a database per user (for security/backup/yet others reasons). Each one of these databases have the identical structure which will invariably be. Whenever we deploy additional features we'll have to expand all databases using the new fields/tables.

I have find out about using dbdeploy for your, but I am unsure they support multiple databases at the same time (without giving within the names 1 by 1). Databases are known as user1, user2, user3 and so forth.

What are the good tools that can make this method for all of us a little simpler and fewer painful? We're running phing for automated deployment and located the guide http://www.davedevelopment.co.uk/2008/04/14/how-to-simple-database-migrations-with-phing-and-dbdeploy/ not too helpful because they do not support multiple databases like we've.

Also, home windows or mac mysql clients that may do that are feasible for us, therefore we are open for anything

This is a PHP script which i come up with for you personally. It will get a listing of databases and is applicable the updates when the database title starts with user.

I additionally get it backup each database before it is applicable the alterations. The backup portion is specific to Linux/Unix at this time, but it may be tweaked to operate on other os's.

It's pretty verbose right now, so that you can change it out when needed. You may also alter the line terminator, depending if you are running it in the CLI or perhaps a browser. I recommend to place this inside your scripts directory and run it in the CLI.

Tell me if you want other things or maybe this does not meet your needs.

<?php
// Configure these as needed
$db_host = 'localhost';
$db_user = 'user';
$db_pass = 'password';

$datetime_pattern       = date('Ymd.His');
$backup_file_path       = "/path/to/db_backups/$datetime_pattern/";
$backup_file_format     = "db_backup.%s.sql";
$backup_syntax_pattern  = "/usr/bin/mysqldump --host=%s --user=%s --password=%s --opt %s > $backup_file_path/db_backup.%s.sql";
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!
// CHANGE THE PERMISSIONS!!!!!!
// !!!!!!!!!!!!!!!!!!!!!!!!!!!!
$backup_file_permission = 0777;

// Choose how to terminate your lines
$line_end = "\n";      // Use for CLI
//$line_end = "<br/>";   // Use for browser

// Match words that begin with 'user', case-insensitive
$pattern = '/^user/i';

// What changes will we be applying?
$db_update_syntax = array("ALTER TABLE foo ADD baz1 VARCHAR(30) AFTER bar1",
                          "ALTER TABLE foo ADD baz2 VARCHAR(30) AFTER bar2",
                          "ALTER TABLE foo ADD baz3 VARCHAR(30) AFTER bar3",
                         );

// END OF CONFIGURATION
/////////////////////////////////////////////////////////////


// Create the database backup directory
if (!mkdir($backup_file_path, $backup_file_permission, true)) {
    die('Failed to create backup directory...');
}

// Connecting to MySQL.
$conn = @mysql_connect($db_host, $db_user, $db_pass)
        or die('Not connected : ' . mysql_errno() . ': ' . mysql_error());

$db_list = mysql_list_dbs($conn);

echo "{$line_end}Starting Database Update.{$line_end}";
while ($row = mysql_fetch_assoc($db_list)) {
    $db_name = $row['Database'];
    if (preg_match($pattern, $db_name)) {
        echo "{$line_end}A match was found: [$db_name]{$line_end}";
        echo "Backing up the database{$line_end}";
        // Backup the database
        $backup_syntax = sprintf($backup_syntax_pattern, $db_host, $db_user, $db_pass, $db_name, $db_name);
        exec($backup_syntax);
        $db_selected = mysql_select_db($db_name, $conn)
                       or die("Can't use [$db_name] : " . mysql_error());

        foreach ($db_update_syntax as $each_update_syntax) {
            echo "Altering using: [$alter_syntax]{$line_end}";
            $update_status = mysql_query($alter_syntax);
            if ($update_status) {
                echo "Success!{$line_end}{$line_end}";
            } else {
                echo "Unable to update [$db_name] : " . mysql_error() . "{$line_end}{$line_end}";
            }
        }
    } else {
        echo "Ignoring: [$db_name]{$line_end}";
    }
}
echo "Finished!{$line_end}";
// Free resources / Close MySQL Connection
mysql_free_result($db_list);
mysql_close($conn);