我对如何通过PHP备份MySQL数据库没有基本的了解。我已经按照一个教程操作了,但是并没有帮助我理解。
有人能够解释一下如何通过PHP创建MySQL备份吗?
虽然你可以在PHP中执行备份命令,但它们与PHP实际上没有任何关系。这完全是关于MySQL的。
我建议使用mysqldump实用程序来备份数据库。文档可以在此处找到:http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html。
mysqldump的基本用法为
mysqldump -u user_name -p name-of-database >file_to_write_to.sql
你可以使用类似以下命令来恢复备份:
mysql -u user_name -p <file_to_read_from.sql
您是否有cron的访问权限?我建议编写一个PHP脚本并将其设置为cron作业来运行mysqldump。示例如下:
<?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(empty($output)){/* no output is good */}
else {/* we have something to log the output here*/}
SELECT INTO OUTFILE
和 LOAD DATA INFILE
命令。与 PHP 的唯一连接是您使用 PHP 连接到数据库并执行 SQL 命令。您还可以从命令行 MySQL 程序、MySQL 监视器中执行此操作。
这很简单,您只需编写一个包含一个命令的 SQL 文件,并在恢复时加载/执行它即可。
您可以在 此处(只需搜索 outfile)找到 select into outfile 的文档。LOAD DATA INFILE 本质上是其反向操作。请参阅此处以获取文档。
mysqldump
。但是我无法在PHP中使用exec()
执行相同的命令。没有错误提示,文件被创建了,但是却是空的。 - vee基于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;
}
}
for $i
循环(在 //Over tables
注释下面)确实是不必要的。运行这段代码,有或没有这个循环都会得到相同的结果。(当然你需要保留循环内的内容) - JasperV如果你想从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*/
?>
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();
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();
}
$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
);
一个简单的方法来完成这个(输出SQL文件)。我们可以使用exec或mysqli方法来实现。
代码1:
$database = 'YOUR-DB-NAME';
$user = 'USERNAME';
$pass = 'PASSWORD';
$host = 'localhost';
$dir_1 = dirname(__FILE__) . '/table-'. $database .'.sql';
exec("mysqldump --user={$user} --password={$pass} --host={$host} {$database} --result-file={$dir_1} 2>&1", $output_1);
var_dump($output_1);
$user = 'USERNAME';
$pass = 'PASSWORD';
$host = 'localhost';
$dir_2 = dirname(__FILE__) . '/all-database.sql';
exec("mysqldump --user={$user} --password={$pass} --host={$host} --all-databases --result-file={$dir_2} 2>&1", $output_2);
var_dump($output_2);
$user = 'USERNAME';
$pass = 'PASSWORD';
$host = 'localhost';
$dir_3 = dirname(__FILE__) . '/skip-lock-tables.sql';
exec("mysqldump --user={$user} --password={$pass} --host={$host} --all-databases --skip-lock-tables --result-file={$dir_3} 2>&1", $output_3);
var_dump($output_3);
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
<?php
$database = 'YOUR-DB-NAME';
$user = 'USERNAME';
$pass = 'PASSWORD';
$host = 'localhost';
$charset = "utf8mb4"; # utf8mb4_unicode_ci
$conn = new mysqli($host, $user, $pass, $database);
$conn->set_charset($charset);
# get all tables
$result = mysqli_query($conn, "SHOW TABLES");
$tables = array();
while ($row = mysqli_fetch_row($result)) {
$tables[] = $row[0];
}
# Get tables data
$sqlScript = "";
foreach ($tables as $table) {
$query = "SHOW CREATE TABLE $table";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_row($result);
$sqlScript .= "\n\n" . $row[1] . ";\n\n";
$query = "SELECT * FROM $table";
$result = mysqli_query($conn, $query);
$columnCount = mysqli_num_fields($result);
for ($i = 0; $i < $columnCount; $i ++) {
while ($row = mysqli_fetch_row($result)) {
$sqlScript .= "INSERT INTO $table VALUES(";
for ($j = 0; $j < $columnCount; $j ++) {
$row[$j] = $row[$j];
$sqlScript .= (isset($row[$j])) ? '"' . $row[$j] . '"' : '""';
if ($j < ($columnCount - 1)) {
$sqlScript .= ',';
}
}
$sqlScript .= ");\n";
}
}
$sqlScript .= "\n";
}
//save file
$mysql_file = fopen($database . '_backup_'.time() . '.sql', 'w+');
fwrite($mysql_file ,$sqlScript );
fclose($mysql_file );
如果要使用 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';
}
}
-u和用户名之间不应该有任何空格,-p和密码之间也不应该有空格。 CRON JOB命令以每个星期日早上8:30运行此脚本:
>> crontab -e
30 8 * * 7 curl -k https://www.websitename.com/takebackup
<?php
error_reporting(E_ALL);
/* Define database parameters here */
define("DB_USER", 'root');
define("DB_PASSWORD", 'root');
define("DB_NAME", 'YOUR_DATABASE_NAME');
define("DB_HOST", 'localhost');
define("OUTPUT_DIR", 'dbBackup'); // Folder Path / 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;
/* The Backup_Database class */
class Backup_Database {
private $conn;
/* Constructor initializes database */
function __construct( $host, $username, $passwd, $dbName, $charset = 'utf8' ) {
$this->dbName = $dbName;
$this->connectDatabase( $host, $username, $passwd, $charset );
}
protected function connectDatabase( $host, $username, $passwd, $charset ) {
$this->conn = mysqli_connect( $host, $username, $passwd, $this->dbName);
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
exit();
}
/* change character set to $charset Ex : "utf8" */
if (!mysqli_set_charset($this->conn, $charset)) {
printf("Error loading character set ".$charset.": %s\n", mysqli_error($this->conn));
exit();
}
}
/* 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 = mysqli_query( $this->conn, 'SHOW TABLES' );
while ( $row = mysqli_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 = mysqli_query( $this->conn, 'SELECT * FROM ' . $table );
// Return the number of fields in result set
$numFields = mysqli_num_fields($result);
$sql .= 'DROP TABLE IF EXISTS ' . $table . ';';
$row2 = mysqli_fetch_row( mysqli_query( $this->conn, 'SHOW CREATE TABLE ' . $table ) );
$sql.= "\n\n" . $row2[1] . ";\n\n";
for ($i = 0; $i < $numFields; $i++) {
while ($row = mysqli_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";
}
} // End :: for loop
mysqli_free_result($result); // Free result set
$sql.="\n\n\n";
echo " OK <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);
mysqli_close( $this->conn );
} catch (Exception $e) {
var_dump($e->getMessage());
return false;
}
return true;
}
} // End :: class Backup_Database
?>
根据@DevWL的回答,我在...处得到了“未定义的偏移量”。
if ($j<($num_fields-1)) { $data.= ','; }
我对以下内容进行了修改:
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);
}
}
使用示例:
$Backup = new DBbackup();
$Backup->backup();
<?php
error_reporting(E_ALL);
// set the path to the backup
$bc_dir = dirname(__FILE__).'/back_db/';
// DB-connect settings
$db_user = '';
$db_name = '';
$db_pass = '';
$db_host = 'localhost';
$dsn = 'mysql:dbname='.$db_name.';host='.$db_host.'';
try {
$pdo = new PDO($dsn, $db_user, $db_pass);
} catch (Exception $e) {
var_dump($e->getMessage());
}
$query = $pdo->query('SHOW TABLES');
while ($row = $query->fetch(PDO::FETCH_NUM)) {
exec("mysqldump --user=".$db_user." --password=".$db_pass." --host="
.$db_host." ".$db_name." ".$row[0]." --skip-lock-tables --result-file="
.$bc_dir.$row[0].".sql > /dev/null 2>&1");
}
尝试使用SELECT INTO OUTFILE查询的以下示例来创建表备份。这将仅备份特定的表。
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn ) {
die('Could not connect: ' . mysql_error());
}
$table_name = "employee";
$backup_file = "/tmp/employee.sql";
$sql = "SELECT * INTO OUTFILE '$backup_file' FROM $table_name";
mysql_select_db('test_db');
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die('Could not take data backup: ' . mysql_error());
}
echo "Backedup data successfully\n";
mysql_close($conn);
?>
information_schema.referential_constraints
来确定正确的顺序。如果由于循环依赖关系而无法确定正确的顺序,则必须遵循 mysqldump 的方法,即先删除外键检查,添加所有数据,然后在最后添加外键。 - Teepeemm