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 先給資料庫做完全備份：

[root@www ~]# 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先檢視完全備份檔案裡邊記錄的位置：

[root@www 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做增量備份

[root@www 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導出這次得二進制日志：

[root@www backup]# mysqlbinlog --start-position=15982 /mydata/data/mysql-bin.000013 檢視删除操作時二進制日志的位置 

[root@www 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開始恢複資料：

[root@www ]# mysql < /backup/hellodb_2013-09-08.sql  //導入完全備份檔案 

[root@www ]# mysql < /backup/hellodb_2013-09-08_05.sql //導入增量備份檔案 

[root@www ]# mysql< hellodb.sql //導入二進制檔案 

本文轉自 sykmiao 51CTO部落格，原文連結：http://blog.51cto.com/syklinux/1759081，如需轉載請自行聯系原作者