mysql備份的三種方式講解

這篇文章主要講解了“MySQL備份的三種方式講解”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“mysql備份的三種方式講解”吧！

公司主營業(yè)務(wù)：成都網(wǎng)站建設(shè)、做網(wǎng)站、移動(dòng)網(wǎng)站開發(fā)等業(yè)務(wù)。幫助企業(yè)客戶真正實(shí)現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。成都創(chuàng)新互聯(lián)是一支青春激揚(yáng)、勤奮敬業(yè)、活力青春激揚(yáng)、勤奮敬業(yè)、活力澎湃、和諧高效的團(tuán)隊(duì)。公司秉承以“開放、自由、嚴(yán)謹(jǐn)、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領(lǐng)域給我們帶來的挑戰(zhàn)，讓我們激情的團(tuán)隊(duì)有機(jī)會用頭腦與智慧不斷的給客戶帶來驚喜。成都創(chuàng)新互聯(lián)推出龍港免費(fèi)做網(wǎng)站回饋大家。

一、備份的目的

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

二、備份需要考慮的問題

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

三、備份的類型

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

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

注：

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

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

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

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

優(yōu)點(diǎn)：

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

建議：

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

優(yōu)點(diǎn)：

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

缺點(diǎn)：

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

四：備份的對象

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

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

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

復(fù)制代碼代碼如下:

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

注意：

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

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

回到tmp目錄下查看剛才備份的文件

[root@www ~]# cd /tmp

[root@www tmp]# cat stud.txt

3Xie Yanke53M216

4Ding Dian32M44

6Shi Qing46M5

13Tian Boguang33M2

25Sun Dasheng100M\N

[root@www tmp]#

你會發(fā)現(xiàn)是個(gè)文本文件。所以不能直接導(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ù)進(jìn)行備份和還原

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

2.1 施加讀鎖的方式：

1.直接在備份的時(shí)候添加選項(xiàng)

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

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

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的時(shí)候，我們要使用mysql> show engine innodb status; 看看InnoDB所有的數(shù)據(jù)都已經(jīng)同步到磁盤上去了，才進(jìn)行備份操作。

2.2備份的策略：

完全備份+增量備份+二進(jìn)制日志

演示備份的過程;

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

復(fù)制代碼代碼如下:

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

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

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

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

復(fù)制代碼代碼如下:

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

2.5先查看完全備份文件里邊記錄的位置：

復(fù)制代碼代碼如下:

[root@www backup]# cat hellodb_2013-09-08.sql | less
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=15684; 記錄了二進(jìn)制日志的位置

2.6 在回到服務(wù)器端：

復(fù)制代碼代碼如下:

2.7做增量備份

復(fù)制代碼代碼如下:

[root@www backup]# mysqlbinlog --start-position=15694 --stop-position=15982
/mydata/data/mysql-bin.000013 > /backup/hellodb_`date +$F_%H`.sql

2.8再回到服務(wù)器

復(fù)制代碼代碼如下:

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

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

復(fù)制代碼代碼如下:

[root@www backup]# mysqlbinlog --start-position=15982 /mydata/data/mysql-bin.000013 查看刪除操作時(shí)二進(jìn)制日志的位置
[root@www backup]# mysqlbinlog --start-position=15982 --stop-position=16176 /mydata/data/mysql-bin.000013 > /tmp/hellodb.sql //導(dǎo)出二進(jìn)制日志

2.10先讓mysql離線

回到服務(wù)器端：

復(fù)制代碼代碼如下:

mysql> set sql_log_bin=0; 關(guān)閉二進(jìn)制日志
mysql> flush logs; 滾動(dòng)下日志

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

復(fù)制代碼代碼如下:

mysql> drop database hellodb;

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

復(fù)制代碼代碼如下:

[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)入二進(jìn)制文件

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

注：

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

3.1策略：

完全備份+二進(jìn)制日志；

3.2準(zhǔn)備：

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

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

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

復(fù)制代碼代碼如下:

[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修改配置文件：

復(fù)制代碼代碼如下:

vim /etc/my.cof
datadir=/mydata/data 添加數(shù)據(jù)目錄
sync_binlog = 1 開啟此功能

3.6 啟動(dòng)服務(wù)

復(fù)制代碼代碼如下:

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

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

復(fù)制代碼代碼如下:

mysql> FLUSH TABLES WITH READ LOCK; 請求讀鎖
注：不要退出，另起一個(gè)終端：
mysql> SHOW MASTER STATUS;          查看二進(jìn)制文件的位置
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> FLUSH LOGS; 建議滾動(dòng)下日志。這樣備份日志的時(shí)候就會很方便了

3.8導(dǎo)出二進(jìn)制文件，創(chuàng)建個(gè)目錄單獨(dú)存放

復(fù)制代碼代碼如下:

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

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

復(fù)制代碼代碼如下:

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

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

復(fù)制代碼代碼如下:

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ù)庫損壞

復(fù)制代碼代碼如下:

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測試

啟動(dòng)服務(wù)

復(fù)制代碼代碼如下:

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

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

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

官方站點(diǎn)：www.percona.com

優(yōu)勢：

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

安裝Xtrabackup

復(fù)制代碼代碼如下:

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

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

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

4.1前提：

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

innodb_file_per_table = 1

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

復(fù)制代碼代碼如下:

[root@www ~]# makdir /innobackup

4.4做完全備份：

復(fù)制代碼代碼如下:

[root@www ~]# innobackupex --user=root --password=mypass /innobackup/

注：

1、只要在最后一行顯示 innobackupex: completed OK!，就說明你的備份是正確的。
2、另外要注意的是每次備份之后，會自動(dòng)在數(shù)據(jù)目錄下創(chuàng)建一個(gè)以當(dāng)前時(shí)間點(diǎn)命名的目錄用于存放備份的數(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)前正在使用的二進(jìn)制日志文件及至備份這一刻為止二進(jìn)制日志事件的位置。
xtrabackup_logfile ：非文本文件，xtrabackup自己的日志文件
xtrabackup_binlog_pos_innodb ：二進(jìn)制日志文件及用于InnoDB或XtraDB表的二進(jìn)制日志文件的當(dāng)前position。
backup-my.cnf ：備份時(shí)數(shù)據(jù)文件中關(guān)于mysqld的配置

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

復(fù)制代碼代碼如下:

mysql> use hellodb;
mysql> delete from students where StuID>=24;

4.6增量備份

復(fù)制代碼代碼如下:

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ù)

復(fù)制代碼代碼如下:

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ù)更改但是沒做增量備份

復(fù)制代碼代碼如下:

mysql> delete from coc where id=14;

4.9把二進(jìn)制日志文件備份出來，(因?yàn)樽詈笠淮涡薷?，沒做增量備份，要依賴二進(jìn)制日志做時(shí)間點(diǎn)恢復(fù))

復(fù)制代碼代碼如下:

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

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

復(fù)制代碼代碼如下:

[root@www data]# service mysqld stop
[root@www data]# rm -rf *

恢復(fù)前準(zhǔn)備

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

復(fù)制代碼代碼如下:

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

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

復(fù)制代碼代碼如下:

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ù)同步

復(fù)制代碼代碼如下:

innobackupex --apply-log --redo-only /innobackup/2013-09-12_11-03-04/ --incremental-basedir=/innobackup/2013-09-12_11-45-53/
--apply-log 的意義在于把備份時(shí)沒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ù)文件，要注意的是有增量的時(shí)候，不能對事務(wù)做數(shù)據(jù)回滾，不然你的增量備份就沒有效果了。

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

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

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

復(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檢測：

復(fù)制代碼代碼如下:

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

檢測結(jié)果數(shù)據(jù)正常。

感謝各位的閱讀，以上就是“mysql備份的三種方式講解”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對mysql備份的三種方式講解這一問題有了更深刻的體會，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點(diǎn)的文章，歡迎關(guān)注！

本文標(biāo)題：mysql備份的三種方式講解
分享URL：http://redsoil1982.com.cn/article24/jhedje.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供定制開發(fā)、建站公司、面包屑導(dǎo)航、全網(wǎng)營銷推廣、品牌網(wǎng)站制作、標(biāo)簽優(yōu)化

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容