php mysql搭建一个saas_php – 一次更改多个mysql数据库(SAAS数据库更改)

这是我为您准备的PHP脚本.它获取所有数据库的列表,并在数据库名称以user开头时应用更新.

我还让它在应用更改之前备份每个数据库.备份部分现在特定于Linux / Unix,但可以调整以适用于其他操作系统.

目前它非常冗长,因此您可以根据需要进行更改.您还可以更改行终止符,具体取决于您是从CLI还是浏览器运行它.我建议将它放在您的脚本目录中并从CLI运行它.

如果您需要其他任何内容或者这对您不起作用,请告诉我.

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