如何用PHP备份MySQL数据库或特定表？

本文共计1281个文字，预计阅读时间需要6分钟。

PHP类实现完整数据库备份，或备份指定表的函数： phpclass Backup { // 存储配置 private $config;

// 存储最终SQL导出 private $dump;

// 存储表结构 private $tableStructure;

public function __construct() { // 初始化配置和导出变量 $this->config=[]; $this->dump=''; $this->tableStructure=[]; }

// 设置配置 public function setConfig(array $config) { $this->config=$config; }

// 备份数据库或指定表 public function backup($tableName=null) { // 连接数据库 $connection=new mysqli($this->config['host'], $this->config['username'], $this->config['password'], $this->config['database']); if ($connection->connect_error) { die('连接失败: ' . $connection->connect_error); }

// 选择所有表或指定表 $tables=$tableName ? [$tableName] : $connection->query('SHOW TABLES FROM ' . $this->config['database'])->fetch_all(MYSQLI_ASSOC);

foreach ($tables as $table) { $tableName=$table['Tables_in_' . $this->config['database']]; $this->tableStructure[$tableName]=$connection->query('DESCRIBE ' . $tableName)->fetch_all(MYSQLI_ASSOC);

// 备份表结构 $this->dump .='CREATE TABLE ' . $tableName . ' (\n'; foreach ($this->tableStructure[$tableName] as $column) { $this->dump .=' ' . $column['Field'] . ' ' . $column['Type'] . ' ' . ($column['Null']=='NO' ? 'NOT NULL' : '') . ' ' . ($column['Default'] !==null ? 'DEFAULT ' . $this->escape($column['Default']) : '') . ' ' . ($column['Extra'] ? ' ' . $column['Extra'] : '') . ',\n'; } $this->dump=rtrim($this->dump, ',\n') . '\n);\n\n';

// 备份数据 $rows=$connection->query('SELECT * FROM ' . $tableName); if ($rows) { $this->dump .='INSERT INTO ' . $tableName . ' (' . implode(', ', array_map(function ($column) { return $column['Field']; }, $this->tableStructure[$tableName])) . ') VALUES (\n'; while ($row=$rows->fetch_assoc()) { $values=array_map(array($this, 'escape'), $row); $this->dump .=' (' . implode(', ', $values) . '),\n'; } $this->dump=rtrim($this->dump, ',\n') . ');\n\n'; } }

// 关闭连接 $connection->close(); }

// 获取备份内容 public function getDump() { return $this->dump; }

// 转义SQL值 private function escape($value) { if (is_numeric($value)) { return $value; } elseif (is_string($value)) { return ' . str_replace(', '', $value) . '; } else { return NULL; } }}

php类实现完整备份数据库，或者备份数据库中指定表的功能：

<?php class Backup { /** * @var stores the options */ var $config; /** * @var stores the final sql dump */ var $dump; /** * @var stores the table structure + inserts for every table */ var $struktur = array(); /** * @var zip file name */ var $datei; /** * this function is the constructor and phrase the options * and connect to the database * @return */ public function Backup($options) { // write options foreach($options AS $name => $value) { $this->config[$name] = $value; } // check mysql connection mysql_connect($this->config['mysql'][0], $this->config['mysql'][1], $this->config['mysql'][2]) or die(mysql_error()); mysql_select_db($this->config['mysql'][3]) or die(mysql_error()); } /** * this function start the backup progress its the core function * @return */ public function backupDB() { // start backup if(isset($_POST['backup'])) { // check if tables are selected if(empty($_POST['table'])) { die("Please select a table."); } /** start backup **/ $tables = array(); $insert = array(); $sql_statement = ''; // lock tables foreach($_POST['table'] AS $table) { mysql_query("LOCK TABLE $table WRITE"); // Read table structure $res = mysql_query('SHOW CREATE TABLE '.$table.''); $createtable = mysql_result($res, 0, 1); $str = "\n\n".$createtable."\n\n"; array_push($tables, $str); // Read table "inserts" $sql = 'SELECT * FROM '.$table; $query = mysql_query($sql) or die(mysql_error()); $feld_anzahl = mysql_num_fields($query); $sql_statement = '-- -- Data Table `$table` -- '; // start reading progress while($ds = mysql_fetch_object($query)){ $sql_statement .= 'INSERT INTO `'.$table.'` ('; for ($i = 0;$i <$feld_anzahl;$i++){ if ($i ==$feld_anzahl-1){ $sql_statement .= mysql_field_name($query,$i); } else { $sql_statement .= mysql_field_name($query,$i).', '; } } $sql_statement .= ') VALUES ('; for ($i = 0;$i <$feld_anzahl;$i++){ $name = mysql_field_name($query,$i); if (empty($ds->$name)){ $ds->$name = 'NULL'; } if ($i ==$feld_anzahl-1){ $sql_statement .= '"'.$ds->$name.'"'; } else { $sql_statement .= '"'.$ds->$name.'", '; } } $sql_statement .= ");\n"; } // insert "Inserts" into an array if not exists if(!in_array($sql_statement, $insert)) { array_push($insert, $sql_statement); unset($sql_statement); } unset($sql_statement); } // put table structure and inserts together in one var $this->struktur = array_combine($tables, $insert); // create full dump $this->createDUMP($this->struktur); // create zip file $this->createZIP(); /** end backup **/ // send an email with the sql dump if(isset($this->config['email']) && !empty($this->config['email'])) { $this->sendEmail(); } // output echo '<h3 style="color:green;">Backup war erfolgreich</h3><a href="'.$this->datei.'">Download Backup</a> <br /> <br />'; } } /** * this function generate an email with attachment * @return */ protected function sendEmail() { // start sending emails foreach($this->config['email'] AS $email) { $to = $email; $from = $this->config['email'][0]; $message_body = "This email contains the database backup as a zip file."; $msep = strtoupper (md5 (uniqid (time ()))); // set email header (only text) $header = "From: $from\r\n" . "MIME-Version: 1.0\r\n" . "Content-Type: multipart/mixed; boundary="$msep"\r\n\r\n" . "--$msep\r\n" . "Content-Type: text/plain\r\n" . "Content-Transfer-Encoding: 8bit\r\n\r\n" . $message_body . "\r\n"; // file name $dateiname = $this->datei; // get filesize of zip file $dateigroesse = filesize ($dateiname); // open file to read $f = fopen ($dateiname, "r"); // save content $attached_file = fread ($f, $dateigroesse); // close file fclose ($f); // create attachment $attachment = chunk_split (base64_encode ($attached_file)); // set attachment header $header .= "--" . $msep . "\r\n" . "Content-Type: application/zip; name='Backup'\r\n" . "Content-Transfer-Encoding: base64\r\n" . "Content-Disposition: attachment; filename='Backup.zip'\r\n" . "Content-Description: Mysql Datenbank Backup im Anhang\r\n\r\n" . $attachment . "\r\n"; // mark end of attachment $header .= "--$msep--"; // eMail Subject $subject = "Database Backup"; // send email to emails^^ if(mail($to, $subject, '', $header) == FALSE) { die("The email could not be sent. Please check the email address."); } echo "<p><small>Email was successfully sent.</small></p>"; } } /** * this function create the zip file with the database dump and save it on the ftp server * @return */ protected function createZIP() { // Set permissions to 777 chmod($this->config['folder'], 0777); // create zip file $zip = new ZipArchive(); // Create file name $this->datei = $this->config['folder'].$this->config['mysql'][3]."_".date("j_F_Y_g:i_a").".zip"; // Checking if file could be created if ($zip->open($this->datei, ZIPARCHIVE::CREATE)!==TRUE) { exit("cannot open <".$this->datei.">\n"); } // add mysql dump to zip file $zip->addFromString("dump.sql", $this->dump); // close file $zip->close(); // Check whether file has been created if(!file_exists($this->datei)) { die("The ZIP file could not be created."); } echo "<p><small>The zip was created.</small></p>"; } /** * this function create the full sql dump * @param object $dump * @return */ protected function createDUMP($dump) { $date = date("F j, Y, g:i a"); $header = <<<HEADER -- SQL Dump -- -- Host: {$_SERVER['HTTP_HOST']} -- Erstellungszeit: {$date} -- -- Datenbank: `{$this->config['mysql'][3]}` -- -- -------------------------------------------------------- HEADER; foreach($dump AS $name => $value) { $sql .= $name.$value; } $this->dump = $header.$sql; } /** * this function displays the output form to select tables * @return */ public function outputForm() { // select all tables from database $result = mysql_list_tables($this->config['mysql'][3]); $buffer = ' <fieldset> <legend>Select some tables</legend> <form method="post" action=""> <select name="table[]" multiple="multiple" size="30">'; while($row = mysql_fetch_row($result)) { $buffer .= '<option value="'.$row[0].'">'.$row[0].'</option>'; } $buffer .= '</select> <br /><br /> <input type="submit" name="backup" value="Backup Tables" /> </form> </fieldset>'; echo $buffer; } } ?>

备份用法:

<?php //You can add as many email addresses as you like $options = array('email' => array('email1', 'email2'), 'folder' => './backup/', 'mysql' => array('localhost', 'root', '****', 'database')); $b = new Backup($options); // if submit form start backup if(isset($_POST['backup'])) { // start backup $b->backupDB(); } // display tables $b->outputForm(); ?>