How to automatically backup database tables in php

How to automatically back up database tables in php? This article will share with you how to use PHP to automatically back up database tables, as a reference for everyone, and I hope it can help you.

1. Preface

There are many ways to back up mysql database;

For example:

1. Use the mysqldump function

mysqldump - u username -p dbname table1 table2 ... > BackupName.sql

The dbname parameter represents the name of the database.

The table1 and table2 parameters represent the names of the tables that need to be backed up. If they are empty, the entire database will be used. Backup;

BackupName.sql parameter table is designed to be the name of the backup file. An absolute path can be added in front of the file name. The database is usually divided into a file with the suffix sql;

Basic usage:

2. Management tools

There are many ways to back up the database, the above two are more common

This article mainly explains how to automatically back up the database through php functions

2. Introduction to related functions

2.1, fopen

##2.2, array_keys

##2.3, array_values

2.4, implode

2.5, substr

##2.6, fwrite

3. Implementation ideas

4. Code composition

/**
 * [copyDb description] 备份数据库
 * @param [type] $dbname [description] 数据库名
 * @param [type] $fileName [description] 存储的文件名
 * @return [type]   [description]
 */
public function copyDb($dbname, $fileName){
	$myfile = fopen($fileName, "w") or die("Unable to open file!");//打开存储文件
	$this->link->query("use {$dbname}");//切换数据库
	$this->changeDb($dbname);
	$tables = $this->link->query('show tables');//获取当期数据库所有表名称
	while($re = $tables->fetch(PDO::FETCH_ASSOC)){
		//var_dump($re);//查看数组构成
		$tableName = $re['Tables_in_'.$dbname];//构成特定的下标
		$sql = "show create table {$tableName};";
		$tableSql = $this->link->query($sql);

		fwrite($myfile, "DROP TABLE IF EXISTS `{$tableName}`;\r\n");//加入默认删除表的遇见
		//下面备份表结构，这个循环之执行一次
		while($re = $tableSql->fetch(PDO::FETCH_ASSOC)){
			// echo "<pre class="brush:php;toolbar:false">";
			// var_dump($re);
			// echo "

"; echo "正在备份表{$re['Table']}结构
"; fwrite($myfile, $re['Create Table'].";\r\n\r\n"); echo "正在备份表{$re['Table']}结构完成
"; } //下面备份表数据 echo "正在备份表{$tableName}数据
"; $sql = "select * from {$tableName};"; $valueSql = $this->link->query($sql); while($re = $valueSql->fetch(PDO::FETCH_ASSOC)){ $keyArr = array_keys($re);//获得对应的键值 $valueArr = array_values($re);//获得对应的值 $keyStr = ''; foreach ($keyArr as $key => $value) { $keyStr .= "`".$value."`,"; } $keyStr = substr($keyStr,0,strlen($keyStr)-1); //取出最后一个逗号 $valueStr = ''; // var_dump($valueArr); foreach ($valueArr as $key => $value) { $valueStr .= "'".$value."',"; } //以上的处理只是对应sql的写法 $valueStr = substr($valueStr,0,strlen($valueStr)-1); //取出最后一个逗号 $sql = "insert into `{$tableName}`({$keyStr}) values({$valueStr})"; fwrite($myfile, $sql.";\r\n\r\n"); } echo "正在备份表{$tableName}数据完成
"; echo "

---

"; } fclose($myfile); }

5. Conclusion

The main process of backing up the database:

Switch to the corresponding database;

Use show create table tableName to obtain the table structure and write it to a file;

Then query all tables Data, loop generates the corresponding sql statement and writes it to the file;

The sql file generated by the trial run;

Related recommendations:

Navicat for MySQL scheduled backup Database and data recovery operations

How to automatically back up the database with PHP

Introduction to graphic code on how to use PHP to automatically back up the database table

The above is the detailed content of How to automatically backup database tables in php. For more information, please follow other related articles on the PHP Chinese website!