PHP database backup and restoration php code
'Localhost', 'port' => 3306, 'user' => 'root', 'password' => '', 'database' => 'test ', 'charset' => 'utf8', 'target' => 'SQL. SQL '); private $ tables = array (); private $ error; private $ begin; // start time/*** architecture method * @ param array $ config */public function _ construct ($ config = array ()) {$ this-> begin = microtime (true); $ config = is_array ($ config )? $ Config: array (); $ this-> config = array_merge ($ this-> config, $ config ); // start the PDO connection try {$ this-> handler = new PDO ("mysql: host ={$ this-> config ['host']}: {$ this-> config ['port']}; dbname = {$ this-> config ['database']} ", $ this-> config ['user'], $ this-> config ['password']);} catch (PDOException $ e) {$ this-> error = $ e-> getMessage (); return false ;} catch (Exception $ e) {$ this-> error = $ e-> getMessage (); return false ;}}/*** Back up * @ param array $ tables * @ return bool */public function backup ($ tables = array () {// store the array of table definition statements $ ddl = array (); // array for storing data $ data = array (); $ this-> setTables ($ tables); if (! Empty ($ this-> tables) {foreach ($ this-> tables as $ table) {$ ddl [] = $ this-> getDDL ($ table ); $ data [] = $ this-> getData ($ table);} // start writing $ this-> writeToFile ($ this-> tables, $ ddl, $ data );} else {$ this-> error = 'no table in the database! '; Return false ;}}/*** set the table to be backed up * @ param array $ tables */private function setTables ($ tables = array () {if (! Empty ($ tables) & is_array ($ tables) {// backup the specified table $ this-> tables = $ tables ;} else {// Back up all tables $ this-> tables = $ this-> getTables ();}} /*** query * @ param string $ SQL * @ return mixed */private function query ($ SQL = '') {$ stmt = $ this-> handler-> query ($ SQL); $ stmt-> setFetchMode (PDO: FETCH_NUM); $ list = $ stmt-> fetchAll (); return $ list;}/*** get all TABLES * @ return array */private function getTables () {$ SQL = 'show TABLES '; $ list = $ This-> query ($ SQL); $ tables = array (); foreach ($ list as $ value) {$ tables [] = $ value [0];} return $ tables;}/*** get table definition statement * @ param string $ table * @ return mixed */private function getDDL ($ table = '') {$ SQL = "show create table '{$ table}"'; $ ddl = $ this-> query ($ SQL) [0] [1]. '; return $ ddl;}/*** get table data * @ param string $ table * @ return mixed */private function getData ($ table = '') {$ SQL = "SHOW COLUMNS FROM' {$ Table} "'; $ list = $ this-> query ($ SQL); // field $ columns =''; // SQL $ query = ''to be returned ''; foreach ($ list as $ value) {$ columns. = "'{$ value [0]}'," ;}$ columns = substr ($ columns, 0,-1 ); $ data = $ this-> query ("SELECT * FROM '{$ table}"'); foreach ($ data as $ value) {$ dataSql = ''; foreach ($ value as $ v) {$ dataSql. = "'{$ v}'," ;}$ dataSql = substr ($ dataSql, 0,-1); $ query. = "insert into '{$ table}' ({$ columns}) VALUES ({$ data SQL}); \ r \ n ";} return $ query ;} /*** write file ** @ param array $ tables * @ param array $ ddl * @ param array $ data */private function writeToFile ($ tables = array (), $ ddl = array (), $ data = array () {$ str = "/* \ r \ nMySQL Database Backup Tools \ r \ n"; $ str. = "Server: {$ this-> config ['host'] }:{$ this-> config ['port']} \ r \ n"; $ str. = "Database: {$ this-> config ['database']} \ r \ n"; $ str. = "Data :". date ('Y-m-d H: I: S', time ()). "\ R \ n */\ r \ n"; $ str. = "SET FOREIGN_KEY_CHECKS = 0; \ r \ n"; $ I = 0; foreach ($ tables as $ table) {$ str. = "-- ---------------------------- \ r \ n"; $ str. = "-- Table structure for {$ table} \ r \ n"; $ str. = "-- ---------------------------- \ r \ n"; $ str. = "drop table if exists '{$ table}'; \ r \ n"; $ str. = $ ddl [$ I]. "\ r \ n"; $ str. = "-- ---------------------------- \ r \ n"; $ str. = "-- Records of {$ table} \ r \ n"; $ str. = "------- ----------------------- \ R \ n "; $ str. = $ data [$ I]. "\ r \ n"; $ I ++;} echo file_put_contents ($ this-> config ['target'], $ str )? 'Backup successful! Time spent '. (microtime (true)-$ this-> begin). 'Ms':' backup failed! ';}/*** Error message * @ return mixed */public function getError () {return $ this-> error;} public function restore ($ path = '') {if (! File_exists ($ path) {$ this-> error ('SQL file does not exist! '); Return false;} else {$ SQL = $ this-> parseSQL ($ path); try {$ this-> handler-> exec ($ SQL ); echo 'restored successfully! Time spent ', (microtime (true)-$ this-> begin ). 'Ms';} catch (PDOException $ e) {$ this-> error = $ e-> getMessage (); return false ;}}} /*** parse the SQL file as an SQL statement array * @ param string $ path * @ return array | mixed | string */private function parseSQL ($ path = '') {$ SQL = file_get_contents ($ path); $ SQL = explode ("\ r \ n", $ SQL); // remove first -- comment $ SQL = array_filter ($ SQL, function ($ data) {if (empty ($ data) | preg_match ('/^ --. */', $ data) {return false;} else {return true ;}}); $ SQL = implode ('', $ SQL ); // delete/**/comment $ SQL = preg_replace ('/\/\*. * \ // ', '', $ SQL); return $ SQL ;}}