幾種MySQL備份常用方法整理

對於MySQL備份方法有很多種，下面我來給大家整理在我們WEB運維過程中一些常用的數據庫備份方法，希望對各位同學會有所幫助。

總結下幾種常用的mysql備份方法：

一、直接拷貝數據庫文件

首先把內存中的數據都刷新到磁盤中，同時鎖定數據表，以保證拷貝過程中不會有新的數據寫入：

 代碼如下 復制代碼

mysql>FLUSH TABLES WITH READ LOCK;

使用tar或cp等命令備份數據庫文件，這裡使用tar：

tar zcvf /backup/mysql_$(date "+%Y%m%d").tar.gz /var/lib/mysql

c、備份完後解鎖數據表

mysql> unlock tables;這種方法備份出來的數據恢復也很簡單，直接拷貝回原來的數據庫目錄下即可。需要注意，對於 Innodb 類型表來說，還需要備份其日志文件，即 ib_logfile* 文件。因為當 Innodb 表損壞時，就可以依靠這些日志文件來恢復。

二、 使用mysqlhotcopy備份

mysqlhotcopy 是一個 PERL 程序，使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 來快速備份數據庫。它是備份數據庫或單個表的最快的途徑，但它只能運行在數據庫文件（包括數據表定義文件、數據文件、索引文件）所在的機器上。

mysqlhotcopy 只能用於備份 MyISAM。

例：

把數據庫test備份到/backup目錄下：

 代碼如下 復制代碼

/usr/local/mysql/bin/mysqlhotcopy -uroot -pxxx test /backup

mysqlhotcopy在執行前將會鎖定數據庫，執行完畢後將自動釋放鎖定

還原：
mysqlhotcopy 備份出來的是整個數據庫目錄，還原時直接拷貝覆蓋就行了，注意權限問題

 代碼如下 復制代碼

cp -rf /backup/test /var/lib/mysql/

注意，想要使用 mysqlhotcopy，必須要有 SELECT、RELOAD(要執行 FLUSH TABLES) 權限，並且還必須要能夠有讀取 datadir/db_name 目錄的權限。

三、mysqldump

mysqldump 是SQL級別的備份機制，它將數據表導成 SQL 腳本文件，可以方便的在不同的mysql版本間進行恢復或升級，這也是最常用的備份方法。

例：

使用mysqldump備份：

 代碼如下 復制代碼 /usr/local/mysql/bin/mysqldump -uroot -pxxx --opt test >/backup/test.sql
還原：
/usr/local/mysql/bin/mysqldump -uroot -pxxx test

 四、使用主從復制機制(replication)（實現數據庫實時備份）推薦

五、mysql自動備份腳本，使用mysqldump工具

 代碼如下 復制代碼

#!/bin/bash
#caishzh 20120509
 
BAKDIR="/backup/mysql"     #mysql備份目錄
MAIL="[email protected]"          #郵箱地址
USER="root"                         #mysql用戶名
PASSWD="jjj"                         #mysql密碼
DB="test"                              #數據庫名
DATE=$(date "+%Y%m%d")     #日期
 
[ -d "$BAKDIR" ] || mkdir -p "$BAKDIR"     #如果備份目錄不存在則創建
 
mysqldump -opt -u$USER -p$PASSWD $DB |gzip > $BKDIR/$DB_$DATA.gz     #備份並使用gzip打包
 
if [ $? -eq 0 ];then
     #echo "$DB mysql backup" |mutt $MAIL -a $BKDIR/$DB_$DATA.sql -s "$DB mysql backup"
     ls -lh $BAKDIR/*.gz>$BAKDIR/messages.txt
     mutt  $MAIL -a $BKDIR/$DB_$DATA.gz -s "$DATE：$DB mysql backup" < $BAKDIR/messages.txt
else
     echo "$DATE mysql backup failed"|mail  -s "$DATE msyql bacup failed"  $MAIL
fi

六，此方法只可用於windows主機

假想環境：
MySQL 安裝位置：C:/MySQL
數據庫名稱為：bbs
數據庫備份目的地：d:/db_bak/
============================
新建db_bak.bat，寫入以下代碼

 代碼如下 復制代碼

net stop mysql
xcopy c:/mysql/data/bbs/*.* d:/db_bak/bbs/%date:~0,3%/ /y
net start mysql

或者

net stop mysql

xcopy D:/Mysql/data D:/Mysql/BAK/BAK-%date:~0,10%  /S/E/I

net start mysql

然後使用Windows的“計劃任務”定時執行該批處理腳本即可。（例如：每天凌晨3點執行back_db.bat）
保存一周的數據，用%date:~0,3%，保存每天的數據，用%date:~4,10%

 代碼如下 復制代碼

xcopy c:/. d:/aaa /s/e
                 
XCOPY source [destination] [/A | /M] [/D[:date]] [/P]
                           [/C] [/I] [/Q] [/F] [/L] [/
                           [/K] [/N] [/O] [/X] [/Y] [/
                           [/EXCLUDE:file1[+file2][+fi

七，linux增量備份

 代碼如下 復制代碼

#!/bin/sh
#+++++++++++++mysqlback++++++++++++
#a mysql incremental backup script.
#by flute
#++++++++++++++++++++++++++++++++++
BAK_DIR="."
while getopts :u:p:d:c OPTION
do
  case ${OPTION} in
      u)
          DB_USER=${OPTARG}
          ;;
      p)
          PASSWD=${OPTARG}
          ;;
      d)
          DB_NAME=${OPTARG}
          ;;
      b)
          BAK_DIR=${OPTARG}
          ;;
      c)
          INIT="true"
          ;;
      /?)
      echo "Usage: mysqlback.sh [OPTIONS]"
      echo "-u        user for login"
      echo "-p        Password to use when connecting to server"
      echo "-d        which database to backup"
      echo "-b        backup database files to directory,default is current directory"
      echo "-c        must be usage for first backup,is complete backup"
      exit 1
      ;;
  esac
done

MYSQL_BIN=/usr/local/mysql/bin
DATADIR=`${MYSQL_BIN}/mysql -u ${DB_USER} -p${PASSWD} -e "show variables like 'datadir'" | sed -n '2p' | awk '{print $NF}'`
upfile ()    #上傳文件
{
    _FNAME=$1
    _TNAME=`echo ${_FNAME}|sed 's//.sql//g'`
    _TNAME="${_TNAME}.tgz"
    tar -czf ${_TNAME} ${_FNAME}
    if [ $? = 0 ]
        then
        scp ${_TNAME} 192.168.1.10:/usr/local/mysql/bak
        if [ $? = 0 ]
            then
            echo "upfile success."
        else
            logger -f ~/mysqlbak.log -t MYSQLBAK 'upload file ${_TNAME} failed'
            ssh 192.168.1.10 "logger -f ~/mysqlbak.log -t MYSQLBAK 'upload file ${_TNAME} failed'"
        fi
    else
        echo "tar file failed."
    fi
}
bak_file ()    #獲取備份文件名
{
    _DB_NAME=$1
    _BAK_DIR=$2
    _BDATE=`date "+%Y-%m-%d"`
    _BAKFILE="${_BAK_DIR}/_${_DB_NAME}_${_BDATE}.sql"
    if [ ! -f ${_BAKFILE} ]
    then
        echo ${_BAKFILE}
        return 0
    else
    ID=1
    while [ 1 ]
          do
          _BAKFILE="${_BAK_DIR}/_${_DB_NAME}_${_BDATE}_${ID}.sql"
          if [ ! -f ${_BAKFILE} ]
              then
              echo ${_BAKFILE}
              return 0
          fi
          ID=`expr $ID + 1`
    done
       
    fi
}

八，windows增量備份

 代碼如下 復制代碼

mkdir %BAKPATH%＼data
 
%MYSQLPATH%＼bin＼mysqldump -u%USERNAME% -p%PASSWORD% --single-transaction --default-
character-set=utf8 --flush-logs --master-data=2 --delete-master-logs spirit_beast > %
BAKPATH%＼data＼spirit_beast%date:~0,10%.sql
 
 
%MYSQLPATH%＼bin＼mysqldump -u%USERNAME% -p%PASSWORD% --single-transaction --default-
character-set=utf8 --flush-logs --master-data=2 --delete-master-logs quartz_event > %
BAKPATH%＼data＼quartz_event%date:~0,10%.sql
 
 
rar a -ag %BAKPATH%＼full＼ %BAKPATH%＼data＼*.sql
 
rmdir /s /q %BAKPATH%＼data＼
 
@echo  %date% %time% full bakup finish >> C:/mysqlbakup.log