MySQL的備份與恢復

jopen 11年前發布 | 49K 次閱讀 MySQL 數據庫服務器

數據庫名稱為datacrawl，數據庫存儲引擎為MyISAM。數據庫運行在本地上，備份數據存儲在主機名稱為hadoop00的服務器上。

備份方案:周一到周日每天凌晨1:30實現一次物理備份，周日凌晨2:30實現一次邏輯備份。所有的備份都通過設置crontab自動執行。

以下是物理備份的腳本。使用shell腳本，mysqlhotcopy進行數據的備份，crontab將腳本設置為自動執行。

#!/bin/bash
#基本的參數配置
#備份文件存放路徑
backup_target_dir=/backup/db
backup_compression_dir=/backup/compressiondb
db=datacrawldb


#復制數據庫文件
mysqlhotcopy -u hive -p 123456 $db $backup_target_dir

#將文件在本地進行壓縮
DATE=$(date +%Y%m%d)
cd $backup_compression_dir
tar -zcvf $backup_compression_dir/$db$DATE.tar.gz $backup_target_dir/$db

#刪除本地的源文件
rm -rf $backup_target_dir/$db/

#將數據備份至hadoop00的機器上
scp -r $backup_compression_dir/$db$DATE.tar.gz root@hadoop00:$backup_compression_dir

#刪除本地的壓縮文件
rm -rf $backup_compression_dir/$db$DATE.tar.gz

#刪除10天前的壓縮數據
ccDATE=$(date "-d 10 day ago" +%Y%m%d)
ssh hadoop00 rm -rf $backup_compression_dir/$db$ccDATE.tar.gz

修改腳本的執行權限

chmod +x physicalbackup.sh

讓腳本每天執行一次，每天備份一次數據庫，在crontab 里面添加一行

30 1 * * * /liuzp/NSTC_DataCrawl/DataCrawl/djcode/website/mysqlbackup/physicalbackup.sh

還原數據庫的時候只要把備份出的文件拷貝到數據庫放置數據的目錄下，修改權限。重啟下數據庫就完成了恢復了。

以下為邏輯備份的腳本。使用mysqldump進行備份。

#!/bin/bash
backup_compression_dir=/backup/logicalbackup
db=datacrawldb

#復制數據庫文件
mysqldump -uhive -p123456 $db > $db.sql

#將文件壓縮
DATE=$(date +%Y%m%d)
tar -zcvf $db$DATE.tar.gz $db.sql

#將文件備份至hadoop00服務器上
scp $db$DATE.tar.gz root@hadoop00:$backup_compression_dir

#刪除源文件
rm -rf $db.sql
rm -rf $db$DATE.tar.gz

#刪除上次的壓縮數據
ccDATE=$(date "-d 7 day ago" +%Y%m%d)
ssh hadoop00 rm -rf $backup_compression_dir/$db$ccDATE.tar.gz

修改腳本的執行權限

chmod +x logicalbackup.sh

crontab用來自動執行備份的腳本文件

30 2 * * 0 /liuzp/NSTC_DataCrawl/DataCrawl/djcode/website/mysqlbackup/logicalbackup.sh

備份的作用

備份主要的作用：

（1）災難恢復：硬件故障、一個不經意的bug導致數據損壞或者服務器機器數據由于某些原因不可獲取或者無法使用

（2）人們改變想法：例如刪除數據后又想恢復這些數據

（3）測試：基于實際數據來測試的方法是，定期用最新的生產環境數據更新測試服務器。

備份策略

（1）首先要確定是事務性還是非事務性，兩種不同的存儲引擎備份方式在處理數據一致性方面是不一樣的；

（2）確定使用全備份還是增量備份；

（3）可以采用復制的方法做異地備份，但是，復制不能代替備份，它對數據庫的誤操作也無能為力；

（4）要定期的做備份，備份的周期要充分考慮系統可以承受的恢復時間，備份要在系統負載較小的時候進行；

（5）確保MySQL打開log-bin選項，有了BINLOG，MySQL才可以在必要的時候做完整恢復。

備份種類

離線備份

離線備份，是關閉MySQL做備份，是一種簡單而且安全的備份方法。它不用關注InnoDB緩沖池中的臟頁或者其他緩存。也不需要擔心數據在嘗試備份的過程中被修改，并且因為服務器不對應用提供訪問，所以可以很快的完成備份，但是在高負載和高并發下對MySQL進行關閉和重啟的時間還是無法容忍的，這會導致昂貴的代價。

在線備份

在線備份是不需要生產服務器停機的備份。但是由于一致性的要求，對服務器進行在線備份的時候仍然會有明顯的服務中斷。MyISAM引擎的數據庫，一般系統會使用FLUSH TABLES WITH READ LOCK操作，這會導致MySQL關閉并鎖住所有的表，將MyISAM的數據文件刷新到磁盤上，并且刷新查詢緩存。

邏輯備份

邏輯備份是將數據導出到相應的文件中，以一種MySQL能夠解析的形式進行保存。

邏輯備份的優點：

1. 可以使用編輯器像grep和sed之類的命令查看和操作的普通文件；

2. 可以通過網絡來備份和恢復；

3. 與存儲引擎無關；

4. 有助于避免數據損壞。

邏輯備份的缺點：

1. 必須由數據庫服務器生成邏輯備份的工作，會消耗很多的CPU周期；

2.邏輯備份在某些場景下比數據庫文件本身更大；

3.從邏輯備份中還原需要MySQL加載與解釋語句，轉化為存儲格式，并重建索引，所有這一切會很慢。

物理備份

物理備份是直接復制原始文件的方式進行備份。原始文件是指存在于硬盤上的文件。

物理備份的優點：

1. 基于文件的物理備份，只需要將需要的文件復制到其他地方即可完成備份。不需要其他額外的工作來生成原始文件。

2. 從物理備份中恢復比較快速，因為MySQL服務器不需要執行任何SQL或構建索引。

物理備份的缺點：

1. InnoDB的原始文件通常比相應的邏輯備份要大很多；

2. 物理備份不總是可以跨平臺、操作系統及MySQL版本。

混合備份

建議混合使用物理和邏輯兩種方式來做備份：先使用物理復制，以此數據啟動MySQL服務器實例并運行mysqlcheck。

增量備份

增量備份是自從任意類型的上次備份后所有修改做的備份。增量備份對減少服務器開銷、備份時間及備份空間而言都很適合。

增量備份的缺點包括增加恢復復雜度，額外的風險，以及更長的恢復時間。如果可以做全備，考慮到簡便性，做好使用全備份。

一致性

數據一致性

在線備份，需要所有的相關表的一致性備份。如果使用的不是事務型存儲引擎，需要在備份時候使用LOCK TABLES來鎖住所有要一起備份的表，備份完成后再釋放鎖。InnoDB需要在服務器上使用REPEATABLE READ事務隔離級別，并且沒有任何DDL以及基于時間點的數據快照，切回備份過程中不會阻塞任何后續的工作。

復制

復制不是備份。備庫對生成備份是一個干涉較少的源，但它不是備份本身。對于RAID卷，SAN和文件系統快照，也同樣如此。從備庫中備份最大的好處是可以不干擾主庫，避免在主庫上增加額外的負載。

備份內容

生成物理備份

冷備份

冷備份就是停掉數據庫服務，cp數據文件的方法，這種方法對MyISAM和InnoDB存儲引擎都適合。但是一般很少用，因為很多應用是不允許長時間停機的。進行備份的操作是：暫停MySQL服務，在操作系統級別備份MySQL的數據文件和日志文件到備份目錄。

熱備份

MyISAM存儲引擎

MyISAM存儲引擎的熱備份是有很多方法，但是其實質就是將要備份的表加讀鎖，然后再cp數據文件到備份目錄。

方法一：使用mysqlhotcopy工具

mysqlhotcopy是MySQL自帶的一個熱備份工具，使用方法很簡單：

shell>mysqlhotcopy db_name [/path/to/new_directory]

方法二：手工鎖表copy

首先數據庫中所有表加讀鎖

mysql>flush tables for read;

然后cp數據文件到備份目錄即可。

InnoDB存儲引擎

Percona XtraBackup工具是InnoDB進行熱備份的工具。它是開源并且免費的，除了核心備份工具外，還有一個用Perl寫的封裝腳本，可以提供更多高級功能，它支持類似流、增量、壓縮和多線程備份操作。也有許多特別的功能，用以降低在高負載的系統上備份的影響。

生成邏輯備份

有兩種類型的邏輯備份：SQL導出和符號分割文件。

SQL導出

我們一般使用mysqldump方式進行SQL導出的操作。如下圖是使用默認參數的mysqldump得到的輸出結果。導出數據包括表結構和數據，都以有效的SQL命令形式寫出。

其中參數-l表示給所有表加一個讀鎖，-F表示生成一個新的日志文件

符號分割文件備份

使用SQL命令SELECT INTO OUTFILE以符號分隔文件格式創建數據的邏輯備份。符號分隔文件包含以ASCII展示的原始數據，沒有SQL、注釋和列名。

mysql->select * into outfile '/tmp/user.txt'
     ->fields terminated by ',' optionally enclosed by '"'
     ->lines terminated by '\n'
     ->from datacrawldb.users_user

以下是user.txt文件的內容：

從備份中恢復

還原邏輯備份

加載SQL文件

如果有一個SQL導出文件，它將包含可執行的SQL。運行這個文件，就可以進行還原功能。

$mysql < sakila-backup.sql

將備份恢復后的數據并不完整，還需要將備份后執行的日志進行重做，語法如下：

$mysqlbinlog binlog-file | mysql -u root -p ***

加載符號分隔文件

如果是通過SELECT INTO OUTFILE導出的符號分隔文件，可以使用LOAD DATA INFILE通過相同的參數來加載，也可以使用mysqlimport，這是LOAD DATA FILE的一個包裝，這種方式依賴命令約定決定從哪里加載一個文件的數據。

基于時間點的恢復

如果發生了誤操作，這時使用完全恢復是沒有用的，因為日志里面還存在誤操作的語句。我們應該首先恢復到誤操作之前的狀態，然后跳過誤操作語句，在恢復后面執行的語句，完成我們的恢復。這種恢復叫做不完全恢復。

還原物理備份

冷備份還原

首先停掉MySQL服務，在操作系統級別恢復MySQL的數據文件；然后重啟MySQL服務，使用mysqlbinlog工具恢復自備份依賴的所有BINLOG。

本文由用戶 jopen 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1382152331946.html

MySQL 數據庫服務器