PHP MYSQL 备份脚本

php-mysql-backup.php

#!/usr/bin/php
<?php
define('MYSQL_HOST', 'localhost');
define('MYSQL_USERNAME', '[USERNAME]');
define('MYSQL_PASSWORD', '[PASSWORD]');

define('BACKUP_TEMP_DIR', '/tmp/mysql_backup/');
define('BACKUP_DIR', '/var/backup/');

$start_time = time();

// 创建MYSQL连接
$DB = new mysqli(MYSQL_HOST, MYSQL_USERNAME, MYSQL_PASSWORD);
$DB->set_charset('utf8');

// 获取用户数据库列表
$dblist = array();
$sys_dblist = array('information_schema', 'mysql', 'performance_schema', 'phpmyadmin');
$query = $DB->query('SHOW DATABASES');
while ($result = $query->fetch_assoc()) {
	if (!in_array($result['Database'], $sys_dblist))
		$dblist[] = $result['Database'];
}

// 创建必须目录
if (!is_dir(BACKUP_TEMP_DIR))
	mkdir(BACKUP_TEMP_DIR, 0755, TRUE);
if (!is_dir(BACKUP_DIR))
	mkdir(BACKUP_DIR, 0755, TRUE);

// 备份数据库
$command_templete = 'mysqldump -u%s -p%s %s > %s';
foreach ($dblist as $dbname) {
	echo "Backup {$dbname}...\r\n";
	$save_path = BACKUP_TEMP_DIR . $dbname . '.sql';
	$command = sprintf($command_templete, MYSQL_USERNAME, MYSQL_PASSWORD, $dbname, $save_path);
	`$command`;
}

// 压缩数据库备份
echo "Compress backup...\r\n";
$zip = new ZipArchive();
$zip_path = BACKUP_DIR . date('Y-m-d') . '.zip';
$zip->open($zip_path, ZipArchive::OVERWRITE);
foreach ($dblist as $dbname) {
	$save_path = BACKUP_TEMP_DIR . $dbname . '.sql';
	$zip->addFile($save_path, $dbname . '.sql');
}
$zip->close();

// 删除旧的备份文件
echo "Delete old backup...\r\n";
$backup_filelist = glob(BACKUP_DIR . '*.zip');
foreach ($backup_filelist as $each_file) {
	if ($start_time - fileatime($each_file) > 604800) {
		echo "Delete {$each_file}\r\n";
		unlink($each_file);
	}
}

// 删除临时文件
echo "Delete temp file...\r\n";
foreach ($dblist as $dbname) {
	unlink(BACKUP_TEMP_DIR . $dbname . '.sql');
}
rmdir(BACKUP_TEMP_DIR);

// 总耗时
echo "All done, cost " . (time() - $start_time) . " seconds\r\n";
?>