这是一个非常简单的利用php来备份mysql数据库的类文件,我们只要简单的在dbmange中配置好连接地址用户名与数据库即可,下面我们一起来看这个例子,代码如下:
- <?php
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- class DBManage
- {
- var $db;
- var $database;
- var $sqldir;
- var $record;
-
- private $ds = "n";
-
- public $sqlContent = "";
-
- public $sqlEnd = ";";
-
-
-
-
-
-
-
-
-
- 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;
-
- $this->db = mysql_connect ( $this->host, $this->username, $this->password ) or die ( "数据库连接失败." );
-
- mysql_select_db ( $this->database, $this->db ) or die ( "无法打开数据库" );
-
- mysql_query ( 'SET NAMES ' . $this->charset, $this->db );
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- function backup($tablename = '', $dir = '', $size = 2000)
- {
-
-
- $sql = '';
-
- if (! emptyempty ( $tablename ))
- {
- echo '正在备份表' . $tablename . '<br />';
-
- $sql = $this->_retrieve();
-
- $sql .= $this->_insert_table_structure ( $tablename );
-
- $data = mysql_query ( "select * from " . $tablename );
-
- $filename = date ( 'YmdHis' ) . "_" . $tablename;
-
- $num_fields = mysql_num_fields ( $data );
-
- $p = 1;
-
- while ( $record = mysql_fetch_array ( $data ) )
- {
-
- $sql .= $this->_insert_record ( $tablename, $num_fields, $record );
-
- if (strlen ( $sql ) >= $size * 1000)
- {
- $file = $filename . "_v" . $p . ".sql";
- if ($this->_write_file ( $sql, $file, $dir ))
- {
- echo "表-" . $tablename . "-卷-" . $p . "-数据备份完成,生成备份文件 <span style='color:#f00;'>$dir$filename</span><br />";
- }
- else
- {
- echo "备份表-" . $tablename . "-失败<br />";
- }
-
- $p ++;
-
- $sql = "";
- }
- }
-
- if ($sql != "")
- {
- $filename .= "_v" . $p . ".sql";
- if ($this->_write_file ( $sql, $filename, $dir ))
- {
- echo "表-" . $tablename . "-卷-" . $p . "-数据备份完成,生成备份文件 <span style='color:#f00;'>$dir$filename</span><br />";
- }
- else
- {
- echo "备份卷-" . $p . "-失败<br />";
- }
- }
- }
- else
- {
- if ($tables = mysql_query ( "show table status from " . $this->database ))
- {
- echo "读取数据库结构成功!<br />";
- }
- else
- {
- exit ( "读取数据库结构成功!<br />" );
- }
-
- $sql .= $this->_retrieve();
-
- $filename = date ( 'YmdHis' ) . "_all";
-
- $tables = mysql_query ( 'SHOW TABLES' );
-
- $p = 1;
-
- while ( $table = mysql_fetch_array ( $tables ) )
- {
-
- $tablename = $table [0];
-
- $sql .= $this->_insert_table_structure ( $tablename );
- $data = mysql_query ( "select * from " . $tablename );
- $num_fields = mysql_num_fields ( $data );
-
-
- while ( $record = mysql_fetch_array ( $data ) )
- {
-
- $sql .= $this->_insert_record ( $tablename, $num_fields, $record );
-
- if (strlen ( $sql ) >= $size * 1000)
- {
-
- $file = $filename . "_v" . $p . ".sql";
-
- if ($this->_write_file ( $sql, $file, $dir ))
- {
- echo "-卷-" . $p . "-数据备份完成,生成备份文件<span style='color:#f00;'>$dir$file</span><br />";
- }
- else
- {
- echo "备份卷-" . $p . "-失败<br />";
- }
-
- $p ++;
-
- $sql = "";
- }
- }
- }
-
- if ($sql != "")
- {
- $filename .= "_v" . $p . ".sql";
- if ($this->_write_file ( $sql, $filename, $dir ))
- {
- echo "-卷-" . $p . "-数据备份完成,生成备份文件 <span style='color:#f00;'>$dir$filename<br />";
- }
- else
- {
- echo "备份卷-" . $p . "-失败<br />";
- }
- }
- }
- }
-
-
-
-
-
-
- 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;
- }
-
-
-
-
-
-
-
- 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;
- }
-
-
-
-
-
-
-
-
-
- private function _insert_record($table, $num_fields, $record) {
-
- $insert = $comma = "";
- $insert .= "INSERT INTO `" . $table . "` VALUES(";
-
- for($i = 0; $i < $num_fields; $i ++) {
- $insert .= ($comma . "'" .mysql_real_escape_string ( $record [$i] ) . "'");
- $comma = ",";
- }
- $insert .= ");" . $this->ds;
- return $insert;
- }
-
-
-
-
-
-
-
-
-
- private function _write_file($sql, $filename, $dir) {
- $dir = $dir ? $dir : './backup/';
-
- if (! file_exists ( $dir )) {
- mkdir ( $dir );
- }
- $re = true;
- if (! @$fp = fopen ( $dir . $filename, "w+" )) {
- $re = false;
- echo "打开文件失败!";
- }
- if (! @fwrite ( $fp, $sql )) {
- $re = false;
- echo "写入文件失败,请文件是否可写";
- }
- if (! @fclose ( $fp )) {
- $re = false;
- echo "关闭文件失败!";
- }
- return $re;
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
- function restore($sqlfile)
- {
-
- if (! file_exists ( $sqlfile ))
- {
- exit ( "文件不存在!请检查" );
- }
- $this->lock ( $this->database );
-
- $sqlpath = pathinfo ( $sqlfile );
- $this->sqldir = $sqlpath ['dirname'];
-
- $volume = explode ( "_v", $sqlfile );
- $volume_path = $volume [0];
- echo "请勿刷新及关闭浏览器以防止程序被中止,如有不慎!将导致数据库结构受损<br />";
- echo "正在导入备份数据,请稍等!<br />";
- if (emptyempty ( $volume [1] ))
- {
- echo "正在导入sql:<span style='color:#f00;'>" . $sqlfile . '</span><br />';
-
- if ($this->_import ( $sqlfile )) {
- echo "数据库导入成功!";
- }
- else
- {
- exit ( '数据库导入失败!' );
- }
- }
- else
- {
-
-
- $volume_id = explode ( ".sq", $volume [1] );
-
- $volume_id = intval ( $volume_id [0] );
- while ( $volume_id )
- {
- $tmpfile = $volume_path . "_v" . $volume_id . ".sql";
-
- if (file_exists ( $tmpfile )) {
-
- echo "正在导入分卷<span style='color:#f00;'>" . $tmpfile . '</span><br />';
- if ($this->_import ( $tmpfile ))
- {
-
- }
- else
- {
- exit ( "导入分卷<span style='color:#f00;'>" . $tmpfile . '</span>失败!可能是数据库结构已损坏!请尝试从分卷1开始导入' );
- }
- }
- else
- {
- echo "此分卷备份全部导入成功!<br />";
- return;
- }
- $volume_id++;
- }
- }
- }
-
-
-
-
-
-
-
- private function _import($sqlfile) {
-
- $sqls = array ();
- $f = fopen ( $sqlfile, "rb" );
-
- $create = '';
- while ( ! feof ( $f ) ) {
-
- $line = fgets ( $f );
-
- if (trim ( $line ) == '' || preg_match ( '/--*?/', $line, $match )) {
- continue;
- }
-
- if (! preg_match ( '/;/', $line, $match ) || preg_match ( '/ENGINE=/', $line, $match )) {
-
- $create .= $line;
-
- if (preg_match ( '/ENGINE=/', $create, $match )) {
-
- $sqls [] = $create;
-
- $create = '';
- }
-
- continue;
- }
- $sqls [] = $line;
- }
- fclose ( $f );
-
- foreach ( $sqls as $sql ) {
- str_replace ( "n", "", $sql );
- if (! mysql_query ( trim ( $sql ) )) {
- echo mysql_error ();
- return false;
- }
- }
- return true;
- }
-
-
-
-
-
-
- private function close() {
- mysql_close ( $this->db );
- }
-
-
- private function lock($tablename, $op = "WRITE") {
- if (mysql_query ( "lock tables " . $tablename . " " . $op ))
- return true;
- else
- return false;
- }
-
-
- private function unlock() {
- if (mysql_query ( "unlock tables" ))
- return true;
- else
- return false;
- }
-
-
- function __destruct() {
- mysql_query ( "unlock tables", $this->db );
- mysql_close ( $this->db );
- }
- }
-
- $db = new DBManage ( 'localhost', 'root', '', 'tao', 'gbk' );
-
- $db->restore ( './backup/20140228222713_tao_admin_v1.sql');
- ?>
|