mysql 備份恢複

標籤：資料庫   備份   mysql   

一、備份的目的

做災難恢複：對損壞的資料進行恢複和還原
需求改變：因需求改變而需要把資料還原到改變以前
測試：測試新功能是否可用

二、備份需要考慮的問題

可以容忍丟失多長時間的資料；
恢複資料要在多長時間內完； 
恢複的時候是否需要持續提供服務；
恢複的對象，是整個庫，多個表，還是單個庫，單個表。

三、備份的類型

1、根據是否需要資料庫離線

冷備（cold backup）：需要關mysql服務，讀寫請求均不允許狀態下進行；
溫備（warm backup）： 服務線上，但僅支援讀請求，不允許寫請求；
熱備（hot backup）：備份的同時，業務不受影響。

註：

1、這種類型的備份，取決於業務的需求，而不是備份工具
2、MyISAM不支援熱備，InnoDB支援熱備，但是需要專門的工具

2、根據要備份的資料集合的範圍
完全備份：full backup，備份全部字元集。
增量備份: incremental backup 上次完全備份或增量備份以來改變了的資料，不能單獨使用，要藉助完全備份，備份的頻率取決於資料的更新頻率。
差異備份：differential backup 上次完全備份以來改變了的資料。
建議的恢複策略：
完全+增量+二進位日誌
完全+差異+二進位日誌

3、根據備份資料或檔案

物理備份：直接備份資料檔案

優點：

備份和恢複操作都比較簡單，能夠跨mysql的版本，
恢複速度快，屬於檔案系統層級的

建議：

不要假裝置份一定可用，要測試
mysql>check tables；檢測表是否可用
邏輯備份: 備份表中的資料和代碼

優點：

恢複簡單、
備份的結果為ASCII檔案，可以編輯
與儲存引擎無關
可以通過網路備份和恢複

缺點：

備份或恢複都需要mysql伺服器處理序參與
備份結果佔據更多的空間，
浮點數可能會丟失精度
還原之後，縮影需要重建

四：備份的對象

1、 資料；
2、設定檔；
3、代碼：預存程序、儲存函數、觸發器
4、os相關的設定檔
5、複製相關的配置
6、二進位日誌

五、備份和恢複的實現

1、利用select into outfile實現資料的備份與還原
1.1把需要備份的資料備份出來

mysql> use hellodb;     //開啟hellodb庫 
mysql> select * from students;  查看students的屬性 
mysql> select * from students where Age > 30 into outfile ‘/tmp/stud.txt‘ ;   //將年齡大於三十的同學的資訊備份出來 

文字檔。所以不能直接匯入資料庫了。需要使用load data infile 恢複

回到mysql伺服器端，刪除年齡大於30的使用者，類比資料被破壞

mysql> delete from students where Age > 30;

mysql> load data infile ‘/tmp/stud.txt‘ into table students;

2、利用mysqldump工具對資料進行備份與還原

mysqldump 常用來做溫備，所以我們首先需要對想備份的資料施加讀鎖，

2.1 施加讀鎖的方式：

1.直接在備份的時候添加選項

--lock-all-tables 是對要備份的資料庫的所有表施加讀鎖

--lock-table 僅對單張表施加讀鎖，即使是備份整個資料庫，它也是在我們備份某張表的時候才對該表施加讀鎖，因此適用於備份單張表

2、在伺服器端書寫命令，

mysql> flush tables with read lock; 施加鎖，表示把位於記憶體上的表統統都同步到磁碟上去，然後施加讀鎖

mysql> flush tables with read lock;釋放讀鎖

但這對於InnoDB儲存引擎來講，雖然你也能夠請求道讀鎖，但是不代表它的所有資料都已經同步到磁碟上，因此當面對InnoDB的時候，我們要使用mysql> show engine innodb status; 看看InnoDB所有的資料都已經同步到磁碟上去了，才進行備份操作。

2.2備份的策略：

完全備份+增量備份+二進位日誌

示範備份的過程;

2.3 先給資料庫做完全備份：

[[email protected] ~]# mysqldump -uroot --single-transaction --master-data=2 --databases hellodb > /backup/hellodb_`date +%F`.sql 

--single-transaction: 基於此選項能實現熱備InnoDB表；因此，不需要同時使用--lock-all-tables； 
--master-data=2  記錄備份那一時刻的二進位日誌的位置，並且注釋掉，1是不注釋的 
--databases hellodb 指定備份的資料庫

然後回到mysql伺服器端，

2.4回到mysql伺服器端更新資料

mysql> create table tb1(id int); 建立表 
mysql> insert into tb1 values (1),(2),(3);  插入資料，這裡只做示範，隨便插入了幾個資料

2.5先查看完全備份檔案裡邊記錄的位置：

[[email protected] backup]# cat hellodb_2013-09-08.sql | less 
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000013‘, MASTER_LOG_POS=15684; 記錄了二進位日誌的位置 

2.6 在回到伺服器端：

mysql> show master status; 顯示此時的二進位日誌的位置 
 從備份檔案裡邊記錄的位置到我們此時的位置，即為增量的部分 
+------------------+----------+--------------+------------------+ 
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000004 |      15982 |              |                  | 
+------------------+----------+--------------+------------------+

2.7做增量備份

[[email protected] backup]# mysqlbinlog --start-position=15694 --stop-position=15982
/mydata/data/mysql-bin.000013 > /backup/hellodb_`date +$F_%H`.sql

2.8再回到伺服器

mysql> insert into tb1 values (4),(5); 在插入一些數值 
mysql> drop database hellodb;   刪除hellodb庫 

2.9匯出這次得二進位日誌：

[[email protected] backup]# mysqlbinlog --start-position=15982 /mydata/data/mysql-bin.000013 查看刪除操作時二進位日誌的位置 
[[email protected] backup]# mysqlbinlog --start-position=15982 --stop-position=16176 /mydata/data/mysql-bin.000013 > /tmp/hellodb.sql  //匯出二進位日誌 

2.10先讓mysql離線

回到伺服器端：

mysql> set sql_log_bin=0;  關閉二進位日誌 
mysql> flush logs; 滾動下日誌 

2.11類比資料庫損壞

mysql> drop database hellodb;

2.12開始恢複資料：

[[email protected] ]# mysql < /backup/hellodb_2013-09-08.sql  //匯入完全備份檔案 
[[email protected] ]# mysql < /backup/hellodb_2013-09-08_05.sql //匯入增量備份檔案 
[[email protected] ]# mysql< hellodb.sql //匯入二進位檔案 

本文出自 “Linux” 部落格，請務必保留此出處http://syklinux.blog.51cto.com/9631548/1759081

mysql 備份恢複