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 databasedatabases 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)。

備份策略三、使用mysqldump備份數據庫

 

 

 

mysqldump 是采用sql級別的備份機制，它將數據表導成 sql 腳本文件，在不同的 mysql 版本之間升級時相對比較合適，這也是最常用的備份方法。mysqldump 比直接拷貝要慢些。關於mysqldump的更詳細解釋見最後的附錄。

對於中等級別業務量的系統來說，備份策略可以這麼定：第一次完全備份，每天一次增量備份，每周再做一次完全備份，如此一直重復。而對於重要的且繁忙的系統來說，則可能需要每天一次全量備份，每小時一次增量備份，甚至更頻繁。為了不影響線上業務，實現在線備份，並且能增量備份，最好的辦法就是采用主從復制機制(replication)，在 slave 機器上做備份。

備份策略布置：

 

（1）、創建備份目錄

 

shell> mkdir /tmp/mysqlbackup

 

shell> mkdir /tmp/mysqlbackup/daily

 

（2）、啟用二進制日志

采用 binlog 的方法相對來說更靈活，省心省力，而且還可以支持增量備份。
啟用 binlog 時必須要重啟 mysqld。首先，關閉 mysqld，打開 /etc/my.cnf，加入以下幾行：
[mysqld]
log-bin

然後啟動 mysqld 就可以了。運行過程中會產生 hostname-bin.000001 以及 hostname-bin.index，前面的文件是 mysqld 記錄所有對數據的更新操作，後面的文件則是所有 binlog 的索引，都不能輕易刪除。關於 binlog 的更詳細信息請查看手冊。

 

（3）、配置ssh密鑰登錄，用於將mysql備份傳送到備份服務器（如果備份服務器為windows，請跳過此部）。

 

1）、在mysql所在服務器（192.168.0.20）生成ssh密鑰

[root@lab ~]# ssh-keygen -t rsa
generating public/private rsa key pair.
enter file in which to save the key (/root/.ssh/id_rsa):
//直接回車
enter passphrase (empty for no passphrase):
//直接回車，不使用密碼
enter same passphrase again:
//直接回車，不使用密碼
your identification has been saved in /root/.ssh/id_rsa.
your public key has been saved in /root/.ssh/id_rsa.pub.
the key fingerprint is:
c2:96:9f:2d:5a:8e:08:42:43:35:2f:85:5e:72:f8:1c root@lab

2）、在備份服務器（192.168.0.200）上創建目錄，修改權限，並傳送公鑰。
[root@lab ~]# ssh 192.168.0.200 "mkdir .ssh;chmod 0700 .ssh"
the authenticity of host '192.168.0.200 (192.168.0.200)' can't be established.
rsa key fingerprint is 37:57:55:c1:32:f1:dd:bb:1b:8a:13:6f:89:fb:b8:9d.
are you sure you want to continue connecting (yes/no)? yes
warning: permanently added '192.168.0.200' (rsa) to the list of known hosts.
[email protected]'s password:
//輸入備份服務器的root密碼
[root@lab ~]# scp .ssh/id_rsa.pub 192.168.0.200:.ssh/authorized_keys2
[email protected]'s password:
id_rsa.pub                                             100%  218     0.2kb/s   00:00   
3）、測試ssh登錄
[root@lab ~]# ssh 192.168.0.200       //測試ssh登錄
last login: fri nov 16 10:34:02 2007 from 192.168.0.20
[root@lib ~]#

 

（4）、設置crontab任務，每天執行備份腳本

 

shell> crontab -e

 

#每個星期日凌晨3:00執行完全備份腳本

 

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

 

#周一到周六凌晨3:00做增量備份

 

0 3 * * 1-6 /root/mysqlbackup/mysqldailybackup.sh >/dev/null 2>&1

 

 

mysqlfullbackup.sh注釋：

#!/bin/sh

 

# name:mysqlfullbackup.sh

 

# ps:mysql database full backup.

 

# write by:i.stone

 

# last modify:2007-11-17

 

#

 

# use mysqldump --help get more detail.

 

#

 

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

 

# 定義腳本目錄

 

scriptsdir=`pwd`

 

# 定義數據庫目錄

 

mysqldir=/usr/local/mysql

 

# 定義用於備份數據庫的用戶名和密碼

 

user=root

 

userpwd=111111

 

# 定義備份目錄

 

databackupdir=/tmp/mysqlbackup

 

# 定義郵件正文文件

 

emailfile=$databackupdir/email.txt

 

# 定義郵件地址

 

[email protected]

 

# 定義備份日志文件

 

logfile=$databackupdir/mysqlbackup.log

 

date=`date -i`

 

 

echo "" > $emailfile

 

echo $(date +"%y-%m-%d %h:%m:%s" >> $emailfile

 

cd $databackupdir

 

# 定義備份文件名

 

dumpfile=mysql_$date.sql

 

gzdumpfile=mysql_$date.sql.tar.gz

 

 

# 使用mysqldump備份數據庫，請根據具體情況設置參數

 

$mysqldir/bin/mysqldump -u$user -p$userpwd

 

--opt --default-character-set=utf8 --extended-insert=false

 

--triggers -r --hex-blob --all-databases

 

--flush-logs --delete-master-logs

 

--delete-master-logs

 

-x > $dumpfile

 

 

# 壓縮備份文件

 

if [[ $? == 0 ]]; then

 

tar czf $gzdumpfile $dumpfile >> $emailfile 2>&1

 

echo "backupfilenamegzdumpfile" >> $emailfile

 

echo "database backup success!" >> $emailfile

 

rm -f $dumpfile

 

 

# delete daily backup files.

 

cd $databackupdir/daily

 

rm -f *

 

 

# delete old backup files(mtime>2).

 

$scriptsdir/rmbackup.sh

 

 

# 如果不需要將備份傳送到備份服務器或備份服務器為windows，請將標綠的行注釋掉

 

# move backup files to backup server.

 

#適合linux（mysql服務器）到linux（備份服務器）

 

$scriptsdir/rsyncbackup.sh

 

if (( !$? )); then

 

    echo "move backup files to backup server success!" >> $emailfile

 

    else

 

    echo "move backup files to backup server fail!" >> $emailfile

  fi

else

echo "database backup fail!" >> $emailfile

fi

# 寫日志文件

echo "--------------------------------------------------------" >> $logfile

 

cat $emailfile >> $logfile

 

# 發送郵件通知

cat $emailfile | mail -s "mysql backup" $email

（5） 、恢復數據庫到備份時的狀態

用 mysqldump 備份出來的文件是一個可以直接倒入的 sql 腳本,直接用 mysql 客戶端導入就可以了。

/usr/local/mysql/bin/mysql -uroot -puserpwd db_name < db_name.sql
對於任何可適用的更新日志，將它們作為 mysql 的輸入：
　　% ls -t -r -1 hostname-bin* | xargs mysqlbinlog | mysql -uuser -puserpwd

ls 命令生成更新日志文件的一個單列列表，根據服務器產生它們的次序排序（注意：如果你修改任何一個文件，你將改變排序次序，這將導致更新日志以錯誤的次序被運用。）

本套備份策略只能恢復數據庫到最後一次備份時的狀態，要想在崩潰時丟失的數據盡量少應該更頻繁的進行備份，要想恢復數據到崩潰時的狀態請使用主從復制機制(replication)。如果使用本套備份腳本，將日志文件和數據文件放到不同的磁盤上是一個不錯的主義，這樣不僅可以提高數據寫入速度，還能使數據更安全
mysql支持單向、異步復制，復制過程中一個服務器充當主服務器，而一個或多個其它服務器充當從服務器。主服務器將更新寫入二進制日志文件，並維護日志文件的一個索引以跟蹤日志循環。當一個從服務器連接到主服務器時，它通知主服務器從服務器在日志中讀取的最後一次成功更新的位置。從服務器接收從那時起發生的任何更新，然後封鎖並等待主服務器通知下一次更新。

為什麼使用主從復制？

1、主服務器/從服務器設置增加了健壯性。主服務器出現問題時，你可以切換到從服務器作為備份。

2、通過在主服務器和從服務器之間切分處理客戶查詢的負荷，可以得到更好的客戶響應時間。但是不要同時在主從服務器上進行更新，這樣可能引起沖突。

3、使用復制的另一個好處是可以使用一個從服務器執行備份，而不會干擾主服務器。在備份過程中主服務器可以繼續處理更新。
 

mysql使用3個線程來執行復制功能(其中1個在主服務器上，另兩個在從服務器上。當發出start slave時，從服務器創建一個i/o線程，以連接主服務器並讓主服務器發送二進制日志。主服務器創建一個線程將二進制日志中的內容發送到從服務器。從服務器i/o線程讀取主服務器binlog dump線程發送的內容並將該數據拷貝到從服務器數據目錄中的本地文件中，即中繼日志。第3個線程是sql線程，從服務器使用此線程讀取中繼日志並執行日志中包含的更新。show processlist語句可以查詢在主服務器上和從服務器上發生的關於復制的信息。

默認中繼日志使用host_name-relay-bin.nnnnnn形式的文件名，其中host_name是從服務器主機名，nnnnnn是序列號。用連續序列號來創建連續中繼日志文件，從000001開始。從服務器跟蹤中繼日志索引文件來識別目前正使用的中繼日志。默認中繼日志索引文件名為host_name-relay-bin.index。在默認情況，這些文件在從服務器的數據目錄中被創建。中繼日志與二進制日志的格式相同，並且可以用mysqlbinlog讀取。當sql線程執行完中繼日志中的所有事件後，中繼日志將會被自動刪除。

從服務器在數據目錄中另外創建兩個狀態文件--master.info和relay-log.info。狀態文件保存在硬盤上，從服務器關閉時不會丟失。下次從服務器啟動時，讀取這些文件以確定它已經從主服務器讀取了多少二進制日志，以及處理自己的中繼日志的程度。
 

設置主從復制：

1、確保在主服務器和從服務器上安裝的mysql版本相同，並且最好是mysql的最新穩定版本。

2、在主服務器上為復制設置一個連接賬戶。該賬戶必須授予replication slave權限。如果賬戶僅用於復制(推薦這樣做)，則不需要再授予任何其它權限。

mysql> grant replication slave on *.*

    -> to 'replication'@'%.yourdomain.com' identified by 'slavepass';

3、執行flush tables with read lock語句清空所有表和塊寫入語句：

mysql> flush tables with read lock；

保持mysql客戶端程序不要退出。開啟另一個終端對主服務器數據目錄做快照。

shell> cd /usr/local/mysql/

shell> tar -cvf /tmp/mysql-snapshot.tar ./data

如果從服務器的用戶賬戶與主服務器的不同，你可能不想復制mysql數據庫。在這種情況下，應從歸檔中排除該數據庫。你也不需要在歸檔中包括任何日志文件或者master.info或relay-log.info文件。

當flush tables with read lock所置讀鎖定有效時（即mysql客戶端程序不退出)，讀取主服務器上當前的二進制日志名和偏移量值：

mysql > show master status;

+---------------+----------+--------------+------------------+

| file          | position | binlog_do_db | binlog_ignore_db |

+---------------+----------+--------------+------------------+

| mysql-bin.003 | 73       | test         | manual,mysql     |

+---------------+----------+--------------+------------------+

file列顯示日志名，而position顯示偏移量。在該例子中，二進制日志值為mysql-bin.003，偏移量為73。記錄該值。以後設置從服務器時需要使用這些值。它們表示復制坐標，從服務器應從該點開始從主服務器上進行新的更新。

如果主服務器運行時沒有啟用--logs-bin，show master status顯示的日志名和位置值為空。在這種情況下，當以後指定從服務器的日志文件和位置時需要使用的值為空字符串('')和4.

取得快照並記錄日志名和偏移量後，回到前一中端重新啟用寫活動：

mysql> unlock tables；

4、確保主服務器主機上my.cnf文件的[mysqld]部分包括一個log-bin選項。該部分還應有一個server-id=master_id選項，其中master_id必須為1到232–1之間的一個正整數值。例如：

[mysqld]

log-bin

server-id=1

如果沒有提供那些選項，應添加它們並重啟服務器。

5、停止從服務器上的mysqld服務並在其my.cnf文件中添加下面的行：

[mysqld]

server-id=2

slave_id值同master_id值一樣，必須為1到232–1之間的一個正整數值。並且，從服務器的id必須與主服務器的id不相同。

6、將數據備據目錄中。確保對這些文件和目錄的權限正確。服務器 mysql運行的用戶必須能夠讀寫文件，如同在主服務器上一樣。

shell> chown -r mysql:mysql /usr/local/mysql/data

7、啟動從服務器。在從服務器上執行下面的語句，用你的系統的實際值替換選項值：

 

        mysql> change master to

            -> master_host='master_host_name',

            -> master_user='replication_user_name',

            -> master_password='replication_password',

            -> master_log_file='recorded_log_file_name',

            -> master_log_pos=recorded_log_position;

8、啟動從服務器線程：

        mysql> start slave；

執行這些程序後，從服務器應連接主服務器，並補充自從快照以來發生的任何更新。

9、如果出現復制錯誤，從服務器的錯誤日志（hostname.err）中也會出現錯誤消息。

10、從服務器復制時，會在其數據目錄中發現文件master.info和hostname-relay-log.info。從服務器使用這兩個文件跟蹤已經處理了多少主服務器的二進制日志。不要移除或編輯這些文件，除非你確切知你正在做什麼並完全理解其意義。即使這樣，最好是使用change master to語句