使用mysqldump匯出資料庫
mysqldump是mysql用於轉儲存資料庫的用戶端程式。它主要產生一系列的SQL語句,可以封裝到檔案,該檔案包含有所有重建您的資料庫所需要的SQL命令如CREATE DATABASE,CREATE TABLE,INSERT等等。可以用來實現輕量級的快速遷移或恢複資料庫。是mysql資料庫實現邏輯備份的一種方式。本文描述了mysqldump的一些重要參數以及給出了相關樣本供大家參考。
Linux下通過mysqldump備份MySQL資料庫成sql檔案
Linux中使用mysqldump對MySQL資料庫進行定時備份
mysqldump缺失-q參數導致MySQL被oom幹掉
mysqldump和LVM邏輯卷快照
MySQL備份方案-->(利用mysqldump以及binlog二進位日誌)
[MySQL] 用mysqldump製作文本備份
1、擷取mysqldump的協助資訊
[root@SZDB ~]# mysqldump --help|more
mysqldump Ver 10.13 Distrib 5.6.12, for Linux (x86_64)
#以下為MySQL dump調用的幾種常用方式
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]
#如果沒有指定任何錶或使用了---database或--all--database選項,則轉儲整個資料庫
--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個參數未使用的情況下,在轉儲結果之前會把全部內容載入到記憶體中,對於較大的資料庫轉儲將嚴重影響效能。
#預設情況下這2個參數為開啟狀態。有些類似於Oracle的繞過PGA而直接寫direct write。
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
#skip-opt與前2個參數相反,在轉儲之前先load到記憶體中。
--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. Legal modes are: ansi, mysql323, mysql40,
postgresql, oracle, mssql, db2, maxdb, no_key_options,
no_table_options, no_field_options. One can use several
modes separated by commas. Note: Requires MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.
#產生與其它資料庫系統或舊版本MySQL伺服器相相容的輸出。用於跨資料庫,跨版本之間的遷移。
#其值可以為ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options或者no_field_options。
#如果要使用多個值,用逗號將它們隔開。該選項不能保證同其它資料庫伺服器之間的完全相容。如Oracle的資料類型等。
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --skip-add-locks
--skip-comments --skip-disable-keys --skip-set-charset.
#該選項使得輸出的檔案更小,啟用後等用於使用一些skip項等。
-B, --databases Dump several databases. Note the difference in usage; in
this case no tables are given. All name arguments are
regarded as database names. 'USE db_name;' will be
included in the output.
#該選項一次匯出多個資料庫所有名字參量看作資料庫名,更重要的是會產生CREATE DATABASE IF NOT EXISTS dbname
--default-character-set=name
Set the default character set.
#設定匯出指令碼的字元集,未指定的情況下為UTF8。
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
#在dump mysql資料庫以及依賴於mysql資料庫恢複時建議使用該選項產生FLUSH PRIVILEGES語句
-F, --flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
--databases= or --all-databases), the logs will be
flushed for each database dumped. The exception is when
using --lock-all-tables or --master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use --lock-all-tables or --master-data with
--flush-logs.
#在啟動dump前會flush日誌,此方式可以用於實現增量備份
-d, --no-data No row information.
#不輸出資料行,僅匯出結構
-f, --force Continue even if we get an SQL error.
#在碰到錯誤時,依舊強制dump
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables
off.
#添加二進位日誌位置到輸出。1表示輸出change master命令,2則注釋輸出change master命令。
-R, --routines Dump stored routines (functions and procedures).
#匯出函數和過程以及觸發器,預設情況下,這些不會被匯出
-t, --no-create-info
Don't write table creation info.
#不產生建表語句
--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.
#建立一致性快照,僅僅針對innodb引擎
#不能存在其他動作:ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE,關閉--lock-tables。
-w, --where=name Dump only selected records. Quotes are mandatory.
#使用where子句只匯出合格記錄
# Author : Leshami
# Blog :