摘要：本教學(xué)使用環(huán)境介紹伺服器端資料庫語言版本本機端本教學(xué)將使用純?nèi)浞葙Y料庫并下載到目錄底下設(shè)定每天凌晨執(zhí)行腳本記得開頭一定要，即便你有啟用縮寫備份后的名稱資料庫設(shè)定資料庫使用者帳

本教學(xué)使用環(huán)境介紹
伺服器端：Ubuntu 18.04 LTS
資料庫：Mariadb 10.1.34（Mysql）
語言版本：php 7.3
本機端：MacOS High Sierra

本教學(xué)將使用純 php 去備份資料庫并下載到目錄底下

$ crontab -e

設(shè)定每天凌晨00:00 執(zhí)行

0 0 * * * php /var/www/backup.php

backup.php 腳本記得開頭一定要 「

header("Content-Type: text/html; charset=utf-8");
function backup_mysql_database($options){
$mtables = array(); $contents = "-- Database: `".$options["db_to_backup"]."` --
";

$mysqli = new mysqli($options["db_host"], $options["db_uname"], $options["db_password"], $options["db_to_backup"]);
if ($mysqli->connect_error) {
????die("Error : (". $mysqli->connect_errno .") ". $mysqli->connect_error);
}
$mysqli->query("SET NAMES utf8");
$mysqli->set_charset("utf8mb4");
$results = $mysqli->query("SHOW TABLES");

while ($row = $results->fetch_array()){
????if (!in_array($row[0], $options["db_exclude_tables"])){
????????$mtables[] = $row[0];
????}
}

foreach($mtables as $table){
????$contents .= "-- Table `".$table."` --
";

????$results = $mysqli->query("SHOW CREATE TABLE ".$table);
????while ($row = $results->fetch_array()){
????????$contents .= $row[1].";

";
????}

????$results = $mysqli->query("SELECT * FROM ".$table);
????$row_count = $results->num_rows;
????$fields = $results->fetch_fields();
????$fields_count = count($fields);

????$insert_head = "INSERT INTO `".$table."` (";
????for($i=0; $i < $fields_count; $i++){
????????$insert_head .= "`".$fields[$i]->name."`";
????????????if($i < $fields_count-1){
????????????????????$insert_head .= ", ";
????????????????}
????}
????$insert_head .= ")";
????$insert_head .= " VALUES
";

????if($row_count>0){
????????$r = 0;
????????while ($row = $results->fetch_array()){
????????????if(($r % 400) == 0){
????????????????$contents .= $insert_head;
????????????}
????????????$contents .= "(";
????????????for($i=0; $i < $fields_count; $i++){
????????????????$row_content = str_replace("
","
",$mysqli->real_escape_string($row[$i]));

????????????????switch($fields[$i]->type){
????????????????????case 8: case 3:
????????????????????????$contents .= $row_content;
????????????????????????break;
????????????????????default:
????????????????????????$contents .= """. $row_content .""";
????????????????}
????????????????if($i < $fields_count-1){
????????????????????????$contents .= ", ";
????????????????????}
????????????}
????????????if(($r+1) == $row_count || ($r % 400) == 399){
????????????????$contents .= ");

";
?????????????} else {
????????????????$contents .= "),
";
????????????}
????????????$r++;
????????}
????}
}

if (!is_dir ( $options["db_backup_path"] )) {
????????mkdir ( $options["db_backup_path"], 0777, true );
?}

## 備份后的 sql 名稱
$backup_file_name = "dev-" . date( "Y-m-d H:i:s").".sql";

$fp = fopen($options["db_backup_path"] . "/" . $backup_file_name ,"w+");
if (($result = fwrite($fp, $contents))) {
???// echo "Backup file created "$backup_file_name" ($result)";

}
fclose($fp);
return $backup_file_name;
}

## 資料庫設(shè)定
$options = array(
????"db_host"=> "localhost",
????"db_uname" => "root", // 資料庫使用者帳號
????"db_password" => "password", // 資料庫密碼
????"db_to_backup" => "db", // 資料庫名稱
????"db_backup_path" => "/var/www/", // 保存到哪個路徑
????"db_exclude_tables" => array()
);
$backup_file_name=backup_mysql_database($options);

這樣就可以透過 php 備份到該主機下，直接產(chǎn)生 sql 檔了。

Line ID：ianmac
QQ：1258554508