mydql數(shù)據(jù)備份

一、備份的目的 做災(zāi)難恢復(fù)：對損壞的數(shù)據(jù)進行恢復(fù)和還原 需求改變：因需求改變而需要把數(shù)據(jù)還原到改變以前 測試：測試新功能是否可用 二、備份需要考慮的問題 可以容忍丟失多長時間的數(shù)據(jù)； 恢復(fù)數(shù)據(jù)要在多長時間內(nèi)完； 恢復(fù)的時候是否需要持續(xù)提供服務(wù)；

一、備份的目的

做災(zāi)難恢復(fù)：對損壞的數(shù)據(jù)進行恢復(fù)和還原
需求改變：因需求改變而需要把數(shù)據(jù)還原到改變以前
測試：測試新功能是否可用

二、備份需要考慮的問題

可以容忍丟失多長時間的數(shù)據(jù)；
恢復(fù)數(shù)據(jù)要在多長時間內(nèi)完；
恢復(fù)的時候是否需要持續(xù)提供服務(wù)；
恢復(fù)的對象，是整個庫，多個表，還是單個庫，單個表。

三、備份的類型

1、根據(jù)是否需要數(shù)據(jù)庫離線

冷備（cold backup）：需要關(guān)mysql服務(wù)，讀寫請求均不允許狀態(tài)下進行；
溫備（warm backup）： 服務(wù)在線，但僅支持讀請求，不允許寫請求；
熱備（hot backup）：備份的同時，業(yè)務(wù)不受影響。

注：

1、這種類型的備份，取決于業(yè)務(wù)的需求，而不是備份工具
2、MyISAM不支持熱備，InnoDB支持熱備，但是需要專門的工具

2、根據(jù)要備份的數(shù)據(jù)集合的范圍
完全備份：full backup，備份全部字符集。
增量備份: incremental backup 上次完全備份或增量備份以來改變了的數(shù)據(jù)，不能單獨使用，要借助完全備份，備份的頻率取決于數(shù)據(jù)的更新頻率。
差異備份：differential backup 上次完全備份以來改變了的數(shù)據(jù)。
建議的恢復(fù)策略：
完全+增量+二進制日志
完全+差異+二進制日志

3、根據(jù)備份數(shù)據(jù)或文件

物理備份：直接備份數(shù)據(jù)文件

優(yōu)點：

備份和恢復(fù)操作都比較簡單，能夠跨mysql的版本，
恢復(fù)速度快，屬于文件系統(tǒng)級別的

建議：

不要假設(shè)備份一定可用，要測試
mysql>check tables；檢測表是否可用
邏輯備份: 備份表中的數(shù)據(jù)和代碼

優(yōu)點：

恢復(fù)簡單、
備份的結(jié)果為ASCII文件，可以編輯
與存儲引擎無關(guān)
可以通過網(wǎng)絡(luò)備份和恢復(fù)

缺點：

備份或恢復(fù)都需要mysql服務(wù)器進程參與
備份結(jié)果占據(jù)更多的空間，
浮點數(shù)可能會丟失精度
還原之后，縮影需要重建

四：備份的對象

1、 數(shù)據(jù)；
2、配置文件；
3、代碼：存儲過程、存儲函數(shù)、觸發(fā)器
4、os相關(guān)的配置文件
5、復(fù)制相關(guān)的配置
6、二進制日志

五、備份和恢復(fù)的實現(xiàn)

1、利用select into outfile實現(xiàn)數(shù)據(jù)的備份與還原
1.1把需要備份的數(shù)據(jù)備份出來

代碼如下:
mysql> use hellodb; //打開hellodb庫
mysql> select * from students; 查看students的屬性
mysql> select * from students where Age > 30 into outfile ‘/tmp/stud.txt' ; //將年齡大于三十的同學(xué)的信息備份出來

注意：

備份的目錄路徑必須讓當(dāng)前運行mysql服務(wù)器的用戶mysql具有訪問權(quán)限

備份完成之后需要把備份的文件從tmp目錄復(fù)制走，要不就失去備份的目的了

回到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]#

你會發(fā)現(xiàn)是個文本文件。所以不能直接導(dǎo)入數(shù)據(jù)庫了。需要使用load data infile 恢復(fù)

回到mysql服務(wù)器端，刪除年齡大于30的用戶，模擬數(shù)據(jù)被破壞

mysql> delete from students where Age > 30;

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

2、利用mysqldump工具對數(shù)據(jù)進行備份和還原

mysqldump 常用來做溫備，所以我們首先需要對想備份的數(shù)據(jù)施加讀鎖，

2.1 施加讀鎖的方式：

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

--lock-all-tables 是對要備份的數(shù)據(jù)庫的所有表施加讀鎖

--lock-table 僅對單張表施加讀鎖，即使是備份整個數(shù)據(jù)庫，它也是在我們備份某張表的時候才對該表施加讀鎖，因此適用于備份單張表

2、在服務(wù)器端書寫命令，

mysql> flush tables with read lock; 施加鎖，表示把位于內(nèi)存上的表統(tǒng)統(tǒng)都同步到磁盤上去，然后施加讀鎖

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

但這對于InnoDB存儲引擎來講，雖然你也能夠請求道讀鎖，但是不代表它的所有數(shù)據(jù)都已經(jīng)同步到磁盤上，因此當(dāng)面對InnoDB的時候，我們要使用mysql> show engine innodb status; 看看InnoDB所有的數(shù)據(jù)都已經(jīng)同步到磁盤上去了，才進行備份操作。

2.2備份的策略：

完全備份+增量備份+二進制日志

演示備份的過程;

2.3 先給數(shù)據(jù)庫做完全備份：

代碼如下:
[root@www ~]# mysqldump -uroot --single-transaction --master-data=2 --databases hellodb > /backup/hellodb_`date +%F`.sql

--single-transaction: 基于此選項能實現(xiàn)熱備InnoDB表；因此，不需要同時使用--lock-all-tables；
--master-data=2 記錄備份那一時刻的二進制日志的位置，并且注釋掉，1是不注釋的
--databases hellodb 指定備份的數(shù)據(jù)庫

然后回到mysql服務(wù)器端，

2.4回到mysql服務(wù)器端更新數(shù)據(jù)

代碼如下:
mysql> create table tb1(id int); 創(chuàng)建表
mysql> insert into tb1 values (1),(2),(3); 插入數(shù)據(jù)，這里只做演示，隨便插入了幾個數(shù)據(jù)

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 在回到服務(wù)器端：

代碼如下:
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再回到服務(wù)器

代碼如下:
mysql> insert into tb1 values (4),(5); 在插入一些數(shù)值
mysql> drop database hellodb; 刪除hellodb庫

2.9導(dǎo)出這次得二進制日志：

代碼如下:
[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 //導(dǎo)出二進制日志

2.10先讓mysql離線

回到服務(wù)器端：

代碼如下:
mysql> set sql_log_bin=0; 關(guān)閉二進制日志
mysql> flush logs; 滾動下日志

2.11模擬數(shù)據(jù)庫損壞

代碼如下: mysql> drop database hellodb;

2.12開始恢復(fù)數(shù)據(jù)：

代碼如下:
[root@www ]# mysql < /backup/hellodb_2013-09-08.sql //導(dǎo)入完全備份文件
[root@www ]# mysql < /backup/hellodb_2013-09-08_05.sql //導(dǎo)入增量備份文件
[root@www ]# mysql< hellodb.sql //導(dǎo)入二進制文件

驗證完成，顯示結(jié)果為我們預(yù)想的那樣

注：

1、真正在生產(chǎn)環(huán)境中，我們應(yīng)該導(dǎo)出的是整個mysql服務(wù)器中的數(shù)據(jù)，而不是單個庫，因此應(yīng)該使用--all-databases
2、在導(dǎo)出二進制日志的時候，可以直接復(fù)制文件即可，但是要注意的是，備份之前滾動下日志。
3、利用lvm快照實現(xiàn)幾乎熱備的數(shù)據(jù)備份與恢復(fù)

3.1策略：

完全備份+二進制日志；

3.2準備：

注：事務(wù)日志必須跟數(shù)據(jù)文件在同一個LV上；

3.3創(chuàng)建lvm Lvm的創(chuàng)建這里就不多說了，想了解話點擊http://www.gxlcms.com/LINUXjishu/105937.html

3.4 修改mysql主配置文件存放目錄內(nèi)的文件的權(quán)限與屬主屬組，并初始化mysql

代碼如下:
[root@www ~]# mkdir /mydata/data //創(chuàng)建數(shù)據(jù)目錄
[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 添加數(shù)據(jù)目錄
sync_binlog = 1 開啟此功能

3.6 啟動服務(wù)

代碼如下:
[root@www mysql]# service mysqld start
mysql> set session sql_log_bin=0; 關(guān)閉二進制日志
mysql> source /backup/all_db_2013-09-08.sql 讀取備份文件

3.7回到mysql服務(wù)器：

代碼如下:
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導(dǎo)出二進制文件，創(chuàng)建個目錄單獨存放

代碼如下:
[root@www ~]# mkdir /backup/limian
[root@www ~]# mysql -e 'show master status;' > /backup/limian/binlog.txt
[root@www ~]#

3.9為數(shù)據(jù)所在的卷創(chuàng)建快照：

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

回到服務(wù)器端，釋放讀鎖

代碼如下:
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更新數(shù)據(jù)庫的數(shù)據(jù)，并刪除數(shù)據(jù)目錄先的數(shù)據(jù)文件，模擬數(shù)據(jù)庫損壞

代碼如下:
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測試

啟動服務(wù)

代碼如下:
[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; #二進制恢復(fù)
mysql> SHOW TABLES; #查看恢復(fù)結(jié)果
mysql> SET sql_log_bin=1; #開啟二進制日志

注：此方式實現(xiàn)了接近于熱備的方式備份數(shù)據(jù)文件，而且數(shù)據(jù)文件放在lvm中可以根據(jù)數(shù)據(jù)的大小靈活改變lvm的大小，備份的方式也很簡單。

4、基于Xtrabackup做備份恢復(fù)

官方站點：www.percona.com

優(yōu)勢：

1、快速可靠的進行完全備份
2、在備份的過程中不會影響到事務(wù)
3、支持數(shù)據(jù)流、網(wǎng)絡(luò)傳輸、壓縮，所以它可以有效的節(jié)約磁盤資源和網(wǎng)絡(luò)帶寬。
4、可以自動備份校驗數(shù)據(jù)的可用性。

安裝Xtrabackup

代碼如下:
[root@www ~]# rpm -ivh percona-xtrabackup-2.1.4-656.rhel6.i686.rpm

其最新版的軟件可從 http://www.percona.com/software/percona-xtrabackup/ 獲得

注意：在備份數(shù)據(jù)庫的時候，我們應(yīng)該具有權(quán)限，但需要注意的是應(yīng)該給備份數(shù)據(jù)庫時的用戶最小的權(quán)限，以保證安全性，

4.1前提：

應(yīng)該確定采用的是單表一個表空間，否則不支持單表的備份與恢復(fù)。
在配置文件里邊的mysqld段加上

innodb_file_per_table = 1

4.2備份策略
完全備份+增量備份+二進制日志
4.3準備個目錄用于存放備份數(shù)據(jù)

代碼如下:
[root@www ~]# makdir /innobackup

4.4做完全備份：

代碼如下:
[root@www ~]# innobackupex --user=root --password=mypass /innobackup/

注：

1、只要在最后一行顯示 innobackupex: completed OK!，就說明你的備份是正確的。
2、另外要注意的是每次備份之后，會自動在數(shù)據(jù)目錄下創(chuàng)建一個以當(dāng)前時間點命名的目錄用于存放備份的數(shù)據(jù)，那我們?nèi)タ纯炊加惺裁?/p>

[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 ：備份類型、備份狀態(tài)和LSN(日志序列號)范圍信息；
xtrabackup_binlog_info ：mysql服務(wù)器當(dāng)前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置。
xtrabackup_logfile ：非文本文件，xtrabackup自己的日志文件
xtrabackup_binlog_pos_innodb ：二進制日志文件及用于InnoDB或XtraDB表的二進制日志文件的當(dāng)前position。
backup-my.cnf ：備份時數(shù)據(jù)文件中關(guān)于mysqld的配置

4.5回到mysql服務(wù)器端對數(shù)據(jù)進行更新操作

代碼如下:
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= 指定這次增量備份是基于哪一次備份的，這里是完全備份文件,這樣可以把增量備份的數(shù)據(jù)合并到完全備份中去

4.7第二次增量

先去修改數(shù)據(jù)

代碼如下:
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/
這里只須要把最后的目錄改為第一次增量備份的數(shù)據(jù)目錄即可

4.8最后一次對數(shù)據(jù)更改但是沒做增量備份

代碼如下: mysql> delete from coc where id=14;

4.9把二進制日志文件備份出來，(因為最后一次修改，沒做增量備份，要依賴二進制日志做時間點恢復(fù))

代碼如下: [root@www data]# cp mysql-bin.000003 /tmp/

4.10模擬數(shù)據(jù)庫崩潰

代碼如下:
[root@www data]# service mysqld stop
[root@www data]# rm -rf *

恢復(fù)前準備

4.11對完全備份做數(shù)據(jù)同步

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

4.12對第一次增量做數(shù)據(jù)同步

代碼如下:
innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/ --incremental-basedir=/innobackup/2013-09-12_11-37-01/

4.13對第二次增量做數(shù)據(jù)同步

代碼如下:
innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/ --incremental-basedir=/innobackup/2013-09-12_11-45-53/
--apply-log 的意義在于把備份時沒commit的事務(wù)撤銷，已經(jīng)commit的但還在事務(wù)日志中的應(yīng)用到數(shù)據(jù)庫

注：

對于xtrabackup來講，它是基于事務(wù)日志和數(shù)據(jù)文件備份的，備份的數(shù)據(jù)中可能會包含尚未提交的事務(wù)或已經(jīng)提交但尚未同步至數(shù)據(jù)庫文件中的事務(wù)，還應(yīng)該對其做預(yù)處理，把已提交的事務(wù)同步到數(shù)據(jù)文件，未提交的事務(wù)要回滾。因此其備份的數(shù)據(jù)庫，不能立即拿來恢復(fù)。

預(yù)處理的過程：

首先對完全備份文件只把已提交的事務(wù)同步至數(shù)據(jù)文件，要注意的是有增量的時候，不能對事務(wù)做數(shù)據(jù)回滾，不然你的增量備份就沒有效果了。

然后把第一次的增量備份合并到完全備份文件內(nèi)，

以此類推，把后幾次的增量都合并到前一次合并之后的文件中，這樣的話，我們只要拿著完全備份+二進制日志，就可以做時間點恢復(fù)。

4.14數(shù)據(jù)恢復(fù)

代碼如下:
[root@www ~]# service mysqld stop
[root@www data]# rm -rf * 模擬數(shù)據(jù)庫崩潰
[root@www ~]# innobackupex --copy-back /innobackup/2013-09-12_11-03-04/
--copy-back數(shù)據(jù)庫恢復(fù)，后面跟上備份目錄的位置

4.15檢測：

代碼如下:
[root@www ~]# cd /mydata/data/
[root@www data]# chown mysql:mysql *
[root@www data]#service mysqld start

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識，若有侵權(quán)等問題請及時與本網(wǎng)聯(lián)系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com