如何在 PHP 中备份 MySQL 数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2170182/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to backup MySQL database in PHP?
提问by user225269
I got this tutorial from the Internet. But I don't even have a basic understanding on how backup of a MySQLdatabase through PHPwould work.
我从互联网上得到了这个教程。但我什至对如何通过PHP备份MySQL数据库没有基本的了解。
Using PHP to Backup MySQL Databases
Can you recommend some sites that I can use as a reference so that I can study it?
你能推荐一些我可以用作参考的网站，以便我可以学习吗？
回答by JAL
While you can execute backup commands from PHP, they don't really have anything to do with PHP. It's all about MySQL.
虽然您可以从 PHP 执行备份命令，但它们与 PHP 没有任何关系。这都是关于 MySQL 的。
I'd suggest using the mysqldump utility to back up your database. The documentation can be found here : http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html.
我建议使用 mysqldump 实用程序来备份您的数据库。文档可以在这里找到：http: //dev.mysql.com/doc/refman/5.1/en/mysqldump.html。
The basic usage of mysqldump is
mysqldump的基本用法是
mysqldump -u user_name -p name-of-database >file_to_write_to.sql
You can then restore the backup with a command like
然后，您可以使用类似的命令恢复备份
mysql -u user_name -p <file_to_read_from.sql
Do you have access to cron? I'd suggest making a PHP script that runs mysqldump as a cron job. That would be something like
您可以访问 cron 吗？我建议制作一个将 mysqldump 作为 cron 作业运行的 PHP 脚本。那会是这样的
<?php
$filename='database_backup_'.date('G_a_m_d_y').'.sql';
$result=exec('mysqldump database_name --password=your_pass --user=root --single-transaction >/var/backups/'.$filename,$output);
if($output==''){/* no output is good */}
else {/* we have something to log the output here*/}
If mysqldump is not available, the article describes another method, using the SELECT INTO OUTFILEand LOAD DATA INFILEcommands. The only connection to PHP is that you're using PHP to connect to the database and execute the SQL commands. You could also do this from the command line MySQL program, the MySQL monitor.
如果mysqldump 不可用，本文介绍了另一种方法，使用SELECT INTO OUTFILE和LOAD DATA INFILE命令。与 PHP 的唯一连接是您使用 PHP 连接到数据库并执行 SQL 命令。您也可以从命令行 MySQL 程序 MySQL 监视器执行此操作。
It's pretty simple, you're writing an SQL file with one command, and loading/executing it when it's time to restore.
这非常简单，您使用一个命令编写 SQL 文件，并在需要恢复时加载/执行它。
You can find the docs for select into outfile here(just search the page for outfile). LOAD DATA INFILE is essentially the reverse of this. See herefor the docs.
您可以在此处找到 select into outfile 的文档（只需在页面中搜索 outfile）。LOAD DATA INFILE 本质上与此相反。有关文档，请参见此处。
回答by T.Todua
using PHP function:
使用PHP函数：
EXPORT_DATABASE("localhost", "user", "pass", "db_name" );
updated function code at github.
在 github 上更新了函数代码。
回答by Mohammad AlBanna
Based on the good solution that provided by tazo todua, I've made some of changes since mysql_connecthas deprecated and not supported in new php version. I've used mysqli_connectinstead and increased the performance of inserting values to the database:
基于tazo todua提供的良好解决方案，我做了一些更改，因为mysql_connect已弃用并且在新的 php 版本中不受支持。我mysqli_connect改用并提高了向数据库插入值的性能：
<?php
/**
* Updated: Mohammad M. AlBanna
* Website: MBanna.info
*/
//MySQL server and database
$dbhost = 'localhost';
$dbuser = 'my_user';
$dbpass = 'my_pwd';
$dbname = 'database_name';
$tables = '*';
//Call the core function
backup_tables($dbhost, $dbuser, $dbpass, $dbname, $tables);
//Core function
function backup_tables($host, $user, $pass, $dbname, $tables = '*') {
$link = mysqli_connect($host,$user,$pass, $dbname);
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit;
}
mysqli_query($link, "SET NAMES 'utf8'");
//get all of the tables
if($tables == '*')
{
$tables = array();
$result = mysqli_query($link, 'SHOW TABLES');
while($row = mysqli_fetch_row($result))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
$return = '';
//cycle through
foreach($tables as $table)
{
$result = mysqli_query($link, 'SELECT * FROM '.$table);
$num_fields = mysqli_num_fields($result);
$num_rows = mysqli_num_rows($result);
$return.= 'DROP TABLE IF EXISTS '.$table.';';
$row2 = mysqli_fetch_row(mysqli_query($link, 'SHOW CREATE TABLE '.$table));
$return.= "\n\n".$row2[1].";\n\n";
$counter = 1;
//Over tables
for ($i = 0; $i < $num_fields; $i++)
{ //Over rows
while($row = mysqli_fetch_row($result))
{
if($counter == 1){
$return.= 'INSERT INTO '.$table.' VALUES(';
} else{
$return.= '(';
}
//Over fields
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = str_replace("\n","\n",$row[$j]);
if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
if ($j<($num_fields-1)) { $return.= ','; }
}
if($num_rows == $counter){
$return.= ");\n";
} else{
$return.= "),\n";
}
++$counter;
}
}
$return.="\n\n\n";
}
//save file
$fileName = 'db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql';
$handle = fopen($fileName,'w+');
fwrite($handle,$return);
if(fclose($handle)){
echo "Done, the file name is: ".$fileName;
exit;
}
}
回答by daniloaz
Here is a pure PHP class to perform backups on MySQL databases not using mysqldump or mysql commands: Backing up MySQL databases with pure PHP
这是一个纯 PHP 类，用于在不使用 mysqldump 或 mysql 命令的情况下对 MySQL 数据库执行备份：Backing up MySQL databases with pure PHP
回答by DevWL
If you want to backup a database from php script you could use a class for example lets call it MySQL. This class will use PDO (build in php class which will handle the connection to the database). This class could look like this:
如果您想从 php 脚本备份数据库，您可以使用一个类，例如让我们调用它MySQL。这个类将使用 PDO（在 php 类中构建，它将处理与数据库的连接）。这个类可能是这样的：
<?php /*defined in your exampleconfig.php*/
define('DBUSER','root');
define('DBPASS','');
define('SERVERHOST','localhost');
?>
<?php /*defined in examplemyclass.php*/
class MySql{
private $dbc;
private $user;
private $pass;
private $dbname;
private $host;
function __construct($host="localhost", $dbname="your_databse_name_here", $user="your_username", $pass="your_password"){
$this->user = $user;
$this->pass = $pass;
$this->dbname = $dbname;
$this->host = $host;
$opt = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
);
try{
$this->dbc = new PDO('mysql:host='.$this->host.';dbname='.$this->dbname.';charset=utf8', $user, $pass, $opt);
}
catch(PDOException $e){
echo $e->getMessage();
echo "There was a problem with connection to db check credenctials";
}
} /*end function*/
public function backup_tables($tables = '*'){ /* backup the db OR just a table */
$host=$this->host;
$user=$this->user;
$pass=$this->pass;
$dbname=$this->dbname;
$data = "";
//get all of the tables
if($tables == '*')
{
$tables = array();
$result = $this->dbc->prepare('SHOW TABLES');
$result->execute();
while($row = $result->fetch(PDO::FETCH_NUM))
{
$tables[] = $row[0];
}
}
else
{
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
//cycle through
foreach($tables as $table)
{
$resultcount = $this->dbc->prepare('SELECT count(*) FROM '.$table);
$resultcount->execute();
$num_fields = $resultcount->fetch(PDO::FETCH_NUM);
$num_fields = $num_fields[0];
$result = $this->dbc->prepare('SELECT * FROM '.$table);
$result->execute();
$data.= 'DROP TABLE '.$table.';';
$result2 = $this->dbc->prepare('SHOW CREATE TABLE '.$table);
$result2->execute();
$row2 = $result2->fetch(PDO::FETCH_NUM);
$data.= "\n\n".$row2[1].";\n\n";
for ($i = 0; $i < $num_fields; $i++)
{
while($row = $result->fetch(PDO::FETCH_NUM))
{
$data.= 'INSERT INTO '.$table.' VALUES(';
for($j=0; $j<$num_fields; $j++)
{
$row[$j] = addslashes($row[$j]);
$row[$j] = str_replace("\n","\n",$row[$j]);
if (isset($row[$j])) { $data.= '"'.$row[$j].'"' ; } else { $data.= '""'; }
if ($j<($num_fields-1)) { $data.= ','; }
}
$data.= ");\n";
}
}
$data.="\n\n\n";
}
//save filename
$filename = 'db-backup-'.time().'-'.(implode(",",$tables)).'.sql';
$this->writeUTF8filename($filename,$data);
/*USE EXAMPLE
$connection = new MySql(SERVERHOST,"your_db_name",DBUSER, DBPASS);
$connection->backup_tables(); //OR backup_tables("posts");
$connection->closeConnection();
*/
} /*end function*/
private function writeUTF8filename($filenamename,$content){ /* save as utf8 encoding */
$f=fopen($filenamename,"w+");
# Now UTF-8 - Add byte order mark
fwrite($f, pack("CCC",0xef,0xbb,0xbf));
fwrite($f,$content);
fclose($f);
/*USE EXAMPLE this is only used by public function above...
$this->writeUTF8filename($filename,$data);
*/
} /*end function*/
public function recoverDB($file_to_load){
echo "write some code to load and proccedd .sql file in here ...";
/*USE EXAMPLE this is only used by public function above...
recoverDB("some_buck_up_file.sql");
*/
} /*end function*/
public function closeConnection(){
$this->dbc = null;
//EXAMPLE OF USE
/*$connection->closeConnection();*/
}/*end function*/
} /*END OF CLASS*/
?>
Now you could simply use this in your backup.php:
现在你可以简单地在你的 backup.php 中使用它：
include ('config.php');
include ('myclass.php');
$connection = new MySql(SERVERHOST,"your_databse_name_here",DBUSER, DBPASS);
$connection->backup_tables(); /*Save all tables and it values in selected database*/
$connection->backup_tables("post_table"); /*Saves only table name posts_table from selected database*/
$connection->closeConnection();
Which means that visiting this page will result in backing up your file... of course it doesn't have to be that way :) you can call this method on every post to your database to be up to date all the time, however, I would recommend to write it to one file at all the time instead of creating new files with time()... as it is above.
这意味着访问此页面将导致备份您的文件......当然它不必那样:) 您可以在数据库的每个帖子上调用此方法以始终保持最新状态，但是，我建议始终将其写入一个文件，而不是使用 time()... 创建新文件，如上所述。
Hope it helps and good luck ! :>
希望它有所帮助，祝你好运！:>
回答by Huntr
From the answer of @DevWL, I got "Undefined offset ..." at
从@DevWL 的回答中，我得到了“未定义的偏移量......”
if ($j<($num_fields-1)) { $data.= ','; }
I made some changes to:
我对以下内容进行了一些更改：
- preserve relationships (foreign keys)
- use Transactions
- remove the uneedy $num_fields
- 保留关系（外键）
- 使用事务
- 删除麻烦的 $num_fields
class DBbackup {
public $suffix;
public $dirs;
protected $dbInstance;
public function __construct() {
try{
$this->dbInstance = new PDO("mysql:host=".$dbhost.";dbname=".$dbname,
$username, $password);
} catch(Exception $e) {
die("Error ".$e->getMessage());
}
$this->suffix = date('Ymd_His');
}
public function backup($tables = '*'){
$output = "-- database backup - ".date('Y-m-d H:i:s').PHP_EOL;
$output .= "SET NAMES utf8;".PHP_EOL;
$output .= "SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';".PHP_EOL;
$output .= "SET foreign_key_checks = 0;".PHP_EOL;
$output .= "SET AUTOCOMMIT = 0;".PHP_EOL;
$output .= "START TRANSACTION;".PHP_EOL;
//get all table names
if($tables == '*') {
$tables = [];
$query = $this->dbInstance->prepare('SHOW TABLES');
$query->execute();
while($row = $query->fetch(PDO::FETCH_NUM)) {
$tables[] = $row[0];
}
$query->closeCursor();
}
else {
$tables = is_array($tables) ? $tables : explode(',',$tables);
}
foreach($tables as $table) {
$query = $this->dbInstance->prepare("SELECT * FROM `$table`");
$query->execute();
$output .= "DROP TABLE IF EXISTS `$table`;".PHP_EOL;
$query2 = $this->dbInstance->prepare("SHOW CREATE TABLE `$table`");
$query2->execute();
$row2 = $query2->fetch(PDO::FETCH_NUM);
$query2->closeCursor();
$output .= PHP_EOL.$row2[1].";".PHP_EOL;
while($row = $query->fetch(PDO::FETCH_NUM)) {
$output .= "INSERT INTO `$table` VALUES(";
for($j=0; $j<count($row); $j++) {
$row[$j] = addslashes($row[$j]);
$row[$j] = str_replace("\n","\n",$row[$j]);
if (isset($row[$j]))
$output .= "'".$row[$j]."'";
else $output .= "''";
if ($j<(count($row)-1))
$output .= ',';
}
$output .= ");".PHP_EOL;
}
}
$output .= PHP_EOL.PHP_EOL;
$output .= "COMMIT;";
//save filename
$filename = 'db_backup_'.$this->suffix.'.sql';
$this->writeUTF8filename($filename,$output);
}
private function writeUTF8filename($fn,$c){ /* save as utf8 encoding */
$f=fopen($fn,"w+");
# Now UTF-8 - Add byte order mark
fwrite($f, pack("CCC",0xef,0xbb,0xbf));
fwrite($f,$c);
fclose($f);
}
}
}
And usage example:
和用法示例：
$Backup = new DBbackup();
$Backup->backup();
This works great on MySQL 10.1.34-MariaDB , PHP : 7.2.7
这在 MySQL 10.1.34-MariaDB、PHP 7.2.7 上效果很好
回答by ziya
回答by diego
Take a look here! It is a native solution written in php. You won't need to exec mysqldump, or cope with incomplete scripts. This is a full mysqldump clone, without dependencies, output compression and sane defaults.
看看这里！它是用 php 编写的本机解决方案。您不需要执行 mysqldump 或处理不完整的脚本。这是一个完整的 mysqldump 克隆，没有依赖项、输出压缩和合理的默认值。
Out of the box, mysqldump-php supports backing up table structures, the data itself, views, triggers and events.
开箱即用，mysqldump-php 支持备份表结构、数据本身、视图、触发器和事件。
MySQLDump-PHP is the only library that supports:
MySQLDump-PHP 是唯一支持的库：
- output binary blobs as hex.
- resolves view dependencies (using Stand-In tables).
- output compared against original mysqldump. Linked to travis-ci testing system (testing from php 5.3 to 7.1 & hhvm)
- dumps stored procedures.
- dumps events.
- does extended-insert and/or complete-insert.
- supports virtual columns from MySQL 5.7.
- 以十六进制输出二进制 blob。
- 解决视图依赖性（使用 Stand-In 表）。
- 输出与原始 mysqldump 进行比较。链接到 travis-ci 测试系统（测试从 php 5.3 到 7.1 & hhvm）
- 转储存储过程。
- 转储事件。
- 做扩展插入和/或完整插入。
- 支持 MySQL 5.7 中的虚拟列。
You can install it using composer, or just download the php file, and it is as easy as doing:
您可以使用 composer 安装它，或者直接下载 php 文件，就像这样做一样简单：
use Ifsnop\Mysqldump as IMysqldump;
try {
$dump = new IMysqldump\Mysqldump('database', 'username', 'password');
$dump->start('storage/work/dump.sql');
} catch (\Exception $e) {
echo 'mysqldump-php error: ' . $e->getMessage();
}
All the options are explained at the github page, but more or less are auto-explicative:
所有选项都在 github 页面上进行了解释，但或多或少是自动解释的：
$dumpSettingsDefault = array(
'include-tables' => array(),
'exclude-tables' => array(),
'compress' => Mysqldump::NONE,
'init_commands' => array(),
'no-data' => array(),
'reset-auto-increment' => false,
'add-drop-database' => false,
'add-drop-table' => false,
'add-drop-trigger' => true,
'add-locks' => true,
'complete-insert' => false,
'databases' => false,
'default-character-set' => Mysqldump::UTF8,
'disable-keys' => true,
'extended-insert' => true,
'events' => false,
'hex-blob' => true, /* faster than escaped content */
'net_buffer_length' => self::MAXLINESIZE,
'no-autocommit' => true,
'no-create-info' => false,
'lock-tables' => true,
'routines' => false,
'single-transaction' => true,
'skip-triggers' => false,
'skip-tz-utc' => false,
'skip-comments' => false,
'skip-dump-date' => false,
'skip-definer' => false,
'where' => '',
/* deprecated */
'disable-foreign-keys-check' => true
);
回答by Hemamalini
for using Cron Job, below is the php function
为了使用 Cron Job，下面是 php 函数
public function runback() {
$filename = '/var/www/html/local/storage/stores/database_backup_' . date("Y-m-d-H-i-s") . '.sql';
/*
* db backup
*/
$command = "mysqldump --single-transaction -h $dbhost -u$dbuser -p$dbpass yourdb_name > $filename";
system($command);
if ($command == '') {
/* no output is good */
echo 'not done';
} else {
/* we have something to log the output here */
echo 'done';
}
}
There should not be any space between -u and username also no space between -p and password. CRON JOB command to run this script every sunday 8.30 am:
-u 和用户名之间不应该有任何空格，-p 和密码之间也不应该有任何空格。每周日上午 8 点 30 分运行此脚本的 CRON JOB 命令：
>> crontab -e
30 8 * * 7 curl -k https://www.websitename.com/takebackup
回答by Irshad Khan
Solution to take backup of your Database in "dbBackup" Folder / Directory
在“dbBackup”文件夹/目录中备份数据库的解决方案
<?php
error_reporting(E_ALL);
/* Define database parameters here */
define("DB_USER", 'root');
define("DB_PASSWORD", '');
define("DB_NAME", 'YOUR_DATABASE _NAME');
define("DB_HOST", 'localhost');
define("OUTPUT_DIR", 'dbBackup'); // Folder / Directory Name
define("TABLES", '*');
/* Instantiate Backup_Database and perform backup */
$backupDatabase = new Backup_Database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$status = $backupDatabase->backupTables(TABLES, OUTPUT_DIR) ? 'OK' : 'KO';
echo "Backup result: " . $status . " - By Irshad Khan";
/* The Backup_Database class */
class Backup_Database {
/* Host where database is located */
var $host = 'localhost';
var $username = 'root';
var $passwd = '';
var $dbName = 'YOUR_DATABASE _NAME';
var $charset = '';
/* Constructor initializes database */
function Backup_Database($host, $username, $passwd, $dbName, $charset = 'utf8') {
$this->host = $host;
$this->username = $username;
$this->passwd = $passwd;
$this->dbName = $dbName;
$this->charset = $charset;
$this->initializeDatabase();
}
protected function initializeDatabase() {
$conn = @mysql_connect($this->host, $this->username, $this->passwd); // Ik Added @ to Hide PDO Error Message
mysql_select_db($this->dbName, $conn);
if (!mysql_set_charset($this->charset, $conn)) {
mysql_query('SET NAMES ' . $this->charset);
}
}
/* Backup the whole database or just some tables Use '*' for whole database or 'table1 table2 table3...' @param string $tables */
public function backupTables($tables = '*', $outputDir = '.') {
try {
/* Tables to export */
if ($tables == '*') {
$tables = array();
$result = mysql_query('SHOW TABLES');
while ($row = mysql_fetch_row($result)) {
$tables[] = $row[0];
}
} else {
$tables = is_array($tables) ? $tables : explode(',', $tables);
}
$sql = 'CREATE DATABASE IF NOT EXISTS ' . $this->dbName . ";\n\n";
$sql .= 'USE ' . $this->dbName . ";\n\n";
/* Iterate tables */
foreach ($tables as $table) {
echo "Backing up " . $table . " table...";
$result = mysql_query('SELECT * FROM ' . $table);
$numFields = mysql_num_fields($result);
$sql .= 'DROP TABLE IF EXISTS ' . $table . ';';
$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE ' . $table));
$sql.= "\n\n" . $row2[1] . ";\n\n";
for ($i = 0; $i < $numFields; $i++) {
while ($row = mysql_fetch_row($result)) {
$sql .= 'INSERT INTO ' . $table . ' VALUES(';
for ($j = 0; $j < $numFields; $j++) {
$row[$j] = addslashes($row[$j]);
// $row[$j] = ereg_replace("\n", "\n", $row[$j]);
if (isset($row[$j])) {
$sql .= '"' . $row[$j] . '"';
} else {
$sql.= '""';
}
if ($j < ($numFields - 1)) {
$sql .= ',';
}
}
$sql.= ");\n";
}
}
$sql.="\n\n\n";
echo " OK <br/><br/>" . "";
}
} catch (Exception $e) {
var_dump($e->getMessage());
return false;
}
return $this->saveFile($sql, $outputDir);
}
/* Save SQL to file @param string $sql */
protected function saveFile(&$sql, $outputDir = '.') {
if (!$sql)
return false;
try {
$handle = fopen($outputDir . '/db-backup-' . $this->dbName . '-' . date("Ymd-His", time()) . '.sql', 'w+');
fwrite($handle, $sql);
fclose($handle);
} catch (Exception $e) {
var_dump($e->getMessage());
return false;
}
return true;
}
}
?>
