mysqldump Ver 10.13 Distrib 5.1.30, for pc-linux-gnu (i686)
By Igor Romanenko, Monty, Jani & Sinisa
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumping definition and data mysql database or table
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]
OR mysqldump [OPTIONS] --all-databases[OPTIONS]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults Print the program argument list and exit

■ディフォルトのオプションを表示(オプションを指定しないと以下のように設定されている)
=======================================================================
[root@colinux mysql]# mysqldump --print-defaults
mysqldump would have been started with the following arguments:
--port=3306 --socket=/tmp/mysql.sock --default-character-set=utf8 --quick --max_
allowed_packet=16M --default-character-set=utf8

[root@colinux mysql]#
=======================================================================


--no-defaults Don't read default options from any options file

■ディフォルトオプションをオプションファイルから読み込まない

--defaults-file=# Only read default options from the given file #

■特定のオプションファイルからのみバックアップオプションを読み込む

--defaults-extra-file=# Read this file after the global files are read

■グローバルファイルを読み込んだ後にこのファイルを読み込む。

-a, --all Deprecated. Use --create-options instead.
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
-Y, --all-tablespaces
Dump all the tablespaces.
-y, --no-tablespaces
Do not dump any tablespace information.
--add-drop-database Add a 'DROP DATABASE' before each create.
--add-drop-table Add a 'drop table' before each create.
--add-locks Add locks around insert statements.
--allow-keywords Allow creation of column names that are keywords.
--character-sets-dir=name
Directory where character sets are.
-i, --comments Write additional information.
--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.
--compact Give less verbose output (useful for debugging). Disables
structure comments and header/footer constructs. Enables
options --skip-add-drop-table --no-set-names
--skip-disable-keys --skip-add-locks
-c, --complete-insert
Use complete insert statements.
-C, --compress Use compression in server/client protocol.
--create-options Include all MySQL specific create options.
-B, --databases To dump several databases. Note the difference in usage;
In this case no tables are given. All name arguments are
regarded as databasenames. 'USE db_name;' will be
included in the output.

-#, --debug[=#] This is a non-debug version. Catch this and exit
--debug-check Check memory and open file usage at exit.
--debug-info Print some debug info at exit.
--default-character-set=name
Set the default character set.

■バックアップデータの文字コード。my.cnfで指定しているdefault-character-setと
同じコードを指定する。 省略した場合、UTF-8に変換されるので文字化けの原因となる。

--delayed-insert Insert rows with INSERT DELAYED;
--delete-master-logs
Delete logs on master after backup. This automatically
enables --master-data.
-K, --disable-keys '/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and
'/*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put
in the output.
-E, --events Dump events.
-e, --extended-insert
Allows utilization of the new, much faster INSERT syntax.
--fields-terminated-by=name
Fields in the textfile are terminated by ...
--fields-enclosed-by=name
Fields in the importfile are enclosed by ...
--fields-optionally-enclosed-by=name
Fields in the i.file are opt. enclosed by ...
--fields-escaped-by=name
Fields in the i.file are escaped by ...
-x, --first-slave Deprecated, renamed to --lock-all-tables.
-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

■ダンプ前にバイナリログをスイッチさせる。差分ファイルを適用する際、
全体バックアップ直後のバイナリログから適用すればよいので、指定しておく。

--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.
-f, --force Continue even if we get an sql-error.
-?, --help Display this help message and exit.
--hex-blob Dump binary strings (BINARY, VARBINARY, BLOB) in
hexadecimal format.

■バイナリ型のデータをエスケープ処理を行わずに実際に格納された値の16進数表記で保存する。
これを指定しないとdefault-character-setがシフトJIS系の場合エスケープ処理に失敗し、
バイナリデータが破壊される場合がある。

-h, --host=name Connect to host.

■ mysqldumpは、サーバー上でもリモートクライアント側でもバックアップ出来るリモートにて
サーバーのバックアップを行う場合はこのオプションにてサーバー名を指定する。

--ignore-table=name Do not dump the specified table. To specify more than one
table to ignore, use the directive multiple times, once
for each table. Each table must be specified with both
database and table names, e.g.
--ignore-table=database.table
--insert-ignore Insert rows with INSERT IGNORE.
--lines-terminated-by=name
Lines in the i.file are terminated by ...
-x, --lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns --single-transaction and
--lock-tables off.

■ダンプ前に全データベースを共有ロックする。"FLUSH TABLES WITH READ LOCK"
これは、すべてのセッションから一切の更新ができなくなる。"UNLOCK TABLES"コマンド
で共有ロックを解除できる。

-l, --lock-tables Lock all tables for read.

■ダンプ前に全テーブルをロックする。但し、データベース単位でロックする為、
DB間の一貫性は保障しない。 MyISAMバックアップ時はこのオプションでロックをかける。
InnoDBの場合は、--single-transactionを使用する。


--log-error=name Append warnings and errors to given file.

--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文がコメントアウトされる。(デフォルト)

--max_allowed_packet=#
--net_buffer_length=#
--no-autocommit Wrap tables with autocommit/commit statements.
-n, --no-create-db 'CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name;' will
not be put in the output. The above line will be added
otherwise, if --databases or --all-databases option was
given.}.
-t, --no-create-info
Don't write table creation info.
-d, --no-data No row information.
-N, --no-set-names Deprecated. Use --skip-set-charset instead.
--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.
--order-by-primary Sorts each table's rows by primary key, or first unique
key, if such a key exists. Useful when dumping a MyISAM
table to be loaded into an InnoDB table, but will make
the dump itself take considerably longer.
-p, --password[=name]
Password to use when connecting to server. If password is
not given it's solicited on the tty.

-P, --port
=# Port number to use for connection.
--protocol=name The protocol of connection (tcp,socket,pipe,memory).
-q, --quick Don't buffer query, dump directly to stdout.
-Q, --quote-names Quote table and column names with backticks (`).
--replace Use REPLACE INTO instead of INSERT INTO.
-r, --result-file=name
Direct output to a given file. This option should be used
in MSDOS, because it prevents new line '\n' from being
converted to '\r\n' (carriage return + line feed).
-R, --routines Dump stored routines (functions and procedures).
--set-charset Add 'SET NAMES default_character_set' to the output.
Enabled by default; suppress with --skip-set-charset.
-O, --set-variable=name
Change the value of a variable. Please note that this
option is deprecated; you can set variables directly with
--variable-name=value.
--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.

■ダンプ前にBEGIN SQL文を発行する。--master-data=2と組み合わせて指定することで、
ロックをかけずに一貫性のあるバックアップを取得できる。但し、InnoDBの場合に限る。

--dump-date Put a dump date to the end of the output.
--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
-S, --socket=name Socket file to use for connection.
-T, --tab=name Creates tab separated textfile for each table to given
path. (creates .sql and .txt files). NOTE: This only
works if mysqldump is run on the same machine as the
mysqld daemon.
--tables Overrides option --databases (-B).
--triggers Dump triggers for each dumped table
--tz-utc SET TIME_ZONE='+00:00' at top of dump to allow dumping of
TIMESTAMP data when a server has data in different time
zones or data is being moved between servers with
different time zones.
-u, --user=name User for login if not current user.
-v, --verbose Print info about the various stages.
-V, --version Output version information and exit.
-w, --where=name Dump only selected records; QUOTES mandatory!
-X, --xml Dump a database as well formed XML.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
all TRUE
all-databases FALSE
all-tablespaces FALSE
no-tablespaces FALSE
add-drop-database FALSE
add-drop-table TRUE
add-locks TRUE
allow-keywords FALSE
character-sets-dir (No default value)
comments TRUE
compatible (No default value)
compact FALSE
complete-insert FALSE
compress FALSE
create-options TRUE
databases FALSE
debug-check FALSE
debug-info FALSE
default-character-set utf8
delayed-insert FALSE
delete-master-logs FALSE
disable-keys TRUE
events FALSE
extended-insert TRUE
fields-terminated-by (No default value)
fields-enclosed-by (No default value)
fields-optionally-enclosed-by (No default value)
fields-escaped-by (No default value)
first-slave FALSE
flush-logs FALSE
flush-privileges FALSE
force FALSE
hex-blob FALSE
host (No default value)
insert-ignore FALSE
lines-terminated-by (No default value)
lock-all-tables FALSE
lock-tables TRUE
log-error (No default value)
master-data 0
max_allowed_packet 16777216
net_buffer_length 1047551
no-autocommit FALSE
no-create-db FALSE
no-create-info FALSE
no-data FALSE
order-by-primary FALSE
port 3306
quick TRUE
quote-names TRUE
replace FALSE
routines FALSE
set-charset TRUE
single-transaction FALSE
dump-date TRUE
socket /tmp/mysql.sock
tab (No default value)
triggers TRUE
tz-utc TRUE
user (No default value)
verbose FALSE
where (No default value)

■■■幾つかのオプションを利用してバックアップ検証■■■

mysql> show databases;
+——————————–+
| Database       |
+——————————–+
| information_schema   |
| DATABASE |
| DB01        | INNODB
| DB02        | INNODB
| TEST        | INNODB
| client_test_db     |
| mysql        |
+———————————+
7 rows in set (0.09 sec)


mysqldump --single-transaction --flush-logs --master-data=2 --databases DB01 DB02 TEST > /tmp/MYSQL_DUMP_3DB_WITHOPT.sql -u root -p

[root@colinux tmp]# ls -l
total 12
-rw-r–r– 1 root root 8843 2009-02-08 08:30 MYSQL_DUMP_3DB_WITHOPT.sql
srwxrwxrwx 1 mysql mysql 0 2009-02-08 08:30 mysql.sock
[root@colinux tmp]#

注意※ バイナリーログをONにしていないと –flush-logが指定出来ないのでONにする。

mysqldump

ログのスイッチが行われているので、メモリー内データもFLUSHされている。
オラクルのオンラインバックアップと同じですね。
mysqldump_log

Comments are closed.

Post Navigation