alter multiple mysql databases at once (SAAS database changes)

Question

We are running a PHP (zend framework) app that creates a database per user (for security/backup/and others reasons). All these databases have exactly the same structure and that will always be the case. When we deploy new features we'll need to expand all databases with the new fields/tables.

I've read about using dbdeploy for that, but I'm not sure that they support multiple databases at once (without giving in the names one by one). Databases are called user1, user2, user3 and so on.

Are there any good tools that will make this process for us a bit easier and less painful? We are running phing for automated deployment and found the guide http://www.davedevelopment.co.uk/2008/04/14/how-to-simple-database-migrations-with-phing-and-dbdeploy/ not so useful because they don't support multiple databases like we have.

Also, windows or mac mysql clients that can do this are possible for us, so we are open for anything

Answer 1

Here is a PHP script that I put together for you. It gets a list of all databases and applies the updates if the database name begins with user.

I also have it backup each database before it applies the changes. The backup portion is specific to Linux/Unix right now, but it can be tweaked to work on other operating systems.

It is pretty verbose at the moment, so you can change it as needed. You can also change the line terminator, depending if you will be running it from the CLI or a browser. I would suggest to put this in your scripts directory and run it from the CLI.

Let me know if you need anything else or if this doesn't work for you.

<?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);