本文和大家分享的主要是mysql
中 mysqldump
備份恢復(fù)相關(guān)內(nèi)容,一起來看看吧,希望對大家
學(xué)習(xí)mysql有所幫助。
mysqldump
程序的實(shí)現(xiàn)原理是通過我們給的參數(shù)信息加上數(shù)據(jù)庫中的系統(tǒng)表信息來一個表一個表獲取數(shù)據(jù)然后生成
INSERT
語句再寫入備份文件中的。這樣就出現(xiàn)了一個問題,在系統(tǒng)正常運(yùn)行過程中,很可能會不斷有數(shù)據(jù)變更的請求正在執(zhí)行,這樣就可能造成在
mysqldump
備份出來的數(shù)據(jù)不一致。也就是說備份數(shù)據(jù)很可能不是同一個時間點(diǎn)的數(shù)據(jù),而且甚至可能都沒辦法滿足完整性約束。這樣的備份集對于有些系統(tǒng)來說可能并沒有太大問題,但是對于有些對數(shù)據(jù)的一致性和完整性要求比較嚴(yán)格系統(tǒng)來說問題就大了,就是一個完全無效的備份集。
對于如此場景,我們該如何做?我們知道,想數(shù)據(jù)庫中的數(shù)據(jù)一致,那么只有兩種情況下可以做到。
第一、同一時刻取出所有數(shù)據(jù);
第二、數(shù)據(jù)庫中的數(shù)據(jù)處于靜止?fàn)顟B(tài)。
對于第一種情況,大家肯定會想,這可能嗎?不管如何,只要有兩個以上的表,就算我們?nèi)绾螌懗绦?,都不可能昨晚完全一致的取?shù)時間點(diǎn)啊。是的,我們確實(shí)無法通過常規(guī)方法讓取數(shù)的時間點(diǎn)完全一致,但是大家不要忘記,在同一個事務(wù)中,數(shù)據(jù)庫是可以做到所讀取的數(shù)據(jù)是處于同一個時間點(diǎn)的。所以,對于事務(wù)支持的存儲引擎,如 Innodb
或者
BDB
等 ,我們就可以通過控制將整個備份過程控制在同一個事務(wù)中,來達(dá)到備份數(shù)據(jù)的一致性和完整性,而且
mysqldump
程序也給我們提供了相關(guān)的參數(shù)選項來支持該功能,就是通過
“--single-transaction”
選項,可以不影響數(shù)據(jù)庫的任何正常服務(wù)。原理是通過快照實(shí)現(xiàn)的。
補(bǔ)充:
single-transaction
可以讓
mysqldump
的時候不鎖表。但是他有
3
個前提:
a
、
innodb
的引擎
b
、不能在執(zhí)行的同時,有其他
alter table ,drop table,rename table,truncate table
的操作。
c
、隔離級別 必須是
REPEATABLE READ
,很多公司都會修改這個隔離級別的,比如阿里云的
rds
,默認(rèn)隔離級別是
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í)只能這么做。我們只能通過一個折衷的處理方式,讓數(shù)據(jù)庫在備份過程中僅提供數(shù)據(jù)的查詢服務(wù),鎖定寫入的服務(wù),來使數(shù)據(jù)暫時處于一個一致的不會被修改的狀態(tài),等mysqldump
完成備份后再取消寫入鎖定,重新開始提供完整的服務(wù)。
mysqldump
程序自己也提供了相關(guān)選項如
“--lock-tables”
和
“--lock-all-tables ”
,在執(zhí)行之前會鎖定表,執(zhí)行結(jié)束后自動釋放鎖定。這里有一點(diǎn)需要注意的就是,
“--lock-tables ”
并不是一次性將需要
dump
的所有表鎖定,而是每次僅僅鎖定一個數(shù)據(jù)庫的表,如果你需要
dump
的表分別在多個不同的數(shù)據(jù)庫中,一定要使用
“--lock-all-tables”
才能確保數(shù)據(jù)的一致完整性。
mysqldump
是
MySQL
用于轉(zhuǎn)存儲數(shù)據(jù)庫的客戶端程序。轉(zhuǎn)儲包含創(chuàng)建表和
/
或裝載表的
SQL
語句 ,用來實(shí)現(xiàn)輕量級的快速遷移或恢復(fù)數(shù)據(jù)庫,是
mysql
數(shù)據(jù)庫實(shí)現(xiàn)邏輯備份的一種方式。
mysqldump
不適用于大型數(shù)據(jù)庫備份與恢復(fù),速度慢,不支持并行,其次
SQL
重放將耗用大量的
I/O
。
1
、查看詳細(xì)
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)儲將嚴(yán)重影響性能。
2.2
--default-character-set=name Set the default character set.
說明:設(shè)置導(dǎo)出腳本的字符集,未指定的情況下為UTF8
。
2.3
-d, --no-data No row information.
說明:不輸出數(shù)據(jù)行,僅導(dǎo)出結(jié)構(gòu)
-t, --no-create-info Don't write table creation info.
說明:只導(dǎo)出表數(shù)據(jù),不導(dǎo)出表結(jié)構(gòu)
2.4
--triggers Dump triggers for each dumped table.(Defaults to on; use --skip-triggers to disable.)
說明:觸發(fā)器默認(rèn)導(dǎo)出
-R, --routines Dump stored routines (functions and procedures).
說明:存儲過程與函數(shù)默認(rèn)不導(dǎo)出
2.5
--single-transaction
說明:創(chuàng)建一致性快照,僅僅針對innodb
引擎
-f, --force Continue even if we get an SQL error.
說明:有錯誤時,依舊強(qiáng)制dump
2.6
--add-drop-table Add a DROP TABLE before each create.
(Defaults to on; use --skip-add-drop-table to disable.)
說明:在導(dǎo)入恢復(fù)的時候,創(chuàng)建表之前,先執(zhí)行刪除表操作。
3
、范例
注意(特別注意這兩點(diǎn),防止誤操作,丟失數(shù)據(jù)):
--database
會生成建庫語句 默認(rèn)是關(guān)閉的,如:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET utf8 */;
意思是如果
mysqldump
的數(shù)據(jù)庫不存在就導(dǎo)入恢復(fù)時自行創(chuàng)建,存在就不創(chuàng)建。
--add-drop-table
默認(rèn)是開啟的,在導(dǎo)入恢復(fù)時,如果該表存在,會先刪除再創(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
恢復(fù)所有數(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
同時恢復(fù)多個數(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
恢復(fù)單個數(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
恢復(fù)數(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
恢復(fù)指定數(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
只導(dǎo)出數(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
來源:
博客園