How to use PHP to automatically back up a database

How to use PHP to automatically back up a database

1. Preface

There are many ways to backup MySQL database;

For example:

1. Using the Mysqldump function

Mysqldump-u username-p dbname table1 table2 ... > Backupname.sql

1. The dbname parameter represents the name of the database
2. The table1 and table2 parameters represent the names of the tables that need to be backed up, and the entire database is backed up as empty;
3. Backupname.sql parameter table design the name of the backup file with an absolute path before the file name. Typically the database is partitioned into a file with a suffix called SQL;

Basic use:

2. Management Tools

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

And this time mainly explains how to use PHP functions to automatically back up the database

2. Introduction of related functions

2.1, fopen

Detailed reference: http://www.w3school.com.cn/php/func_filesystem_fopen.asp

2.2, Array_keys

Detailed reference: http://www.w3school.com.cn/php/func_array_keys.asp

2.3, Array_values

2.4, implode

Detailed reference: http://www.w3school.com.cn/php/func_string_implode.asp

2.5, SUBSTR

Detailed reference: http://www.w3school.com.cn/php/func_string_substr.asp

2.6, Fwrite

Detailed reference: https://www.w3cschool.cn/php/func-filesystem-fwrite.html

3. Realization of Ideas

4. Code composition

/** * [copydb description] Backing up database * @param [type] $dbname [description] Database name * @param [Type] $fileName [description] Stored file name * @return [Type] [description] */Public functionCopydb($dbname,$fileName){$myfile=fopen($fileName, "W")Or Die("Unable to open file!");Open storage File$this-Link-Query("Use {$dbname}");Switch database$this-Changedb($dbname);$tables=$this-Link-Query(' Show Tables ');Get all table names for the current databaseWhile($re=$tables-Fetch(Pdo::Fetch_assoc)){Var_dump ($re);//view Array composition$tableName=$re[' Tables_in_ '.$dbname];Make up a specific subscript$sql= "Show create TABLE {$tableName};";$tableSql=$this-Link-Query($sql);Fwrite($myfile, "DROP TABLE IF EXISTS ' {$tableName} '; \ r \ n");Join the default Delete table to meetTo back up the table structure, this loop executes onceWhile($re=$tableSql-Fetch(Pdo::Fetch_assoc)){echo "<pre>";Var_dump ($re);echo "</pre>";Echo"Backing up table {$re [' table ']} structure <br/>";Fwrite($myfile,$re[' Create Table ']."; \r\n\r\n");Echo"Backing up table {$re [' table ']} structure complete <br/>";}Back up table data belowEcho"Backing up Table {$tableName} data <br/>";$sql= "SELECT * from {$tableName};";$valueSql=$this-Link-Query($sql);While($re=$valueSql-Fetch(Pdo::Fetch_assoc)){$keyArr=Array_keys($re);Get the corresponding key value$valueArr=Array_values($re);Get the corresponding value$keyStr= ‘‘;Foreach($KEYARR as $key=$value) {$keyStr.= "`".$value."`,";}$keyStr=Substr($keyStr,0,Strlen($keyStr)-1); Remove the last comma$valueStr= ‘‘;Var_dump ($VALUEARR);Foreach($VALUEARR as $key=$value) {$valueStr.= "‘".$value."‘,";}The above processing is only the corresponding SQL notation$valueStr=Substr($valueStr,0,Strlen($valueStr)-1); //take out the last comma  $sql =  "INSERT INTO ' {$tableName} ' ({$keyStr}) VALUES ({$valueStr})" ; $myfile , $sql ); }echo  "backing up table {$tableName} data completion <br/> "echo  "<br/>; }fclose ( $myfile }            

5. Conclusion

The primary process for backing up a database:

1. Switch to the corresponding database;

1. Use show create TABLE TableName to get the tables structure and write to the file;

1. Then query all the table data, the loop generated relative to the SQL statement, write to the file;

1. The SQL file generated by the trial run;

How to use PHP to automatically back up a database