前回に引き続き、今回はmysqldumpのお話です。
オプションの一覧
mysqldumpを使っていて、CREATE TABLE文だけほしいときや、データをファイルで分けてほしいことがありました。オプションの一覧がMySQLの本サイトやhelpで出てくるものでは、いちいち調べるのが不便だったので一覧を作成してみました。
順番やグルーピングは個人的につけたものです。
また、デフォルトで有効になっているものには「初期」の欄に「○」をつけました。
| オプション | 短縮 | 値 | 初期 | 説明 |
|---|---|---|---|---|
| 基本 | ||||
| user | u | ユーザ名 | サーバ接続に使用するユーザ名を指定します。 | |
| password | p | パスワード | パスワードを指定します。値を指定しない場合は、入力を求められます。 | |
| host | h | ホスト | ダンプを取得するホストを指定します。 デフォルトではlocalhostがホストになります。 |
|
| port | P | ポート番号 | 接続に指定されたTCP/IPポート番号を使用します。 | |
| login-path | ファイルパス | ユーザの名前がついたログインファイルを指定します。 ファイルはフルパスで指定します。 このログインファイルにはhost,user,passwordのみ指定できます。 MySQL 5.6で追加されました。 |
||
| デフォルト値 | ||||
| print-defaults | 指定されたオプションを表示する。 このオプションを指定すると、オプションを表示して終了するため、ダンプは取得しない。 |
|||
| no-defaults | 設定ファイルを読み込まない。 | |||
| defaults-file | ファイルパス | 指定された設定ファイルだけ読み込みます。 ファイルはフルパスで指定します。 ファイルが存在しないか、アクセス不能な場合はエラーとなります。 |
||
| defaults-extra-file | ファイルパス | グローバル設定ファイルを読み取った後、ユーザ設定ファイルの前に指定した設定ファイルを読み取ります。 ファイルはフルパスで指定します。 後から読み込んだファイルに設定されているオプションが有効になるため注意が必要です。 |
||
| defaults-group-suffix | 接尾辞 | オプショングループを設定します。 オプショングループは、[mysqldump接尾辞]と指定できます。 |
||
| 情報 | ||||
| help | ? | ヘルプを表示します。 | ||
| version | V | バージョンが出力されます。 他のオプションは無視されます。 |
||
| verbose | v | プログラムの動作の詳細が出力されます。 | ||
| 接続 | ||||
| force | f | テーブルダンプ中にSQLエラーが発生しても続行します。 このオプションが使用しない場合、エラーメッセージを出力して終了します。 このオプションを使用した場合は、エラーメッセージを出力しますが、実行を続けます。エラーとなったSQL定義などはSQLコメントで書き出します。 |
||
| bind-address | アドレス | MySQLサーバーへの接続に指定されたネットワークアドレスを使用します。 MySQL 5.6で追加されました。 |
||
| pipe | W | Windowsで接続サーバに名前付きパイプを使います。 サーバが名前付きパイプ接続をサポートしている必要があります。 |
||
| protocol | プロトコル | サーバへ接続する際のプロトコルを指定します。 指定可能なのは[TCP|SOCKET|PIPE|MEMORY]です。 |
||
| socket | S | ファイルパス | UNIXではソケットファイル、Windowsでは名前付きパイプを指定して、localhostの接続に使用します。 | |
| compress | C | クライアントとサーバ間で圧縮に対応している場合、情報を全て圧縮して送ります。 | ||
| 指定 | ||||
| tables | オプションに続く引数をテーブル名として認識します。 databases(B)オプションを上書きします。 |
|||
| databases | B | ダンプするデータベースを指定します。 通常は、データベース名の後の引数はテーブル名として解釈されますが、このオプションを使用するとデータベースとして解釈します。 |
||
| all-databases | A | 全てのデータベースの全てのテーブルをダンプします。 databasesオプションで全てのデータベースを指定するのと同じです。 |
||
| ignore-table | テーブル | ダンプしないテーブルを指定します。 テーブルは、[データベース名.テーブル名]と指定します。 複数テーブルを無視する場合は、複数回使用してください。 |
||
| トランザクション／ロック | ||||
| single-transaction | サーバからダンプを開始する前に、BEGIN文を実行します。 InnoDBといったトランザクションが有効なテーブルに有用です。 ロックを取得せずにBEGIN文が実行された時のデータベースの状態をダンプできます。 但し、MyISAMやMEMORYなどのテーブルは、ダンプ中に変化する可能性があります。 他に接続が、テーブルに対してDDLを実行すると、読み取り一貫性が崩れてしまいます。 lock-tablesオプションはトランザクションが暗黙的にコミットされますので、相互に排他です。 |
|||
| lock-tables | l | ○ | ダンプする前に全てのテーブルをロックしします。 テーブルはREAD LOCALでロックされます。そのため、MyISAMの場合、並列インサートが実行できます。 トランザクションをサポートするテーブル(InnoDBなど)では、テーブルをロックしない、single-transactionを利用したほうが良いです。 |
|
| lock-all-tables | x | データベースの全てのテーブルをロックしてダンプを行います。 global read lockを取得して実現されます。 自動的にsingle-transactionオプションとlock-tablesオプションはオフにされます。 |
||
| first-slave | x | 非推奨。MySQL 5.5で廃止されました。 lock-all-tablesを使用してください。 |
||
| 読み取り／バッファ | ||||
| max_allowed_packet | バイト数 | クライアントとサーバ間通信の最大バッファサイズを指定します。 デフォルトでは24MB、最大は1GBです。 |
||
| net_buffer_length | サイズ | クライアントとサーバ間通信の初期バッファサイズを指定します。 これは、(extended-insertやoptオプションが指定されて)複数行INSERT文を作成する時、ここで指定されたサイズまでの長さの文を作成します。 このサイズを増やした場合、MySQLサーバで指定されているnet_buffer_lengthもサイズ以上にする必要があります。 |
||
| quick | q | ○ | 通常は、ダンプに書き込む前に一気にテーブルデータを取得しメモリへバッファしますが、このオプションを指定すると1行ごとに処理します。 大きなテーブルのダンプに有用です。 |
|
| shared-memory-base-name | メモリ名 | 共有メモリ名を指定します。 | ||
| 文字コード | ||||
| character-sets-dir | パス | キャラクタセットがインストールされているディレクトリパスを指定します。 | ||
| default-character-set | キャラクタセット | デフォルトのキャラクタセットを指定します。 特に指定されていない場合、utf8を使用します。 |
||
| 出力ファイル／ログ／デバッグ | ||||
| result-file | r | ファイルパス | 結果の出力先ファイルを指定します。 Windows上では、改行コードが\n(newline)から\r\n(carriage return, newline)に変換されてしまうため、変換されないように指定したファイルに出力します。 ファイルは必ず作成され上書きされ、以前の内容が消えます。ダンプ中にエラーが発生しても上書きされますので、注意してください。 |
|
| log-error | ファイルパス | ワーニングとエラーログをファイルに出力します。 デフォルトでは、ログをとりません。 |
||
| flush-logs | F | ダンプを始める前にMySQLサーバログファイルをフラッシュします。 このオプションはRELOAD権限が必要です。 all-databasesオプションと併用した場合、ログはデータベース毎にフラッシュされます。 また、lock-all-tablesまたはmaster-dataを使用している場合は、全てのテーブルがロックされた瞬間に一度だけフラッシュされます。ログのフラッシュとダンプを同時に行いたい場合は、このオプションを併用してください。 |
||
| delete-master-logs | マスタの複製サーバでダンプの実行後にバイナリログを削除します。 このオプションは自動的にmastar-dataオプションを有効にします。 |
|||
| debug | # | デバッグオプション | デバッグログをデバッグファイルに書き込みます。 デバッグオプションは、主に’d:t:o,ファイル名’とします。 デフォルトは’d:t:o,/tmp/mysqldump.trace.’です。 |
|
| debug-check | プログラムが閉じるときに、デバッグ情報をプリントします。 | |||
| debug-info | プログラムの終了時にデバッグ情報と、メモリーおよび CPU 使用率の統計を表示します。 | |||
| SSL接続 | ||||
| ssl | sslで始まるオプションはSSLでサーバに接続し、SSLキーや証明の場所を指定するか否かを記載します。 SSLを使用しない場合は、skip-sslかssl=0と、指定します。 |
|||
| ssl-ca | ファイルパス | 信頼されたSSL認証局(trustedSSLCA)の一覧があるファイのルパスを指定します。 | ||
| ssl-capath | ディレクトリパス | PEM形式の信頼されたCA証明書を保存しているディレクトリのパスを指定します。 | ||
| ssl-cert | ファイルパス | 接続安全を確立するために使用するSSL証明書ファイル名を指定します。 | ||
| ssl-cipher | 暗号 | SSL暗号化に使用できる暗号を指定します。 openssl ciphersコマンドと同じ形式で指定可能です。 例)ssl-cipher=ALL:-AES:-EXP |
||
| ssl-crl | ファイルパス | PEM形式の証明書失効リストのファイルのパスを指定します。 このオプションかssl-crlpathが指定されている場合は、CA証明書のあるパスに証明書失効リストをチェックしません。 MySQL 5.6.3で追加されました。 |
||
| ssl-crlpath | ディレクトリパス | PEM形式の証明書失効リストのファイルのあるディレクトリのパスを指定します。 このオプションかssl-crlが指定されている場合は、CA証明書のあるパスに証明書失効リストをチェックしません。 MySQL 5.6.3で追加されました。 |
||
| ssl-key | ファイルパス | 接続安全を確立するために使用するSSLキーファイル名を指定します。 | ||
| ssl-verify-server-cert | クライアントプログラム用のオプション。サーバに接続するときに使用するホスト名に対して、サーバ証明書のCommonName値を検証するようにするオプションで、一致しない場合には接続却下になる。この機能は、中間者攻撃対策として使用できる。この検証のデフォルトは無効。 | |||
| プラグイン | ||||
| default-auth | ファイルパス | クライアント側の認証に指定されたプラグインファイルを使用します。 MySQL 5.5で追加されました。 |
||
| plugin-dir | ディレクトリパス | プラグインが配置されているディレクトリのパスを指定します。 MySQL 5.5で追加されました。 |
||
| 変数 | ||||
| set-variable | O | 変数と値 | このオプションは非推奨、5.5で廃止されました。 変数に値を指定します。 |
|
| ダンプ対象 | ||||
| all-tablespaces | Y | テーブルダンプに、NDB Clusterテーブルに使用されるテーブルスペース作成に必要なSQLステートメントを追加します。でなければ、この情報はmysqldumpの出力には含まれていません。このオプションは、現在MySQLクラスタテーブルに対してのみ有効です。 | ||
| no-tablespaces | y | デーブルダンプに、NDB Clusterテーブルに使用されるテーブルスペース作成に必要なSQLステートメントを出力しません。 | ||
| no-create-db | n | databasesまたはall-databasesオプションが指定された場合に出力するCREATE DATABASE文を出力しないようにします。 | ||
| create-options | a | ○ | MySQL独自のオプションをCREATE TABLE文に含めます。 例えばAUTO_INCREMENTするカラムがある場合、出力されます。 |
|
| all | a | 非推奨。MySQL 5.5で廃止されました。 create-optionsオプションのエイリアスです。 |
||
| no-create-info | t | CREATE TABLE文を出力しません。 | ||
| no-data | d | テーブルダンプのテーブルの内容(INSERT文)を出力しません。 テーブルのCREATE TABLE文のみ出力したいときに使用します。 |
||
| where | w | 条件 | WHERE句で選択された行のみダンプします。 | |
| triggers | ○ | ダンプするテーブルごとにトリガーを出力します。 このオプションはデフォルトで有効です。 無効にする場合はskip-triggersを使用してください。 |
||
| routines | R | ストアドルーチン(プロシージャとファンクション)をダンプに含めます。 このオプションはmysqlのprocテーブルのSELECT権限が必要です。 |
||
| events | E | Event Schedulerのイベントをダンプに含めます。 | ||
| DROP文 | ||||
| add-drop-database | CREATE DATABASE文の前にDROP DATABASE文を追加します。 | |||
| add-drop-table | ○ | CREATE TABLE文の前にDROP TABLE文を追加します。 | ||
| add-drop-trigger | CREATE TRIGGER文の前にDROP TRIGGER文の前に追加します。 なお、テーブルを削除すると一緒にトリガーも削除されます。 MySQL 5.5で追加されました。 |
|||
| INSERT文 | ||||
| complete-insert | c | INSERT文にカラム名を含めます。 | ||
| delayed-insert | INSERT DELAYEDが使用できる場合、INSERT DELAYED文を使用します。 | |||
| disable-keys | K | ○ | テーブルダンプ毎にINSERT文を /*!40000 ALTER TABLE tbl_name DISABLE KEYS */; /*!40000 ALTER TABLE tbl_name ENABLE KEYS */; で囲みます。 行が全て挿入されてからインデックスが有効になり作成されるため、ダンプのロード時間が早くなります。 なお、MyISAMにのみ有効です。 |
|
| extended-insert | e | ○ | INSERT文の出力を複数のVALUESを含む、複数行INSERT文にします。 ダンプファイルのサイズを小さくし、ダンプファイルを再ロードするINSERTの速度が向上します。 |
|
| insert-ignore | テーブルダンプのINSERT文にIGNOREオプションを追加します。 | |||
| replace | INSERT文をREPLACE文で出力します。 | |||
| quote-names | Q | ○ | データベース名、テーブル名、カラム名を「`」で囲みます。 このオプションはデフォルトで有効です。 ANSI_QUOTES SQLモードの場合は「”」で囲みます。 skip-quote-namesで無効にできますが、compatibleオプションのようなこのオプションを有効にするオプションの後に指定してください。 |
|
| order-by-primary | テーブルが主キーまたは最初のユニークキーを持っている場合、キーでソートし出力します。 これはMyISAMからInnoDBへロードするデータをダンプするときに有用ですが、ダンプに時間がかかるようになります。 |
|||
| add-locks | ○ | テーブルダンプ(INSERT)をLOCK TABLES文とUNLOCK TABLES文で囲みます。 ダンプファイルを再ロードするINSERTの速度が向上します。 |
||
| allow-keywords | キーワードであるカラム名の作成を許可します。 | |||
| CHANGE MASTER TO文 | ||||
| master-data | [1,2] | ダンプの最初にバイナリログのファイル名とポジションを出力します。 このオプションはRELOAD権限が必要で、バイナリログが有効である必要があります。 オプション値が1の場合、ダンプのロード時に有効になるようにCHANGE MASTER TO文が出力されます。 オプション値が2の場合、コメントとして出力されます。 オプション値が指定されなかった場合のデフォルト値は1です。 このオプションは自動的にlock-tablesを無効にします。single-transactionが指定されていなかった場合、lock-all-tablesを有効にします。 single-transactionオプションも参照のこと |
||
| dump-slave | [1,2] | スレーブサーバからダンプする時に、ダンプするスレーブのマスタの情報を出力します。 オプション値が1の場合、ロード時に効果があるようCHANGE MASTER TO文が出力されます。 オプション値が2の場合、コメントとして書かれているため、ロード時に効果がありません。 指定されていない場合のデフォルト値は1です。 MySQL 5.5で追加されました。 |
||
| apply-slave-statements | CHANGE MASTER TO文の前にSTOP SLAVE文、後にSTART SLAVE文を出力する。 MySQL 5.5で追加されました。 |
|||
| include-master-host-port | dump-slaveオプションで出力するCHANGE MASTER TO文に、マスターのホストの情報(ホスト名とポート番号)を含める。 MySQL 5.5で追加されました。 |
|||
| SET文 | ||||
| set-gtid-purged | [OFF,ON,AUTO] | GTID(Global Transaction ID)の情報を出力するかを制御します。 SET @@global.gtid_purged文を追加します。 オプション値がOFFの場合、GTIDは出力されません。 ONは、GTIDを出力します。GTIDがMySQLサーバで無効だと、エラーが発生します。 AUTOは、GTIDが有効である場合にGTIDを出力します。 オプション値のデフォルトはAUTOです。 MySQL 5.6.9で追加されました。 |
||
| tz-utc | ○ | ダンプにSET TIME_ZONE=’+00:00’を追加します。 これによりTIMESTAMPカラムは異なるタイムゾーンのサーバ間でリロードされても値は変わりません。またサマータイムによる時間変更に対しても保護します。 このオプションはデフォルトで有効です。 無効化するにはskip-tz-utcを使用してください。 |
||
| set-charset | ○ | SET NAMES default_character_setを出力に追加します。 このオプションはデフォルトで有効です。 無効にするにはskip-set-charsetを使用してください。 |
||
| no-set-names | N | デフォルトで有効なset-charsetを無効化します。 skip-set-charsetと同じです。 |
||
| COMMIT文 | ||||
| no-autocommit | ダンプするテーブルごとに、SET AUTOCOMMIT=0とCOMMIT文を出力します。 | |||
| コメント | ||||
| comments | i | ○ | プログラムのバージョン、サーバのバージョン、ホストといった追加情報をダンプファイルに書き込みます。 このオプションはデフォルトで有効です。 |
|
| dump-date | ○ | commentsオプションが指定された場合、ダンプした日時をコメントで出力します。 このため、ダンプした日時以外が同一であってもファイル内容として際が発生します。 skip-dump-dateでダンプした日時を出力しないようにできます。 |
||
| FLUSH PRIVILEGES文 | ||||
| flush-privileges | FLUSH PRIVILEGES文がダンプの最後に追加されます。 これはダンプにmysqlデータベースが含まれるか、mysqlデータベースに依存するデータベースが含まれている場合に使用します。 |
|||
| 出力形式 | ||||
| xml | X | ダンプ出力を整形式のXMLで出力します。 | ||
| tab | T | ディレクトリパス | ダンプするテーブルごとに、テーブルを作成するCREATE TABLE文を含む[テーブル名].sqlとデータを含む[テーブル名].txtを作成します。 オプション値にはファイルを作成するディレクトリを指定します。 他のデータファイル用のオプションで区切り文字などを指定できます。 デフォルトでタブ区切り、\n(newline)で改行されます。 このオプションは、mysqldとmysqldumpが同一のマシンで動いている場合に使用してください。また、ファイルの書き込み権限が必要で、サーバはディレクトリ内のファイルへの書き込み権限をもらう必要があります。 |
|
| fields-enclosed-by | 囲み文字 | tabオプションと共に使用されます。 データファイルに出力するフィールドの囲み文字を指定します。 全てのフィールドを囲み文字によって囲まれます。 LOAD DATA INFILE文を参照のこと |
||
| fields-escaped-by | エスケープ文字 | tabオプションと共に使用されます。 データファイルに出力するフィールドのエスケープ文字を指定します。 LOAD DATA INFILE文を参照のこと |
||
| fields-optionally-enclosed-by | 囲み文字 | tabオプションと共に使用されます。 データファイルに出力するフィールドの区切り文字を指定します。 文字列データのフィールド(CHAR、BINARY、TEXT、ENUM)を囲み文字によって囲まれます。 LOAD DATA INFILE文を参照のこと |
||
| fields-terminated-by | 区切り文字 | tabオプションと共に使用されます。 データファイルに出力するフィールドの区切り文字を指定します。 LOAD DATA INFILE文を参照のこと |
||
| lines-terminated-by | 区切り文字 | tabオプションと共に使用されます。 データファイルに出力する行の区切り文字を指定します。 LOAD DATA INFILE文を参照のこと |
||
| hex-blob | データタイプがBINARY、VARBINARY、BLOB、BITのカラムを、16進数変換してダンプします。 | |||
| compatible | オプション値 | 他のデータベースや、古いMySQLと互換性のあるダンプを出力します。 オプション値には、[ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, no_field_options]が指定できます。 このオプションは他のサーバとの互換性を保証するものではありません。現在提供されている、ダンプ出力の互換性を挙げるためのSQLモード値を有効化するだけです。例えば、compatible=oracleはデータタイプをOracleタイプにマップしたり、Oracleコメント構文を使用したりしません。 |
||
| エイリアス | ||||
| compact | skip-add-drop-table、no-set-names、skip-disable-keys、skip-add-locksのエイリアスです。 | |||
| opt | ○ | add-drop-table、add-locks、create-options、disable-keys、extended-insert、lock-tables、quick、set-charsetを指定するエイリアスです。 速いダンプと、MySQLサーバへのリロードを目的としています。 このオプションはデフォルトで有効になっています。 |
||
コマンドラインでの使用例
mysqldump -uroot --password -hlocalhost --skip-extended-insert database_name --ignore-table=database_name.table_name
(短縮形の場合はハイフン1つ)
また、skip-を頭につけると無効にするオプションになります。
最後に
これで皆さんも立派なmysqldumpマニアになれますね。楽しいMySQL生活をお過ごしください！
