php mysql backup and recovery volume processing (database import and export)_PHP tutorial

Volume processing is to divide the data we want to process into small files for processing. Now I will introduce to you a php mysql backup and recovery volume processing class to realize mysql database volume backup and select tables for backup. Implement single sql file and volume sql import.

Detailed explanation of sub-volume import categories and ideas

Database import and export is a necessary function for a backend. If you search online, you will find a lot about database import and export, but basically it is a large system that contains many things that we do not need, and they are all their own backends. What I don’t like is taking other people’s stuff and integrating it into my own backend. What I need is my own stuff. So I made a lot of references and wrote a class about volume import. for easy calling. Welcome everyone to make bricks.

Here, the volume file ends with '_v1.sql' to implement single sql file and volume sql import. For volume import, you can choose whether to import the current volume into the remaining volumes. We only need to call the class directly. Complete

//They are the host, user name, password, database name, and database encoding
$db = new DataManage ( 'localhost', 'root', 'root', 'test', 'utf8' );
//sql file, whether to import only a single sql (that is, if there are other volumes, it will not be imported)
$db->restore ( './backup/20120516211738_all_v1.sql', false ); Corresponds to how to list the backup sql files or select sql, etc., you can implement it yourself. That is not in this category and is very simple. .

Actual demonstration effect:

At present, only database import has been implemented, and functions for database export are being written.

The following is the complete class code: (The specific ideas and implementation code are explained in the code, so I won’t go into details here)

The code is as follows

Copy code

/** * @author yanue * @copyright Copyright (c) 2012 www.yanue.net * Note: The volume file ends with _v1.sql * Function: Implement single sql file and volume sql import. For volume import, you can choose whether to import the current volume into the remaining volumes * How to use: * * *------------------------------------------------ ------------------ //They are the host, user name, password, database name, and database encoding $db = new DataManage ( 'localhost', 'root', 'root', 'test', 'utf8' ); //sql file, whether to import only a single sql (that is, if there are other volumes, it will not be imported) $db->restore ( './backup/20120516211738_all_v1.sql', false ); *------------------------------------------------ -------------------------- ​*/ class DataManage { var $db; // Database connection var $database; // Database used var $sqldir; // Database backup folder /**   * 初始化   *   * @param string $host   * @param string $username   * @param string $password   * @param string $database   * @param string $charset  */ function __construct($host = 'localhost', $username = 'root', $password = '', $database = 'test', $charset = 'utf8') { $this->host = $host; $this->username = $username; $this->password = $password; $this->database = $database; $this->charset = $charset; // Connect to database $this->db = mysql_connect ( $this->host, $this->username, $this->password ) or die ( "Database connection failed." ); //Choose which database to use mysql_select_db ( $this->database, $this->db ) or die ( "Unable to open database" ); // Database encoding method mysql_query ( 'SET NAMES ' . $this->charset, $this->db ); } /** * Import backup data * Description: Volume file format 20120516211738_all_v1.sql * * @param string $sqlfile * @param bool $single ​*/ function restore($sqlfile, $single = FALSE) { // Check if the file exists if (! file_exists ( $sqlfile )) { exit ( "File does not exist! Please check" ); } $this->lock ( $this->database ); // Get the database storage location $sqlpath = pathinfo ($sqlfile); $this->sqldir = $sqlpath ['dirname']; // Check whether it contains sub-volumes, separate files like 20120516211738_all_v1.sql from _v, if there are sub-volumes, it means there are sub-volumes $volume = explode ( "_v", $sqlfile ); $volume_path = $volume [0]; echo "Do not refresh and close the browser to prevent the program from being terminated. If you are not careful! The database structure will be damaged "; echo "Importing backup data, please wait! "; if (empty ( $volume [1] ) || $single) { echo "Importing sql:" . $sqlfile . ' '; // No paper division if ($this->_import ( $sqlfile )) { echo "Database imported successfully!"; } else { exit ( 'Database import failed!' ); } } else { // If there are sub-volumes, get the current sub-volume and execute the remaining sub-volumes in a loop $volume_id = explode ( ".sq", $volume [1] ); //The current volume is $volume_id $volume_id = intval ( $volume_id [0] ); while ( $volume_id ) { $tmpfile = $volume_path . "_v" . $volume_id . ".sql"; // Other volumes exist, continue execution If (file_exists ( $tmpfile )) { // Execute the import method echo "Importing volume $volume_id: " . $tmpfile . ' '; If ($this->_import ( $tmpfile )) { } else { exit ( "Import volume $volume_id: " . $tmpfile . 'Failed! The database structure may be damaged! Please try to start from volume 1 Import' ); } } else { echo "All backups of this volume were imported successfully! "; Return; } $volume_id ++; } } } /** * Import sql into the database (normal import) * * @param string $sqlfile * @return boolean ​*/ private function _import($sqlfile) { $name = basename ( $sqlfile ); $sqls = file ( $sqlfile ); foreach ( $sqls as $sql ) { str_replace ( "r", "", $sql ); str_replace ( "n", "", $sql ); if (! mysql_query ( trim ( $sql ), $this->db )) Return false; } return true; } // Close database connection private function close() { mysql_close ( $this->db ); } // Lock the database to avoid errors during backup or import private function lock($tablename, $op = "WRITE") { if (mysql_query ( "lock tables " . $tablename . " " . $op )) Return true; else Return false; } // Unlock private function unlock() { if (mysql_query ( "unlock tables" )) Return true; else Return false; } // Destruction function __destruct() { mysql_query ( "unlock tables", $this->db ); mysql_close ( $this->db ); } }

mysql backup and restore is processed in volumes, easy to call

The idea of ​​importing by volume: read the sql file line by line, save each line as a complete sql statement to the array, and then loop and execute the insertion into the database. However, the create table statement is divided into multiple lines, which needs to be processed separately (just this It took me a long time);

(Wow, the article feels so long! It’s mainly occupied by the class file).

Updated: October 6, 2012
Update instructions: 1. Remove the comment '– ' in the sql file when importing sql, thereby solving the problem that single and double quotes in sql cannot be imported
2. Directly execute the sql after reading a single row, avoiding the need to recombine the sql statement into the array and then read and import the sql from the array to improve efficiency

Download address: https://github.com/yanue/Dbmanage

The code is as follows

Copy code

The exported sql file format is as follows: -- -- MySQL database dump -- Created by DBManage class, Power By yanue. -- -- Host: localhost -- Generated date: October 06, 2012 22:32 -- MySQL version: 5.1.50-community -- PHP version: 5.3.9-ZS5.6.0 -- -- Database: `test` -- -------------------------------------------------- ---------- -- -- Table structure aa -- DROP TABLE IF EXISTS `aa`; CREATE TABLE `aa` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `content` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; -- -- Transfer data in the table aa -- INSERT INTO `aa` VALUES('1',' I am test data haha< ;/span> ');The following is the class code: /** * @author yanue * @copyright Copyright (c) 2012 yanue.net * @link http://yanue.net/archives/174.html * @version 1.1 * Creation time: May 21, 2012 Updated: October 6, 2012 Update instructions: 1. Remove the comment '--' in the sql file when importing sql to solve the problem that single and double quotes in sql cannot be imported 2. Directly execute the sql after reading a single row, avoiding the need to recombine the sql statement into the array and then read and import the sql from the array to improve efficiency * Note: The volume file ends with _v1.sql (20120522021241_all_v1.sql) * Function: realize mysql database volume backup, select tables for backup, realize single sql file and volume sql import * How to use: * * ------1. Database backup (export) ---------------------------------- -------------------------- //They are the host, user name, password, database name, and database encoding $db = new DBManage ( 'localhost', 'root', 'root', 'test', 'utf8' ); // Parameters: Which table to back up (optional), backup directory (optional, default is backup), volume size (optional, default is 2000, which is 2M) $db->backup (); * ------2. Database recovery (import) ---------------------------------- -------------------------- //They are the host, user name, password, database name, and database encoding $db = new DBManage ( 'localhost', 'root', 'root', 'test', 'utf8' ); //Parameter: sql file $db->restore ('./backup/20120516211738_all_v1.sql'); *------------------------------------------------ -------------------------- ​*/ class DbManage { var $db; // Database connection var $database; // Database used var $sqldir; // Database backup folder // Line break Private $ds = "n"; // Variables to store SQL Public $sqlContent = ""; //The ending character of each sql statement Public $sqlEnd = ";";     /**      * 初始化      *      * @param string $host      * @param string $username      * @param string $password      * @param string $database      * @param string $charset     */     function __construct($host = 'localhost', $username = 'root', $password = '', $database = 'test', $charset = 'utf8') {         $this->host = $host;         $this->username = $username;         $this->password = $password;         $this->database = $database;         $this->charset = $charset;         set_time_limit(0);//无时间限制 @ob_end_flush();         // 连接数据库         $this->db = @mysql_connect ( $this->host, $this->username, $this->password ) or die( ' Mysql Connect Error : '.mysql_error().' ');         // 选择使用哪个数据库         mysql_select_db ( $this->database, $this->db ) or die(' Mysql Connect Error:'.mysql_error().' ');         // 数据库编码方式         mysql_query ( 'SET NAMES ' . $this->charset, $this->db );     }     /*      * 新增查询数据库表      */     function getTables() {         $res = mysql_query ( "SHOW TABLES" );         $tables = array ();         while ( $row = mysql_fetch_array ( $res ) ) {             $tables [] = $row [0];         }         return $tables;     }     /*      *      * ------------------------------------------数据库备份start----------------------------------------------------------      */ /** * Database backup * Parameters: Which table to back up (optional), backup directory (optional, default is backup), volume size (optional, default is 2000, which is 2M) * * @param $string $dir * @param int $size * @param $string $tablename ​​*/ Function backup($tablename = '', $dir, $size) { $dir = $dir ? $dir : './backup/'; //Create directory If (! is_dir ( $dir )) {                  mkdir ( $dir, 0777, true ) or die ( 'Failed to create folder' ); } $size = $size ? $size : 2048;           $sql = ''; // Back up only a certain table If (! empty ( $tablename )) { If(@mysql_num_rows(mysql_query("SHOW TABLES LIKE '".$tablename."'")) == 1) {                 } else { $this->_showMsg('Table-' . $tablename .'-does not exist, please check!',true);                   die();             }                   $this->_showMsg('Backing up table ' . $tablename.''); // Insert dump information $sql = $this->_retrieve (); //Insert table structure information $sql .= $this->_insert_table_structure ( $tablename ); // Insert data                  $data = mysql_query ( "select * from " . $tablename );                                     // The first part of the file name $filename = date ( 'YmdHis' ) . "_" . $tablename; //Number of fields                 $num_fields = mysql_num_fields ($data); // Ji Ji Volume >                 $p = 1;                          // Loop through each record                  while ( $record = mysql_fetch_array ( $data ) ) { //Single record                         $sql .= $this->_insert_record ( $tablename, $num_fields, $record ); // If it is larger than the volume size, write the file If (strlen ( $sql ) >= $size * 1024) { $file = $filename . "_v" . $p . ".sql"; If ($this->_write_file ($sql, $file, $dir)) { $this->_showMsg("Table-" . $tablename . "-Volume-" . $p . "-data backup completed, backup file [ " .$dir . $file ." ]");                       } else {                            $this->_showMsg("Backup table -" . $tablename . "- failed",true);                                                   return false;                  }                                                                                                                                                                                                                               $p ++; //Reset the $sql variable to empty and recalculate the size of the variable                           $sql = "";                 }             } //Clear data promptly                unset($data,$record); // The size of the sql is not enough.                  if ($sql != "") { $filename .= "_v" . $p . ".sql"; If ($this->_write_file ( $sql, $filename, $dir )) { $this->_showMsg( "Table-" . $tablename . "-Volume-" . $p . "-data backup completed, backup file [ " .$dir . $filename ." ]");                      } else {                                $this->_showMsg("Backup Volume-" . $p . "-Failed ");                            return false;                 }             }                   $this->_showMsg("Congratulations! Backup successful");          } else {                $this->_showMsg('Backing up'); // Back up all tables If ($tables = mysql_query ( "show table status from " . $this->database )) { ​​ ​ ​ ​ ​ ​​               } else {                     $this->_showMsg("Failed to read database structure!"); exit ( 0 );             } // Insert dump information $sql .= $this->_retrieve (); //The first part of the file name                  $filename = date ( 'YmdHis' ) . "_all"; // Find out all tables                  $tables = mysql_query ( 'SHOW TABLES' ); // Ji Ji Volume >                 $p = 1; // Loop through all tables                  while ( $table = mysql_fetch_array ( $tables ) ) {                                                                                                                                                                                                                                                                   $tablename = $table [0]; // Get the table structure $sql .= $this->_insert_table_structure ( $tablename );                       $data = mysql_query ( "select * from " . $tablename );                     $num_fields = mysql_num_fields ($data); // Loop through each record                     while ( $record = mysql_fetch_array ( $data ) ) { //Single record $sql .= $this->_insert_record ($tablename, $num_fields, $record); // If it is larger than the volume size, write the file If (strlen ( $sql ) >= $size * 1000) { $file = $filename . "_v" . $p . ".sql";                                                                                                                                                                             If ($this->_write_file ($sql, $file, $dir)) { $this->_showMsg("-Volume-" . $p . "-Data backup completed, backup file[".$dir.$ file." ]");                              } else { $this->_showMsg("Volume-" . $p . "-Backup failed!",true);                                                                                                                 return false;                                                                                                           } // Next sub -roll >                             $p ++; //Reset the $sql variable to empty and recalculate the size of the variable                                 $sql = "";                  }                 }             } // The size of the sql is not enough.                  if ($sql != "") { $filename .= "_v" . $p . ".sql"; If ($this->_write_file ($sql, $filename, $dir)) { $this->_showMsg("-Volume-" . $p . "-Data backup completed, backup file [ ".$dir.$ filename." ]");                      } else {                                 $this->_showMsg("Volume-" . $p . "-Backup failed",true);                          return false;                 }             }                  $this->_showMsg("Congratulations! Backup successful"); } } // Output information in time Private function _showMsg($msg,$err=false){            $err = $err ? "ERROR:" : '' ; echo " ".$err . $msg." "; flush(); }     /** * Insert basic database backup information * * @return string ​​*/     private function _retrieve() {         $value = '';         $value .= '--' . $this->ds;         $value .= '-- MySQL database dump' . $this->ds;         $value .= '-- Created by DbManage class, Power By yanue. ' . $this->ds;         $value .= '-- http://yanue.net ' . $this->ds;         $value .= '--' . $this->ds;         $value .= '-- 主机: ' . $this->host . $this->ds;         $value .= '-- 生成日期: ' . date ( 'Y' ) . ' 年  ' . date ( 'm' ) . ' 月 ' . date ( 'd' ) . ' 日 ' . date ( 'H:i' ) . $this->ds;         $value .= '-- MySQL版本: ' . mysql_get_server_info () . $this->ds;         $value .= '-- PHP 版本: ' . phpversion () . $this->ds;         $value .= $this->ds;         $value .= '--' . $this->ds;         $value .= '-- 数据库: `' . $this->database . '`' . $this->ds;         $value .= '--' . $this->ds . $this->ds;         $value .= '-- -------------------------------------------------------';         $value .= $this->ds . $this->ds;         return $value;     }     /** * Insert table structure * * @param unknown_type $table * @return string ​​*/     private function _insert_table_structure($table) {         $sql = '';         $sql .= "--" . $this->ds;         $sql .= "-- 表的结构" . $table . $this->ds;         $sql .= "--" . $this->ds . $this->ds;         // 如果存在则删除表         $sql .= "DROP TABLE IF EXISTS `" . $table . '`' . $this->sqlEnd . $this->ds;         // 获取详细表信息         $res = mysql_query ( 'SHOW CREATE TABLE `' . $table . '`' );         $row = mysql_fetch_array ( $res );         $sql .= $row [1];         $sql .= $this->sqlEnd . $this->ds;         // 加上         $sql .= $this->ds;         $sql .= "--" . $this->ds;         $sql .= "-- 转存表中的数据 " . $table . $this->ds;         $sql .= "--" . $this->ds;         $sql .= $this->ds;         return $sql;     } /** * Insert a single record * * @param string $table * @param int $num_fields * @param array $record * @return string ​​*/ Private function _insert_record($table, $num_fields, $record) {                     // sql field comma separated           $insert = '';          $comma = ""; $insert .= "INSERT INTO `" . $table . "` VALUES(";                // Loop the content under each subsection for($i = 0; $i $insert .= ($comma . "'" . mysql_escape_string ( $record [$i] ) . "'");               $comma = ","; } $insert .= ");" . $this->ds;           return $insert; } /** *Write to file * * @param string $sql * @param string $filename * @param string $dir * @return boolean ​​*/ Private function _write_file($sql, $filename, $dir) { $dir = $dir ? $dir : './backup/'; //Create directory If (! is_dir ( $dir )) {                 mkdir ( $dir, 0777, true ); }          $re = true; If (! @$fp = fopen ( $dir . $filename, "w+" )) {                $re = false;                  $this->_showMsg("Failed to open sql file!",true); } If (! @fwrite ( $fp, $sql )) {                $re = false; $ This-& gt; _ShowMSG ("Write in the SQL file failed, please write whether the file can be written", true); } If (! @fclose ( $fp )) {                $re = false; ​​​​​​ $this->_showMsg("Failed to close sql file!",true); }          return $re; } /* * * ---------------------------- Top: Database export ----------- dividing line - ---------Next: Database import-------------------------------- */ /** * Import backup data * Description: Volume file format 20120516211738_all_v1.sql * Parameter: File path (required) * * @param string $sqlfile ​​*/ Function restore($sqlfile) {                  // Check whether the file exists If (! file_exists ( $sqlfile )) { ​​​​​​ $this->_showMsg("The sql file does not exist! Please check",true); exit (); } $this->lock ( $this->database );                // Get the database storage location           $sqlpath = pathinfo ($sqlfile);            $this->sqldir = $sqlpath ['dirname'];                                                 // Check whether it contains sub-volumes, separate files like 20120516211738_all_v1.sql from _v, if there are sub-volumes, it means there are sub-volumes           $volume = explode ( "_v", $sqlfile );           $volume_path = $volume [0];                  $this->_showMsg("Do not refresh and close the browser to prevent the program from being terminated. If you are not careful! The database structure will be damaged"); ​​​​ $this->_showMsg("Importing backup data, please wait!"); If (empty ( $volume [1] )) {                   $this->_showMsg ( "Importing sql: " . $sqlfile . ''); // No scroll If ($this->_import ( $sqlfile )) {                       $this->_showMsg( "Database imported successfully!");               } else { $ This-& gt; _ShowMSG ('database instruction failed!', True); exit ();             }          } else { // There is a scroll, then the current is the current roll, and the remaining scrolls are performed by the cycle                 $volume_id = explode ( ".sq", $volume [1] ); //The current volume is $volume_id                  $volume_id = intval ( $volume_id [0] );                while ( $volume_id ) { $tmpfile = $volume_path . "_v" . $volume_id . ".sql"; // Other volumes exist, continue execution If (file_exists ( $tmpfile )) { // Execute the import method $this->msg .= "Importing volume $volume_id: " . $tmpfile . ' '; If ($this->_import ($tmpfile)) {                } else { $volume_id = $volume_id ? $volume_id :1; exit ( "Import volume: " . $tmpfile . 'Failed! The database structure may be damaged! Please try to import from volume 1' );                  }                      } else {                                                                                                                                                                                                                    $this->msg .= "All backups of this volume have been imported successfully! ";                     return;                 }                     $volume_id ++;             }            }if (empty ( $volume [1] )) {                    $this->_showMsg ( "Importing sql: " . $sqlfile . ''); // No scroll If ($this->_import ( $sqlfile )) {                                                $this->_showMsg( "Database imported successfully!");               } else { ​​ ​ ​ ​ ​ ​​ exit ();             }          } else { // There is a scroll, then the current is the current roll, and the remaining scrolls are performed by the cycle                 $volume_id = explode ( ".sq", $volume [1] ); //The current volume is $volume_id                 $volume_id = intval ( $volume_id [0] );                while ( $volume_id ) { $tmpfile = $volume_path . "_v" . $volume_id . ".sql"; // Other volumes exist, continue execution If (file_exists ( $tmpfile )) { // Execute the import method $this->msg .= "Importing volume $volume_id: " . $tmpfile . ' '; If ($this->_import ($tmpfile)) {                } else { $volume_id = $volume_id ? $volume_id :1; exit ( "Import volume: " . $tmpfile . 'Failed! The database structure may be damaged! Please try to import from volume 1' );                  }                      } else {                                                                                                                                                                                                                    $this->msg .= "All backups of this volume have been imported successfully! ";                     return;                 }                     $volume_id ++;             } } } /** * Import sql into the database (normal import) * * @param string $sqlfile * @return boolean ​​*/ Private function _import($sqlfile) {                        // SQL statement array contained in the sql file           $sqls = array ();             $f = fopen ( $sqlfile, "rb" ); ​​​​ //Create table buffer variable           $create_table = '';             while ( ! feof ( $f ) ) {                                                                                                                                                                                                                                                                  // Read each row of sql                  $line = fgets ( $f );                                      // This step is to synthesize the created table into a complete sql statement // If the end does not contain ';' (that is, a complete sql statement, here is the insert statement), and does not contain 'ENGINE=' (that is, the last sentence to create a table) If (! preg_match ( '/;/', $line ) || preg_match ( '/ENGINE=/', $line )) { // Save this sql statement and the created table sql connection                      $create_table .= $line; // If it contains the last sentence of creating a table If (preg_match ( '/ENGINE=/', $create_table)) { //Execute sql statement to create table $this->_insert_into($create_table); //Clear the current table and prepare to create the next table                             $create_table = '';                 }                                                                      // Skip this time                        continue;             } //Execute sql statement                  $this->_insert_into($line); }            fclose ( $f );         return true; } //Insert a single sql statement Private function _insert_into($sql){ If (! mysql_query ( trim ( $sql ) )) { $this->msg .= mysql_error ();               return false; } } /* * -------------------------------Database importend--------------- ------------------ */ // Close database connection private function close() { mysql_close ($this->db); } // Lock the database to avoid errors during backup or import Private function lock($tablename, $op = "WRITE") { If (mysql_query ( "lock tables " . $tablename . " " . $op ))              return true;         else               return false; } // Unlock private function unlock() {            if (mysql_query ( "unlock tables" ))              return true;         else               return false; } // Destruction Function __destruct() { If($this->db){ mysql_query ( "unlock tables", $this->db ); mysql_close ($this->db); } } }

http://www.bkjia.com/PHPjc/630687.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/630687.htmlTechArticleVolume processing means dividing the data we want to process into small files for processing. Let me tell you Introducing a php mysql backup and recovery volume processing class to implement mysql database volume...