Get to a Tinkphp database backup restore and download the complete example

Takeaway:

It took a lot of references to make a complete tinkPHP5.0 database backup, restore and download function. My database is relatively small, so the backup did not make sub-volume, there is a need to sub-volume of children’s shoes, you can refer to other database sub-volume tutorial, and then modify it! The first two renderings for you to see:

Backup-php (); backup-php ()

White may wonder, why am I encapsulating a class? Because these methods may be called multiple times and encapsulated into a single class, code reuse is reduced. For example, the code is called for manual backup and is also called for automatic backup on a daily basis. Ok, go straight to the code:

      
namespace app\common;

use think\Db;
use think\Model;
use think\Cookie;
use think\Exception;
/** * Backup database class */
class Backup {
	/* * Export database to SQL file *@param$string $dir Backup directory (optional, default data) */
	public static function export($dir='data') {
		// The default path is the data folder in the root directory of the site
		$path = ROOT_PATH .$dir.DIRECTORY_SEPARATOR;	
		// Get the database name
		$dbname=config("database.database");	
		// Check whether the directory exists
		if(! is_dir($path)) { 
			// Create a directory
			mkdir($path.0777.true);
		}
		// Check whether the directory is writable
		if(! is_writable($path)) {
			chmod($path.0777);
		}
		// Check whether the file exists
		$fileName=$dbname.'_'.date("Y-m-d",time()).'.sql';
		// The database saves relative paths
		$filePath = $dir.DIRECTORY_SEPARATOR.$fileName;
		// The disk saves the absolute path
		$savepath = $path.$fileName;
		try{
			if(! file_exists($savepath)) {
				// Get the mysql version
				$version = Db::query("select version() as ver");
				$ver = $version[0] ['ver'];
				$info = "-- ----------------------------\r\n";
				$info. =-- Date:".date("Y-m-d H:i:s",time())."\r\n";
				$info. ="-- MySQL - ".$ver." : Database - ".$dbname."\r\n";
				$info. ="-- ----------------------------\r\n\r\n";
				$info. ="CREATE DATAbase IF NOT EXISTS `".$dbname."` DEFAULT CHARACTER SET utf8 ; \r\n\r\n";
				$info. ="USE `".$dbname."`; \r\n\r\n";
				file_put_contents($savepath.$info,FILE_APPEND);
				// Query all tables
				$sql="show tables";
				// Execute native SQL statements
				$result=Db::query($sql);			
				foreach ($result as $k= >$v) {
					// Query the table structure
					$table = $v['Tables_in_'.$dbname];
					$sql_table = "show create table ".$table;
					$res = Db::query($sql_table); 			
					$info_table = "-- ----------------------------\r\n";
					$info_table. ="-- Table structure for `".$table."`\r\n";
					$info_table. ="-- ----------------------------\r\n\r\n";
					$info_table. ="DROP TABLE IF EXISTS `".$table."`; \r\n\r\n";
					$info_table. =$res[0] ['Create Table']."; \r\n\r\n";
					// Query table data
					$info_table. ="-- ----------------------------\r\n";
					$info_table. ="-- Data for the table `".$table."`\r\n";
					$info_table. ="-- ----------------------------\r\n\r\n";
					file_put_contents($savepath.$info_table,FILE_APPEND);
					$sql_data = "select * from ".$table;
					$data = Db::query($sql_data);
					$count= count($data); 
					if($count<1) continue;
					foreach ($data as $key= >$value) {
						$sqlStr = "INSERT INTO `{$table}` VALUES (";
						foreach($value as $column) {
							// Escape single quotes and newlines
							$column = str_replace( array("'"."\r\n"), array("\ '"."\\r\\n"),$column);
							// When the value is null, use the default value
							$sqlStr. =empty($column)?"default, " : "'".$column."',";
						}
						// Remove the last comma and space
						$sqlStr = substr($sqlStr.0,strlen($sqlStr) -2);
						$sqlStr. ="); \r\n";
						//$sqlStr = "INSERT INTO `{$table}` VALUES ('" . str_replace(array("\r", "\n"), array('\\r', '\\n'), implode("', '", $value)) . "'); \n";
						file_put_contents($savepath.$sqlStr,FILE_APPEND);
					}
					$info = "\r\n";
					file_put_contents($savepath.$info,FILE_APPEND);
				}
				// Calculate the file size
				$size=filesize($savepath);
				return array('code'= >1.'msg'= >'Database backup successful'.'name'= >$fileName.'size'= >$size.'path'= >$filePath);
			}else{
				return array('code'= >0.'msg'= >'Backup file already exists'); }}catch (\Exception $e) {
			return array('code'= >0.'msg'= >'Backup database failed, Error:'.$e); }}/** * import SQL file into database **@paramString $sqlFILE SQL file */
	public static function import($sqlfile) {
		if(! file_exists($sqlfile)) {
			return array('code'= >0.'msg'= >'Backup file not found');
		}
		try {
			// Create an array to hold the SQLSQL statements
			$sqls = array(a);$file = fopen ($sqlfile."rb" );
			// Create a table buffer variable
			$table_buffer = ' ';
			while(! feof ($file)) {// Read each row of SQL
				$row = fgets ( $file);
				// If the end does not contain '; '(that is, a full SQL statement, in this case an insert statement), and does not contain 'ENGINE='(that is, the last sentence to create a table)
				if (! preg_match ( '/; / '.$row ) || preg_match ( '/ENGINE=/'.$row )) {
					// store the SQL statement with the create table SQL join
					$table_buffer. =$row;
					// If it contains the last sentence to create the table
					if (preg_match ( '/ENGINE=/'.$table_buffer)) {
						// Execute the SQL statement to create the table
						Db::execute($table_buffer);
						// Clear the current table to prepare the next table creation
						$table_buffer = ' ';
					}
					// Skip this one
					continue;
				}
				// Execute the SQL statement
				Db::execute($row);
			}
			fclose ($file);
			return array('code'= >1.'msg'= >'Database restore successful');	
		}
		catch (\Exception $e) {
			return array('code'= >0.'msg'= >'Restore database failed, Error:'.$e); }}}Copy the code

Create a database table to store the backup contents

-- ----------------------------
-- Table structure for `database`
-- ----------------------------
DROP TABLE IF EXISTS `database`;
CREATE TABLE `database` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `size` varchar(20) DEFAULT '0',
  `path` varchar(255) DEFAULT NULL,
  `create_time` int(11) DEFAULT NULL,
  `update_time` int(11) DEFAULT NULL,
  `delete_time` int(11) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
Copy the code

Backup database

Thinkphp back-end controller code

      
namespace app\admin\controller;
use think\Controller;
use think\Db;
use app\common\Backup;
    / * * * *@name=' backup '*/
    public function backup()
    {
        if(request()->isPost()){
			$res=Backup::export();
			if($res['code'] = =1) {// Write to the database
				DatabaseModel::create([
					'name'= >$res['name'].'size'= >$res['size'].'path'= >$res['path']]);$this->success($res['msg']);	
			}else{
				$this->error($res['msg']); }}}Copy the code

4. Download backup files

1, download the database backup file, need to first. SQL files compressed into a zip, it is recommended that the front-end used jszip plug-in, this article jszip tutorial parameters: blog.csdn.net/qq15577969/…

Ps: Some friends may say, directly use PHP built-in zip compression function can not it, why bother. This is also possible, but only if the PHp_zip. DLL extension is installed on your server’s VERSION of PHP, and then zip compression can be implemented using the ZipArchive class. The version of PHP5.6 used by the blogger does not have this extension, and the system developed by the blogger is to be authorized to other users, users may not know how to install PHP extension, so in order to be safe, directly choose the solution with front-end JsZip plug-in to achieve compression.

2. Front-end JS code:

<script type="text/javascript" src="./jszip.min.js">
</script>
<script type="text/javascript">
	$.post('Back-end download address', {
		'token': '{$token}'.'id': data.id
	},
	function(res) {
		if (res.code == 1) {
			var zip = new JSZip();
			Res.name is the file name, res.content is the file content
			zip.file(res.name, res.content);
			// Generate a zip file and download it
			zip.generateAsync({
				type: 'blob'.// Compression type
				compression: "DEFLATE".// STORE: DEFLATE is not compressed by default
				compressionOptions: {
					level: 9 // Compression levels 1 to 9 1 Has the fastest compression speed and 9 has the best compression mode
				}
			}).then(function(content) {
				// The file name of the download
				var filename = res.name + '.zip';
				// Create hidden downloadable links
				var eleLink = document.createElement('a');
				eleLink.download = filename;
				eleLink.style.display = 'none';
				// The downloaded content is converted to a BLOB address
				eleLink.href = URL.createObjectURL(content);
				// Trigger the click
				document.body.appendChild(eleLink);
				eleLink.click();
				// Then remove
				document.body.removeChild(eleLink);
			});
		} else{ mui.toast(res.msg); }});</script>
Copy the code

3, ThinkPHP back-end controller code:

      
   / * * * *@name=' download '*/
    public function down()
    {
		if(request()->isPost()){
			$data=input('post.');
			!isset($data['id'&&])$this->error('Parameter invalid'); ! is_numeric(trim($data['id'&&]))$this->error('Parameter invalid');	
			$file = DatabaseModel::field('name,path')->where('id',intval($data['id']))->find();
			empty($file) && $this->error('Backup data does not exist');
			$path=ROOT_PATH.$file['path'];
			// Read the file
			$content=file_get_contents($path);
			if (!empty($content)) {
				$arr= ['code'= >1.'name'= >$file['name'].'content'= >$content
				];
                header('Content-Type:application/json; charset=utf-8');
                echo json_encode($arr, JSON_UNESCAPED_UNICODE);
                exit;
			}else{
				$this->error('Backup file corrupted'); }}}Copy the code

5. Restore backup files

Mysql > alter database configuration in database.php

// Database connection parameters
'params'= > ['MYSQL_ATTR_USE_BUFFERED_QUERY'= >true,].Copy the code

2, ThinkPHP backend controller code:

/ * * * *@name=' restore '*/
    public function recover()
    {
		if(request()->isPost()){
			$data=input('post.');
			!isset($data['id'&&])$this->error('Parameter invalid'); ! is_numeric(trim($data['id'&&]))$this->error('Parameter invalid');	
			$file = DatabaseModel::field('id,name,path')->where('id',intval($data['id']))->find();
            empty($file) && $this->error('Backup data does not exist');
			// Full path to the.sql file
			$path=ROOT_PATH.$file['path'];
			$res=Backup::import($path);
			if($res['code'] = =1) {$result=DatabaseModel::where('id',intval($file['id']))->find();
				if(!$result) {// Delete the backup file
					unlink($path);
				}
				$this->success($res['msg']);
			}else{
				$this->error($res['msg']); }}}Copy the code

3. During restoration, users must be warned first, otherwise data will be lost, as follows:

6. Delete backup files

Thinkphp backend controller code:

   / * * * *@name=' delete '*/
    public function del()
    {
		if(request()->isPost()){
			$data=input('post.');
			!isset($data['id'&&])$this->error('Parameter invalid'); ! is_numeric(trim($data['id'&&]))$this->error('Parameter invalid');
			Db::startTrans();
			try{
				$file = DatabaseModel::field('path')->where('id',intval($data['id']))->find();
				$path=ROOT_PATH.$file['path'];
				if(file_exists($path)) {// ThinkPHP uses the unlink function to delete files. The argument is the file's address
					unlink($path);					
				}	
				DatabaseModel::where('id',intval($data['id']))->delete();				
				Db::commit();
			} catch (\Exception $e) {
				Db::rollback();
				$this->error('Failed to delete backup');				
			}
			$this->success('Backup deleted successfully'); }}Copy the code