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.

我已读到为此使用挖掘系统,但我不敢肯定它们会一度支持多个数据库(不以一个名称提供)。数据库称为用户1、用户2、用户3等。

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.

而且,我们能够做到这一点的窗口或马奇我的客户也有可能这样做,因此,我们对任何事情开放。

Answer 1

这里是我为你们而共同编写的文件。如果数据库名称从<代码>用户<>/代码>开始,则该数据库将获得所有数据库的清单,并采用最新资料。

在应用这些变化之前,我也支持每个数据库。备份部分目前是专门针对短链氯化石蜡/Unix的,但可能难以在其他操作系统上开展工作。

此时此刻,它很粗略,因此,你可以视需要改变。你们也可以改变分界线的确定者,这取决于你们能否从CLI或浏览器那里管理。我建议把这一内容列入你的文字目录,并从国家扫盲委员会管理。

让我知道,你们是否需要其他东西,或者这是否给你工作。

<?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 = "
";      // 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);

友情链接