PHP备份MySQL数据库的详解

一、标准的数据库导出内容讲解

先看一个标准的数据库导出的sql内容：

/*
 Navicat Premium Data Transfer
 Source Server         : 192.168.1.122     
 Source Server Type    : MySQL
 Source Server Version : 50621
 Source Host           : 192.168.1.122:3306
 Source Schema         : db1
 Target Server Type    : MySQL
 Target Server Version : 50621
 File Encoding         : 65001
 Date: 20/07/2022 17:44:22
*/
 
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
 
-- ----------------------------
-- Table structure for table1
-- ----------------------------
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `name`(`name`) USING BTREE,
  INDEX `age`(`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
 
-- ----------------------------
-- Records of table1
-- ----------------------------
INSERT INTO `table1` VALUES (1, '王明', 30);
INSERT INTO `table1` VALUES (2, '张芳', 28);
 
SET FOREIGN_KEY_CHECKS = 1;

标注导出的数据库信息：

/*
 Navicat Premium Data Transfer
 
 Source Server         : 192.168.1.122     
 Source Server Type    : MySQL
 Source Server Version : 50621
 Source Host           : 192.168.1.122:3306
 Source Schema         : db1
 
 Target Server Type    : MySQL
 Target Server Version : 50621
 File Encoding         : 65001
 
 Date: 20/07/2022 17:44:22
*/

设置UTF-8编码格式：

SET NAMES utf8mb4;

取消外键约束 (参数为0) 和设置外键约束 (参数为1) ： 

SET FOREIGN_KEY_CHECKS = 0;
SET FOREIGN_KEY_CHECKS = 1;

 表结构：

-- ----------------------------
-- Table structure for table1
-- ----------------------------
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `name`(`name`) USING BTREE,
  INDEX `age`(`age`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

表数据：

-- ----------------------------
-- Records of table1
-- ----------------------------
INSERT INTO `table1` VALUES (1, '王明', 30);
INSERT INTO `table1` VALUES (2, '张芳', 28);

二、PHP导出数据库的常用操作

查询某个库是否存在，有两种写法：

SELECT * FROM `information_schema`.SCHEMATA WHERE SCHEMA_NAME="db1";
SHOW DATABASES LIKE "db1";

查询某个表是否存在，有两种写法：

SELECT * FROM `information_schema`.TABLES WHERE TABLE_NAME="table1";
SHOW TABLES LIKE "table1";

查询某个库的某个表的某个字段是否存在：

SELECT * FROM `information_schema`.COLUMNS WHERE TABLE_SCHEMA="db1" AND TABLE_NAME="table1" AND COLUMN_NAME="name";

获取某个库下的全部表名称：

SHOW TABLES;

获取表结构：

SHOW CREATE TABLE `table1`;

获取表数据，包括表字段和表数据：

SHOW COLUMNS FROM `table1`;
SELECT * FROM `table1`;

完整的扩展类代码：

 
header("Content-Type: text/html;charset=utf-8");
/**
* 备份数据库的扩展类
*/
 
class BackService{
	private $config=[];
	private $handler;
	private $table = array(); //需要备份的表
	private $begin; //开始时间
	private $error; //错误信息
 
	public function __construct($config) {
		$config['path'] = './file/'; //默认路径
		$config['sqlbakname'] = 'bak.sql'; //备份文件名称
		$this->config = $config;
		$this->connect();
	}
 
	//首次进行pdo连接
	private function connect() {
		try{
			$this->handler = new \PDO("{$this->config['type']}:host={$this->config['hostname']};port={$this->config['hostport']};dbname={$this->config['database']}",
				 $this->config['username'],
				 $this->config['password'],
				 array(
				 		\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES {$this->config['charset']}",
				 		\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
				 		\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
 
				 	));
		}catch (PDOException $e) {
			exit( "Error! ".$e->getMessage()."
" );
		}
	}
 
	/**
	*  查询
	*  @param string $sql
	*  @return mixed
	*/
	private function query($sql = '')
	{
		$stmt = $this->handler->query($sql);
		$stmt->setFetchMode(\PDO::FETCH_NUM);
		$list = $stmt->fetchAll();
		return $list;
	}
 
	/**
	*  获取全部表
	*  @param string $dbName
	*  @return array
	*/
	private function get_dbname($dbName = '*') {
		$sql = "SHOW TABLES";
		$list = $this->query($sql);
		$tables = array();
		foreach($list as $value){
			$tables[] = $value[0];
		}
		return $tables;
	}
 
	/**
	*  获取表定义语句
	*  @param string $dbName
	*  @return mixed
	*/
	private function get_dbhead($table = '') {
		$sql = "SHOW CREATE TABLE `{$table}`";
		$ddl = $this->query($sql)[0][1].";";
		return $ddl;
	}
 
	/**
	*  获取表数据
	*  @param string $table
	*  @return mixed
	*/
	private function get_dbdata($table = '')
	{
		$sql = "SHOW COLUMNS FROM `{$table}`";
		$list = $this->query($sql);
		//字段
		$columns = '';
		foreach($list as $value){
			$columns .= "`".$value[0]."`,";
		}
		$columns = substr($columns, 0, -1);
		//需要返回的SQL
		$query = '';
		$data = $this->query("SELECT * FROM `{$table}`");
		foreach($data as $value){
			$dataSql = '';
			foreach($value as $v){
				$dataSql .= "'".$v."',";
			}
			$dataSql = substr($dataSql, 0, -1);
			$query .= "INSERT INTO `{$table}` ({$columns}) VALUES ({$dataSql});\r\n";
		}
		return $query;
	}
 
	/**
	*  写入文件
	*  @param array $tables
	*  @param array $ddl
	*  @param array $data
	*/
	private function writeToFile($tables = array(), $ddl = array(), $data = array())
	{
		$verRes = $this->query("SELECT VERSION()");
		$version = str_replace('.', '', str_replace('-log', '', $verRes[0][0]));
		$version = substr($version, 0, 1).'0'.substr($version, 1);
		$str = "/*\r\nMySQL Database Backup Tools\r\n";
		$str .= "Source Server Type    : {$this->config['type']}\r\n";
		$str .= "Source Server Version : {$version}\r\n";
		$str .= "Source Host           : {$this->config['hostname']}:{$this->config['hostport']}\r\n";
		$str .= "Source Schema         : {$this->config['database']}\r\n\r\n";
		$str .= "Date: ".date('d/m/Y H:i:s')."\r\n*/\r\n";
		$str .= "SET NAMES utf8mb4;\r\n";
		$str .= "SET FOREIGN_KEY_CHECKS = 0;\r\n";
 
		$i = 0;
		foreach($tables as $table){
			$str .= "-- ----------------------------\r\n";
			$str .= "-- Table structure for {$table}\r\n";
			$str .= "-- ----------------------------\r\n";
			$str .= "DROP TABLE IF EXISTS `{$table}`;\r\n";
			$str .= $ddl[$i]."\r\n";
			$str .= "-- ----------------------------\r\n";
			$str .= "-- Records of {$table}\r\n";
			$str .= "-- ----------------------------\r\n";
			$str .= $data[$i]."\r\n";
			$i++;
		}
 
		if(!file_exists($this->config['path'])){
			mkdir($this->config['path']);
		}
 
		return file_put_contents($this->config['path'].$this->config['sqlbakname'], $str) ? true : false;
	}
 
	/**
	*  设置要备份的表
	*  @param array $tables
	*/
	private function setTables($tables = array())
	{
		if(!empty($tables) && is_array($tables)){
			//备份指定表
			$this->tables = $tables;
		}else{
			//备份全部表
			$this->tables = $this->get_dbname();
		}
	}
 
	/**
	*  备份
	*  @param array $tables
	*  @return bool
	*/
	public function backup($tables = array())
	{
		//存储表定义语句的数组
		$ddl = array();
		//存储数据的数组
		$data = array();
		$this->setTables($tables);
		if(!empty($this->tables)){
 
			foreach($this->tables as $table){
				$tableIsExist = $this->query("SELECT * FROM `information_schema`.TABLES WHERE TABLE_NAME='{$table}'");
				if(!$tableIsExist){
					$this->error = "表 {$table} 不存在";
					exit($this->getError());
				}
				$ddl[] = $this->get_dbhead($table);
				$data[] = $this->get_dbdata($table);
			}
 
			//开始写入
			return $this->writeToFile($this->tables, $ddl, $data);
		}else{
 
			$this->error = '数据库中没有表';
			return false;
		}
	}
 
	/**
	*  错误信息
	*  @return mixed
	*/
	public function getError()
	{
		return $this->error;
	}
 
	/**
	* 恢复数据库
	*/
	public function restore()
	{
		$filename = $this->config['sqlbakname'];
		$path = $this->config['path'].$filename;
		if(!file_exists($path)){
			
			$this->error("SQL文件不存在");
			return false;
		}else{
 
			$sql = $this->parseSQL($path);
			try{
				$this->handler->exec($sql);
				return true;
			}catch (PDOException $E){
				$this->error = $e->getMessage();
				return false;
			}
		}
	}
 
	/**
	*  解析SQL文件为SQL语句数组
	*  @param string $path
	*  @return array|mixed|string
	*/
	private function parseSQL($path = '')
	{
		$sql = file_get_contents($path);
		$sql = explode("\r\n", $sql);
 
		//先消除 -- 注释
		$sql = array_filter($sql, function($data){
			if(empty($data) || preg_match('/.*--.*/', $data)){
				return false;
			}else{
				return true;
			}
		});
		$sql = implode('', $sql);
		//删除 /**/ 注释
		$sql = preg_replace('/\/\*.*\*\//', '', $sql);
		return $sql;
	}
 
	/**
	*  下载备份文件
	*  @param string $fileName
	*  @return array|mixed|string
	*/
	public function downloadFile()
	{
		$fileName = $this->config['sqlbakname'];
		$fileName = $this->config['path'].$fileName;
		if(file_exists($fileName)){
			ob_end_clean();
			header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
			header("Content-Description: File Transfer");
			header("Content-Type: application/octet-stream");
			header("Content-Length: ".filesize($fileName));
			header("Content-Disposition: attachment; filename=".basename($fileName));
			readfile($fileName);
		}else{
			$this->error="文件不存在";
		}
	}
 
	/**
	*  获取文件时间
	*  @param string $file
	*  @return string
	*/
	private function getfiletime($file)
	{
		$path = $this->config['path'].$file;
		$a = filemtime($path);
		$time = date('Y-m-d H:i:s', $a);
		return $time;
	}
 
	/**
	*  获取文件大小
	*  @param string $file
	*  @return string
	*/
	private function getfilesize($file)
	{
		$perms = stat($this->config['path'].$file);
		$size = $perms['size'];
		$a = ['B', 'KB', 'MB', 'GB', 'TB'];
		$pos = 0;
		while ($size >= 1024) {
			$size /= 1024;
			$pos++;
		}
 
		return round($size, 2).$a[$pos];
	}
 
	/**
	*  获取文件列表
	*  @param string $order 级别
	*  @return string
	*/
	public function get_filelist($order = 0)
	{
		$filepath = opendir($this->config['path']);
		$fileAndFolderAyy = array();
		$i = 1;
		while (false != ($filename = readdir($filepath))) {
			
			if($filename != '.' && $filename != '..'){
				$i++;
				$fileAndFolderAyy[$i]['name'] = $filename;
				$fileAndFolderAyy[$i]['time'] = $this->getfiletime($filename);
				$fileAndFolderAyy[$i]['size'] = $this->getfilesize($filename);
			}
		}
 
		$order == 0 ? sort($fileAndFolderAyy) : rsort($fileAndFolderAyy);
		return $fileAndFolderAyy;
	}
 
	/**
	* 删除备份文件
	*/
	public function delfile()
	{
		$filename = $this->config['sqlbakname'];
		$path = $this->config['path'].$filename;
		
		if(file_exists($path)){
			unlink($path);
		}
 
		return true;
	}
}