mysql的備份和還原
用處:
1、災難恢複
2、審計
3、測試
備份類型:
熱備份、溫備份、冷備份
熱備份:線上備份,讀寫操作可繼續進行,不受影響,隻有基于事務的存儲引擎才能完成
溫備份:能讀不能寫
冷備份:讀寫均不能進行,離線備份
實體備份、邏輯備份
實體備份:複制資料檔案
邏輯備份:把資料從庫中提出出來儲存為文本檔案,無法保證浮點型的精度,和存儲引擎無關,跨平台好,mysqldump
完全備份、增量備份、差異備份
完全備份:全部備份
增量備份:僅備份上次完全備份或增量備份以後變化的資料
差異備份:僅備份上次完全備份以來變化的資料
還原:需要測試
備份對象:
資料
配置檔案
代碼:存儲過程,存儲函數,觸發器
OS相關的配置檔案,如crontab配置計劃及相關的腳本
跟複制相關的配置資訊
二進制日志檔案
熱備份:
myisam:幾乎不可能,可以用邏輯卷來備份,最好溫備份
innodb:可以,xtrabackup,mysqldump
實體備份還是邏輯備份:
實體備份:速度快,和檔案系統無關
邏輯備份:速度慢,丢失浮點數精度,友善使用文本處理工具直接對其處理,可移植性強跨mysql伺服器版本
備份政策:
完全+增量
完全+差異
備份工具:
mysqldump:邏輯備份,myisam(溫備份),innodb(熱備份),備份和恢複過程較慢,适于備份10G以下的資料,因為速度太慢。mysqldumper:多線程的mysqldump。很難實作差異或增量備份。
mysqlhotcopy:實體備份,溫備份工具,僅适用于MyIsam引擎
lvm-snapshot:接近于熱備的工具,因為要先請求全局鎖,而後建立快照,并在建立快照之後釋放全局鎖,使用cp,tar等工具進行實體備份,備份恢複速度快。很難實作增量備份,并且請求全局鎖需要等待一段時間,在繁忙的伺服器上尤其如此
innodb:不能立即建立快照
備份備份工具:select clause into outfile ‘/path/to/somefile’
恢複:load data infile ‘/path/from/somefile’
不會備份關系定義,僅備份表中的資料
也是邏輯備份工具,速度上快于mysqldump
沒法實作增量
第三組工具:
innodb:ibbackup商業工具
xtrabackup:開源工具
備份政策:
1、mysqldump
2、lvm+二進制日志
3、xtrabackup
mysqldump用法:
mysqldump [options] [db_name[tbl_name ...]]
-u:指定使用者名
-h:指定主機
--all-databases:備份所有資料庫
--databases:備份指定的多個庫tb1tb2 >/tmp/a.sql,這種會自動建立庫
恢複時需先有資料庫,沒有則需建立
注意:備份前要加鎖
--lock-all-tables:請求鎖定所有表之後再備份,對myisam、innodb、aria做溫備
--single-tranaction:單事務,能夠對innodb存儲引擎實作熱備,會自動加鎖,與—lock-all-tables不能同時使用
備份代碼:
--events:備份事件、排程器、代碼
--routines:備份存儲過程和存儲函數
--triggers:備份觸發器
備份時滾動日志:
--flush-logs:備份前、加鎖後,滾動日志
複制時的同步位置标記:
--master-data={0|1|2}0:不記錄,1:記錄為changemaster語句2、記錄為注釋change master語句的
使用mysqldump備份:
請求鎖:--lock-all-tables或—single-tranaction進行innodb熱備
滾動日志:--flush-logs
選擇要備份的庫:--databases
記錄二進制日志檔案及位置:--master-data=
恢複:恢複過程沒有必要記錄到二進制日志中,是以臨時關閉二進制日志,關閉其他使用者連接配接source
備份政策:基于mysqldump
備份:mysqldump+二進制日志檔案;
周日做一次完全備份:備份的同時滾動日志
周一至周六:備份二進制日志;
恢複:
完全備份+各二進制日志檔案中至此刻的事件
對MySQL配置檔案,以及與MySQL相關的OS配置檔案在每次修改後都應該直接進行備份;
例1:備份和恢複單個庫:
備份hellodb資料庫
檢視
删除mysql中的資料庫
導入備份
說明導入之前資料庫必須存在才行
建立資料庫
檢視表
例2:備份所有庫:
--all-databases
如果mysql資料庫被删除,恢複時自動建立資料庫
例3:備份多個庫:
--databases db1 db2
例4:備份前應事先加鎖
--lock-all-tables建議使用
--lock-tables,會導緻表時間不一緻,不建議使用
例5:
建立ha資料庫
使用資料庫ha,插入資料
檢視表中資料
備份
檢視二進制日志位置
新增内容
檢視新增資料
删除資料庫
備份新增資料的二進制日志檔案
恢複前先臨時關閉二進制日志功能(目前會話)
導入完全備份
檢視資料庫
使用ha資料庫,并檢視表
導入增量備份
檢視新增的資料
還原二進制日志功能
lvm-snapshot:基于LVM快照的備份
1、事務日志和資料檔案必須在同一個卷上
2、建立快照卷之前,要請求全局鎖,在快照建立完成之後釋放鎖
3、請求全局鎖完成後做日志滾動;做二進制日志檔案及位置标記(手動進行);
備份步驟:
1、請求全局鎖,并滾動日志
mysql> FLUSH TABLES WITH READLOCK;
mysql> FLUSH LOGS;
2、做二進制日志檔案及位置标記(手動進行);
# mysql -e 'show master status'> /path/to/somefile
3、建立快照卷
# lvcreate -L -s -n -p r /path/to/some_lv
4、釋放全局鎖
mysql> UNLOCK TABLES;
5、挂載快照卷并備份
# cp
6、備份完成之後,删除快照卷
1、二進制日志儲存好;
提取備份之後的所有事件至某sql腳本中;
2、還原資料,修改權限及屬主屬組等,并啟動mysql
3、做即時點還原
使用Xtrabackup進行MySQL備份:
1、簡介
Xtrabackup是由percona提供的mysql資料庫備份工具,據官方介紹,這也是世界上惟一一款開源的能夠對innodb和xtradb資料庫進行熱備的工具。特點:
(1)備份過程快速、可靠;
(2)備份過程不會打斷正在執行的事務;
(3)能夠基于壓縮等功能節約磁盤空間和流量;
(4)自動實作備份檢驗;
(5)還原速度快;
安裝:
[root@school ~]# yum -y install percona-toolkit-2.2.4-1.noarch.rpm percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm
innobackupex:
完全備份:
[root@school data]# mkdir /mybackups #準備備份目錄
臨時改變二進制日志功能,導入資料庫
開啟二進制功能
檢視表的屬性
停止mysql服務
删除mysql所有資料庫
準備階段
複制備份回mysql資料目錄
進入mysql資料目錄
更改屬主屬組并啟動服務
增量備份
每一次恢複後都要立即做一次完全備份
檢視備份檔案
新增資料
檢視備份
再新增資料
檢視備份資訊
最後二進制日志的位置
恢複完全備份
恢複第一次增量
恢複第二次增量
檢視完全備份的及時點,和最後一次的增量及時點一樣
二進制點也和最後一次增量一樣
更改屬主屬組
儲存二進制日志
啟動伺服器
關閉二進制功能,導入資料