mysql備份恢復(fù)詳解

前言

為什么需要備份數(shù)據(jù)？

數(shù)據(jù)的備份類型

MySQL備份數(shù)據(jù)的方式

備份需要考慮的問(wèn)題

設(shè)計(jì)合適的備份策略

實(shí)戰(zhàn)演練

使用cp進(jìn)行備份

使用mysqldump+復(fù)制BINARY LOG備份

使用lvm2快照備份數(shù)據(jù)

使用Xtrabackup備份

總結(jié)

前言

我們?cè)囍胍幌? 在生產(chǎn)環(huán)境中什么最重要骚勘？如果我們服務(wù)器的硬件壞了可以維修或者換新, 軟件問(wèn)題可以修復(fù)或重新安裝, 但是如果數(shù)據(jù)沒(méi)了呢隘膘？這可能是最恐怖的事情了吧, 我感覺(jué)在生產(chǎn)環(huán)境中應(yīng)該沒(méi)有什么比數(shù)據(jù)跟更為重要. 那么我們?cè)撊绾伪ＷC數(shù)據(jù)不丟失蹬挤、或者丟失后可以快速恢復(fù)呢？只要看完這篇文章, 大家應(yīng)該就能對(duì)MySQL中實(shí)現(xiàn)數(shù)據(jù)備份和恢復(fù)能有一定的了解棘幸。

為什么需要備份數(shù)據(jù)焰扳？

在前言中大概說(shuō)明了為什么要備份數(shù)據(jù), 但是我們還是應(yīng)該具體了解一下為什么要備份數(shù)據(jù)

在生產(chǎn)環(huán)境中我們數(shù)據(jù)庫(kù)可能會(huì)遭遇各種各樣的不測(cè)從而導(dǎo)致數(shù)據(jù)丟失, 大概分為以下幾種.

硬件故障

軟件故障

自然災(zāi)害

黑客攻擊

誤操作 (占比最大)

所以, 為了在數(shù)據(jù)丟失之后能夠恢復(fù)數(shù)據(jù), 我們就需要定期的備份數(shù)據(jù), 備份數(shù)據(jù)的策略要根據(jù)不同的應(yīng)用場(chǎng)景進(jìn)行定制, 大致有幾個(gè)參考數(shù)值, 我們可以根據(jù)這些數(shù)值從而定制符合特定環(huán)境中的數(shù)據(jù)備份策略

能夠容忍丟失多少數(shù)據(jù)

恢復(fù)數(shù)據(jù)需要多長(zhǎng)時(shí)間

需要恢復(fù)哪一些數(shù)據(jù)

數(shù)據(jù)的備份類型

數(shù)據(jù)的備份類型根據(jù)其自身的特性主要分為以下幾組

完全備份

部分備份

完全備份指的是備份整個(gè)數(shù)據(jù)集( 即整個(gè)數(shù)據(jù)庫(kù) )、部分備份指的是備份部分?jǐn)?shù)據(jù)集(例如: 只備份一個(gè)表)

而部分備份又分為以下兩種

增量備份

差異備份

增量備份指的是備份自上一次備份以來(lái)(增量或完全)以來(lái)變化的數(shù)據(jù); 特點(diǎn): 節(jié)約空間误续、還原麻煩

差異備份指的是備份自上一次完全備份以來(lái)變化的數(shù)據(jù)特點(diǎn): 浪費(fèi)空間吨悍、還原比增量備份簡(jiǎn)單

示意圖

MySQL備份數(shù)據(jù)的方式

在MySQl中我們備份數(shù)據(jù)一般有幾種方式

熱備份

溫備份

冷備份

熱備份指的是當(dāng)數(shù)據(jù)庫(kù)進(jìn)行備份時(shí),數(shù)據(jù)庫(kù)的讀寫操作均不是受影響?

溫備份指的是當(dāng)數(shù)據(jù)庫(kù)進(jìn)行備份時(shí),數(shù)據(jù)庫(kù)的讀操作可以執(zhí)行, 但是不能執(zhí)行寫操作

冷備份指的是當(dāng)數(shù)據(jù)庫(kù)進(jìn)行備份時(shí),數(shù)據(jù)庫(kù)不能進(jìn)行讀寫操作, 即數(shù)據(jù)庫(kù)要下線

MySQL中進(jìn)行不同方式的備份還要考慮存儲(chǔ)引擎是否支持

MyISAM?

?熱備 ×

?溫備 √

?冷備 √

InnoDB

?熱備 √

?溫備 √

?冷備 √

我們?cè)诳紤]完數(shù)據(jù)在備份時(shí), 數(shù)據(jù)庫(kù)的運(yùn)行狀態(tài)之后還需要考慮對(duì)于MySQL數(shù)據(jù)庫(kù)中數(shù)據(jù)的備份方式

物理備份

邏輯備份

物理備份一般就是通過(guò)tar,cp等命令直接打包復(fù)制數(shù)據(jù)庫(kù)的數(shù)據(jù)文件達(dá)到備份的效果

邏輯備份一般就是通過(guò)特定工具從數(shù)據(jù)庫(kù)中導(dǎo)出數(shù)據(jù)并另存?zhèn)浞?/b>(邏輯備份會(huì)丟失數(shù)據(jù)精度)

備份需要考慮的問(wèn)題

定制備份策略前, 我們還需要考慮一些問(wèn)題

我們要備份什么?

一般情況下, 我們需要備份的數(shù)據(jù)分為以下幾種

數(shù)據(jù)

二進(jìn)制日志, InnoDB事務(wù)日志

代碼(存儲(chǔ)過(guò)程、存儲(chǔ)函數(shù)蹋嵌、觸發(fā)器育瓜、事件調(diào)度器)

服務(wù)器配置文件

備份工具

這里我們列舉出常用的幾種備份工具

mysqldump: 邏輯備份工具, 適用于所有的存儲(chǔ)引擎, 支持溫備、完全備份栽烂、部分備份躏仇、對(duì)于InnoDB存儲(chǔ)引擎支持熱備

cp, tar 等歸檔復(fù)制工具: 物理備份工具, 適用于所有的存儲(chǔ)引擎, 冷備、完全備份腺办、部分備份

lvm2 snapshot: 幾乎熱備, 借助文件系統(tǒng)管理工具進(jìn)行備份

mysqlhotcopy: 名不副實(shí)的的一個(gè)工具, 幾乎冷備, 僅支持MyISAM存儲(chǔ)引擎

xtrabackup: 一款非常強(qiáng)大的InnoDB/XtraDB熱備工具, 支持完全備份焰手、增量備份, 由percona提供

設(shè)計(jì)合適的備份策略

針對(duì)不同的場(chǎng)景下, 我們應(yīng)該制定不同的備份策略對(duì)數(shù)據(jù)庫(kù)進(jìn)行備份, 一般情況下, 備份策略一般為以下三種

直接cp,tar復(fù)制數(shù)據(jù)庫(kù)文件

mysqldump+復(fù)制BIN LOGS

lvm2快照+復(fù)制BIN LOGS

xtrabackup

以上的幾種解決方案分別針對(duì)于不同的場(chǎng)景

如果數(shù)據(jù)量較小, 可以使用第一種方式, 直接復(fù)制數(shù)據(jù)庫(kù)文件

如果數(shù)據(jù)量還行, 可以使用第二種方式, 先使用mysqldump對(duì)數(shù)據(jù)庫(kù)進(jìn)行完全備份, 然后定期備份BINARY LOG達(dá)到增量備份的效果

如果數(shù)據(jù)量一般, 而又不過(guò)分影響業(yè)務(wù)運(yùn)行, 可以使用第三種方式, 使用lvm2的快照對(duì)數(shù)據(jù)文件進(jìn)行備份, 而后定期備份BINARY LOG達(dá)到增量備份的效果

如果數(shù)據(jù)量很大, 而又不過(guò)分影響業(yè)務(wù)運(yùn)行, 可以使用第四種方式, 使用xtrabackup進(jìn)行完全備份后, 定期使用xtrabackup進(jìn)行增量備份或差異備份

實(shí)戰(zhàn)演練

使用cp進(jìn)行備份

我們這里使用的是使用yum安裝的mysql-5.1的版本, 使用的數(shù)據(jù)集為從網(wǎng)絡(luò)上找到的一個(gè)員工數(shù)據(jù)庫(kù)

查看數(shù)據(jù)庫(kù)的信息

mysql>SHOWDATABASES; ? ?#查看當(dāng)前的數(shù)據(jù)庫(kù), 我們的數(shù)據(jù)庫(kù)為employees

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| employees ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| test ? ? ? ? ? ? ? |

+--------------------+

4 rows in set (0.00 sec)

mysql> USE employees;

Database changed

mysql> SHOW TABLES; ? ? ? ? #查看當(dāng)前庫(kù)中的表

+---------------------+

| Tables_in_employees |

+---------------------+

| departments ? ? ? ? |

| dept_emp ? ? ? ? ? ?|

| dept_manager ? ? ? ?|

| employees ? ? ? ? ? |

| salaries ? ? ? ? ? ?|

| titles ? ? ? ? ? ? ?|

+---------------------+

6 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM employees; ? #由于篇幅原因, 我們這里只看一下employees的行數(shù)為300024

+----------+

| COUNT(*) |

+----------+

| ? 300024 |

+----------+

1 row in set (0.05 sec)

向數(shù)據(jù)庫(kù)施加讀鎖

mysql>FLUSHTABLESWITHREADLOCK; ? ?#向所有表施加讀鎖

Query OK, 0 rows affected (0.00 sec)

備份數(shù)據(jù)文件

[root@node1 ~]# mkdir /backup ? #創(chuàng)建文件夾存放備份數(shù)據(jù)庫(kù)文件

[root@node1 ~]# cp -a /var/lib/mysql/* /backup ? ? #保留權(quán)限的拷貝源數(shù)據(jù)文件

[root@node1 ~]# ls /backup ? #查看目錄下的文件

employees ?ibdata1 ?ib_logfile0 ?ib_logfile1 ?mysql ?mysql.sock ?test

模擬數(shù)據(jù)丟失并恢復(fù)

[root@node1 ~]# rm -rf /var/lib/mysql/* ? ?#刪除數(shù)據(jù)庫(kù)的所有文件

[root@node1 ~]# service mysqld restart ? #重啟MySQL, 如果是編譯安裝的應(yīng)該不能啟動(dòng), 如果rpm安裝則會(huì)重新初始化數(shù)據(jù)庫(kù)

mysql> SHOW DATABASES; ? ?#因?yàn)槲覀兪莚pm安裝的, 連接到MySQL進(jìn)行查看, 發(fā)現(xiàn)數(shù)據(jù)丟失了！

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| mysql ? ? ? ? ? ? ?|

| test ? ? ? ? ? ? ? |

+--------------------+

3 rows in set (0.00 sec)

[root@node1 ~]# rm -rf /var/lib/mysql/* ? ?#這一步可以不做

[root@node1 ~]# cp -a /backup/* /var/lib/mysql/ ? ?#將備份的數(shù)據(jù)文件拷貝回去

[root@node1 ~]# service mysqld restart ?#重啟MySQL

#重新連接數(shù)據(jù)并查看

mysql> SHOW DATABASES; ? ?#數(shù)據(jù)庫(kù)已恢復(fù)

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| employees ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| test ? ? ? ? ? ? ? |

+--------------------+

4 rows in set (0.00 sec)

mysql> USE employees; ? ? ?

mysql> SELECT COUNT(*) FROM employees; ? ?#表的行數(shù)沒(méi)有變化

+----------+

| COUNT(*) |

+----------+

| ? 300024 |

+----------+

1 row in set (0.06 sec)

##完成

使用mysqldump+復(fù)制BINARY LOG備份

我們這里使用的是使用yum安裝的mysql-5.1的版本, 使用的數(shù)據(jù)集為從網(wǎng)絡(luò)上找到的一個(gè)員工數(shù)據(jù)庫(kù)

我們通過(guò)mysqldump進(jìn)行一次完全備份, 再修改表中的數(shù)據(jù), 然后再通過(guò)binary log進(jìn)行恢復(fù)二進(jìn)制日志需要在mysql配置文件中添加 log_bin=on 開啟

mysqldump命令介紹

mysqldump是一個(gè)客戶端的邏輯備份工具, 可以生成一個(gè)重現(xiàn)創(chuàng)建原始數(shù)據(jù)庫(kù)和表的SQL語(yǔ)句, 可以支持所有的存儲(chǔ)引擎, 對(duì)于InnoDB支持熱備

官方文檔介紹

#基本語(yǔ)法格式

shell> mysqldump[options]db_name[tbl_name ...]恢復(fù)需要手動(dòng)CRATE DATABASES

shell> mysqldump[options]--databases db_name ... ? 恢復(fù)不需要手動(dòng)創(chuàng)建數(shù)據(jù)庫(kù)

shell> mysqldump[options]--all-databases ? ? ? ? ? 恢復(fù)不需要手動(dòng)創(chuàng)建數(shù)據(jù)庫(kù)

其他選項(xiàng):

-E,--events: 備份事件調(diào)度器

-R,--routines: 備份存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)

--triggers: 備份表的觸發(fā)器; --skip-triggers

--master-date[=value]

1: 記錄為CHANGE MASTER TO 語(yǔ)句怀喉、語(yǔ)句不被注釋

2: 記錄為注釋的CHANGE MASTER TO語(yǔ)句

基于二進(jìn)制還原只能全庫(kù)還原

--flush-logs: 日志滾動(dòng)

鎖定表完成后執(zhí)行日志滾動(dòng)

查看數(shù)據(jù)庫(kù)的信息

mysql>SHOWDATABASES; ? ?#查看當(dāng)前的數(shù)據(jù)庫(kù), 我們的數(shù)據(jù)庫(kù)為employees

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| employees ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| test ? ? ? ? ? ? ? |

+--------------------+

4 rows in set (0.00 sec)

mysql> USE employees;

Database changed

mysql> SHOW TABLES; ? ? ? ? #查看當(dāng)前庫(kù)中的表

+---------------------+

| Tables_in_employees |

+---------------------+

| departments ? ? ? ? |

| dept_emp ? ? ? ? ? ?|

| dept_manager ? ? ? ?|

| employees ? ? ? ? ? |

| salaries ? ? ? ? ? ?|

| titles ? ? ? ? ? ? ?|

+---------------------+

6 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM employees; ? #由于篇幅原因, 我們這里只看一下employees的行數(shù)為300024

+----------+

| COUNT(*) |

+----------+

| ? 300024 |

+----------+

1 row in set (0.05 sec)

使用mysqldump備份數(shù)據(jù)庫(kù)

[root@node1 ~]# mysql -e 'SHOWMASTERSTATUS' ? #查看當(dāng)前二進(jìn)制文件的狀態(tài), 并記錄下position的數(shù)字

+------------------+----------+--------------+------------------+

| File ? ? ? ? ? ? | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | ? ? ?106 | ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ?|

+------------------+----------+--------------+------------------+

[root@node1 ~]# mysqldump --all-databases --lock-all-tables ?> backup.sql ? #備份數(shù)據(jù)庫(kù)到backup.sql文件中

mysql> CREATE DATABASE TEST1; ? #創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)

Query OK, 1 row affected (0.00 sec)

mysql> SHOW MASTER STATUS; ? #記下現(xiàn)在的position

+------------------+----------+--------------+------------------+

| File ? ? ? ? ? ? | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 | ? ? ?191 | ? ? ? ? ? ? ?| ? ? ? ? ? ? ? ? ?|

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

[root@node1 ~]# cp /var/lib/mysql/mysql-bin.000003 /root ?#備份二進(jìn)制文件

[root@node1 ~]# service mysqld stop ? #停止MySQL

[root@node1 ~]# rm -rf /var/lib/mysql/* ? #刪除所有的數(shù)據(jù)文件

[root@node1 ~]# service mysqld start ? ?#啟動(dòng)MySQL, 如果是編譯安裝的應(yīng)該不能啟動(dòng)(需重新初始化), 如果rpm安裝則會(huì)重新初始化數(shù)據(jù)庫(kù)

mysql> SHOW DATABASES; ? #查看數(shù)據(jù)庫(kù), 數(shù)據(jù)丟失!

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| mysql ? ? ? ? ? ? ?|

| test ? ? ? ? ? ? ? |

+--------------------+

3 rows in set (0.00 sec)

mysql> SET sql_log_bin=OFF; ? #暫時(shí)先將二進(jìn)制日志關(guān)閉 ?

Query OK, 0 rows affected (0.00 sec)

mysql> source backup.sql ?#恢復(fù)數(shù)據(jù)书妻，所需時(shí)間根據(jù)數(shù)據(jù)庫(kù)時(shí)間大小而定

mysql> SET sql_log_bin=ON; 開啟二進(jìn)制日志

mysql> SHOW DATABASES; ? #數(shù)據(jù)庫(kù)恢復(fù), 但是缺少TEST1

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| employees ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| test ? ? ? ? ? ? ? |

+--------------------+

4 rows in set (0.00 sec)

[root@node1 ~]# mysqlbinlog --start-position=106 --stop-position=191 mysql-bin.000003 | mysql employees #通過(guò)二進(jìn)制日志增量恢復(fù)數(shù)據(jù)

mysql> SHOW DATABASES; ? ?#現(xiàn)在TEST1出現(xiàn)了！

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| TEST1 ? ? ? ? ? ? ?|

| employees ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| test ? ? ? ? ? ? ? |

+--------------------+

5 rows in set (0.00 sec)

#完成

使用lvm2快照備份數(shù)據(jù)

做實(shí)驗(yàn)之前我們先回顧一下lvm2-snapshot的知識(shí)

LVM快照簡(jiǎn)單來(lái)說(shuō)就是將所快照源分區(qū)一個(gè)時(shí)間點(diǎn)所有文件的元數(shù)據(jù)進(jìn)行保存躬拢，如果源文件沒(méi)有改變躲履，那么訪問(wèn)快照卷的相應(yīng)文件則直接指向源分區(qū)的源文件见间，如果源文件發(fā)生改變，則快照卷中與之對(duì)應(yīng)的文件不會(huì)發(fā)生改變工猜∶姿撸快照卷主要用于輔助備份文件。 這里只簡(jiǎn)單介紹篷帅，點(diǎn)擊查看詳細(xì)介紹

部署lvm環(huán)境

添加硬盤; 這里我們直接實(shí)現(xiàn)SCSI硬盤的熱插拔, 首先在虛擬機(jī)中添加一塊硬盤, 不重啟

[root@node1 ~]# ls /dev/sd* ? #只有以下幾塊硬盤, 但是我們不重啟可以讓系統(tǒng)識(shí)別新添加的硬盤

/dev/sda ?/dev/sda1 ?/dev/sda2

[root@node1 ~]# echo '- - -' > /sys/class/scsi_host/host0/scan

[root@node1 ~]#echo'---'>/sys/class/scsi_host/host1/scan

[root@node1 ~]#echo'---'>/sys/class/scsi_host/host2/scan

[root@node1 ~]#ls/dev/sd*??#看荒辕！sdb識(shí)別出來(lái)了

/dev/sda?/dev/sda1?/dev/sda2?/dev/sdb

[root@node1 ~]#fdisk/dev/sdb?#分區(qū)

DevicecontainsneitheravalidDOSpartitiontable,norSun,SGIorOSFdisklabel

BuildinganewDOSdisklabelwithdiskidentifier0xd353d192.

Changeswillremaininmemoryonly,untilyoudecidetowritethem.

Afterthat,ofcourse,thepreviouscontentwon'tberecoverable.

Warning: invalid flag0x0000of partition table4will be corrected by w(rite)

WARNING:DOS-compatible mode is deprecated.It'sstrongly recommendedto

switchoff the mode (command'c')and change display unitsto

sectors (command'u').

Command(mforhelp): n

Command action

e ? extended

p ? primary partition (1-4)

p

Partition number (1-4):1

First cylinder (1-2610,default1):

Usingdefaultvalue1

Last cylinder, +cylindersor +size{K,M,G} (1-2610,default2610): +15G

Command (mforhelp): t

Selected partition1

Hex code (typeLtolistcodes):8e

Changed systemtypeofpartition1 to8e(LinuxLVM)

Command(m for help): w

The partition table has been altered!

Callingioctl()to re-read partition table.

Syncingdisks.

Youhavenewmailin /var/spool/mail/root

[root@node1 ~]# partx -a /dev/sdb

BLKPG:Deviceor resource busy

error adding partition1

##創(chuàng)建邏輯卷

[root@node1 ~]# pvcreate /dev/sdb1

Physicalvolume"/dev/sdb1"successfully created

[root@node1 ~]# vgcreate myvg /dev/sdb1

Volumegroup"myvg"successfully created

[root@node1 ~]# lvcreate -n mydata -L5G myvg

Logicalvolume"mydata"created.

[root@node1 ~]# mkfs.ext4 /dev/mapper/myvg-mydata ? #格式化

[root@node1 ~]# mkdir /lvm_data

[root@node1 ~]# mount /dev/mapper/myvg-mydata /lvm_data ?#掛載到/lvm_data

[root@node1 ~]# vim /etc/my.cnf ? ?#修改mysql配置文件的datadir如下

datadir=/lvm_data

[root@node1 ~]# service mysqld restart ?#重啟MySQL

####重新導(dǎo)入employees數(shù)據(jù)庫(kù)########略過(guò)####

查看數(shù)據(jù)庫(kù)的信息

mysql>SHOWDATABASES; ? ?#查看當(dāng)前的數(shù)據(jù)庫(kù), 我們的數(shù)據(jù)庫(kù)為employees

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| employees ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| test ? ? ? ? ? ? ? |

+--------------------+

4 rows in set (0.00 sec)

mysql> USE employees;

Database changed

mysql> SHOW TABLES; ? ? ? ? #查看當(dāng)前庫(kù)中的表

+---------------------+

| Tables_in_employees |

+---------------------+

| departments ? ? ? ? |

| dept_emp ? ? ? ? ? ?|

| dept_manager ? ? ? ?|

| employees ? ? ? ? ? |

| salaries ? ? ? ? ? ?|

| titles ? ? ? ? ? ? ?|

+---------------------+

6 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM employees; ? #由于篇幅原因, 我們這里只看一下employees的行數(shù)為300024

+----------+

| COUNT(*) |

+----------+

| ? 300024 |

+----------+

1 row in set (0.05 sec)

創(chuàng)建快照卷并備份

mysql> FLUSH TABLES WITH READ LOCK;#鎖定所有表

Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data]# lvcreate -L 1G -n mydata-snap -p r -s /dev/mapper/myvg-mydata ? #創(chuàng)建快照卷

?Logical volume "mydata-snap" created.

mysql> UNLOCK TABLES; ?#解鎖所有表

Query OK, 0 rows affected (0.00 sec)

[root@node1 lvm_data]# mkdir /lvm_snap ?#創(chuàng)建文件夾

[root@node1 lvm_data]# mount /dev/myvg/mydata-snap /lvm_snap/ ?#掛載snap

mount: block device /dev/mapper/myvg-mydata--snap is write-protected, mounting read-only

[root@node1 lvm_data]# cd /lvm_snap/

[root@node1 lvm_snap]# ls

employees ?ibdata1 ?ib_logfile0 ?ib_logfile1 ?mysql ?mysql-bin.000001 ?mysql-bin.000002 ?mysql-bin.000003 ?mysql-bin.index ?test

[root@node1 lvm_snap]# tar cf /tmp/mysqlback.tar * ?#打包文件到/tmp/mysqlback.tar

[root@node1 ~]# umount /lvm_snap/ ?#卸載snap

[root@node1 ~]# lvremove myvg mydata-snap ?#刪除snap

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

[root@node1 lvm_snap]# rm -rf /lvm_data/*

[root@node1 ~]# service mysqld start ? ?#啟動(dòng)MySQL, 如果是編譯安裝的應(yīng)該不能啟動(dòng)(需重新初始化), 如果rpm安裝則會(huì)重新初始化數(shù)據(jù)庫(kù)

mysql> SHOW DATABASES; ? #查看數(shù)據(jù)庫(kù), 數(shù)據(jù)丟失!

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| mysql ? ? ? ? ? ? ?|

| test ? ? ? ? ? ? ? |

+--------------------+

3 rows in set (0.00 sec)

[root@node1 ~]# cd /lvm_data/

[root@node1 lvm_data]# rm -rf * #刪除所有文件

[root@node1 lvm_data]# tar xf /tmp/mysqlback.tar ? ? #解壓備份數(shù)據(jù)庫(kù)到此文件夾

[root@node1 lvm_data]# ls ?#查看當(dāng)前的文件

employees ?ibdata1 ?ib_logfile0 ?ib_logfile1 ?mysql ?mysql-bin.000001 ?mysql-bin.000002 ?mysql-bin.000003 ?mysql-bin.index ?test

mysql> SHOW DATABASES; ?#數(shù)據(jù)恢復(fù)了

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| employees ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| test ? ? ? ? ? ? ? |

+--------------------+

4 rows in set (0.00 sec)

##完成

使用Xtrabackup備份

為了更好地演示, 我們這次使用mariadb-5.5的版本, 使用xtrabackup使用InnoDB能夠發(fā)揮其最大功效, 并且InnoDB的每一張表必須使用單獨(dú)的表空間, 我們需要在配置文件中添加innodb_file_per_table = ON來(lái)開啟

下載安裝xtrabackup

我們這里通過(guò)wget percona官方的rpm包進(jìn)行安裝

[root@node1 ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.3.4/binary/redhat/6/x86_64/percona-xtrabackup-2.3.4-1.el6.x86_64.rpm ?

[root@node1 ~]# yum localinstall percona-xtrabackup-2.3.4-1.el6.x86_64.rpm ? #需要EPEL源

xtrabackup介紹

Xtrabackup是由percona提供的mysql數(shù)據(jù)庫(kù)備份工具，據(jù)官方介紹犹褒，這也是世界上惟一一款開源的能夠?qū)nnodb和xtradb數(shù)據(jù)庫(kù)進(jìn)行熱備的工具。特點(diǎn)：

備份過(guò)程快速弛针、可靠叠骑；

備份過(guò)程不會(huì)打斷正在執(zhí)行的事務(wù)；

能夠基于壓縮等功能節(jié)約磁盤空間和流量削茁；

自動(dòng)實(shí)現(xiàn)備份檢驗(yàn)宙枷；

還原速度快；

摘自馬哥的文檔

xtrabackup實(shí)現(xiàn)完全備份

我們這里使用xtrabackup的前端配置工具innobackupex來(lái)實(shí)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的完全備份

使用innobackupex備份時(shí), 會(huì)調(diào)用xtrabackup備份所有的InnoDB表, 復(fù)制所有關(guān)于表結(jié)構(gòu)定義的相關(guān)文件(.frm)茧跋、以及MyISAM慰丛、MERGE、CSV和ARCHIVE表的相關(guān)文件, 同時(shí)還會(huì)備份觸發(fā)器和數(shù)據(jù)庫(kù)配置文件信息相關(guān)的文件, 這些文件會(huì)被保存至一個(gè)以時(shí)間命名的目錄.

備份過(guò)程

[root@node1 ~]# mkdir /extrabackup ?#創(chuàng)建備份目錄

[root@node1 ~]# innobackupex --user=root /extrabackup/ #備份數(shù)據(jù)

###################提示complete表示成功*********************

[root@node1 ~]# ls /extrabackup/ ?#看到備份目錄

2016-04-27_07-30-48

一般情況, 備份完成后, 數(shù)據(jù)不能用于恢復(fù)操作, 因?yàn)閭浞莸臄?shù)據(jù)中可能會(huì)包含尚未提交的事務(wù)或已經(jīng)提交但尚未同步至數(shù)據(jù)文件中的事務(wù)瘾杭。因此, 此時(shí)的數(shù)據(jù)文件仍不一致, 所以我們需要”準(zhǔn)備”一個(gè)完全備份

[root@node1 ~]# innobackupex --apply-log /extrabackup/2016-04-27_07-30-48/ ?#指定備份文件的目錄

#一般情況下下面三行結(jié)尾代表成功*****************

InnoDB: Starting shutdown...

InnoDB: Shutdown completed; log sequence number 369661462

160427 07:40:11 completed OK!

[root@node1 ~]# cd /extrabackup/2016-04-27_07-30-48/

[root@node1 2016-04-27_07-30-48]# ls -hl ?#查看備份文件

total 31M

-rw-r----- 1 root root ?386 Apr 27 07:30 backup-my.cnf

drwx------ 2 root root 4.0K Apr 27 07:30 employees

-rw-r----- 1 root root ?18M Apr 27 07:40 ibdata1

-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile0

-rw-r--r-- 1 root root 5.0M Apr 27 07:40 ib_logfile1

drwx------ 2 root root 4.0K Apr 27 07:30 mysql

drwx------ 2 root root 4.0K Apr 27 07:30 performance_schema

drwx------ 2 root root 4.0K Apr 27 07:30 test

-rw-r----- 1 root root ? 27 Apr 27 07:30 xtrabackup_binlog_info

-rw-r--r-- 1 root root ? 29 Apr 27 07:40 xtrabackup_binlog_pos_innodb

-rw-r----- 1 root root ?117 Apr 27 07:40 xtrabackup_checkpoints

-rw-r----- 1 root root ?470 Apr 27 07:30 xtrabackup_info

-rw-r----- 1 root root 2.0M Apr 27 07:40 xtrabackup_logfile

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

[root@node1 ~]# rm -rf /data/* ? #刪除數(shù)據(jù)文件

***不用啟動(dòng)數(shù)據(jù)庫(kù)也可以還原*************

[root@node1 ~]# innobackupex --copy-back /extrabackup/2016-04-27_07-30-48/ ? #恢復(fù)數(shù)據(jù), 記清使用方法

#########我們這里是編譯安裝的mariadb所以需要做一些操作##########

[root@node1 data]# killall mysqld

[root@node1 ~]# chown -R mysql:mysql ./*

[root@node1 ~]# ll /data/ ? ? ?#數(shù)據(jù)恢復(fù)

total28704

-rw-rw----1mysql mysql16384Apr2707:43aria_log.00000001

-rw-rw----1mysql mysql52Apr2707:43aria_log_control

-rw-rw----1mysql mysql18874368Apr2707:43ibdata1

-rw-rw----1mysql mysql5242880Apr2707:43ib_logfile0

-rw-rw----1mysql mysql5242880Apr2707:43ib_logfile1

-rw-rw----1mysql mysql264Apr2707:43mysql-bin.000001

-rw-rw----1mysql mysql19Apr2707:43mysql-bin.index

-rw-r-----1mysql mysql2166Apr2707:43node1.anyisalin.com.err

[root@node1 data]# service mysqld restart

MySQL server PID file could not be found![FAILED]

Starting MySQL..[ ?OK ?]

MariaDB[(none)]> SHOW DATABASES;#查看數(shù)據(jù)庫(kù), 已經(jīng)恢復(fù)

+--------------------+

|Database ? ? ? ? ? |

+--------------------+

|information_schema |

|employees ? ? ? ? ?|

|mysql ? ? ? ? ? ? ?|

|performance_schema |

|test ? ? ? ? ? ? ? |

+--------------------+

5rows in set (0.00sec

增量備份

#########創(chuàng)建連兩個(gè)數(shù)據(jù)庫(kù)以供測(cè)試#####################

MariaDB [(none)]> CREATE DATABASE TEST1;

Query OK,1row affected (0.00sec)

MariaDB [(none)]> CREATE DATABASE TEST2;

Query OK,1row affected (0.00sec)

[root@node1 ~]# innobackupex --incremental /extrabackup/ --incremental-basedir=/extrabackup/2016-04-27_07-30-48/

[root@node1 ~]# ls /extrabackup/2016-04-27_07-57-22/ #查看備份文件

total 96

-rw-r----- 1 root root ? 386 Apr 27 07:57 backup-my.cnf

drwx------ 2 root root ?4096 Apr 27 07:57 employees

-rw-r----- 1 root root 49152 Apr 27 07:57 ibdata1.delta

-rw-r----- 1 root root ? ?44 Apr 27 07:57 ibdata1.meta

drwx------ 2 root root ?4096 Apr 27 07:57 mysql

drwx------ 2 root root ?4096 Apr 27 07:57 performance_schema

drwx------ 2 root root ?4096 Apr 27 07:57 test

drwx------ 2 root root ?4096 Apr 27 07:57 TEST1

drwx------ 2 root root ?4096 Apr 27 07:57 TEST2

-rw-r----- 1 root root ? ?21 Apr 27 07:57 xtrabackup_binlog_info

-rw-r----- 1 root root ? 123 Apr 27 07:57 xtrabackup_checkpoints

-rw-r----- 1 root root ? 530 Apr 27 07:57 xtrabackup_info

-rw-r----- 1 root root ?2560 Apr 27 07:57 xtrabackup_logfile

BASEDIR指的是完全備份所在的目錄诅病，此命令執(zhí)行結(jié)束后，innobackupex命令會(huì)在/extrabackup目錄中創(chuàng)建一個(gè)新的以時(shí)間命名的目錄以存放所有的增量備份數(shù)據(jù)粥烁。另外贤笆，在執(zhí)行過(guò)增量備份之后再一次進(jìn)行增量備份時(shí)，其--incremental-basedir應(yīng)該指向上一次的增量備份所在的目錄讨阻。

需要注意的是芥永，增量備份僅能應(yīng)用于InnoDB或XtraDB表，對(duì)于MyISAM表而言钝吮，執(zhí)行增量備份時(shí)其實(shí)進(jìn)行的是完全備份埋涧。

整理增量備份

[root@node1 ~]# innobackupex --apply-log --redo-only /extrabackup/2016-04-27_07-30-48/

[root@node1 ~]# innobackupex --apply-log --redo-only /extrabackup/2016-04-27_07-30-48/ --incremental-dir=/extrabackup/2016-04-27_07-5

7-22/

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

[root@node1 ~]# rm -rf /data/* ? #刪除數(shù)據(jù)

[root@node1 ~]# innobackupex --copy-back /extrabackup/2016-04-27_07-30-48/ ? ? #整理增量備份之后可以直接通過(guò)全量備份還原

[root@node1 ~]# chown -R mysql.mysql /data/

[root@node1 ~]# ls /data/ -l

total 28732

-rw-rw---- 1 mysql mysql ? ? 8192 Apr 27 08:05 aria_log.00000001

-rw-rw---- 1 mysql mysql ? ? ? 52 Apr 27 08:05 aria_log_control

drwx------ 2 mysql mysql ? ? 4096 Apr 27 08:05 employees

-rw-r----- 1 mysql mysql 18874368 Apr 27 08:05 ibdata1

-rw-r----- 1 mysql mysql ?5242880 Apr 27 08:05 ib_logfile0

-rw-r----- 1 mysql mysql ?5242880 Apr 27 08:05 ib_logfile1

drwx------ 2 mysql mysql ? ? 4096 Apr 27 08:05 mysql

-rw-rw---- 1 mysql mysql ? ? ?245 Apr 27 08:05 mysql-bin.000001

-rw-rw---- 1 mysql mysql ? ? ? 19 Apr 27 08:05 mysql-bin.index

-rw-r----- 1 mysql mysql ? ? 1812 Apr 27 08:05 node1.anyisalin.com.err

-rw-rw---- 1 mysql mysql ? ? ? ?5 Apr 27 08:05 node1.anyisalin.com.pid

drwx------ 2 mysql mysql ? ? 4096 Apr 27 08:05 performance_schema

drwx------ 2 mysql mysql ? ? 4096 Apr 27 08:05 test

drwx------ 2 mysql mysql ? ? 4096 Apr 27 08:05 TEST1

drwx------ 2 mysql mysql ? ? 4096 Apr 27 08:05 TEST2

-rw-r----- 1 mysql mysql ? ? ? 29 Apr 27 08:05 xtrabackup_binlog_pos_innodb

-rw-r----- 1 mysql mysql ? ? ?530 Apr 27 08:05 xtrabackup_info

MariaDB [(none)]> SHOW DATABASES; ?#數(shù)據(jù)還原

+--------------------+

| Database ? ? ? ? ? |

+--------------------+

| information_schema |

| TEST1 ? ? ? ? ? ? ?|

| TEST2 ? ? ? ? ? ? ?|

| employees ? ? ? ? ?|

| mysql ? ? ? ? ? ? ?|

| performance_schema |

| test ? ? ? ? ? ? ? |

+--------------------+

7 rows in set (0.00 sec)

#關(guān)于xtrabackup還有很多強(qiáng)大的功能沒(méi)有敘述、有興趣可以去看官方文檔

總結(jié)

備份方法備份速度恢復(fù)速度便捷性功能一般用于

cp快快一般奇瘦、靈活性低很弱少量數(shù)據(jù)備份

mysqldump慢慢一般棘催、可無(wú)視存儲(chǔ)引擎的差異一般中小型數(shù)據(jù)量的備份

lvm2快照快快一般、支持幾乎熱備耳标、速度快一般中小型數(shù)據(jù)量的備份

xtrabackup較快較快實(shí)現(xiàn)innodb熱備巧鸭、對(duì)存儲(chǔ)引擎有要求強(qiáng)大較大規(guī)模的備份