バックアップ（ダンプ） MySQL

分類 1

論理バックアップ
物理バックアップ

分類 2

オンラインバックアップ： MySQL サーバーを停止しない
オフラインバックアップ： MySQL サーバーを停止する

分類 3

フルバックアップ
増分バックアップ

MySQL は差分バックアップに対応していません。

論理バックアップ

mysqldump コマンドを使用する方法を記載します。

フルバックアップ

# ストレージエンジンが InnoDB のオンラインバックアップ例
mysqldump \
  -h 127.0.0.1 \
  -P 3306 \
  -u root \
  -p \
  --single-transaction \
  --default-character-set=utf8mb4 \
  --source-data=2 \
  --routines --triggers --events \
  --hex-blob \
  --flush-logs \
  --all-databases > source.dump

// PROCESS 権限がないときは --no-tablespaces も付与

mysqldump オプション	内容
–single-transaction	一貫性のあるデータベースをバックアップ（ –lock-tables と排他的）
–lock-tables	テーブルをダンプする前にすべてロック（ –single-transaction と排他的）
–skip-lock-tables	–lock-tables を打ち消す。–single-transaction と –lock-tables は排他的なので –single-transaction を指定した場合には必要ない。
–source-data	「ポジションレプリケーションのためのソースのバイナリログファイル系とポジションを出力（Show Master STATUS の値）」 MySQL 運用・管理実践入門 p158
–dump-replica	ポジションレプリケーションのためのコマンド実行対象おレプリカのソースのバイナリログファイル名とポジションを出力（SHOW REPLICA STATUS ステートメントの値）
–no-tablespaces	後述
–flush-logs	MySQL で新しいログファイルの使用を強制的に開始する

⚠️ 注意（バージョン依存）
--source-data / --dump-replica は MySQL 8.0.26 以降に導入された名称です。それより前のバージョンでは、それぞれ旧名称の --master-data / --dump-slave を使用してください。

オンラインバックアップ

mysqldump のデフォルトはダンプ処理中の書き込み処理をロックします（通常 --lock-tables が暗黙的に有効なっています）。
そのためにタンプ処理中はデータベースに書き込みができないために（メンテナンスモードなどで）サイトを停止する必要があります。

しかしストレージエンジンが InnoDB の場合は --single-transaction オプションを付与することでサイトを停止せずにダンプすることができます。

--single-transaction は --lock-tables を使用ぜずに整合性を保ってダンプするためのオプションです
--single-transaction を使用せずに整合性を保ってダンプするには --lock-tables が必要です（通常 --lock-tables はデフォルトで有効）
–single-transaction オプションおよび –lock-tables オプションは相互に排他的です。これは、保留中のトランザクションが LOCK TABLES により暗黙的にコミットされるためです。

https://dev.mysql.com/doc/refman/8.0/ja/mysqldump.html#option_mysqldump_single-transaction
--skip-lock-tables は --lock-tables を打ち消します（ –single-transaction を指定している場合は必要ありません）

The –single-transaction option and the –lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

– man mysqldump

–no-tablespaces

MySQL 5.7.31以降から、--no-tablespaces も必要になりました。

mysqldump には、ダンプされたテーブルに対する SELECT 以上の権限、ダンプされたビューに対する SHOW VIEW、ダンプされたトリガーに対する TRIGGER、–single-transaction オプションが使用されていない場合 LOCK TABLES、および (MySQL 8.0.21 時点で)–no-tablespaces オプションが使用されなければ、PROCESS が必要です。

ref. https://dev.mysql.com/doc/refman/8.0/ja/mysqldump.html

MySQL 5.7.31以降では –no-tablespaces オプションが使用されなければ、PROCESS が必要で満たさない場合は以下エラーが発生します

Access denied; you need (at least one of) the PROCESS privilege(s) for this operation’ when trying to dump tablespaces
根本的には PROCESS 権限を付与して解消することが望ましいが、レンタルサーバなどの権限付与ができない場合は --no-tablespaces を付与します

例えばさくらのレンタルサーバでは PROCESS 権限を付与することはできませんでした。

> GRANT PROCESS ON *.* TO 'example';
ERROR 1045 (28000): Access denied for user 'example'@'%' (using password: YES)

–no-tablespaces を付けて良いか

以下リンクを読む限り tablespace は DB の論理情報と物理データを結びつける情報を格納しているようなので、新サーバでリストアする時には必要はなさそう。よって --no-tablespaces を付けても良さそう。

ref. https://anothercoffee.net/how-to-fix-the-mysqldump-access-denied-process-privilege-error/

リストア

$ mysql -h  -P 3306 -u root -p < source.dump