mysql中mysqldump備份恢復的示例分析

123次閱讀

共計 4906 個字符，預計需要花費 13 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 mysql 中 mysqldump 備份恢復的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

mysqldump 程序的實現(xiàn)原理是通過我們給的參數(shù)信息加上數(shù)據(jù)庫中的系統(tǒng)表信息來一個表一個表獲取數(shù)據(jù)然后生成 INSERT 語句再寫入備份文件中的。這樣就出現(xiàn)了一個問題，在系統(tǒng)正常運行過程中，很可能會不斷有數(shù)據(jù)變更的請求正在執(zhí)行，這樣就可能造成在 mysqldump 備份出來的數(shù)據(jù)不一致。也就是說備份數(shù)據(jù)很可能不是同一個時間點的數(shù)據(jù)，而且甚至可能都沒辦法滿足完整性約束。這樣的備份集對于有些系統(tǒng)來說可能并沒有太大問題，但是對于有些對數(shù)據(jù)的一致性和完整性要求比較嚴格系統(tǒng)來說問題就大了，就是一個完全無效的備份集。
對于如此場景，我們該如何做？我們知道，想數(shù)據(jù)庫中的數(shù)據(jù)一致，那么只有兩種情況下可以做到。
第一、同一時刻取出所有數(shù)據(jù)；
第二、數(shù)據(jù)庫中的數(shù)據(jù)處于靜止狀態(tài)。
對于第一種情況，大家肯定會想，這可能嗎？不管如何，只要有兩個以上的表，就算我們?nèi)绾螌懗绦?，都不可能昨晚完全一致的取?shù)時間點啊。是的，我們確實無法通過常規(guī)方法讓取數(shù)的時間點完全一致，但是大家不要忘記，在同一個事務(wù)中，數(shù)據(jù)庫是可以做到所讀取的數(shù)據(jù)是處于同一個時間點的。所以，對于事務(wù)支持的存儲引擎，如 Innodb 或者 BDB 等，我們就可以通過控制將整個備份過程控制在同一個事務(wù)中，來達到備份數(shù)據(jù)的一致性和完整性，而且 mysqldump 程序也給我們提供了相關(guān)的參數(shù)選項來支持該功能，就是通過“–single-transaction”選項，可以不影響數(shù)據(jù)庫的任何正常服務(wù)。原理是通過快照實現(xiàn)的。
補充：
single-transaction 可以讓 mysqldump 的時候不鎖表。但是他有 3 個前提：
a、innodb 的引擎
b、不能在執(zhí)行的同時，有其他 alter table ,drop table,rename table,truncate table 的操作。
c、隔離級別必須是 REPEATABLE READ，很多公司都會修改這個隔離級別的，比如阿里云的 rds，默認隔離級別是 READ-COMMITTED
–single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
–single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off –lock-tables.

對于第二種情況我想大家首先想到的肯定是將需要備份的表鎖定，只允許讀取而不允許寫入。是的，我們確實只能這么做。我們只能通過一個折衷的處理方式，讓數(shù)據(jù)庫在備份過程中僅提供數(shù)據(jù)的查詢服務(wù)，鎖定寫入的服務(wù)，來使數(shù)據(jù)暫時處于一個一致的不會被修改的狀態(tài)，等 mysqldump 完成備份后再取消寫入鎖定，重新開始提供完整的服務(wù)。mysqldump 程序自己也提供了相關(guān)選項如“–lock-tables”和“–lock-all-tables”，在執(zhí)行之前會鎖定表，執(zhí)行結(jié)束后自動釋放鎖定。這里有一點需要注意的就是，“–lock-tables”一次性將需要 dump 的所有表鎖定，如果你需要 dump 的表分別在多個不同的數(shù)據(jù)庫中，一定要使用“–lock-all-tables”才能確保數(shù)據(jù)的一致完整性。

mysqldump 是 MySQL 用于轉(zhuǎn)存儲數(shù)據(jù)庫的客戶端程序。轉(zhuǎn)儲包含創(chuàng)建表和 / 或裝載表的 SQL 語句，用來實現(xiàn)輕量級的快速遷移或恢復數(shù)據(jù)庫，是 mysql 數(shù)據(jù)庫實現(xiàn)邏輯備份的一種方式。mysqldump 不適用于大型數(shù)據(jù)庫備份與恢復，速度慢，不支持并行，其次 SQL 重放將耗用大量的 I /O。

1、查看詳細 mysqldump 幫助信息
[root@mysql ~]# mysqldump –help
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]
OR mysqldump [OPTIONS] –all-databases [OPTIONS]
2、mysqldump 中主要參數(shù)介紹
2.1
–opt Same as –add-drop-table, –add-locks, –create-options,
–quick, –extended-insert, –lock-tables, –set-charset,
and –disable-keys. Enabled by default, disable with –skip-opt.
-q, –quick Don t buffer query, dump directly to stdout.
(Defaults to on; use –skip-quick to disable.)
說明：缺省情況下以上 2 個參數(shù)為開啟狀態(tài)，如果 2 個參數(shù)未使用的情況下，在轉(zhuǎn)儲結(jié)果之前會把全部內(nèi)容載入到內(nèi)存中，對于較大的數(shù)據(jù)庫轉(zhuǎn)儲將嚴重影響性能。
2.2
–default-character-set=name Set the default character set.
說明：設(shè)置導出腳本的字符集，未指定的情況下為 UTF8。
2.3
-d, –no-data No row information.
說明：不輸出數(shù)據(jù)行，僅導出結(jié)構(gòu)
-t, –no-create-info Don t write table creation info.
說明：只導出表數(shù)據(jù)，不導出表結(jié)構(gòu)
2.4
–triggers Dump triggers for each dumped table.(Defaults to on; use –skip-triggers to disable.)
說明：觸發(fā)器默認導出
-R, –routines Dump stored routines (functions and procedures).
說明：存儲過程與函數(shù)默認不導出
2.5
–single-transaction
說明：創(chuàng)建一致性快照，僅僅針對 innodb 引擎
-f, –force Continue even if we get an SQL error.
說明：有錯誤時，依舊強制 dump
2.6
–add-drop-table Add a DROP TABLE before each create.
(Defaults to on; use –skip-add-drop-table to disable.)
說明：在導入恢復的時候，創(chuàng)建表之前，先執(zhí)行刪除表操作。

3、范例

注意（特別注意這兩點，防止誤操作，丟失數(shù)據(jù)）：
–database 會生成建庫語句默認是關(guān)閉的，如：CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */; 意思是如果 mysqldump 的數(shù)據(jù)庫不存在就導入恢復時自行創(chuàng)建，存在就不創(chuàng)建。
–add-drop-table 默認是開啟的，在導入恢復時，如果該表存在，會先刪除再創(chuàng)建如：DROP TABLE IF EXISTS `test`;
禁用是參數(shù) –skip-add-drop-table。

3.1
備份服務(wù)器上的所有數(shù)據(jù)庫
shell mysqldump -uXX -pXX –all-databases –opt –compact –flush-privileges –routines alldb_$(date +%F).sql
恢復所有數(shù)據(jù)庫
shell mysql -uXX -pXX alldb_$(date +%F).sql

同時備份多個數(shù)據(jù)庫
shell mysqldump -uXX -pXX –database db_1 db_2 –routines multidb_$(date +%F).sql
同時恢復多個數(shù)據(jù)庫
shell mysql -uroot -poracle multidb_$(date +%F).sql

備份單個數(shù)據(jù)庫
shell mysqldump -uXX -pXX db_name –opt –routines db_name_$(date +%F).sql
恢復單個數(shù)據(jù)庫
shell mysql -uXX -pXX db_name db_name_$(date +%F).sql

3.2
備份數(shù)據(jù)庫的結(jié)構(gòu)，不備份數(shù)據(jù)
shell mysqldump -uXX -pXX db_name –no-data –routines db_name_onlystructure_$(date +%F).sql

備份數(shù)據(jù)庫的數(shù)據(jù)，不備份結(jié)構(gòu)
shell mysqldump -uXX -pXX –opt db_name –no-create-info db_name_onlydata_$(date +%F).sql
3.3
備份數(shù)據(jù)庫上的特定表
shell mysqldump -uXX -pXX –opt db_name t_name db.t_name_$(date +%F).sql
恢復數(shù)據(jù)庫上的特定表
shell mysql -uXX -pXX dbname db_name_$(date +%F)_$(date +%F).sql

備份指定數(shù)據(jù)庫上的多個指定表
shell mysqldump -uroot -poracle –database test test02 –routines multi_t_$(date +%F).sql
恢復指定數(shù)據(jù)庫上的多個指定表
mysql -uroot -poracle /tmp/multi_t_$(date +%F).sql

備份表上特定的記錄
shell mysqldump -uXX -pXX db_name t_name -w first_name= NICK db.t_name_row_$(date +%F).sql
3.4
只導出數(shù)據(jù)庫中的存儲過程，函數(shù)，觸發(fā)器
shell mysqldump -uXX -pXX db_name –no-create-db –no-data –no-tablespaces –no-create-info –routines db_name_$(date +%F).sql

以上是“mysql 中 mysqldump 備份恢復的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學習更多知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！

正文完