小型Drupal数据库备份以及大型站点MySQL备份策略分享(2)

#!/usr/bin/php -q <?php $to = "gaoxinzhao@gmail.com"; $hostname = exec('/bin/hostname'); $mycnf = "/home/robbin/.my.cnf"; $ignore = array('information_schema', 'test', 'mysql', 'wdcpdb'); function trimw($str) { $str = str_replace(array("n", "r", "t", " ", "o", "xOB"), '', $str); return $str; } if (!file_exists($mycnf)) { mail($to, "No .my.cnf exists on $hostname", "MySQL cannot dump because .my.cnf is missing on $hostname .") ; exit("cant get user creds"); } $myconf = file_get_contents($mycnf) or die( "Failed to open bmesh_admin's .my.cnf" ); preg_match( "/buser(.*)/", $myconf, $matches ) or die( mail($to, "No username in .my.cnf on $hostname", "MySQL cannot dump on $hostname")); $usr = (explode('=', $matches[0])); $user = trimw($usr[1]); preg_match( "/bpassword(.*)/", $myconf, $matches ) or die( mail($to, "No password in .my.cnf on $hostname", "MySQL cannot dump on $hostname")); $pass = (explode('=', $matches[0])); $password = trimw($pass[1]); mysql_connect("localhost",$user,$password) or die ("could not connect: " . mysql_error()); mysql_select_db("mysql"); $result = mysql_query("show databases"); $bpath = "/home/robbin/backup/mysql"; $btime = date("Y-m-d H:i:s"); $bstamp = strtotime($btime); $byear = date("Y", $bstamp); $bmonth = date("m", $bstamp); $bday = date("d", $bstamp); $btod = date("H-i-s", $bstamp); while ($res = mysql_fetch_array($result)) { $myDb = $res["Database"]; if (in_array($myDb, $ignore)) continue; $mdir = "$bpath/$byear/$bmonth/$bday/$btod/$myDb"; $out = `mkdir -p $mdir`; $myFile = $myDb . ".sql"; $bldCmd = "cd $mdir ; "; $bldCmd .= "mysqldump -u$user -p$password --single-transaction --add-drop-table -R -c -Q $myDb > $myFile ;"; //$bldCmd .= "chmod 644 $myFile ; "; //$bldCmd .= "chown root:root $myFile ; "; $bldCmd .= "gzip -9 $myFile"; print "Backing up $myDbn"; print "Securing $myDbn"; $out = `$bldCmd`; } $out = `chmod 700 $bpath/$byear`; print "$outn"; print "Backups are in $bpathn";

crontab的设置

复制代码 代码如下:

0 1 * * * /home/robbin/bin/mysql_backup.php

此外我们需要把备份的数据还要定期传送到其他服务器上，才会避免服务器崩溃而引发数据丢失。备份及时网站才有保证，这里仅仅只是笔者的一点点操作分享，大家有更好的备份策略，欢迎共享。