詳解mysql的備份與恢復(fù)

前言：

前面幾篇文章為大家介紹了 MySQL 各種語句語法的用法及用戶權(quán)限相關(guān)知識(shí)。本篇文章將主要講解 MySQL 數(shù)據(jù)庫數(shù)據(jù)備份與恢復(fù)相關(guān)知識(shí)，主要聚焦于邏輯備份，介紹mysqldump工具的使用以及恢復(fù)方法。

這里簡(jiǎn)單講下物理備份和邏輯備份的概念：
物理備份：備份數(shù)據(jù)文件，轉(zhuǎn)儲(chǔ)數(shù)據(jù)庫物理文件到某一目錄。物理備份恢復(fù)速度比較快，但占用空間比較大，MySQL中可以用 xtrabackup 工具來進(jìn)行物理備份。
邏輯備份：對(duì)數(shù)據(jù)庫對(duì)象利用工具進(jìn)行導(dǎo)出工作，匯總?cè)雮浞菸募?nèi)。邏輯備份恢復(fù)速度慢，但占用空間小，更靈活。MySQL 中常用的邏輯備份工具為mysqldump。

1.備份全部數(shù)據(jù)庫

若想用mysqldump備份整個(gè)實(shí)例，可以使用 --all-databases 或 -A 參數(shù)：

mysqldump -uroot -pxxxxxx --all-databases > /tmp/all_database.sqlmysqldump -uroot -pxxxxxx -A > /tmp/all_database.sql

2.備份部分?jǐn)?shù)據(jù)庫

有的時(shí)候我們會(huì)遇到只需要備份某些庫的需求，這個(gè)時(shí)候我們就可以使用 --databases 或 -B 參數(shù)了，該參數(shù)后面跟數(shù)據(jù)庫名稱，多個(gè)數(shù)據(jù)庫間用空格隔開。

mysqldump -uroot -pxxxxxx --databases testdb1 testdb2 > /tmp/testdb.sqlmysqldump -uroot -pxxxxxx -B testdb1 testdb2 > /tmp/testdb.sql

3.備份部分表

平時(shí)我們也會(huì)有備份部分表的需求，比如說在表變更前做個(gè)備份，那么我們可以這樣做：

只備份testdb庫中的test_tb表

mysqldump -uroot -pxxxxxx testdb test_tb > /tmp/test_tb.sql#備份多張表mysqldump -uroot -pxxxxxx testdb tb1 tb2 tb3 > /tmp/tb.sql

4.備份單表的部分?jǐn)?shù)據(jù)

有些時(shí)候一張表的數(shù)據(jù)量很大，我們只需要部分?jǐn)?shù)據(jù)，那么該怎么辦呢？這時(shí)候就可以使用 --where 選項(xiàng)了。where后面附帶需要滿足的條件。例如：我們只需要tb1表中create_time大于2019-08-01的數(shù)據(jù)，那么可以這樣導(dǎo)出：

mysqldump -uroot -pxxxxxx testdb tb1 --where=" create_time >= '2019-08-01 00:00:00' " > /tmp/tb1.sql

5.排除某些表導(dǎo)出

如果我們想備份某個(gè)庫，但是某些表數(shù)據(jù)量很大或者與業(yè)務(wù)關(guān)聯(lián)不大，這個(gè)時(shí)候可以考慮排除掉這些表，同樣的，選項(xiàng) --ignore-table 可以完成這個(gè)功能。

mysqldump -uroot -pxxxxxx testdb --ignore-table=testdb.tb1 > /tmp/testdb.sql

6.只備份結(jié)構(gòu)或只備份數(shù)據(jù)

只備份結(jié)構(gòu)的話可以使用 --no-data 簡(jiǎn)寫為 -d 選項(xiàng)；只備份數(shù)據(jù)可以使用 --no-create-info 簡(jiǎn)寫為 -t 選項(xiàng)。

mysqldump -uroot -pxxxxxx testdb --no-data > /tmp/testdb_jiegou.sqlmysqldump -uroot -pxxxxxx testdb --no-create-info > /tmp/testdb_data.sql

7.備份中包含存儲(chǔ)過程函數(shù)，事件

mysqldump備份默認(rèn)是不包含存儲(chǔ)過程，自定義函數(shù)及事件的。我們可以使用 --routines 或 -R 選項(xiàng)來備份存儲(chǔ)過程及函數(shù)，使用 --events 或 -E 參數(shù)來備份事件。例如：我們想備份整個(gè)testdb庫，包含存儲(chǔ)過程及事件：

mysqldump -uroot -pxxxxxx -R -E --databases testdb > /tmp/testdb.sql

8.以事務(wù)的形式備份

如果我們想在dump過程中保證數(shù)據(jù)的一致性，減少鎖表，則可以用 --single-transaction 選項(xiàng)，這個(gè)選項(xiàng)對(duì)InnoDB的數(shù)據(jù)表很有用，且不會(huì)鎖表。

mysqldump -uroot -pxxxxxx --single-transaction --databases testdb > /tmp/testdb.sql

9.全量備份恢復(fù)

如果我們現(xiàn)在有昨天的全量備份，現(xiàn)在想整個(gè)恢復(fù)，則可以這樣操作：

mysql -uroot -pxxxxxx  /tmp/all_database.sql

10.從全量備份中恢復(fù)單庫

可能有這樣的需求，比如說我們只想恢復(fù)某一個(gè)庫，但是我們有的是整個(gè)實(shí)例的備份，這個(gè)時(shí)候我們想到能不能從全量備份中分離出單個(gè)庫的備份，答案是可以的，下面這個(gè)簡(jiǎn)單的shell可以幫到你哦：

sed -n '/^-- Current Database: `testdb`/,/^-- Current Database: `/p' all_databases.sql > testdb.sql

分離完成后我們?cè)賹?dǎo)入testdb.sql即可恢復(fù)單個(gè)庫

11.從單庫備份中恢復(fù)單表

這個(gè)需求還是比較常見的，畢竟單庫或全量恢復(fù)涉及的業(yè)務(wù)還是比較多的，恢復(fù)時(shí)間也比較長(zhǎng)，比如說我們知道哪個(gè)表誤操作了，那么我們就可以用單表恢復(fù)的方式來恢復(fù)。例如：現(xiàn)在我們有testdb整庫的備份，但是由于tb1表誤操作，需要單獨(dú)恢復(fù)出這張表，那么我們可以這么做

cat testdb.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `tb1`/!d;q' > /tmp/tb1_jiegou.sqlcat testdb.sql | grep --ignore-case 'insert into `tb1`' > /tmp/tb1_data.sql

用shell語法分離出創(chuàng)建表的語句及插入數(shù)據(jù)的語句后 再依次導(dǎo)出即可完成恢復(fù)

總結(jié)：

本篇文章給出了在不同場(chǎng)景下的備份及恢復(fù)方法，可能生產(chǎn)中還會(huì)有更復(fù)雜的場(chǎng)景，需要大家靈活應(yīng)變。

以上就是詳解mysql的備份與恢復(fù)的詳細(xì)內(nèi)容，更多關(guān)于MySQL備份與恢復(fù)的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！