利用PHPMAILER每天邮件备份mysql数据

通过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", "XXXX@163.com");

define("MAIL_PASSWORD", "XXXXXX");

define("MAIL_FROM", "XXXXX@163.com");

define("FROM_NAME", "XXXXX");

define("MAIL_TO", "XXXXXX@qq.com");

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