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' ; //將年齡大於三十的同學的資訊備份出來
注意：
備份的目錄路徑必須讓當前執行mysql伺服器的使用者mysql具有訪問許可權
備份完成之後需要把備份的檔案從tmp目錄複製走，要不就失去備份的目的了
回到tmp目錄下檢視剛才備份的檔案
[root@www ~]# cd /tmp
[root@www tmp]# cat stud.txt
3Xie Yanke53M216
4Ding Dian32M44
6Shi Qing46M5\N
13Tian Boguang33M2\N
25Sun Dasheng100M\N\N
[root@www tmp]#
你會發現是個文字檔案。所以不能直接匯入資料庫了。需要使用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先檢視完全備份檔案裡邊記錄的位置：
-- 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模擬資料庫損壞
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 //匯入二進位制檔案
驗證完成，顯示結果為我們預想的那樣
注：
1、真正在生產環境中，我們應該匯出的是整個mysql伺服器中的資料，而不是單個庫，因此應該使用--all-databases
2、在匯出二進位制日誌的時候，可以直接複製檔案即可，但是要注意的是，備份之前滾動下日誌。
3、利用lvm快照實現幾乎熱備的資料備份與恢復
3.1策略：
完全備份+二進位制日誌；
3.2準備：
注：事務日誌必須跟資料檔案在同一個LV上；
3.3建立lvm Lvm的建立這裡就不多說了，想了解話點選
3.4 修改mysql主配置檔案存放目錄內的檔案的許可權與屬主屬組，並初始化mysql
[root@www ~]# mkdir /mydata/data //建立資料目錄
[root@www ~]# chown mysql:mysql /mydata/data //改屬組屬主
[root@www ~]#
[root@www ~]# cd /usr/local/mysql/ //必須站在此目錄下
[root@www mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data //初始化mysql
3.5修改配置檔案：
vim /etc/my.cof
datadir=/mydata/data 新增資料目錄
sync_binlog = 1 開啟此功能
3.6 啟動服務
[root@www mysql]# service mysqld start
mysql> set session sql_log_bin=0; 關閉二進位制日誌
mysql> source /backup/all_db_2013-09-08.sql 讀取備份檔案
3.7回到mysql伺服器：
mysql> FLUSH TABLES WITH READ LOCK; 請求讀鎖
注：不要退出，另起一個終端：
mysql> SHOW MASTER STATUS; 檢視二進位制檔案的位置
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> FLUSH LOGS; 建議滾動下日誌。這樣備份日誌的時候就會很方便了
3.8匯出二進位制檔案，建立個目錄單獨存放
[root@www ~]# mkdir /backup/limian
[root@www ~]# mysql -e 'show master status;' > /backup/limian/binlog.txt
[root@www ~]#
3.9為資料所在的卷建立快照：
回到伺服器端，釋放讀鎖
mysql> UNLOCK TABLES;
[root@www ~]# mount /dev/myvg/mysql_snap /mnt/data
[root@www data]# cp * /backup/limian/
[root@www data]#lvremove /dev/myvg/mylv_snap
3.10更新資料庫的資料，並刪除資料目錄先的資料檔案，模擬資料庫損壞
mysql> create table limiantb (id int,name CHAR(10));
mysql> insert into limiantb values (1,'tom');
[root@www data]# mysqlbinlog --start-position=187 mysql-bin.000003 > /backup/limian/binlog.sql
[root@www backup]# cd /mydata/data/
[root@www data]# rm -rf *
[root@www ~]# cp -a /backup/limian/* /mydata/data/
[root@www data]# chown mysql:mysql *
3.11測試
啟動服務
[root@www data]# service mysqld start
[root@www data]# mysql 登陸測試
mysql> SHOW DATABASES;
mysql> SET sql_log_bin=0
mysql> source/backup/limian/binlog.sql; #二進位制恢復
mysql> SHOW TABLES; #檢視恢復結果
mysql> SET sql_log_bin=1; #開啟二進位制日誌
注：此方式實現了接近於熱備的方式備份資料檔案，而且資料檔案放在lvm中可以根據資料的大小靈活改變lvm的大小，備份的方式也很簡單。
4、基於Xtrabackup做備份恢復
官方站點：
優勢：
1、快速可靠的進行完全備份
2、在備份的過程中不會影響到事務
3、支援資料流、網路傳輸、壓縮，所以它可以有效的節約磁碟資源和網路頻寬。
4、可以自動備份校驗資料的可用性。
安裝Xtrabackup
[root@www ~]# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.i686.rpm
其最新版的軟體可從 http:///software/percona-xtrabackup/ 獲得
注意：在備份資料庫的時候，我們應該具有許可權，但需要注意的是應該給備份資料庫時的使用者最小的許可權，以保證安全性，
4.1前提：
應該確定採用的是單表一個表空間，否則不支援單表的備份與恢復。
在配置檔案裡邊的mysqld段加上
innodb_file_per_table = 1
4.2備份策略
完全備份+增量備份+二進位制日誌
4.3準備個目錄用於存放備份資料
[root@www ~]# makdir /innobackup
4.4做完全備份：
[root@www ~]# innobackupex --user=root --password=mypass /innobackup/
注：
1、只要在最後一行顯示 innobackupex: completed OK!，就說明你的備份是正確的。
2、另外要注意的是每次備份之後，會自動在資料目錄下建立一個以當前時間點命名的目錄用於存放備份的資料，那我們去看看都有什麼
[root@www 2013-09-12_11-03-04]# ls
backup-my.cnf ibdata1 performance_schema xtrabackup_binary xtrabackup_checkpoints
hellodb mysql test xtrabackup_binlog_info xtrabackup_logfile
[root@www 2013-09-12_11-03-04]#
xtrabackup_checkpoints ：備份型別、備份狀態和LSN(日誌序列號)範圍資訊；
xtrabackup_binlog_info ：mysql伺服器當前正在使用的二進位制日誌檔案及至備份這一刻為止二進位制日誌事件的位置。
xtrabackup_logfile ：非文字檔案，xtrabackup自己的日誌檔案
xtrabackup_binlog_pos_innodb ：二進位制日誌檔案及用於InnoDB或XtraDB表的二進位制日誌檔案的當前position。
backup-my.cnf ：備份時資料檔案中關於mysqld的配置
4.5回到mysql伺服器端對資料進行更新操作
mysql> use hellodb;
mysql> delete from students where StuID>=24;
4.6增量備份
innobackupex --user=root --password=mypass --incremental /innobackup/--incremental-basedir=/innobackup/2013-09-12_11-03-04/
--incremental 指定備份型別
--incremental-basedir= 指定這次增量備份是基於哪一次備份的，這裡是完全備份檔案,這樣可以把增量備份的資料合併到完全備份中去
4.7第二次增量
先去修改資料
mysql> insert into students (Name,Age,Gender,ClassID,TeacherID) values ('tom',33,'M',2,4);
innobackupex --user=root --password=mypass --incremental /innobackup/ --incremental-basedir=/innobackup/2013-09-12_11-37-01/
這裡只須要把最後的目錄改為第一次增量備份的資料目錄即可
4.8最後一次對資料更改但是沒做增量備份
4.9把二進位制日誌檔案備份出來，(因為最後一次修改，沒做增量備份，要依賴二進位制日誌做時間點恢復)
4.10模擬資料庫崩潰
[root@www data]# service mysqld stop
[root@www data]# rm -rf *
恢復前準備
4.11對完全備份做資料同步
4.12對第一次增量做資料同步
innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/ --incremental-basedir=/innobackup/2013-09-12_11-37-01/
4.13對第二次增量做資料同步
innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/ --incremental-basedir=/innobackup/2013-09-12_11-45-53/
--apply-log 的意義在於把備份時沒commit的事務撤銷，已經commit的但還在事務日誌中的應用到資料庫
注：
對於xtrabackup來講，它是基於事務日誌和資料檔案備份的，備份的資料中可能會包含尚未提交的事務或已經提交但尚未同步至資料庫檔案中的事務，還應該對其做預處理，把已提交的事務同步到資料檔案，未提交的事務要回滾。因此其備份的資料庫，不能立即拿來恢復。
預處理的過程：
首先對完全備份檔案只把已提交的事務同步至資料檔案，要注意的是有增量的時候，不能對事務做資料回滾，不然你的增量備份就沒有效果了。
然後把第一次的增量備份合併到完全備份檔案內，
以此類推，把後幾次的增量都合併到前一次合併之後的檔案中，這樣的話，我們只要拿著完全備份+二進位制日誌，就可以做時間點恢復。
4.14資料恢復
[root@www ~]# service mysqld stop
[root@www data]# rm -rf * 模擬資料庫崩潰
[root@www ~]# innobackupex --copy-back /innobackup/2013-09-12_11-03-04/
--copy-back資料庫恢復，後面跟上備份目錄的位置
4.15檢測：
[root@www ~]# cd /mydata/data/
[root@www data]# chown mysql:mysql *
[root@www data]#service mysqld start
檢測結果資料正常。
本文出自 “遺失ぜ的ァ美好~” 部落格
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28686045/viewspace-2137995/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- RMAN加密備份的三種方式加密
- MySQL多種需求的備份方式MySql
- MySql資料庫備份的幾種方式MySql資料庫
- MySQL 資料庫定時備份的幾種方式MySql資料庫
- Oracle兩種備份方式Oracle
- MYSQL資料匯出備份、匯入的幾種方式MySql
- (7) MySQL資料庫備份詳解MySql資料庫
- mysql之 mysqldump 備份恢復詳解MySql
- 詳解MYSQL的備份還原(PHP實現)MySqlPHP
- MPLS-VPN的幾種備份方式——VecloudCloud
- mysql 啟停三種方式MySql
- MongoDB備份方式詳細介紹MongoDB
- RMAN 備份詳解
- RMAN備份詳解
- Mysqlbackup 備份詳解MySql
- -- RMAN備份詳解
- mysqlbinlog命令詳解 Part 9 MySQL備份策略MySql
- EF三種程式設計方式圖文詳解程式設計
- Mysql的幾種備份與恢復MySql
- 為MySQL選擇合適的備份方式MySql
- "三高"Mysql - Mysql備份概覽MySql
- 詳解MySQL資料備份之mysqldump使用方法MySql
- 三種OracleRMAN備份加密策略（上）Oracle加密
- 跨平臺程式碼三種組織方式詳解
- 解密MySQL備份恢復的4種方法解密MySql
- 詳解Nginx 虛擬主機配置的三種方式（基於埠）Nginx
- 詳解Nginx 虛擬主機配置的三種方式（基於IP）Nginx
- SSO單點登入三種情況的實現方式詳解
- 三種Oracle RMAN備份加密策略（上）Oracle加密
- 三種Oracle RMAN備份加密策略（下）Oracle加密
- Oracle資料庫三種備份方案Oracle資料庫
- MySQL資料庫備份種類以及常用備份工具彙總MySql資料庫
- MySQL 資料庫備份種類以及常用備份工具彙總MySql資料庫
- RMAN備份詳解(轉載)
- 轉載：RMAN 備份詳解
- 四種XML解析方式詳解XML
- mysql的冷備份與熱備份MySql
- postgresql備份方式SQL