php implements MySQL database backup and restore class examples, mysql database backup
The example in this article describes the implementation of MySQL database backup and restore class in PHP. Share it with everyone for your reference. The specific analysis is as follows:
This is a very simple class file that uses PHP to back up mysql database. We only need to simply configure the connection address, user name and database in dbmange. Let's take a look at this example together. The code is as follows:
Copy code The code is as follows:
/**
* Creation time: May 21, 2012
*
* 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
var $record;
// Line break character
Private $ds = "n";
// Variables to store SQL
Public $sqlContent = "";
// Ending character of each sql statement
Public $sqlEnd = ";";
/**
* 初始化
*
* @param string $host
* @param string $username
* @param string $password
* @param string $thisatabase
* @param string $charset
*/
Function __construct($host = 'localhost', $username = 'root', $password = '', $thisatabase = 'test', $charset = 'utf8')
{
$this->host = $host;
$this->username = $username;
$this->password = $password;
$this->database = $thisatabase;
$this->charset = $charset;
// Connect to the 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 );
}
/*
*------------------------------------------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 = 2000)
{
//$dir = $dir ? $dir : 'backup/';
//$size = $size ? $size : 2000;
$sql = '';
// Back up only a certain table
If (! emptyempty ( $tablename ))
echo '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 );
// File name The previous part
$filename = date ( 'YmdHis' ) . "_" . $tablename;
// Number of fields
$num_fields = mysql_num_fields ($data);
using
// Loop through each record
while ( $record = mysql_fetch_array ( $data ) )
// Single records
$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))
echo "table-" . $tablename . "-volume-" . $p . "-Data backup is completed, and the backup file is generated$dir$filename< ;br />";
else
echo "Backup table-" . $tablename . "-Failed
";
// Next sub -roll >
// 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))
echo "table-" . $tablename . "-volume-" . $p . "-Data backup is completed, and the backup file is generated$dir$filename< ;br />";
else
with with with echo "Backup volume-" . $p . "-Failed
";
else
{ // Back up all tables
If ($tables = mysql_query ( "show table status from " . $this->database ))
echo "Reading database structure successfully!
";
else
.
// Insert dump information
$sql .= $this->_retrieve();
// File name The previous part
$filename = date ( 'YmdHis' ) . "_all";
// Find all tables
$tables = mysql_query ( 'SHOW TABLES' );
using
// 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_ARAY ($ 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 the file
If ($this->_write_file ($sql, $file, $dir))
echo "-Volume-" . $p . "-Data backup completed, backup file generated$dir$file
";
else
echo "Backup Volume-" . $p . "-Failed
";
// Next sub -roll >
// 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))
not not not not Not not without echo "-Volume-" . $p . "-Data backup completed, backup file generated $dir$filename
";
else
with with with echo "Backup volume-" . $p . "-Failed
";
}
/**
* 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_real_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/';
// If the folder does not exist, create it
If (! file_exists ( $dir )) {
mkdir ( $dir );
$re = true;
If (! @$fp = fopen ( $dir . $filename, "w+" )) {
$re = false;
echo "Failed to open file!";
If (! @fwrite ( $fp, $sql )) {
$re = false;
echo "Failed to write the file, please check if the file is writable";
If (! @fclose ( $fp )) {
$re = false;
echo "Failed to close file!";
return $re;
}
/*
* *
* ---------------------------- Top: Database export ----------- dividing line - ----------Part 2: Database import-----------------------------
*/
/**
* Import backup data
* Description: Volume file format 20120516211738_all_v1.sql
* Parameter: File path (required)
* *
* @param string $sqlfile
*/
Function restore($sqlfile)
{
// Detect whether 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 20120516211738_all_v1.sql from _v, if there is, 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] ))
echo "Importing sql: " . $sqlfile . '
';
’ to ’ ’ s to not ward ’ ’ s ’ ’ s ’ ’ s ’ ’ s 1 1 way ’ ’ s 1 t t t it
If ($this->_import ( $sqlfile )) {
echo "Database imported successfully!";
else
.
else
//$volume_id = array();
// There is a scroll, then the current is the first volume, 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";
// There are other scrolls, continue to execute
If (file_exists ( $tmpfile )) {
// Execute the import method
echo "Importing volume" . $tmpfile . '
';
If ($this->_import ($tmpfile))
else
exit ( "Import volume" . $tmpfile . 'Failed! The database structure may be damaged! Please try to import from volume 1' ) ;
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) {
// sql statement array contained in sql file
$sqls = array ();
$f = fopen ( $sqlfile, "rb" );
//Create table buffer variable
$create = '';
while ( ! feof ( $f ) ) {
// Read each line of sql
$line = fgets ($f);
// If it contains comments such as '--', or is a blank line, skip
If (trim ( $line ) == '' || preg_match ( '/--*?/', $line, $match )) {
Continue;
// If the ending contains'; that is, a complete SQL statement, this is inserting statement), and does not include 'engine =' (that is, the last sentence of the creation table),
If (! preg_match ( '/;/', $line, $match ) || preg_match ( '/ENGINE=/', $line, $match )) {
// Save this sql statement and the created table sql connection
$create .= $line;
// If it contains the last sentence of creating a table
If (preg_match ( '/ENGINE=/', $create, $match )) {
// Merge it into the sql array
$sqls [] = $create;
//Clear the current table and prepare to create the next table
$create = '';
// Skip this time
Continue;
$sqls [] = $line;
fclose ( $f );
// Loop through the sql statement array and execute them separately
foreach ( $sqls as $sql ) {
str_replace ( "n", "", $sql );
If (! mysql_query ( trim ( $sql ) )) {
echo mysql_error ();
return false;
return true;
}
/*
* -------------------------------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() {
mysql_query ( "unlock tables", $this->db );
mysql_close ($this->db);
}
}
$db = new DBManage ( 'localhost', 'root', '', 'tao', 'gbk' );
//$db->backup ('tao_admin');
$db->restore ('./backup/20140228222713_tao_admin_v1.sql');
?>
I hope this article will be helpful to everyone’s PHP programming design.
http://www.bkjia.com/PHPjc/924539.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/924539.htmlTechArticlephp implements MySQL database backup and restore class examples, mysql database backup This article tells the example of php implementing MySQL database backup and restore kind. Share it with everyone for your reference. Specific analysis is as follows...
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
