この記事のポイント

・mysqldump は MySQL/MariaDB 標準のダンプツールで追加インストール不要
・フルバックアップには --single-transaction と --events オプションが安全
・リストアは mysql コマンドでリダイレクト入力するだけでできる
・cronと圧縮を組み合わせた自動バックアップが実務の基本形

# フルバックアップ（推奨オプション付き）
$ mysqldump -u root -p \
  --single-transaction \
  --routines \
  --events \
  mydb > /backup/mydb_$(date +%Y%m%d).sql

Enter password: （パスワードを入力）

-- MySQL dump 10.13  Distrib 8.0.36, for Linux (x86_64)
--
-- Host: localhost    Database: mydb
-- ------------------------------------------------------
-- Server version   8.0.36

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
...

# users テーブルと orders テーブルだけダンプ
$ mysqldump -u root -p mydb users orders > /backup/mydb_partial.sql

# 全 DB をダンプ（--single-transaction は InnoDB のみ有効）
$ mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --events \
  > /backup/all_databases_$(date +%Y%m%d).sql

# ダンプと同時に gzip 圧縮
$ mysqldump -u root -p --single-transaction --routines --events mydb \
  | gzip > /backup/mydb_$(date +%Y%m%d).sql.gz

# 圧縮ファイルのサイズ確認
$ ls -lh /backup/mydb_*.sql.gz
-rw-r--r--. 1 root root 4.2M May 29 13:00 /backup/mydb_20260529.sql.gz

# 事前にデータベースを作成しておく（必要な場合）
mysql -u root -p -e "CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# リストア実行
mysql -u root -p mydb < /backup/mydb_20260529.sql

# gunzip と mysql をパイプで接続してリストア
$ gunzip < /backup/mydb_20260529.sql.gz | mysql -u root -p mydb

# テーブル一覧を確認
mysql -u root -p mydb -e "SHOW TABLES;"

# レコード数を確認（例: users テーブル）
mysql -u root -p mydb -e "SELECT COUNT(*) FROM users;"

# /root/.my.cnf を作成（root でバックアップする場合）
$ cat > /root/.my.cnf
[mysqldump]
user=backup_user
password=YourSecurePassword

# 他ユーザーに読まれないようにパーミッションを設定
$ chmod 600 /root/.my.cnf

# /usr/local/bin/mysql_backup.sh
#!/bin/bash
set -euo pipefail

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydb"
KEEP_DAYS=14

# バックアップディレクトリを作成
mkdir -p "${BACKUP_DIR}"

# ダンプ実行（~/.my.cnf の認証情報を使用）
mysqldump \
  --single-transaction \
  --routines \
  --events \
  "${DB_NAME}" \
  | gzip > "${BACKUP_DIR}/${DB_NAME}_${DATE}.sql.gz"

# 古いバックアップを削除（14日以上前のファイル）
find "${BACKUP_DIR}" -name "*.sql.gz" -mtime +${KEEP_DAYS} -delete

echo "[$(date)] Backup completed: ${DB_NAME}_${DATE}.sql.gz"

# スクリプトに実行権限を付与
$ chmod 755 /usr/local/bin/mysql_backup.sh

# 動作確認
$ /usr/local/bin/mysql_backup.sh
[2026-05-29 03:00:01] Backup completed: mydb_20260529_030001.sql.gz

# root の crontab を編集
$ crontab -e

# 毎日 3:00 に実行（追加する行）
0 3 * * * /usr/local/bin/mysql_backup.sh ＞＞ /var/log/mysql_backup.log 2>&1

# テーブル構造のみ（INSERT 文を含めない）
$ mysqldump -u root -p --no-data mydb > /backup/mydb_schema.sql

# INSERT 文のみ（CREATE TABLE を含めない）
$ mysqldump -u root -p --no-create-info mydb > /backup/mydb_data.sql

# UTF-8 を明示（utf8mb4 推奨）
$ mysqldump -u root -p \
  --default-character-set=utf8mb4 \
  --single-transaction \
  mydb > /backup/mydb_utf8mb4.sql

# リモートサーバー（db01.example.com）のDBをダンプ
$ mysqldump -u dbuser -p -h db01.example.com \
  --single-transaction mydb > /backup/remote_mydb.sql

# SSH ポートフォワード経由（3307 をローカルにマッピング済みの場合）
$ mysqldump -u dbuser -p -h 127.0.0.1 -P 3307 \
  --single-transaction mydb > /backup/remote_mydb.sql

# エラー例
mysqldump: Got error: 1045: Access denied for user 'backup'@'localhost' ...

# 対処：バックアップ用ユーザーに必要な権限を付与
mysql -u root -p -e "
  GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON *.*
  TO 'backup'@'localhost';
  FLUSH PRIVILEGES;
"

# MariaDB での対処
mysql -u root -p -e "
  GRANT RELOAD ON *.* TO 'backup'@'localhost';
  FLUSH PRIVILEGES;
"

# max_allowed_packet を増やしてダンプ
$ mysqldump -u root -p \
  --max-allowed-packet=256M \
  --single-transaction mydb > /backup/mydb.sql

# MyISAM テーブルを含む場合（書き込みロックが発生）
$ mysqldump -u root -p --lock-tables mydb > /backup/mydb.sql

# InnoDB のみなら --lock-tables=false で明示的に無効化
$ mysqldump -u root -p \
  --single-transaction \
  --lock-tables=false \
  mydb > /backup/mydb.sql