您的位置:首页 > MySQL MySQL备份脚本
站内搜索

MySQL备份脚本

mysqlbackup.php:

array(			'localhost',			'root',			'password',			array(),//为空备份全部数据库,否则备份这些数据库			'D:/xampp/mysql/bin/mysqldump',	//备份工具			dirname(__FILE__)."/localhost",//目录加主机名			5,//删除前5天的SQL文件		),);	foreach($configs as $config) {		backup($config);	}	function backup($config) {		list($host, $username, $password, $databases, $backuptool, $backupdir, $day) = $config;		$command = "$backuptool -u $username -h $host -p$password %s > %s";		$logsfile = $backupdir.'/'.date('ymd').'.log';		$backfilename = $backupdir.'/'.date('Ymd')."%s.sql"; //备份的SQL文件,以数据库命名				if(!is_dir($backupdir)) {			mkdir($backupdir, 0755 , true); 		}				//删除前十天的备份文件		get_dir_files($backupdir, $returnval);		if($returnval) {			foreach($returnval as $v) {				$time = filemtime($v);				if($time < strtotime("-$day day") && (pathinfo($v,PATHINFO_EXTENSION))=='zip') {					unlink($v);				}			}		}				if(!$databases) {			$databases = getdatabases($host, $username, $password);		}		//开始备份		foreach($databases as $database) {			$outputfile = sprintf($backfilename, $database);			$execcommand = sprintf($command, $database, $outputfile);			try {				if(system($execcommand) === false) {					throw new Exception('execute backup command error!');				}				if(file_exists($outputfile)) {					$zip = new ZipArchive();					$filename = pathinfo($outputfile,PATHINFO_FILENAME);					$zipname = $filename.'.zip';					if($zip->open($zipname, ZIPARCHIVE::OVERWRITE) === true) {						$zip->addFile($outputfile, $filename.'.'.pathinfo($outputfile,PATHINFO_EXTENSION));												$zip->close();					}else {						throw new Exception('ZipArchive open error!');					}					$resource = file_get_contents($zipname);					file_put_contents($backupdir.'/'.$zipname, $resource);					unlink($zipname);					unlink($outputfile);					$message = date('Y-m-d H:i:s')."$database backup complete!/r/n";					logs($logsfile, $message);				}			}catch(Exception $e) {				$message = date('Y-m-d H:i:s').$e->getLine().' '.$e->getMessage()."/r/n";				logs($logsfile, $message);			}		}	}	function getdatabases($host, $username, $password) {		$databases = array();		try {				$mysqli = new Mysqli($host, $username, $password);				$result = $mysqli->query("show databases");				if($result) {					while($row = $result->fetch_row()) {						(current($row)!='information_schema' && current($row)!='mysql' && current($row)!='performance_schema') && $databases[] = current($row);					}					$result->free_result();				}				$mysqli->close();							}catch(Exception $e) {				$message = date('Y-m-d H:i:s').$e->getLine().' '.$e->getMessage()."/r/n";				logs($logsfile, $message);		}		return $databases;	}		function logs($file, $contents) {		$dirname = dirname($file);		try {			if(!is_dir(dirname($file)) || !is_writeable(dirname($file))) {				throw new Exception("file is not direcotory or file can't write");			}			file_put_contents($file, $contents, FILE_APPEND);		}catch(Exception $e) {			$message = date('Y-m-d H:i:s').$e->getLine.' '.$e->getMessage()."/r/n";			logs($logfile, $message);		}	}			//获取当前目录下的文件(不包含子文件夹)	function get_dir_files($currPath, &$returnVal=array()) {		if(is_dir($currPath)) {			$currPath = (substr($currPath,-1,1)=='/')?substr($currPath,0,strlen($currPath)-1):$currPath;			if($handler = opendir($currPath)) {				while(($fileName = readdir($handler)) !== false) {					if($fileName != '.' && $fileName != '..' && $fileName[0] != '.') {						if(is_file($currPath . '/' . $fileName)) {							$returnVal[] = $currPath . '/' . $fileName;						}					}				}				closedir($handler);			}		}	}?>

mysqlbackup.bat:

D:/xampp/php/php.exe -q D:/wamp/www/php_lib/basic/mysqlbackup.phppause;

linux系统shell备份MySQL:

#!/bin/sh# sed -i 's/^M//g' /home/taskschd/backup.sh#注意:^M的输入方式是 Ctrl + v ,然后Ctrl + Mdbs=(test)ROOT_DIR=/home/backup/for dbname in ${dbs[@]} do#备份数据BACKUP_DIR=$ROOT_DIR$dbname'_'$(date +%Y%m%d).sql/usr/local/mysql/bin/mysqldump --opt -uroot -pabc $dbname > $BACKUP_DIR#删除三天前数据delete_file=$dbname'_'$(date -d "-5 day" "+%Y%m%d")'.sql'rm $ROOT_DIR$delete_file done

另为一篇shell备份mysql脚本:http://www.cnblogs.com/luoyunshu/p/3435378.html

  • 上一篇:[MySQL5.6] 最近对group commit的小优化_MySQL
  • 下一篇:数据库中的左连接和右连接的区别_MySQL