PHP定时备份MySQL与mysqldump语法参数详解

d:/PHP/xampp/mysql/bin/mysqldump -h127.0.0.1 -P3306 -uroot -p123456 snsgou_sns_test --no-data --default_character-set=utf8 > d:/Python/data/snsgou_sns_test_table.sql

d:/PHP/xampp/mysql/bin/mysqldump -h127.0.0.1 -P3306 -uroot -p123456 snsgou_sns_test --no-create-info --default_character-set=utf8 > d:/Python/data/snsgou_sns_test_data.sql

d:/PHP/xampp/mysql/bin/mysqldump -h127.0.0.1 -P3306 -uroot -p123456 snsgou_sns_test --default_character-set=utf8 > d:/Python/data/snsgou_sns_test.sql

d:/PHP/xampp/mysql/bin/mysql -h127.0.0.1 -P3306 -uroot -p123456 snsgou_sns_test --force < d:/Python/data/snsgou_sns_table.sql 

d:/PHP/xampp/mysql/bin/mysql -h127.0.0.1 -P3306 -uroot -p123456 snsgou_sns_test --force < d:/Python/data/snsgou_sns_data.sql

/usr/local/mysql/bin/mysql -h127.0.0.1 -P3306 -uroot -p'sg$123456' snsgou_sns_test --force < /root/2/snsgou_sns_test.sql

<?php
/**
 * 数据库配置文件
 */
$dbs = array(
  array(
    'host' => '127.0.0.1',
    'port' => 6603,
    'user' => 'root',
    'pass' => '123456',
    'dbname' => 'xxxyyy',
    'charset' => 'utf8'
  ),
  array(
    'host' => '127.0.0.1',
    'port' => 6603,
    'user' => 'root',
    'pass' => '123456',
    'dbname' => 'yyyzzz',
    'charset' => 'utf8'
  ),
);
 
$backupPath = 'F:/mysql_bak/' . date('Ym') . '/' . date('d');
$oldPath = 'F:/mysql_bak/' . (intval(date('Ym')) - 1);
 
// 注意：如果路径中有空格，需要用 双引号 引起来
$cmd = '"D:/Program Files/MySQL/MySQL Server 5.5/bin/mysqldump"';
 
// 全部导出
$cmd .= " -h%s -P%s -u%s -p%s %s --default_character-set=%s > %s";
 
// 只导出 表结构
//$cmd .= " -h%s -P%s -u%s -p%s %s --no-data --default_character-set=%s > %s";
 
// 只导出 数据
//$cmd .= " -h%s -P%s -u%s -p%s %s --no-create-info --default_character-set=%s > %s";

<?php
/**
 * 备份数据库
 */
 
set_time_limit(0);
define('ROOT_PATH', dirname(__FILE__));
 
if (!@include ROOT_PATH . '/db.inc.jb51.net.php') {
  echo 'args error';
  //write('args error');
  exit(1);
}
 
// 新建备份目录
new_mkdir($backupPath);
 
// 删除一个月前的数据
new_rmdir($oldPath);
 
 
// 批量备份数据库
foreach ($dbs as $db) {
  $cmd2 = '';
  if (isset($db['name'])) {
    $fileName = $backupPath . '/' . $db['name'] . '_' . date('Ymd') . '_' . time() . '.sql';
    $cmd2 = sprintf($cmd, $db['host'], $db['port'], $db['user'], $db['pass'], $db['name'], $db['charset'], $fileName);
    //write($cmd2);
    try {
      exec($cmd2);
    } catch (Exception $e) {
      // 写错误日志(省略)
      echo $e->getMessage();
    }
  }
}
 
 
/**
 * 创建文件夹
 *
 * @param string $path 文件夹路径
 * @param int $mode 访问权限
 * @param boolean $recursive 是否递归创建
 */
function new_mkdir($path = '', $mode = 0777, $recursive = true) {
  clearstatcache();
  if (!is_dir($path)) {
    mkdir($path, $mode, $recursive);
    chmod($path, $mode);
    $ret = @touch($path . '/index.html');
    @chmod($path . '/index.html', 0777);
    return $ret;
  }
 
  return true;
}
 
/**
 * 清空/删除 文件夹
 *
 * @param string $dirname 文件夹路径
 * @param bool $self 是否删除当前文件夹
 * @return bool
 */
function new_rmdir($dirname = '', $self = true) {
  if (!file_exists($dirname)) {
    return false;
  }
  if (is_file($dirname) || is_link($dirname)) {
    return unlink($dirname);
  }
  $dir = dir($dirname);
  if ($dir) {
    while (false !== $entry = $dir->read()) {
      if ($entry == '.' || $entry == '..') {
        continue;
      }
      new_rmdir($dirname . '/' . $entry);
    }
  }
  $dir->close();
  $self && rmdir($dirname);
}
 
/**
 * 写文件
 *
 * @param string $filename 文件名
 * @param string $text 要写入的文本字符串
 * @param string $openmod 文本写入模式（'w':覆盖重写，'a':文本追加）
 * @return boolean
 */
function write_file($filename = '', $text = '', $openmod = 'w') {
  if (@$fp = fopen($filename, $openmod)) {
    flock($fp, 2);
    fwrite($fp, $text);
    fclose($fp);
    return true;
  } else {
    return false;
  }
}
 
/**
 * 写对象（包括 数字、字符串、数组）
 *
 * @param string $text 要写入的文本字符串
 * @param string $type 文本写入类型（'w':覆盖重写，'a':文本追加）
 */
function write($text = '', $type = 'a') {
  if (!is_dir('d:/PHP/xampp/htdocs')) {
    return false;
  }
  $filename = 'd:/PHP/xampp/htdocs/write.txt';
 
  $text = "\r\n++++++++++++++++++++++++++++++++++++++++++\r\n"
    . date('Y-m-d H:i:s') . "\r\n"
    . print_r($text, true);
  write_file($filename, $text, $type);
}

d:/PHP/xampp/php/php.exe  D:/PHP/xampp/htdocs/BackupDb_git/backup_db.php  snsgou.com

# 注意：首先要确保上面命令在 cmd 命令中能成功执行！