Modify multiple mysql databases at once (SAAS database changes)

Question

We are launching the PHP application (zend framework), which creates a database for each user (for security / backup and other reasons). All these databases have exactly the same structure, and this will always be the case. When we deploy new features, we need to deploy all databases with new fields / tables.

I read about using dbdeploy for this, but I'm not sure that they support multiple databases at once (without specifying names one by one). Databases are called user1, user2, user3, etc.

Are there any good tools that will make this process a little easier and less painful for us? We run phing for automatic deployment and found the manual http://www.davedevelopment.co.uk/2008/04/14/how-to-simple-database-migrations-with-phing-and-dbdeploy/ is not very useful, because that they do not support multiple databases, like ours.

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

Answer 1

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

I also have a backup of each database before it applies the changes. Part of the backup is for Linux / Unix right now, but it can be configured to work with other operating systems.

This is pretty verbose at the moment, so you can change it as needed. You can also change the line terminator, depending on whether you run it from the CLI or browser. I would suggest putting this in your script directory and running it from the CLI.

Let me know if you need anything else, or if this does not 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);