mysqldumpバックアップとリストア｜オプション一覧と自動化スクリプト

mysqldumpとは、MySQLのデータベースを CREATE TABLE と INSERT のSQL文として書き出す標準のバックアップツールです。 書き出したファイルを mysql コマンドに流し込めば、同じ状態に戻せます（リストア）。InnoDBなら --single-transaction を付けるだけで、サービスを止めずに一貫したバックアップが取れます。

この記事では、MySQL 8.0.46 で実際に実行して、用途別のオプションの組み合わせとダンプの中身、書き込み中にダンプしたときの一貫性（2セッションで検証）、よく出る警告・エラーの対処、gzip圧縮＋世代管理のbashスクリプトとリストア後の件数照合までを結果つきで紹介します。

MySQL 8.0 は2026年4月にサポートが終了しています。この記事の手順は MySQL 8.4 LTS／9.7 でも同じコマンドで使えます。8.4 で変わった点（mysqlpump の削除、--master-data から --source-data への置き換えなど）は「8.4／9.7 で変わったこと」にまとめました。検証は手元の 8.0 で行っています。

mysqldumpの早見表

検証環境とサンプルDB

• MySQL 8.0.46（Ubuntu 24.04 パッケージ）、mysqldump Ver 8.0.46
• サンプルDB app：テーブル3つ（members・orders 1,000件・order_log）、ビュー1つ、関数・プロシージャ・トリガー・イベントを1つずつ

CREATE DATABASE app CHARACTER SET utf8mb4;
USE app;
CREATE TABLE members (id INT PRIMARY KEY, name VARCHAR(30), created_at DATE);
CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY, member_id INT, item VARCHAR(30),
                     qty INT, ordered_at DATETIME, KEY (member_id));
CREATE TABLE order_log (id INT AUTO_INCREMENT PRIMARY KEY, order_id INT, logged_at DATETIME);
CREATE VIEW member_totals AS SELECT member_id, SUM(qty) AS total_qty FROM orders GROUP BY member_id;
CREATE FUNCTION tax_in(p INT) RETURNS INT DETERMINISTIC RETURN FLOOR(p * 1.1);
CREATE PROCEDURE recent_orders(IN n INT) SELECT * FROM orders ORDER BY id DESC LIMIT n;
CREATE TRIGGER trg_order_log AFTER INSERT ON orders FOR EACH ROW
  INSERT INTO order_log(order_id, logged_at) VALUES (NEW.id, NOW());
CREATE EVENT ev_purge_log ON SCHEDULE EVERY 1 DAY
  DO DELETE FROM order_log WHERE logged_at < NOW() - INTERVAL 30 DAY;

接続オプション（-u -p -h -P、--login-path など）は mysql コマンドと共通です。以下のコマンド例では省略しています。

用途別オプションの組み合わせと、ダンプに入るもの

同じDBを8通りのオプションでダンプし、ファイルに含まれる文を数えました。

（ビューはどのDB単位のダンプにも含まれました。INSERT は orders と members の2つで、空の order_log は INSERT なしです）

表から読み取れる要点は次の3つです。

• 何も付けないと、トリガーは入るが関数・プロシージャ・イベントは入りません。 既定値が triggers=TRUE、routines=FALSE、events=FALSE だからです（mysqldump --help の末尾で確認できます）。DBを丸ごと戻したいときは --routines --events を必ず付けます。
• --databases を付けると CREATE DATABASE と USE が入ります。 付けないダンプは、リストア時にDB名の指定が必要です。
• --all-databases には mysql システムDB（ユーザーと権限）も含まれます。 サーバーごと移すとき向けで、1つのアプリのDBだけなら --databases が扱いやすいです。

ダンプの中身を読む

--single-transaction --routines --events のダンプ（#2）の主な部分です。

-- MySQL dump 10.13  Distrib 8.0.46, for Linux (x86_64)
--
-- Host: localhost    Database: app
-- ------------------------------------------------------
-- Server version   8.0.46-0ubuntu0.24.04.4

/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
...
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  ...
) ENGINE=InnoDB AUTO_INCREMENT=1024 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

LOCK TABLES `orders` WRITE;
/*!40000 ALTER TABLE `orders` DISABLE KEYS */;
INSERT INTO `orders` VALUES (1,2,'商品1',2,'2026-09-01 00:01:00'),(2,3,'商品2',3,'2026-09-01 00:02:00'),...;
/*!40000 ALTER TABLE `orders` ENABLE KEYS */;
UNLOCK TABLES;
...
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `trg_order_log` AFTER INSERT ON `orders` ...
DELIMITER ;
...
-- Dumping events for database 'app'
...
-- Dumping routines for database 'app'
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `tax_in`(p INT) RETURNS int
...
-- Dump completed on 2026-10-02  0:27:48

--where のダンプ（#6）には、条件がコメントで残ります。

-- WHERE:  ordered_at >= '2026-09-01 12:00:00'

オプション一覧（よく使うもの）

既定で有効なものは --opt（--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset）にまとまっています。 --single-transaction を付けると、--lock-tables は使われなくなります。

--single-transaction の意味を2セッションで確かめる

--single-transaction は、ダンプの最初に START TRANSACTION WITH CONSISTENT SNAPSHOT を実行し、その時点のスナップショットから全テーブルを読む仕組みです。 書き込み中でも、ダンプ開始時点の整合した状態が保存されます。

検証では、2つのテーブル stock_a・stock_b（各40万行、各行 qty=10、合計は常に 8,000,000）を用意し、別セッションで「Aから1個減らし、Bに1個足す」トランザクションを繰り返しながらダンプしました。

# mover.py：別セッションで在庫を移し続ける（合計は常に 8,000,000）
import pymysql, random, sys, time
conn = pymysql.connect(unix_socket="/tmp/t108my/mysql.sock", user="root", database="app")
end = time.time() + float(sys.argv[1]); n = 0
with conn.cursor() as cur:
    while time.time() < end:
        i = random.randint(1, 400000)
        cur.execute("UPDATE stock_a SET qty = qty - 1 WHERE id = %s", (i,))
        cur.execute("UPDATE stock_b SET qty = qty + 1 WHERE id = %s", (i,))
        conn.commit(); n += 1
print("moved", n)

python3 mover.py 6 &          # セッション1：6秒間書き込み続ける
sleep 1
mysqldump --skip-lock-tables   app stock_a stock_b > st.sql   # セッション2
# 別DBに戻して SUM(qty) の合計を確認（--single-transaction でも同じ手順）

ロックもスナップショットもないと、stock_a を読み終えてから stock_b を読むまでの間に移された149個が二重に数えられました。 --single-transaction では合計がぴったり一致しています。

既定の --lock-tables でも一致しますが、こちらはダンプ中そのDBのテーブルへの書き込みを待たせる方式です。InnoDBのテーブルなら、書き込みを止めない --single-transaction が適しています。

--single-transaction で気をつけること

よく出る警告・エラーと対処

PROCESS権限エラー（when trying to dump tablespaces）

バックアップ専用の、権限を絞ったユーザーで実行すると出ます。

mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

InnoDBだけのDBなら --no-tablespaces を付けると出なくなります。 テーブルスペース情報は、一般的なアプリのDBでは不要です。

--routines を付けたのに関数・プロシージャが入らない

SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT を app.* に付けたユーザーで --routines を付けると、エラーも警告も出ずに終了コード0で、関数・プロシージャが0件のダンプができました。

-- Dumping routines for database 'app'
--
（中身なし）

MySQL 8.0.20以降は、ルーチンの定義を読むのに SHOW_ROUTINE 権限（またはグローバルな SELECT）が必要です。 付与すると2件とも出力されました。

CREATE USER 'backup'@'localhost' IDENTIFIED BY '********';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON app.* TO 'backup'@'localhost';
GRANT SHOW_ROUTINE ON *.* TO 'backup'@'localhost';

GTID有効サーバーの警告とリストア時の ERROR 3546

gtid_mode=ON のサーバーで1つのDBだけをダンプすると、次の警告が出ます。

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

ダンプの先頭には、GTIDの設定文が入っていました。

SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '7def0173-bdab-11f1-ad0e-02fc00000001:1-2';

このダンプを、同じサーバー（GTIDが既に実行済み）の別DBへ戻すとエラーになりました。

ERROR 3546 (HY000) at line 24: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED

--set-gtid-purged=OFF で取り直したダンプは、GTID_PURGED の行が0件になり、警告も出ませんでした。

--column-statistics の ERROR 1109（未実行）

8.0 の mysqldump は column-statistics が既定で TRUE です。5.7 や MariaDB のサーバーに 8.0 以降の mysqldump で接続すると、Unknown table 'COLUMN_STATISTICS' in information_schema (1109) で止まることがあります。 その場合は --column-statistics=0 を付けます。この組み合わせは手元に古いサーバーがないため未実行です（--column-statistics の意味は公式ドキュメントのオプション説明で確認しています）。

リストア時のエラー

1046 と 1049 は、検証環境で実際に出たメッセージです。

自動化スクリプト：gzip圧縮＋世代管理

cronで毎日動かす想定のスクリプトです。pipefail で mysqldump の失敗を検知し、gzip -t と Dump completed の行で「最後まで書けたか」を確認してから正式なファイル名にします。

まず、パスワードをコマンドラインに書かないよう、接続情報を権限600のファイルに置きます。

# /etc/mysql/backup.cnf（chmod 600）
[client]
user=backup
password=********
socket=/var/run/mysqld/mysqld.sock

#!/usr/bin/env bash
# mysql_backup.sh：mysqldump + gzip + 世代管理（MySQL 8.0 / 8.4 / 9.x 共通）
set -euo pipefail

CNF="${CNF:-/etc/mysql/backup.cnf}"         # 接続情報（chmod 600）
BACKUP_DIR="${BACKUP_DIR:-/var/backups/mysql}"
DATABASES="${DATABASES:-app}"              # 空白区切りで複数可
KEEP="${KEEP:-7}"                          # 残す世代数（DBごと）
STAMP="$(date +%Y%m%d_%H%M%S)"

mkdir -p "$BACKUP_DIR"
umask 077
trap 'rm -f -- "${tmp:-}"; echo "$(date "+%F %T") NG  ${db:-?}" >&2' ERR

for db in $DATABASES; do
  out="$BACKUP_DIR/${db}_${STAMP}.sql.gz"
  tmp="$out.part"
  start=$(date +%s)

  mysqldump --defaults-extra-file="$CNF" \
    --single-transaction --quick \
    --routines --triggers --events \
    --no-tablespaces --set-gtid-purged=OFF \
    --databases "$db" | gzip -c > "$tmp"

  gzip -t "$tmp"                                    # 圧縮ファイルの破損チェック
  zcat "$tmp" | tail -n 1 | grep -q 'Dump completed' # 最後まで書けたか
  mv "$tmp" "$out"
  echo "$(date '+%F %T') OK  $db -> $out ($(du -h "$out" | cut -f1), $(( $(date +%s) - start ))s)"

  # 古い世代を削除（新しい順に並べて KEEP 件より後ろ）
  ls -1t "$BACKUP_DIR/${db}_"*.sql.gz | tail -n +"$((KEEP + 1))" | while read -r old; do
    rm -f -- "$old"
    echo "$(date '+%F %T') DEL $old"
  done
done

KEEP=3 にして5回続けて実行した結果です（パスは短くしています）。

$ for i in 1 2 3 4 5; do KEEP=3 ./mysql_backup.sh; sleep 1; done
2026-10-02 00:29:54 OK  app -> backups/app_20261002_002952.sql.gz (2.1M, 2s)
2026-10-02 00:29:56 OK  app -> backups/app_20261002_002955.sql.gz (2.1M, 1s)
2026-10-02 00:29:58 OK  app -> backups/app_20261002_002957.sql.gz (2.1M, 1s)
2026-10-02 00:30:01 OK  app -> backups/app_20261002_002959.sql.gz (2.1M, 2s)
2026-10-02 00:30:01 DEL backups/app_20261002_002952.sql.gz
2026-10-02 00:30:03 OK  app -> backups/app_20261002_003002.sql.gz (2.1M, 2s)
2026-10-02 00:30:04 DEL backups/app_20261002_002955.sql.gz
$ ls -l backups
-rw------- 1 root root 2107802 Oct  2 00:29 app_20261002_002957.sql.gz
-rw------- 1 root root 2107800 Oct  2 00:30 app_20261002_002959.sql.gz
-rw------- 1 root root 2107800 Oct  2 00:30 app_20261002_003002.sql.gz

4回目から古いものが削除され、常に3世代が残りました。 圧縮前43.8MB（80万行＋1,000行）が2.1MBになっています。umask 077 により、ファイルは所有者だけが読める -rw------- です。

パスワードを誤った設定ファイルで動かすと、途中ファイルを消して終了コード2で止まり、既存の世代は残りました。

mysqldump: Got error: 1045: Access denied for user 'backup'@'localhost' (using password: YES) when trying to connect
2026-10-02 00:30:11 NG  app

cron に登録する例です（毎日2時15分）。

15 2 * * * DATABASES="app blog" KEEP=14 /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

リストアして件数を照合する

バックアップは「戻せることを確かめて」はじめて安心できます。 最新のファイルを別名DB（app_verify）に戻し、テーブルごとの件数を元DBと比べるスクリプトです。

#!/usr/bin/env bash
# verify_restore.sh：最新のバックアップを別名DBに戻し、件数を元DBと照合する
set -euo pipefail
SRC_DB="${1:-app}"; DST_DB="${SRC_DB}_verify"
FILE="$(ls -1t "$BACKUP_DIR/${SRC_DB}_"*.sql.gz | head -n 1)"
MYSQL="mysql --defaults-extra-file=$ADMIN_CNF -N -B"

$MYSQL -e "DROP DATABASE IF EXISTS \`$DST_DB\`; CREATE DATABASE \`$DST_DB\`"
# --databases 付きダンプは USE 文を含むので、DB名を置き換えて流し込む
time (zcat "$FILE" | sed -e "s/^USE \`$SRC_DB\`;/USE \`$DST_DB\`;/" \
                         -e "/^CREATE DATABASE /d" | $MYSQL)

ng=0
for t in $($MYSQL -e "SELECT TABLE_NAME FROM information_schema.TABLES
                      WHERE TABLE_SCHEMA='$SRC_DB' AND TABLE_TYPE='BASE TABLE' ORDER BY 1"); do
  a=$($MYSQL -e "SELECT COUNT(*) FROM \`$SRC_DB\`.\`$t\`")
  b=$($MYSQL -e "SELECT COUNT(*) FROM \`$DST_DB\`.\`$t\`")
  [ "$a" = "$b" ] && r=OK || { r=NG; ng=1; }
  printf '%-10s %8s %8s %s\n' "$t" "$a" "$b" "$r"
done
exit $ng

$ BACKUP_DIR=./backups ADMIN_CNF=./admin.cnf ./verify_restore.sh app

real    0m6.734s
user    0m0.422s
sys 0m0.090s
members           4        4 OK
order_log         0        0 OK
orders         1000     1000 OK
stock_a      400000   400000 OK
stock_b      400000   400000 OK

戻した app_verify には、関数・プロシージャ2件とイベント1件も入っていました。件数に加えて、中身まで比べたいときは CHECKSUM TABLE app.orders, app_verify.orders; が使えます（検証では両方 1742228606 で一致）。

戻し先の app_verify にはイベント ev_purge_log も作られ、スケジュールどおり動きます。検証用DBでは、確認後に DROP DATABASE するか SET GLOBAL event_scheduler=OFF の環境で行うと安心です。

8.4／9.7 で変わったこと

cronやスクリプトに --master-data や mysqlpump が残っていたら、8.4へ移る前に --source-data・mysqldump に書き換えておくとスムーズです。8.0 の mysqlpump は、実行すると次の表示が出ました。

WARNING: mysqlpump is deprecated and will be removed in a future version. Use mysqldump instead.

MySQL Shell の dump utility（概要）

数十GB以上のDBでは、MySQL Shell の util.dumpInstance()／util.dumpSchemas()／util.dumpTables() と util.loadDump() が選択肢になります。公式ドキュメントでは、複数スレッドでの並列ダンプ・圧縮・進捗表示に対応すると説明されています。出力はSQL1ファイルではなくディレクトリ形式です（この記事では未実行）。

PostgreSQL の pg_dump と比べると、mysqldump は SQLテキスト形式のみ・並列なし という違いがあります（pg_dump の使い方は別の記事で扱っています）。

よくある質問

Q. mysqldump 中にサービスは止まりますか？ A. InnoDBのテーブルで --single-transaction を付ければ、読み書きは続けられます。ダンプ中の ALTER TABLE などのDDLだけは避けます。

Q. 1つのテーブルだけ戻すことはできますか？ A. 最初から mysqldump app orders > orders.sql のようにテーブル単位で取っておくのが簡単です。DB全体のダンプから取り出す場合は、-- Table structure for table の見出しを目印に該当部分を切り出します。

Q. 文字化けを防ぐには？ A. 8.0 の mysqldump は既定で SET NAMES utf8mb4 をダンプに書きます。Windows の PowerShell で > を使うとUTF-16で保存されることがあるため、--result-file=app.sql でファイルに直接書くのがおすすめです。

Q. どのくらいの頻度・世代で取ればよいですか？ A. 「どの時点まで戻せればよいか」で決めます。1日1回のダンプなら最大1日分の更新が戻せない範囲になるため、それより細かく戻したい場合はバイナリログと組み合わせます（--source-data=2 でダンプ時点のログ位置を記録できます）。

まとめ

• 基本形は mysqldump --single-transaction --routines --events --databases app | gzip
• 既定では関数・プロシージャ・イベントは入らないので --routines --events を付ける
• --single-transaction は書き込み中でも整合したダンプになる（検証で合計が一致、ロックなしでは149ずれた）
• 権限を絞ったユーザーは --no-tablespaces と SHOW_ROUTINE 権限を用意する
• GTID有効サーバーから別環境へ戻すなら --set-gtid-purged=OFF
• 自動化は pipefail・gzip -t・Dump completed の確認・世代削除をセットに
• 8.4では mysqlpump が削除、--master-data は --source-data へ。リストア検証で件数を照合しておく

株式会社RJCでは、データベースのバックアップと復元の手順を仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。