通過mysqldump把sql文件導出,再用PHPMAILER把sql文件以附件的形式發送到指定的郵箱裡。最後再通過crontab任務每天去執行這個腳本,實現自動備份數據庫。
第一步先去github上把PHPMAILER下載到本地,地址:
https://github.com/PHPMailer/PHPMailer
第二步複製下面的mysql_mail_backup.php腳本,完成mysqldump導出數據及發送郵件的功能,需要配置一下數據庫的連接信息及郵箱的SMTP服務。
define('DB_HOST', '127.0.0.1');
define('DB_PORT', '3306');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', 'root');
define('DB_NAME', 'wordpress');
define('DB_TABLES', 'wp_posts');
$cmd = sprintf('mysqldump -h %s -P %s -u %s -p%s %s %s --skip-lock-table > ./%s.sql', DB_HOST, DB_PORT, DB_USERNAME, DB_PASSWORD, DB_NAME, DB_TABLES, DB_NAME);
printf($cmd."\n");
exec($cmd, $outArr, $ret);
if ($ret == 0) {
printf("mysqldump success \n");
} else {
printf("mysqldump failed \n");
}
require("PHPMailerAutoload.php");
define("SMTP_HOST", "smtp.163.com");
define("SMTP_PORT", 25);
define("MAIL_USERNAME", "[email protected]");
define("MAIL_PASSWORD", "XXXXXX");
define("MAIL_FROM", "[email protected]");
define("FROM_NAME", "XXXXX");
define("MAIL_TO", "[email protected]");
define("TO_NAME", "XXXXX");
$attachment = "./wordpress.sql";
$subject = "Mysql Backup";
$body = "New mysql backup";
$mail = new PHPMailer();
$mail - >CharSet = 'UTF-8';
$mail - >IsSMTP();
$mail - >Host = SMTP_HOST;
$mail - >SMTPAuth = true;
$mail - >Username = MAIL_USERNAME;
$mail - >Password = MAIL_PASSWORD;
$mail - >Port = SMTP_PORT;
$mail - >From = MAIL_FROM;
$mail - >FromName = FROM_NAME;
$mail - >AddAddress(MAIL_TO, TO_NAME);
$mail - >AddAttachment($attachment);
$mail - >IsHTML(true);
$mail - >Subject = $subject;
$mail - >Body = $body;
if (!$mail - >Send()) {
echo "send mail failed";
echo $mail - >ErrorInfo;
exit;
}
$clearCMD = "rm -rf ".DB_NAME.".sql";
exec($clearCMD, $outARR, $ret);
第三步,創建CRONTAB定時任務
crontab -e 加入下面這條任務
00 1 * * * /usr/bin/flock -xn /var/www/html/mysql_mail_backup/mysql_mail_backup.lock php /var/www/html/mysql_mail_backup/mysql_mail_backup.php >/dev/null 2>&1