Mysql Data Backup for php instance sharing

This code completely separates the table structure from the data. By default, a file records the structure of all tables, and data backup in the table is divided into multiple files if the size of the sub-volume is exceeded, otherwise, a file

This code completely separates the table structure from the data. By default, a file records the structure of all tables, and data backup in the table is divided into multiple files if the size of the sub-volume is exceeded, otherwise, a file

Backup: The table structure and data are completely separated. By default, a file records the structure of all tables. If the data backup in the table exceeds the volume size, it is divided into multiple files ,, otherwise, a file is referenced by other people's Code. However, it is not easy to write. Please change it later...

The Code is as follows:

The Code is as follows:

/*
* Created on 2014.
* Link for 527891885@qq.com
* This is seocheck backup class
*/
Class DbBackUp {
Private $ conn;
Private $ dbName;
Private $ host;
Private $ tag = '_ B ';
// Constructor link database
Public function _ construct ($ host = 'localhost', $ dbUser = 'root', $ dbPwd = '', $ dbName =" seocheck ", $ charset = 'utf8 ') {
@ Ob_start ();
@ Set_time_limit (0 );
$ This-> conn = mysql_connect ($ host, $ dbUser, $ dbPwd, true );
If (! $ This-> conn) die ("database system connection failed! ");
Mysql_query ("set names". $ charset, $ this-> conn );
Mysql_select_db ($ dbName, $ this-> conn) or die ("database connection failed! ");
$ This-> host = $ host;
$ This-> dbName = $ dbName;
}

// Obtain the names of all tables in the database
Public function getTableNames (){
$ Tables = array ();
$ Result = mysql_list_tables ($ this-> dbName, $ this-> conn );
If (! $ Result) die ('mysql Error: '. mysql_error ());
While ($ row = mysql_fetch_row ($ result )){
$ Tables [] = $ row [0];
}
Return $ tables;
}

// Obtain the field information of the database table
Public function getFieldsByTable ($ table ){
$ Fields = array ();
$ Str = '';
$ Res = mysql_query ("show create table '{$ table}'", $ this-> conn );
If (! $ Res) die ('mysql Error: '. mysql_error ());
While ($ rows = mysql_fetch_assoc ($ res )){
$ Str = str_replace ("create table '{$ table}' (", "", $ rows ['create table']); // drop table if exists '{$ table}' \ n
$ Str = "-- \ n -- Table structure for table' {$ table} '\ n -- \ n \ nCREATE table if not exists' {$ TABLE }'(". $ str;
$ Str = str_replace (",", $ str );
$ Str = str_replace ("') ENGINE = InnoDB", "') \ n) ENGINE = InnoDB", $ str );
$ Str. = "; \ n ";
// $ Str = $ str. "; \ n -- Dumping data for table '{$ table}' \ n -- \ n ";
$ Fields [$ rows ['table'] = $ str;
}
Return $ fields;
}

// Obtain table data
Public function getDataByTable ($ table ){
$ Data = array ();
$ Str = '';
$ Res = mysql_query ("SELECT * FROM '{$ table}'", $ this-> conn );
If (! $ Res) die ('mysql Error: '. mysql_error ());
While ($ rows = mysql_fetch_assoc ($ res )){
If (! Empty ($ rows )){
$ Data [] = $ rows;
}
}
$ Keys = array_keys ($ data [0]);
Foreach ($ keys as $ k => $ v ){
$ Keys [$ k] = '''. $ v .''';
}
$ Key = join (',', $ keys );
$ Str = "insert into '{$ table}' ({$ key}) VALUES \ n ";
Foreach ($ data as $ k => $ v ){
$ Str. = "(";
While (list ($ key, $ val) = each ($ v )){
If (! Is_numeric ($ val )){
$ Str. = "'". $ val ."',";
} Else {
$ Str. = $ val .',';
}
}
$ Str = substr ($ str, 0,-2); // there is a space behind it, so it is intercepted from-2.
If ($ k + 1 = count ($ data )){
$ Str. = "); \ n -- -------------------------------------------------------- \ n ";
} Else {
$ Str. = "), \ n ";
}
}
Return $ str;
}

// Back up the database
Public function getBackUpDataByTable ($ tables, $ path = '', $ fileName = 'secret', $ subsection = '2 '){
If (empty ($ tables) $ this-> _ showMsg ('the table to be backed up is not specified !!! ', True );
$ Page = 0; // Number of volumes
$ Path = empty ($ path )? $ _ SERVER ['document _ root']. '/core/Runtime/Data/'. $ fileName. 'demo/': $ path;
If (! File_exists ($ path )){
Mkdir ($ path, 0777, true );
}
$ Mysql_info = $ this-> _ retrieve ();
$ FieldsByTable = array ();
If (is_array ($ tables )){
$ This-> _ showMsg ('Back Up, data is initializing. Do not close your browser ...');
$ Fw = $ this-> writeFileByBackUpData ($ path. $ this-> dbName. '_ table. SQL', $ mysql_info, $ method = "AB + ");
If ($ fw! = False ){
$ This-> _ showMsg ('the basic information of the backup database is successful... ');
}
Foreach ($ tables as $ table ){
$ TableInfo = $ this-> getFieldsByTable ($ table );
If (! Empty ($ tableInfo )){
$ This-> _ showMsg ('table ['. $ table.'] structure retrieved successfully... ');
$ Fw = $ this-> writeFileByBackUpData ($ path. $ this-> dbName. '_ table. SQL ', $ tableInfo [$ table], $ method = "AB + ");
If ($ fw = false ){
$ This-> _ showMsg ('backup table ['. $ table.'] structure failed... ', True );
} Else {
$ This-> _ showMsg ('backup table ['. $ table.'] structure successful, start to get data... ');
};
} Else {
$ This-> _ showMsg ('retrieve database ['. $ this-> dbName.'] table structure failed. Please try again later !... ', True );
}
$ This-> _ insertSqlByTableForAll ($ path, $ table, $ subsection );
}
} Else {
$ This-> _ showMsg ('Back Up, data is initializing. Do not close your browser ...');
$ TableInfo = $ this-> getFieldsByTable ($ tables );
If (! Empty ($ tableInfo )){
$ This-> _ showMsg ('retrieve table ['. $ tables.'] structure successful... ');
$ Fw = $ this-> writeFileByBackUpData ($ path. $ this-> dbName. '_'. $ tables. '_ table. SQL ', $ mysql_info. $ tableInfo [$ tables]);
If ($ fw = false ){
$ This-> _ showMsg ('backup table ['. $ tables.'] structure failed... ', True );
} Else {
$ This-> _ showMsg ('backup table ['. $ tables.'] structure successful, start to get data... ');
}
} Else {
$ This-> _ showMsg ('retrieve table ['. $ tables.'] structure failed. Please try again later !... ', True );
}
$ Res = $ this-> _ insertSqlByTableForAll ($ path, $ tables, $ subsection );
}
}

// Basic database information
Private function _ retrieve (){
$ BackUp = '';
$ BackUp. = '--'. "\ n ";
$ BackUp. = '-- MySQL database dump'. "\ n ";
$ BackUp. = '-- Created by DbBackUp class, Power By chujiu.'. "\ n ";
$ BackUp. = '--'. "\ n ";
$ BackUp. = '-- host:'. $ this-> host. "\ n ";
$ BackUp. = '-- generation Date :'. date ('y '). 'Year '. date ('M '). 'month '. date ('D '). 'day '. date ('H: I '). "\ n ";
$ BackUp. = '-- MySQL version:'. mysql_get_server_info (). "\ n ";
$ BackUp. = '-- PHP version:'. phpversion (). "\ n ";
$ BackUp. = "\ n ";
$ BackUp. = "SET SQL _MODE = 'no _ AUTO_VALUE_ON_ZERO '; \ n ";
$ BackUp. = "SET time_zone = '+ 00:00'; \ n ";
$ BackUp. = "/*! 40101 SET @ OLD_CHARACTER_SET_CLIENT = @ CHARACTER_SET_CLIENT */; \ n ";
$ BackUp. = "/*! 40101 SET @ OLD_CHARACTER_SET_RESULTS = @ CHARACTER_SET_RESULTS */; \ n ";
$ BackUp. = "/*! 40101 SET @ OLD_COLLATION_CONNECTION = @ COLLATION_CONNECTION */; \ n ";
$ BackUp. = "/*! 40101 set names utf8 */; \ n ";
$ BackUp. = "-- \ n -- Database: '{$ this-> dbName}' \ n -- ------------------------------------------------------ \ n ";
Return $ backUp;
}

/**
* Insert a single record
*
* @ Param string $ row
*/
Private function _ insertSql ($ row, $ table ){
// Separate SQL fields with commas
$ Insert = '';
$ Insert. = "insert into '". $ table. "'values (";
Foreach ($ row as $ key => $ val ){
$ Insert. = "'". $ val ."',";
}
$ Insert = substr ($ insert, 0,-1 );
$ Insert. = ");". "\ n ";
Return $ insert;
}

/**
* Generate an inser statement for a table
* @ Param string $ table
* @ Param string $ subsection volume size
*/
Private function _ insertSqlByTableForAll ($ path, $ table, $ subsection ){
$ I = 0;
$ InsertSqlByTable = '';
$ Res = mysql_query ("SELECT * FROM '{$ table}'", $ this-> conn );
If (! $ Res) die ('mysql Error: '. mysql_error ());
While ($ rows = mysql_fetch_assoc ($ res )){
$ InsertSqlByTable. = $ this-> _ insertSql ($ rows, $ table );
$ Size = strlen ($ insertSqlByTable );
If ($ size> $ subsection * 1024*1024 ){
$ Fw = $ this-> writeFileByBackUpData ($ path. $ table. $ I. $ this-> tag. '. SQL', $ insertSqlByTable );
If ($ fw = false) $ this-> _ showMsg ('database table ['. $ table. '], Volume '. $ I. 'failed to write the file. Please try again later !!! ', True );
$ This-> _ showMsg ('database table ['. $ table.'], Volume '. $ I.' is backed up successfully! Backup File: ['. $ path. $ table. $ I. $ this-> tag.'. SQL] ');
$ InsertSqlByTable = '';
$ I + = 1;
}
}
// The insertSqlByTable size is not enough.
If ($ insertSqlByTable! = ""){
$ Fw = $ this-> writeFileByBackUpData ($ path. $ table. $ this-> tag. '. SQL', $ insertSqlByTable );
If ($ fw = false) $ this-> _ showMsg ('database table ['. $ table.'] failed to write the file. Please try again later !!! Backup File: ['. $ path. $ table. $ this-> tag.'. SQL] ', true );
$ This-> _ showMsg ('database table ['. $ table.'] backup successful! Backup File: ['. $ path. $ table. $ this-> tag.'. SQL] ');
}
$ This-> _ showMsg ('database table ['. $ table.'] all backed up successfully! ');
}

// Write a file
Public function writeFileByBackUpData ($ fileName, $ data, $ method = "rb +", $ iflock = 1, $ check = 1, $ chmod = 1 ){
$ Check & @ strpos ($ fileName ,'..')! = False & exit ('foridden ');
@ Touch ($ fileName );
$ Handle = @ fopen ($ fileName, $ method );
If ($ iflock ){
@ Flock ($ handle, LOCK_EX );
}
$ Fw = @ fwrite ($ handle, $ data );
If ($ method = "rb +") ftruncate ($ handle, strlen ($ data ));
Fclose ($ handle );
$ Chmod & @ chmod ($ fileName, 0777 );
Return $ fw;
}

/**
* Path: path for generating the compressed package
* FileName: the file name to be compressed is usually the same directory as path
*/
Public function createZipByBackUpFile ($ path ){
$ Db_base_files = $ this-> getFileByBackUpDir ($ path );
If (! Empty ($ db_base_files )){
$ Zip = new ZipArchive;
If ($ zip-> open ($ path. $ this-> dbname.date('ymd'{.'.zip ', ZipArchive: CREATE | ZIPARCHIVE: OVERWRITE )! = True)
Die ("cannot open". $ this-> dbName. date ('ymmd'). "zip for writing .");
Foreach ($ db_base_files as $ key => $ value ){
If (is_file ($ value )){
$ File_name = basename ($ value );
$ Info [] = $ zip-> addFile ($ value, $ file_name); // avoid file paths in the compressed package
}
}
$ Zip-> close ();
If (file_exists ($ path. $ this-> dbname.date('ymd'{.'.zip '))
Foreach ($ db_base_files as $ val ){
Unlink ($ val );
}
If (count (array_filter ($ info)> 0) return true;
}
Return false;
}

// Obtain the object
Public function getFileByBackUpDir ($ path ){
$ Info = array ();
$ Db_base_files = array ();
If (@ file_exists ($ path) & is_dir ($ path )){
If ($ dh = opendir ($ path )){
While ($ file = readdir ($ dh ))! = False ){
If ($ file! = '.' & $ File! = '..'){
If (strripos ($ file, 'seocheck ')! = False ){
$ Db_base_files [] = $ path. $ file;
}
}
}
Closedir ($ dh );
}
}
Return $ db_base_files;
}

/**
* @ Path: path for generating the compressed package
* @ FileName: the name of the file to be decompressed is decompressed to the path directory by default.
*/
Public function uncompressZip ($ path, $ zipName ){
$ Path = empty ($ path )? $ _ SERVER ['document _ root']. '/core/Runtime/Data/': $ path;
$ Zip = new ZipArchive;
If ($ zip-> open ($ path. $ zipName) === TRUE ){
$ Zip-> extracloud ($ path );
$ Zip-> close ();
Return true;
} Else {
Return false;
}
}

// Import the database
Public function importingDataBySqlFile (){

}

// Output information in a timely manner
Private function _ showMsg ($ msg, $ err = false ){
If ($ err = true ){
Echo"

ERROR: --- ". $ msg ."

"; Exit;
}
Echo"

OK: --- ". $ msg ."

";
}

// Lock the database to avoid errors during backup or import
Private function lock ($ table, $ op = "WRITE "){
If (mysql_query ("lock tables". $ table. "". $ op ))
Return true;
Else
Return false;
}

// Unlock
Private function unlock (){
If (mysql_query ("unlock tables "))
Return true;
Else
Return false;
}

// Structure
Public function _ destruct (){
If ($ this-> conn ){
Mysql_query ("unlock tables", $ this-> conn );
Mysql_close ($ this-> conn );
}
}
}
?>