MariaDB講座 / mariadb-dumpでInnoDBをバックアップする

MariaDB講座

mariadb-dumpは定義と行を再作成するSQLを出力する論理バックアップツールです。整合性の条件、出力先とエラーを確認し、別のDBへ復元して初めて利用できるか判断します。ファイルがあるだけでは十分ではありません。

範囲と復旧目標を決める

対象はbackup46_labの四つのInnoDB表で、全インスタンスのアカウント、設定や外部ファイルを保存する例ではありません。許容損失と復旧時間に応じて頻度、保存先、追加ログを定めます。Dockerボリュームも別バックアップを不要にはしません。

作成したSQLを別の空DBで復元し、構造、行、制約と業務額を照合します。元DBをファイル作成だけで削除しないでください。

独立したバックアップ元を作る

管理者でSHOW DATABASESを読み、backup46_labが未使用と確認します。存在すれば別名を選び、既存データを上書きしません。

CREATE DATABASE backup46_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE backup46_lab;
CREATE TABLE c46_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c46_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c46_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c46_order_customer FOREIGN KEY (customer_id)
  REFERENCES c46_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c46_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c46_item_order FOREIGN KEY (order_id)
  REFERENCES c46_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c46_item_product FOREIGN KEY (product_id)
  REFERENCES c46_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c46_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c46_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c46_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c46_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

顧客名と都市、商品名は原文の韓国語サンプルを保っています。商品1～4はノート、ペン、マグカップ、ライト、顧客1・3はソウル、2は釜山、4は大田です。UTF-8で保存・接続し、IDと数値で結果を照合します。

shopcourseの既存権限は新DBへ及ばないため、管理者が実習範囲だけを与えます。

GRANT ALL PRIVILEGES ON backup46_lab.* TO 'course_user'@'127.0.0.1';

このALLは使い捨て実習DBに限ります。本番のバックアップ用は対象とオプションに合う権限を選びます。

ツールと新規ファイル、終了状態を確認する

端末で実行ファイルを確認します。

mariadb-dump --version
mariadb --version

Windows PowerShellでは--result-fileで出力し、リダイレクトの文字コード差を避けます。

New-Item -ItemType Directory -Path 'C:/mariadb-lab' -Force | Out-Null
$backup46Path='C:/mariadb-lab/backup46-first.sql'
if (Test-Path -LiteralPath $backup46Path) { throw 'Choose a new backup filename.' }
mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --default-character-set=utf8mb4 --single-transaction --quick --skip-lock-tables --result-file=C:/mariadb-lab/backup46-first.sql backup46_lab
if ($LASTEXITCODE -ne 0) { throw 'Backup failed: inspect the error and verify the file.' }
Get-Item -LiteralPath $backup46Path | Select-Object Name,Length
Get-FileHash -LiteralPath $backup46Path -Algorithm SHA256

パスワードは求められて入力します。失敗時は空や途中ファイルが残ることがあり、サイズやSHA-256があっても復元可能の証明にはなりません。既存先なら新しい名前を選びます。

Ubuntuでは制限した新規出力を使う

Windows版との代替で、両方を実行する必要はありません。サブシェル内だけでumaskと上書き防止を使います。

mkdir -p "$HOME/mariadb-lab"
(
  umask 077
  set -C
  backup_file="$HOME/mariadb-lab/backup46-first.sql"
  if mariadb-dump --protocol=tcp --host=127.0.0.1 --port=3306 \
      --user=course_user --password --default-character-set=utf8mb4 \
      --single-transaction --quick --skip-lock-tables backup46_lab > "$backup_file"; then
    test -s "$backup_file" || exit 1
    sha256sum "$backup_file"
  else
    printf '%s\n' 'Backup failed; preserve the partial file for diagnosis.' >&2
    exit 1
  fi
)

失敗したファイルを復元せず、原因を調べ新しい名前で再試行します。TLSエラーなら版、証明書の名前、信頼CAと必要なクライアント証明書を確認し、バックアップ・復元クライアントにも適切な設定を適用します。検証無効化を習慣的に使いません。

一貫したスナップショットの条件

--single-transactionはInnoDBなどのトランザクション表の一貫性読み取りに対応します。同時のALTER、CREATE、DROP、RENAME、TRUNCATEは前提を妨げ得るため調整し、非トランザクション表にも同じ保証が生じるとは考えません。

--quickは行を逐次読みます。--skip-lock-tablesはこの表ロックの選択を明示しますが、負荷・ロック・通信への影響がゼロという意味ではありません。別名へ復元するため、この命令は--databasesを使いません。

オブジェクトと保護、照合値を確認する

この実習にはビュー、ルーチン、トリガー、イベントはありません。本番は一覧を作り、--routines、--events、トリガーの実出力、必要権限とDEFINERを確認します。オプション名だけで依存が全部保存されたとは思いません。

SQLには業務データも含むため、アクセス制限、暗号化、保持と別障害領域のコピーを用意します。同じディスクの一ファイルだけではディスク喪失へ対応できず、公開Web領域やリポジトリへ置きません。元の件数は4・4・4・6、paid商品合計は39000です。

SELECT SUM(i.quantity*i.unit_price) AS paid_goods
FROM backup46_lab.c46_orders o
JOIN backup46_lab.c46_items i ON i.order_id=o.order_id
WHERE o.status='paid';

元データとファイルは、別DBでの検証が完了するまで保持します。