MySQL數據庫備份與還原方法總結

MySQL數據庫備份與還原方法不少因為有命令方式與工具方式還有就是用戶自定義的程序備份，下面我來稍加整理一些常用的MySQL數據庫備份與還原方法，希望例子能幫助到大家。

備份策略一：直接拷貝數據庫文件（不推薦）
備份策略二：使用mysqlhotcopy備份數據庫（完全備份，適合小型數據庫備份）
備份策略三：使用mysqldump備份數據庫（完全+增量備份，適合中型數據庫備份）
備份策略四：使用主從復制機制(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 protected]

# 如果臨時備份目錄存在，清空它，如果不存在則創建它
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

# 得到數據庫備份列表，在此可以過濾不想備份的數據庫
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腳本上傳數據庫備份到備份服務器
# $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)。

一、Win32系統下MySQL的備份還原方法

備份：在“運行”中輸入“cmd ”，利用“cd /Program Files/MySQL/MySQL Server 5.0/bin”進入bin文件夾，輸入“mysqldump -u 用戶名 -p databasename >exportfilename”導出數據庫到文件，如mysqldump -u root -p voice>voice.sql，然後輸入密碼即可開始導出MYSQL數據，實現備份操作。

還原：進入MySQL Command Line Client，輸入密碼，進入到“mysql>”，輸入命令"show databases；"，回車，看看有些什麼數據庫；建立你要還原的數據庫，輸入"create database voice；"，回車；切換到剛建立的數據庫，輸入"use voice；"，回車；導入數據，輸入"source voice.sql；"，回車，開始導入，再次出現"mysql>"並且沒有提示錯誤即還原成功。

二、Linux下MySQL的備份與還原方法：

備份：[root@localhost ~]# cd /var/lib/mysql (進入到MySQL庫目錄，根據自己的MySQL的安裝情況調整目錄)
[root@localhost mysql]# mysqldump -u root -p voice>voice.sql，輸入密碼即可。
還原：有兩種方法可選：

第一種方法：[root@localhost ~]# mysql -u root -p 回車，輸入密碼，進入MySQL的控制台"mysql>"，同1.2還原。

第二種方法：

[root@localhost ~]# cd /var/lib/mysql (進入到MySQL庫目錄，根據自己的MySQL的安裝情況調整目錄)
[root@localhost mysql]# mysql -u root -p voice<voice.sql，輸入密碼即可。