PHP method to implement mysql backup and recovery volume processing, mysql volume_PHP tutorial

php method to implement mysql backup and recovery volume processing, mysql volume

The example in this article describes the method of implementing mysql backup and recovery volume processing in PHP. Share it with everyone for your reference. The specific analysis is as follows:

Volume processing is to divide the data we want to process into small files for processing. Here I will introduce to you a php mysql backup and restore 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 integrating other people’s stuff into my own backend. What I need is my own stuff, so I referenced it a lot and wrote a volume import class myself to make it easier to call. Everyone is welcome to contribute.

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, username, password, database name, and database encoding

Copy code The code is as follows:

$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).

Copy code The code is as follows:

$db->restore ( './backup/20120516211738_all_v1.sql', false );

Corresponding to how to list the backup sql files or select sql, you can implement it yourself. That is not in this category and is also very simple.

At present, only database import has been implemented. Regarding database export, the function is being written. The following is the complete class code. The specific ideas and implementation code are explained. I will not go into details here. The code is as follows:

Copy code The code is as follows:

/**
* @author yanue
* 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 (emptyempty ( $volume [1] ) || $single) {
echo "Importing sql:" . $sqlfile . '
';
// No divisions
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 and is easy to call.

Volume introduction ideas:

Read the sql file line by line, save each line as a complete sql statement to an array, and then loop and execute it to insert into the database. However, the create table statement is divided into multiple lines. This needs to be processed separately. This is all. It took me a long time. I feel that the article is so long, mainly because the class file is occupied.

Update description:

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 exported sql file format is as follows:

Copy code The code 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');

The following is the class code:

Copy code The code is as follows:

/**
* @author yanue
* @copyright Copyright (c) 2012 yanue.net
* @version 1.1
* Creation time: May 21, 2012

Update time: 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
// newline character
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);//No time limit
@ob_end_flush();
// Connect to database
$this->db = @mysql_connect ( $this->host, $this->username, $this->password ) or die( '

Mysql Connect Error: '.mysql_error().'

');
//Choose which database to use
mysql_select_db ( $this->database, $this->db ) or die('

Mysql Connect Error:' .mysql_error().'

');
// Database encoding method
mysql_query ( 'SET NAMES ' . $this->charset, $this->db );

}

/*
* * Added new query database table
*/
Function getTables() {
           $res = mysql_query ( "SHOW TABLES" );
          $tables = array ();
             while ( $row = mysql_fetch_array ( $res ) ) {
                  $tables [] = $row [0];
          }
          return $tables;
}

/*
* *
*---------------------------------------------Database backup 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 (! emptyempty ( $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 ." ]");
                                                                                                                                                                                                                                                                                                                                                                             $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 all tables
                  $tables = mysql_query ( 'SHOW TABLES' );
// Ji Ji Volume >                  $p = 1;
// Loop through all tables
                  while ( $table = mysql_fetch_array ( $tables ) ) {
// Get the table name
                      $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";
                                                                                                                                                                                                                                                                // Write file
If ($this->_write_file ($sql, $file, $dir)) {
$this->_showMsg("-Volume-" . $p . "-Data backup completed, backup file [ ".$dir.$ file." ]");
                                                                                                                               $this->_showMsg("Volume-" . $p . "-Backup failed!",true);
                                                                                         return false;                                                                                                       }                                                                                                                                                                                                                 $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 .= '-- Host: ' . $this->host . $this->ds;
$value .= '-- Generate date: ' . date ( 'Y' ) . ' year ' . date ( 'm' ) . ' month ' . date ( 'd' ) . ' day' . date ( 'H: i' ) . $this->ds;
$value .= '--MySQL version: ' . mysql_get_server_info () . $this->ds;
$value .= '-- PHP version: ' . phpversion () . $this->ds;
$value .= $this->ds;
$value .= '--' . $this->ds;
$value .= '-- Database: `' . $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 structure" . $table . $this->ds;
$sql .= "--" . $this->ds . $this->ds;

// Delete the table if it exists
$sql .= "DROP TABLE IF EXISTS `" . $table . '`' . $this->sqlEnd . $this->ds;
// Get detailed table information
$res = mysql_query ( 'SHOW CREATE TABLE `' . $table . '`' );
           $row = mysql_fetch_array ( $res );
           $sql .= $row [1];
$sql .= $this->sqlEnd . $this->ds;
              // Add
$sql .= $this->ds;
$sql .= "--" . $this->ds;
             $sql .= "-- Transfer data in the table " . $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) {
// Comma-separated sql fields
          $insert = '';
         $comma = "";
$insert .= "INSERT INTO `" . $table . "` VALUES(";
// Loop the content under each subsection
for($i = 0; $i < $num_fields; $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->_showMsg("Failed to write sql file, please check if the file is writable",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 (emptyempty ( $volume [1] )) {
                   $this->_showMsg ( "Importing sql: " . $sqlfile . '');
// No scroll If ($this->_import ( $sqlfile )) {
                     $this->_showMsg( "Database imported successfully!");
                } else {
                                                                                                                                                                                                                $this->_showMsg('Database import 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- & gt; msg. = "This sub-backup is successfully introduced! & Lt; br /& gt;";
                       return;
                                                                                                                           $volume_id ++;
                                                                         }if (emptyempty ( $volume [1] )) {
                   $this->_showMsg ( "Importing sql: " . $sqlfile . '');
// No scroll If ($this->_import ( $sqlfile )) {
                     $this->_showMsg( "Database imported successfully!");
                } else {
                                                                                                                                                                                                                $this->_showMsg('Database import 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- & gt; msg. = "This sub-backup is successfully introduced! & Lt; br /& gt;";
                       return;
                                                                                                                           $volume_id ++;
                                                                       }
}

/**
* Import sql into the database (normal import)
* *
* @param string $sqlfile
* @return boolean
​​*/
Private function _import($sqlfile) {
​​​​ //The 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);
          }
}
}
?>

I hope this article will be helpful to everyone’s PHP programming design.

http://www.bkjia.com/PHPjc/933601.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/933601.htmlTechArticlephp method to implement mysql backup and recovery volume processing, mysql volume This article describes the example of php implementing mysql backup and recovery partitioning Volume handling methods. Share it with everyone for your reference. The specific analysis is as follows...