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先檢視完全備份檔案裡邊記錄的位置：

複制代碼 代碼如下:

[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 //導入二進制檔案

驗證完成，顯示結果為我們預想的那樣

注：

1、真正在生産環境中，我們應該導出的是整個mysql伺服器中的資料，而不是單個庫，是以應該使用--all-databases

2、在導出二進制日志的時候，可以直接複制檔案即可，但是要注意的是，備份之前滾動下日志。

3、利用lvm快照實作幾乎熱備的資料備份與恢複

3.1政策：

完全備份+二進制日志；

3.2準備：

注：事務日志必須跟資料檔案在同一個LV上；

3.3建立lvm Lvm的建立這裡就不多說了，想了解話點選http://www.jb51.net/LINUXjishu/105937.html

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為資料所在的卷建立快照：

複制代碼 代碼如下:

[root@www ~]# lvcreate -L 100M -s -p r -n mysql_snap /dev/myvg/mydata

回到伺服器端，釋放讀鎖

複制代碼 代碼如下:

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做備份恢複

官方站點：www.percona.com

優勢：

1、快速可靠的進行完全備份

2、在備份的過程中不會影響到事務

3、支援資料流、網絡傳輸、壓縮，是以它可以有效的節約磁盤資源和網絡帶寬。

4、可以自動備份校驗資料的可用性。

安裝Xtrabackup

複制代碼 代碼如下:

[root@www ~]# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.i686.rpm

其最新版的軟體可從 http://www.percona.com/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最後一次對資料更改但是沒做增量備份

複制代碼 代碼如下:

mysql> delete from coc where id=14;

4.9把二進制日志檔案備份出來，(因為最後一次修改，沒做增量備份，要依賴二進制日志做時間點恢複)

複制代碼 代碼如下:

[root@www data]# cp mysql-bin.000003 /tmp/

4.10模拟資料庫崩潰

複制代碼 代碼如下:

[root@www data]# service mysqld stop

[root@www data]# rm -rf *

恢複前準備

4.11對完全備份做資料同步

複制代碼 代碼如下:

[root@www ~]# innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/

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

檢測結果資料正常。

最後：對于網站而言資料就是生命，不管怎麼樣做好資料備份機制還是非常重要的。重要資料務必勤備份。