mysqldump の mysql dump オプションには接続・対象指定・出力制御・ロックと整合性保持など、目的の異なるものが多数あります。主要なものを整理していないと、InnoDB テーブルで不整合なダンプを取ってしまったり、--lock-tables によって本番サービスに影響を与えるリスクがあります。
この記事では、MySQL 8.0系・MariaDB 10.6系（CentOS Stream 9／Rocky Linux 9／Ubuntu 22.04 LTS 上のセルフホスト環境）を想定環境として、mysqldump の主要オプションをカテゴリ別に整理し、実務でそのまま使える組み合わせパターンを解説します。
この記事のポイント
・--single-transaction が InnoDB の整合バックアップに不可欠なオプション
・--no-data でスキーマのみ、--no-create-info でデータのみを個別に取得できる
・--ignore-table・--where でバックアップ対象を柔軟に絞り込める
・--quick を追加するとメモリ消費を抑えて大規模 DB にも対応できる
でも安心してください。プロのエンジニアはコマンドを暗記していません。
「現場で使える型」を効率よく使いこなしているだけです。
mysqldumpの出力形式と基本動作
mysqldump は MySQL・MariaDB が提供するクライアントツールで、データベースの内容を SQL テキストとして出力します。出力はCREATE TABLE 文と INSERT 文の組み合わせで構成されており、mysql コマンドに読み込ませるだけでリストアできます。オプションはおおきく4カテゴリに分けられます。
・接続・認証：どのサーバー・ユーザーで接続するか
・対象指定：どのデータベース・テーブルをダンプするか
・出力制御：スキーマ・データ・ストアドプロシージャ等のどの部分を含めるか
・ロック・整合性：ダンプ中のデータ整合性をどう確保するか
主要オプション一覧｜カテゴリ別に整理
1. 接続・認証オプション
接続先サーバーとユーザーを指定するオプションです。省略時はローカルホストへのソケット接続になります。・-u ユーザー名（--user）：接続ユーザーを指定。省略するとOSのログインユーザー名が使われる
・-p（--password）：パスワードを対話入力させる。
-pPASSWORD のように直接書くとシェル履歴に残るため運用上は避ける・-h ホスト名（--host）：接続先ホストを指定。省略時はローカルソケット経由で接続
・-P ポート番号（--port）：TCP ポートを指定。デフォルトは 3306
# remote host へ接続する場合はホストとポートを明示する mysqldump -u backup_user -p -h 192.168.1.100 -P 3306 mydb > mydb.sql
2. バックアップ対象の指定オプション
どのデータベース・テーブルをダンプするかを制御するオプションです。・引数にデータベース名のみ：単一データベースをダンプ（例：
mysqldump mydb）・--all-databases（-A）：全データベースをダンプ。USE 文も出力に含まれる
・--databases（-B）：複数のデータベースを名前で指定。CREATE DATABASE 文と USE 文が出力に追加される
・テーブル名の列挙：データベース名の後にテーブル名を並べると特定テーブルのみダンプ
・--ignore-table=db.table：指定テーブルをダンプ対象から除外。複数除外する場合はオプションを繰り返す
# all-databases でサーバー全体をダンプ mysqldump -u root -p --all-databases > all.sql # ignore-table でログテーブルを除外（複数の場合はオプションを繰り返す） mysqldump -u root -p \ --ignore-table=mydb.access_log \ --ignore-table=mydb.debug_log \ mydb > mydb_no_logs.sql
3. 出力内容の制御オプション
SQL ダンプに含める要素を制御するオプションです。スキーマとデータを分けて取得したい場面で重宝します。・--no-data（-d）：INSERT 文を出力しない（テーブル定義のみ取得）
・--no-create-info（-t）：CREATE TABLE 文を出力しない（データのみ取得）
・--complete-insert：INSERT 文にカラム名を含める。カラム順変更への耐性が上がり移行時に有効
・--skip-extended-insert：1行ずつ独立した INSERT 文で出力。diff でデータ差分を確認したい場合などに使う
・--routines（-R）：ストアドプロシージャ・ファンクションを含める。デフォルトはオフ
・--events（-E）：イベントスケジューラの定義を含める。デフォルトはオフ
・--triggers：トリガーを含める。デフォルトはオン（除外したい場合は --skip-triggers を指定）
# no-data でテーブル定義のみ取得（INSERT 文は出力されない） mysqldump -u root -p --no-data mydb > mydb_schema.sql # routines と events も含めてフルダンプ mysqldump -u root -p --all-databases --routines --events > all_complete.sql
4. ロックと整合性のオプション（最重要）
ダンプ中のデータ整合性をどう確保するかを制御します。ここが最も重要なカテゴリで、誤ったオプション選択はデータ不整合や本番への影響につながります。・--single-transaction：トランザクションを使って整合スナップショットを取得。InnoDB テーブル専用。ダンプ中にテーブルロックをかけないため、本番サービスへの影響が最小になる。デフォルトはオフ
・--lock-tables（デフォルト動作）：ダンプするテーブルを1テーブルずつ READ LOCAL ロック。MyISAM テーブルの整合性に必要だが、データベースをまたいだ整合性は保証されない
・--lock-all-tables（-x）：全データベースの全テーブルをグローバルロック（FLUSH TABLES WITH READ LOCK）。完全な整合性が得られるが、ロック時間が長くなるため本番での使用は慎重に
・--quick（-q）：大きなテーブルを1行ずつ取得する。デフォルトは全件をメモリに読み込むため、大規模 DB ではメモリ不足が発生する場合がある
# single-transaction でInnoDB の整合スナップショットを取得（サービス無停止） mysqldump -u root -p --single-transaction mydb > mydb.sql # quick を追加して大規模 DB のメモリ消費を抑える mysqldump -u root -p --single-transaction --quick mydb > mydb.sql
MySQL 5.5 以降は InnoDB がデフォルトストレージエンジンです。既存テーブルのエンジンは
SHOW TABLE STATUS FROM mydb; で確認できます。InnoDB テーブルには --single-transaction を使い、MyISAM テーブルが混在する環境では --lock-all-tables を検討してください。目的別｜よく使うオプションの組み合わせパターン
日次バックアップ運用や環境移行でよく使う定番の組み合わせを紹介します。mysqldump を含む Linux サーバーの実務運用スキルを体系的に身につけたい方には、Linux Master Pro Seminar も参考にしてください。1. InnoDB対応フルバックアップ（最頻出）
日次バックアップスクリプトで最もよく使われるパターンです。--single-transaction でロックなしの整合バックアップを取りつつ、ストアドプロシージャとイベントも含めます。# single-transaction・routines・events を組み合わせた日次フルバックアップ mysqldump -u root -p \ --all-databases \ --single-transaction \ --routines \ --events \ --quick \ > /var/backup/mysql/all_$(date +%Y%m%d).sql
・--routines / --events：ストアドプロシージャとイベントスケジューラも含める
・--quick：大規模 DB でも1行ずつ取得してメモリ消費を抑える
2. スキーマ（テーブル定義）のみ取得
新しい環境にデータベース構造だけ移行したいときや、DDL バックアップを取りたい場合に使います。# no-data でスキーマのみ取得（INSERT 文なし・プロシージャも含める） mysqldump -u root -p \ --no-data \ --routines \ --events \ mydb > mydb_schema_$(date +%Y%m%d).sql
3. データのみ取得（移行・差し替え用）
テーブル構造はすでに移行先に存在し、データだけ投入し直したい場合に使います。既存データをいったんTRUNCATE してから適用するワークフローで有効です。# no-create-info でデータのみ取得（CREATE TABLE なし） mysqldump -u root -p \ --no-create-info \ --single-transaction \ mydb > mydb_data_only.sql
4. 特定テーブルのみ・ログテーブルを除外
アクセスログや一時データを格納する大きなテーブルを除外してバックアップサイズを削減するパターンです。# ignore-table でアクセスログを除外してバックアップ mysqldump -u root -p \ --single-transaction \ --ignore-table=mydb.access_log \ mydb > mydb_no_accesslog.sql # 特定テーブルのみ（データベース名の後にテーブル名を列挙） mysqldump -u root -p mydb orders order_items customers > core_tables.sql
5. --whereで条件指定してデータ抽出
特定の日付以降のデータだけ取り出して移行・調査したい場合に使います。# where で 2026年以降のデータのみ取得（単一テーブルにのみ適用される） mysqldump -u root -p \ --where="created_at >= '2026-01-01 00:00:00'" \ mydb orders > orders_2026.sql
トラブルシュート｜よくあるエラーと対処
「Access denied」が出る場合
mysqldump を実行するユーザーには SELECT 権限に加えて、使用するオプションに応じた追加権限が必要です。・--single-transaction 使用時：LOCK TABLES 権限（MySQL 8.0.32 以降は不要になった場合あり）
・--all-databases 使用時：全データベースへの SELECT 権限
・--routines 使用時：mysql.proc テーブルへの SELECT 権限または SHOW_ROUTINE 権限
バックアップ専用ユーザーを作成する場合の参考例（出力例・環境により権限要件は異なります）。
-- backup_user を作成してバックアップに必要な最小権限を付与する CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'パスワードをここに設定'; GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost'; FLUSH PRIVILEGES;
「Error 2013: Lost connection to MySQL server during query」が出る場合
大規模なテーブルのダンプ中に接続タイムアウトが発生するエラーです。・--quick オプションを追加して、全件一括ではなく1行ずつ取得するようにする
・/etc/my.cnf（または /etc/mysql/mysql.conf.d/mysqld.cnf）の
net_read_timeout と net_write_timeout の値を大きくする# quick を追加してタイムアウトを回避（1行ずつ取得するためメモリ消費も削減） mysqldump -u root -p --single-transaction --quick mydb > mydb.sql
「Unknown table 'COLUMN_STATISTICS' in information_schema」が出る場合
MySQL 8.0 以降の mysqldump クライアントを使って MySQL 5.7 以前のサーバーに接続すると発生するエラーです。# column-statistics=0 で MySQL 5.7 接続時のエラーを回避 mysqldump -u root -p --column-statistics=0 mydb > mydb.sql
本記事のまとめ
mysqldump の主要オプションをカテゴリ別・目的別に整理しました。以下の表で組み合わせパターンを確認してください。| やりたいこと | コマンド |
|---|---|
| InnoDB対応フルバックアップ | mysqldump -u root -p --all-databases --single-transaction --routines --quick > all.sql |
| スキーマのみ取得 | mysqldump -u root -p --no-data mydb > schema.sql |
| データのみ取得 | mysqldump -u root -p --no-create-info mydb > data.sql |
| 特定テーブルを除外してダンプ | mysqldump -u root -p --ignore-table=mydb.log_table mydb > db.sql |
| 条件指定でデータ抽出 | mysqldump -u root -p --where="created_at>='2026-01-01'" mydb t1 > t1.sql |
| 大規模DBのメモリ節約 | mysqldump -u root -p --single-transaction --quick mydb > mydb.sql |
| MySQL 5.7接続のエラー回避 | mysqldump -u root -p --column-statistics=0 mydb > mydb.sql |
現場で通用する安全なLinuxサーバー構築の「型」を体系的に身につけたい方へ、MySQL・MariaDB のバックアップ設計を含む Linux サーバー運用を 2 日間のハンズオンで習得できるセミナーがあります。3,100名以上のエンジニアが受講した実践カリキュラムの詳細は以下からご確認ください。
＞＞ Linux Master Pro Seminar の詳細はこちら
3,100名以上が実践した「型」を無料で公開中
プロのエンジニアはコマンドを暗記していません。
「現場で使える型」を効率よく使いこなしているだけです。
その「型」を図解60Pにまとめた入門マニュアルを、完全無料でプレゼントしています。
姓・名・メールの3つだけ／30秒／解除は3秒 ／ 詳細はこちら
- 前のページへ：mysqldumpで取得したdumpファイルをインポートする手順｜文字化けとエラー対処まで
- この記事の属するカテゴリ：データーベース管理へ戻る
無料メルマガで学習を続ける
Linuxの実践スキルをメールで毎週お届け。
登録は30秒、解除もいつでも可。
登録無料・いつでも解除できます