mysqldumpのバックアップとリストア手順と主なオプション

mysqldump は、MySQL のデータベースを CREATE TABLE と INSERT が並んだ SQL ファイルに書き出すコマンドです。そのファイルを mysql コマンドに流しこめば、別のサーバーや別のデータベースに同じ中身を戻せます。通販サイトのデータベースを例に、バックアップの取り方、–single-transaction などのよく使うオプション、リストアと中身の確かめ方を順に試します。バックアップ用ユーザーに渡す権限と、ストアドプロシージャが抜ける・PowerShell で文字化けするといった落とし穴も扱います。

記事の SQL と、ダンプファイルの主な部分は MySQL 8.0 で実際に実行したものです。

このシナリオで考える

shop データベースに、顧客の customers と注文の orders の2つのテーブルがあります。月ごとの売上を返すストアドプロシージャ monthly_sales も1つ作っておきます。この shop を丸ごとバックアップし、shop_restore という別のデータベースに戻すのが目標です。

CREATE DATABASE shop;
USE shop;

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  amount INT NOT NULL,
  ordered_at DATETIME NOT NULL
);

INSERT INTO customers VALUES
  (1, '佐藤', 'sato@example.com'),
  (2, '鈴木', 'suzuki@example.com');

INSERT INTO orders (customer_id, amount, ordered_at) VALUES
  (1, 3200, '2026-11-01 10:00:00'),
  (2, 1800, '2026-11-02 15:30:00'),
  (1, 5400, '2026-11-05 09:15:00');

DELIMITER //
CREATE PROCEDURE monthly_sales(IN ym CHAR(7))
BEGIN
  SELECT SUM(amount) AS total
  FROM orders
  WHERE DATE_FORMAT(ordered_at, '%Y-%m') = ym;
END//
DELIMITER ;

mysqldumpでバックアップを取る

mysqldump は SQL 文ではなく、OS のコマンドラインで実行するプログラムです。MySQL を入れると、mysql コマンドと一緒に入ります。いちばん短い形は、データベース名を渡して、出力をファイルに書き出す形です。

mysqldump -u backup -p shop > shop.sql
Enter password:

-p のあとにパスワードを続けて書かず、聞かれてから打ちます。理由は落とし穴の節で説明します。何を渡すかで、書き出される範囲が変わります。

書き方	書き出す範囲	CREATE DATABASE と USE
mysqldump shop	shop の全テーブル	入らない
mysqldump shop orders	shop の orders だけ	入らない
mysqldump –databases shop sales	shop と sales	入る
mysqldump –all-databases	サーバーのすべてのデータベース	入る

データベース名だけを渡した形では、ダンプに CREATE DATABASE と USE が入りません。戻すときは、戻し先のデータベースを自分で指定します。名前の違うデータベースに戻したいときは、この形が向いています。

shop.sql を開くと、テーブルごとに次のような SQL が並んでいます。コメントや、/*!40101 … */ で囲まれた設定の行を省き、orders の部分だけを抜き出しました。

DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
  `id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `amount` int NOT NULL,
  `ordered_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

LOCK TABLES `orders` WRITE;
INSERT INTO `orders` VALUES (1,1,3200,'2026-11-01 10:00:00'),(2,2,1800,'2026-11-02 15:30:00'),(3,1,5400,'2026-11-05 09:15:00');
UNLOCK TABLES;

先頭に DROP TABLE IF EXISTS があるので、戻し先に同じ名前のテーブルがあれば、消してから作り直します。INSERT は複数の行を1文にまとめた形です。AUTO_INCREMENT の今の値も CREATE TABLE の中に残ります。

よく使うオプション

実際の運用では、データベース名のほかにいくつかオプションを足します。よく使うものを表にまとめました。

オプション	意味	既定
–single-transaction	トランザクションの中で読み、ロックせずにある時点のデータを取る	無効
–routines（-R）	ストアドプロシージャと関数も書き出す	無効
–events（-E）	イベント（決まった時刻に動く処理）も書き出す	無効
–triggers	トリガーも書き出す	有効
–no-data（-d）	テーブルの定義だけを書き出し、データは出さない	無効
–where（-w）	条件に合う行だけを書き出す	なし
–no-tablespaces	テーブルスペースの情報を出さない（PROCESS 権限が要らなくなる）	無効
–source-data	バイナリログの位置を書きこむ（8.0.26 より前は –master-data）	無効
–result-file（-r）	出力先のファイルを指定する	なし

表にない –opt も既定で有効です。DROP TABLE の追加、複数行の INSERT、テーブルのロック（–lock-tables）などをまとめて有効にするオプションで、上のダンプの形はこれで決まっています。InnoDB のテーブルを使っているなら、次の形を基本にするとよいでしょう。

mysqldump -u backup -p --single-transaction --routines --events shop > shop.sql

–where は、11月分の注文だけを検証用の環境に持っていきたいときなどに使えます。テーブル名を続けて書き、条件をダブルクォートで囲みます。

mysqldump -u backup -p shop orders --where="ordered_at >= '2026-11-01'" > orders_nov.sql

–single-transactionでアプリを止めずに取る

–single-transaction を付けると、mysqldump は分離レベルを REPEATABLE READ にしてトランザクションを始め、その中で全テーブルを読みます。読んでいる途中でほかの接続が注文を追加しても、ダンプに入るのは始めた時点のデータだけです。テーブルをロックしないので、アプリを動かしたままバックアップを取れます。

付けない場合は、–opt に含まれる –lock-tables が、書き出すテーブルを読み取りロックします。ロックの間、アプリの書き込みは待たされます。–single-transaction には次の注意点があります。

• 効くのは InnoDB のようにトランザクションが使えるテーブルだけです。MyISAM のテーブルは、読んでいる間にも中身が変わります
• ダンプの途中で、ほかの接続から ALTER TABLE・CREATE TABLE・DROP TABLE・RENAME TABLE・TRUNCATE TABLE を実行しないでください。ダンプの中身が食い違うことがあります
• –lock-tables とは一緒に使えません。LOCK TABLES を実行すると、始めたトランザクションが確定してしまうためです

REPEATABLE READ で途中の変更が見えない理由は「MySQLのトランザクション分離レベル：ACID・異常現象を整理」で説明しています。

リストアして中身を確かめる

データベース名だけで取ったダンプは、戻し先のデータベースを先に作ってから流しこみます。

mysql -u root -p -e "CREATE DATABASE shop_restore"
mysql -u root -p shop_restore < shop.sql

mysql にログインしたあとなら、SOURCE でもファイルを読みこめます。

USE shop_restore;
SOURCE shop.sql;

戻したら、件数と合計を元のデータベースと比べます。CHECKSUM TABLE を使うと、テーブルの中身から計算した値を並べて比べられます。

SELECT 'shop' AS db, COUNT(*) AS cnt, SUM(amount) AS total FROM shop.orders
UNION ALL
SELECT 'shop_restore', COUNT(*), SUM(amount) FROM shop_restore.orders;

CHECKSUM TABLE shop.orders, shop_restore.orders;

+--------------+-----+-------+
| db           | cnt | total |
+--------------+-----+-------+
| shop         |   3 | 10400 |
| shop_restore |   3 | 10400 |
+--------------+-----+-------+

+---------------------+------------+
| Table               | Checksum   |
+---------------------+------------+
| shop.orders         | 1824792002 |
| shop_restore.orders | 1824792002 |
+---------------------+------------+

件数・合計・チェックサムがそろっていれば、中身は同じです。チェックサムは同じバージョンの MySQL で、同じ定義のテーブルどうしを比べたときに使えます。全行を読むので、大きなテーブルでは時間がかかります。

AUTO_INCREMENT の値も CREATE TABLE ごと戻るので、戻したあとに追加した行は続きの番号になります。

INSERT INTO shop_restore.orders (customer_id, amount, ordered_at)
VALUES (2, 900, '2026-11-08 12:00:00');
SELECT id FROM shop_restore.orders ORDER BY id DESC LIMIT 1;

+----+
| id |
+----+
|  4 |
+----+

ファイルが大きいときは、gzip で圧縮しながら書き出し、戻すときに展開しながら流しこめます。

mysqldump -u backup -p --single-transaction --routines --events shop | gzip > shop.sql.gz
gunzip < shop.sql.gz | mysql -u root -p shop_restore

バックアップ用のユーザーを作る

バックアップに root を使う必要はありません。書き出しに必要な権限だけを持つユーザーを用意します。

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'Backup#Pass_2026';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS ON *.* TO 'backup'@'localhost';
SHOW GRANTS FOR 'backup'@'localhost';

+----------------------------------------------------------------------------------------------+
| Grants for backup@localhost                                                                  |
+----------------------------------------------------------------------------------------------+
| GRANT SELECT, PROCESS, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO `backup`@`localhost` |
+----------------------------------------------------------------------------------------------+

それぞれの権限が要る場面は次のとおりです。

権限	要る場面
SELECT	テーブルを読む。–routines を付けるときは *.* に対して必要
SHOW VIEW	ビューを書き出す
TRIGGER	トリガーを書き出す
LOCK TABLES	–single-transaction を付けないとき
EVENT	–events を付けるとき
PROCESS	MySQL 8.0.21 以降で、–no-tablespaces を付けないとき

PROCESS は、ほかの接続が実行中の SQL まで見られる権限です。渡したくなければ、mysqldump に –no-tablespaces を付けてください。–source-data でバイナリログの位置を書きこむときは、RELOAD 権限も要ります。GRANT の書き方は「MySQLのユーザーと権限：GRANTで最小権限にする方法」で説明しています。

よくある落とし穴

–routines を付けずに取ると、ストアドプロシージャと関数はダンプに入りません。トリガーは何も付けなくても入るので、同じ扱いだと思いこみがちです。戻したあとに information_schema.ROUTINES を見れば、抜けに気づけます。

SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA IN ('shop', 'shop_restore');

+----------------+---------------+--------------+
| ROUTINE_SCHEMA | ROUTINE_NAME  | ROUTINE_TYPE |
+----------------+---------------+--------------+
| shop           | monthly_sales | PROCEDURE    |
+----------------+---------------+--------------+

shop にある monthly_sales が、shop_restore にはありません。イベントも同じで、–events を付けないと書き出されません。

Windows の PowerShell で > を使って書き出すと、ファイルが UTF-16 で保存されます。MySQL は UTF-16 のファイルを正しく読みこめないので、このファイルからは戻せません。PowerShell では –result-file で出力先を指定してください。

mysqldump -u backup -p --single-transaction --routines --events --result-file=shop.sql shop

PowerShell では < も使えません。戻すときは cmd.exe /c “mysql -u root -p shop_restore < shop.sql” のように cmd.exe を通すか、mysql にログインして SOURCE を使います。

-p のすぐあとにパスワードを書くと、次の警告が出ます。コマンドの履歴や、ほかのユーザーが見られるプロセスの一覧にパスワードが残るためです。

mysqldump: [Warning] Using a password on the command line interface can be insecure.

cron などで自動で取るときは、オプションファイル（~/.my.cnf など）や mysql_config_editor にパスワードを保存し、ファイルを読めるのを本人だけに絞ります。ほかにも次の点に気をつけてください。

• データが大きくなるほど、戻すのに時間がかかります。SQL を1文ずつ流しこみ、インデックスも作り直すためです。公式マニュアルは、大きなデータには MySQL Shell のダンプ機能（並列で書き出せる）や、ファイルごと写す物理バックアップを勧めています
• GTID を有効にしたサーバーで取ると、ダンプに SET @@GLOBAL.gtid_purged の行が入ります。ほかのデータが入ったサーバーに戻すとエラーになることがあるので、ただのコピーなら –set-gtid-purged=OFF を付けて取ります
• バックアップは、戻せるかどうかを確かめて初めて使えます。月に1回など日を決めて検証用のサーバーに戻し、件数を比べておくと、ファイルが壊れていた・プロシージャが抜けていた、といった問題に早く気づけます

まとめ

mysqldump は、データベースを SQL ファイルに書き出し、mysql コマンドで戻すための道具です。InnoDB なら –single-transaction を付けてアプリを止めずに取り、ストアドプロシージャやイベントがあれば –routines と –events を足します。データベース名だけで取ったダンプは、戻し先を先に作ってから流しこみます。戻したら件数と CHECKSUM TABLE で元と比べてください。バックアップには、読み取りに必要な権限だけを持つユーザーを使うと安心です。

参考リンク

• MySQL 8.0 Reference Manual: mysqldump — A Database Backup Program
• MySQL 8.0 Reference Manual: Dumping Data in SQL Format with mysqldump
• MySQL 8.0 Reference Manual: Reloading SQL-Format Backups
• MySQL 8.0 Reference Manual: CHECKSUM TABLE Statement

アイキャッチ画像: Photo by Kelli McClintock on Unsplash