MySQL 備份和恢複策略筆記(1)

•  
  MySQL 備份和恢複策略
     
  2007-11-18 11:06:05 大 中 小 標籤：知識/探索 mysql備份恢複 mysql備份策略 mysql恢複策略 在資料庫表丟失或損壞的情況下，備份你的資料庫是很重要的。如果發生系統崩潰，你肯定想能夠將你的表儘可能丟失最少的資料恢複到崩潰發生時的狀態。本文主要對MyISAM表做備份恢複。

   

  備份策略一：直接拷貝資料庫檔案（不推薦）

  備份策略二：使用mysqlhotcopy備份資料庫（完全備份，適合小型Database Backup）

  備份策略三：使用mysqldump備份資料庫（完全+增量備份，適合中型Database Backup）

  備份策略四：使用主從複製機制(replication)（實現資料庫即時備份）

   

  指令碼：點擊下載指令碼

   

  備份策略一、直接拷貝資料庫檔案

  直接拷貝資料檔案最為直接、快速、方便，但缺點是基本上不能實現增量備份。為了保證資料的一致性，需要在備份檔案前，執行以下 SQL 語句：

  FLUSH TABLES WITH READ LOCK;

  也就是把記憶體中的資料都重新整理到磁碟中，同時鎖定資料表，以保證拷貝過程中不會有新的資料寫入。這種方法備份出來的資料恢複也很簡單，直接拷貝回原來的資料庫目錄下即可。

   

  備份策略二、使用mysqlhotcopy備份資料庫

  mysqlhotcopy 是一個 PERL 程式，最初由Tim Bunce編寫。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 來快速備份資料庫。它是備份資料庫或單個表的最快的途徑，但它只能運行在資料庫檔案（包括資料表定義檔案、資料檔案、索引檔案）所在的機器上，並且mysqlhotcopy 只能用於備份 MyISAM表。

  本備份策略適合於小型資料庫的備份，資料量不大，可以採用mysqlhotcopy程式每天進行一次完全備份。

  備份策略布置：

  （1）、安裝DBD-mysql perl模組，支援mysqlhotcopy指令碼串連到MySQL資料庫。

  shell> tar -xzvf  DBD-mysql-4.005.tar.gz

  shell> cd DBD-mysql-4.005

  shell> unset LANG

  shell> perl Makefile.PL -mysql_config=/usr/local/mysql/bin/mysql_config -testuser=root -testpassword=UserPWD

  shell> make

  shell> make test

  shell> make install

  （2）、設定crontab任務，每天執行備份指令碼

  shell> crontab -e

  0 3 * * * /root/MySQLBackup/mysqlbackup.sh >/dev/null 2>&1

  每天淩晨3:00執行備份指令碼。

   

  mysqlbackup.sh注釋：

  #!/bin/sh

  # Name:mysqlbackup.sh

  # PS:MySQL DataBase Backup,Use mysqlhotcopy script.

  # Write by:i.Stone

  # Last Modify:2007-11-15

  #

  # 定義變數，請根據具體情況修改

  # 定義指令碼所在目錄

  scriptsDir=`pwd`

  # 資料庫的資料目錄

  dataDir=/usr/local/mysql/data/

  # 資料備份目錄

  tmpBackupDir=/tmp/tmpbackup/

  backupDir=/tmp/mysqlbackup/

  # 用來備份資料庫的使用者名稱和密碼

  mysqlUser=root

  mysqlPWD=111111

  # 定義eMail地址

  eMail=alter@somode.com

  
  # 如果臨機操作備份目錄存在，清空它，如果不存在則建立它

  if [[ -e $tmpBackupDir ]]; then

    rm -rf $tmpBackupDir/*

  else

    mkdir $tmpBackupDir

  fi

  # 如果備份目錄不存在則建立它

  if [[ ! -e $backupDir ]];then

    mkdir $backupDir

  fi

  # 清空MySQLBackup.log

  if [[ -s MySQLBackup.log ]]; then

    cat /dev/null >MySQLBackup.log

  fi

  # 得到Database Backup列表，在此可以過濾不想備份的資料庫

  for databases in `find $dataDir -type d | /

    sed -e "s///usr//local//mysql//data////" | /

    sed -e "s/test//"`; do

    if [[ $databases == "" ]]; then

      continue

    else

  # 備份資料庫

      /usr/local/mysql/bin/mysqlhotcopy --user=$mysqlUser --password=$mysqlPWD -q "$databases" $tmpBackupDir

      dateTime=`date "+%Y.%m.%d %H:%M:%S"`

      echo "$dateTime Database:$databases backup success!" >>MySQLBackup.log

    fi

  done

  # 壓縮備份檔案

  date=`date -I`

  cd $tmpBackupDir

  tar czf $backupDir/mysql-$date.tar.gz ./

  # 發送郵件通知

  if [[ -s MySQLBackup.log ]]; then

    cat MySQLBackup.log | mail -s "MySQL Backup" $eMail

  fi

  # 使用smbclientmv.sh指令碼上傳Database Backup到備份伺服器

  # $scriptsDir/smbclientmv.sh

   

  smbclientmv.sh注釋

  #!/bin/sh

  # Name:smbclientmv.sh

  # PS:Move the data to Backup Server.

  # Write by:i.Stone

  # Last Modify:2007-11-15

  #

  # 定義變數

  # 備份伺服器名

  BackupServer="BackupServerName"

  # 共用資料夾名

  BackupShare="ShareName"

  # 備份伺服器的訪問使用者名稱和密碼

  BackupUser="SMBUser"

  BackupPW="SMBPassword"

  # 定義備份目錄

  BackupDir=/tmp/mysqlbackup

  date=`date -I`

  # Move the data to BackupServer

  smbclient //$BackupServer/$BackupShare /

  $BackupPW -d0 -W WORKGROUP -U $BackupUser /

  -c "put $BackupDir/mysql-$date.tar.gz /

  mysql-$date.tar.gz"

  # Delete temp files

  rm -f $BackupDir/mysql-$date.tar.gz

  (3)、恢複資料庫到備份時的狀態
  mysqlhotcopy 備份出來的是整個資料庫目錄，使用時可以直接拷貝到 mysqld 指定的 datadir (在這裡是 /usr/local/mysql/data/)目錄下即可，同時要注意許可權的問題，如下例：
  shell> cp -rf db_name /usr/local/mysql/data/
  shell> chown -R mysql:mysql /usr/local/mysql/data/ (將 db_name 目錄的屬主改成 mysqld 運行使用者)
  本套備份策略只能恢複資料庫到最後一次備份時的狀態，要想在崩潰時丟失的資料盡量少應該更頻繁的進行備份，要想恢複資料到崩潰時的狀態請使用主從複製機制(replication)。