Go to Qiita Advent Calendar 2024 Top
LoginSignup
94
96

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@Tocyuki

MySQL入門　バックアップ編

Last updated at Posted at 2016-02-26

#経緯
とある勉強会の内容の復習＆整理

#タイトル

#バックアップ編アジェンダ

  1. バックアップ手法の概要
  2. バックアップ運用設計時の主な考慮事項
  3. バックアップ対象のファイル
  4. バックアップリストア例

#1. バックアップ手法の概要
##1.1. バックアップ手法の違い

  • ホットバックアップ or コールドバックアップ
  • 物理バックアップ or 論理バックアップ
  • フルバックアップ or 差分/増分バックアップ
  • スタンバイサイトを利用

－　ホットスワップとしての利用
－　バックアップ取得先としての利用

##1.2. ホットバックアップ or コールドバックアップ
###1.2.1. ホットバックアップ（オンラインバックアップ）の例

  1. トランザクションの仕組みを利用してバックアップをする
  • mysqldumpでInnoDBテーブルをバックアップする
  1. ロックを利用してバックアップを取得
  • mysqldumpでMyISAMテーブルをバックアップする
  1. OSやハードウェアのスナップショットを利用する
  1. 独自の方法でバックアップを取得する

###1.2.2. コールドバックアップ（オフラインバックアップ）の例

  • MySQLサーバをシャットダウンして、データディレクトリ以下のディレクトリとファイルを全てOSのコマンドでコピー

##1.3. 物理バックアップ or 論理バックアップ
###1.3.1. 物理バックアップ

  • 物理的なファイルのバックアップ
  • OSファイルのコピー、MySQL Enterprise Backup等で取得可能

利点
・最小限のサイズで取得できる
・バックアップ/リストアの速度が早い

欠点
・バックアップ/リストアの単位はツール次第
・異機種間、バージョン間で互換性が取れない場合がある（※）

※注意事項
・テーブル名に日本語文字を使用している場合
・浮動小数点が混じっている場合
・テーブル名に大文字/小文字が混在している場合

###1.3.2. 論理バックアップ

  • データベースからデータを抜き出してバックアップする
  • MySQLの場合は、SQLベースのバックアップを取得可能
  • mysqldumpで取得可能

利点
・バックアップファイルを編集できる
・移植性が高い（他のバージョン、他のRDBMS）

欠点
・物理バックアップに比べてサイズが大きくなる
・バックアップ、リストアに時間がかかる（バイナリ <---> テキストの変換が入るため）

1.4. フルバックアップ or 差分/増分バックアップ

1.4.1. フルバックアップ（全体バックアップ）

  • データベース全体をバックアップする

利点
・ リストア処理が単純（バックアップデータが1ヶ所にまとまっているため）

欠点
・ バックアップにかかる時間が長くなる（データベース全体をバックアップするため）
・ バックアップデータのサイズが大きくなる（データベースに対する更新料が少なくても、データベース全体をバックアップするため）

フルバックアップイメージ
図面1.png

1.4.2. 差分/増分バックアップ（部分バックアップ）

  • 直近のバックアップ以降に更新されたデータのみをバックアップする
  • 差分バックアップ
    • 直近のフルバックアップ以降に更新されたデータをバックアップ
  • 増分バックアップ
    • 直近のバックアップ（種別はフルとは限らない）以降に更新されたデータをバックアップする

利点
・ バックアップ時間が短くなる（更新したデータだけを対象にするため）
・ バックアップデータのサイズが小さくなる（フルバックアップと部分バックアップを使ってリストア）

欠点
・ リストア処理の手順が複雑になる（フルバックアップと部分バックアップを使ってリストア）

差分/増分バックアップイメージ
図面2.png

1.4.2.1. MySQLでの差分/増分バックアップ手法

  • バイナリログを差分バックアップとして利用する
  • MySQL Enterprise Backup（有償ツール）でInnoDBデータの増分/差分バックアップを取得する

1.5. ポイントインタイムリカバリとバイナリログ

ポイントインタイムリカバリ

  • 特定の日時の状態にデータを復旧すること（例：障害発生直前の状態まで復旧する）
  • MySQLでは、バックアップファイルとバイナリログファイルを利用して、ポイントインタイムリカバリが可能（バックアップファイルに対して、バイナリログファイルを使ってロールフォワードリカバリする）

バイナリログ

  • 発行されたクエリのうち、更新系のSQL分のみを記録しているログファイル
  • --log-binオプションを設定することで出力できる（デフォルトでは出力されない）
  • コミット時にバイナリログに同期書き込みするためには、sync_binlog=1を設定する

※バックアップを取得しているだけでは、バックアップ取得時点のデータしか復旧できないが、バイナリログを出力してリカバリに利用することで、特定時点のデータを復旧可能

1.6. スタンバイサイトの利用

  • レプリケーション機能を利用して、スタンバイサイトを構築しておき、障害発生時はフェイルオーバする

利点
・復旧時間が短い

欠点
・復旧ポイントは障害発生直前の状態のみ（※）
・人的ミスに対する対策にはならない（※）
※MySQL5.6では、遅延レプリケーション機能で、意図的にスタンバイサイトを遅延させることも可能

  • スタンバイサイトをバックアップ取得先として利用することも可能（本番環境に影響を与えずにバックアップ取得可能）

1.7. レプリケーション

  • MySQLの標準機能
    • シンプルな設定で利用可能
    • 多数のWebサイトで実績あり
  • 同期方式：非同期 or 準同期
  • 特徴
    • 参照性能を向上させる構成
    • バックアップ用途でも利用可能
    • バイナリログを利用して、更新内容をスレーブに伝搬

図面3.png

2. バックアップ運用設計時の主な考慮事項

2.1. RTO(Recoverry Time Objective)：目標復旧時間

  • 障害発生時に「いつまでに復旧するか」という目標時間
例）データベースを1時間以内に復旧する必要がある
バックアップのリストア（再配置）＋リカバリ（復旧）が1時間以内に終わる手法を採用する必要がある

2.2. RPO(Recovery Point Objective)：目標復旧時点

  • 障害発生時に「どの時点までのデータを復旧する必要があるのか」という目標時点
例）前日のバックアップ取得時点まででいい
バックアップのリストアだけできれば要件を満たせるので、バイナリログの保全は必須ではない

2.3. バックアップの保存期間、保存場所

  • バックアップを何世代保管するか？（最低2世代は必要）
  • どこに保存するか？（古いバックアップはテープ等に保管するなど）

2.4. バックアップの取得頻度、手法

  • システム要件（システム稼働時間等）やRTO、RPO等を考慮して決定する

3. 対象のファイル

3.1. バックアップ対象

データディレクトリまたはデータ全体

  • フルバックアップ
  • 論理または物理バックアップ

ログファイル（バイナリログ）

  • 増分バックアップ
  • ポイントインタイムリカバリに利用

設定ファイル

  • my.cnf

3.2. データディレクトリ

  • データベース内容、ログ、およびステータスファイルの格納先
  • デフォルトのディレクトリはインストール形式による
デフォルトディレクトリ
/usr/local/mysql/data/ (tar形式)
/var/lib/mysql (RPMパッケージ)
  • サーバ起動オプションで設定可能
datadir=/{path to datadir}/
  • サーバが利用しているディレクトリは下記コマンドで確認可能
mysql> SHOW VARIABLES like 'datadir';

※InnoDB関連ファイルのパスをデータディレクトリ以外に設定している場合は、バックアップ時にそれらも取得する（デフォルト設定では、InnoDB関連ファイルはdatadir配下に出力されている）

3.3. バイナリログ

  • 発行されたクエリのうち、更新系のSQL分のみを記録しているログファイル
    • クエリ実行日時などのメタデータも記録
    • トランザクションのコミット時に同期的に記録（sync_binlog=1）
  • バイナリ形式で記録
    • mysqlbinlogコマンドにてテキスト化が可能
  • 起動オプションを指定して出力する
    • `--log-bin[=file_name]
    • 通常の運用時には利用することを推奨
    • データディレクトリとは別のディスクに出力することを推奨
  • ログファイル名の拡張子に通し番号を記録
    • 例）file_name-bin.001、file_name-bin.002、etc
    • 現在利用中のログ番号はインデックスファイルに記録（file_name.index）

3.4. バイナリログの管理

現在使用中のバイナリログファイル名とポジションを確認
mysql> SHOW MASTER STATUS;
コマンドで全てのバイナリログファイル名を列挙
mysql> SHOW MASTER LOGS;
コマンドまたはMySQLサーバの再起動でログファイルのローテーション
mysql> FLUSH [BINARY] LOGS;
コマンドで特定の時点までのバイナリログを削除
mysql> PURGE MASTER;
コマンドで全てのバイナリログを削除
mysql> RESET MASTER;

3.5. 補足

3.6.1. InnoDB関連パス

データファイル（ibdata）のパス
innodb_data_home_dir + innodb_data_file_path
REDOログファイルのパス
innodb_log_group_home_dir
Undoログ用のテーブルスペースのパス
innodb_undo_directory

※指定したディレクトリにUndoログを出力するためには、innodb_undo_tablespacesも合わせて設定が必要

3.6.2. バイナリログのリモートバックアップ（MySQL5.6以降）

  • mysqlbinlogコマンドを使って、リモートサーバのバイナリログをコピー可能
    • 最新のバイナリログをコピーし続けることも可能
    • 接続先ホストを自ノードに設定すれば、自ノード内でバイナリログをコピーすることも可能

実行例

指定したバイナリログをコピー
mysqlbinlog --read-from-remote-server --host=host_name --raw binlog.000130 binlog.000131 binlog.000132
指定したファイル以降に生成されたバイナリログを全てコピー
mysqlbinlog --read-from-remote-server --host=host_name --raw --to-last-log binlog.000130
指定したファイル以降に生成されたバイナリログを全てコピーし、その後生成されたファイルもコピーし続ける
mysqlbinlog --read-from-remote-server --host=host_name --raw --stop-never binlog.000130

3.6.3. ストレージエンジンごとの特性について

InnoDB

  • 共有テーブルスペースファイルとInnoDBログファイルの組で1つの単位（innodb_file_per_table設定時は*.idbも必要）

    • 共有テーブルスペースファイル
      • idbata1, idbata2, ・・・
    • InnoDBデータファイル
      • idbファイル
    • InnoDBログファイル
      • ib_logfile0, ib_logfile1, ・・・
    • テーブル定義ファイル
      • .frmファイル

  • MySQL5.6ではトランスポータブル表領域機能を利用して、テーブル単位でファイルコピーして移動可能

    • MySQL5.5以前のバージョンではファイルコピーだけでは移動はできない

  • クラッシュセーフ、トランザクション対応

  • ロックは行単位

MyISAM

  • 3つのファイルから構成される（.frm, .MYD, .MYI）
  • ファイルとして書き込み禁止（FLUSH TABLES WITH READ LOCK）すればファイルコピーできる
  • クラッシュセーフではない（MySQLサーバやOSのクラッシュ時にはrepairが必要）
  • ロックはテーブル単位

4. バックアップ/リストア例

前提

  • 設定ファイルmy.cnfの配置先
    • /usr/local/mysql/data/my.cnf
[mysqld]
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
user=mysql
log-bin=MySQL

4.2. 物理バックアップ/リストア

4.2.1. OSコマンドによる物理バックアップ例

① MySQLサーバを停止

$ mysqladmin --user=root --password=root --socket=/usr/local/mysql/data/mysql.sock shutdown

② コールドバックアップを取得

$ cp -rp /usr/local/mysql/data /backup/mysql-20160226

③ MySQLサーバを起動する

$ mysqld --defaults-file=/usr/local/mysql/data/my.cnf &

4.2.2. 物理バックアップのリストア例

① マシンやディスクが壊れている場合は復旧する
② MySQLサーバが起動している場合は停止する
③ 既存のデータベース領域を削除

$ rm -rf /usr/local/mysql/data

④ バックアップファイルをリストア

$ cp -rp /backup/mysql-20160226 /usr/local/mysql/data

⑤ MySQLサーバを起動する

$ mysqld --defaults-file=/usr/local/mysql/data/my.cnf &

4.3. mysqldumpによるバックアップ/リストア例

4.3.1. mysqldumpのオプション

  • --master-data=2
    • バックアップ取得のバイナリファイル名とバイナリファイル内の位置（Position）をコメントとしてバックアップファイルに記録
  • --hex-blob
    • バイナリ型（BINARY、VARBINARY、BLOG）のBIT型のデータを16進数表記で出力
  • --default-character-set
    • mysqldumpがデフォルトで利用するキャラクタセットを指定
    • 通常はMySQLサーバのシステム変数default-character-setと同じものを指定すれば良い
  • --all-databases
    • 全てのデータベースをバックアップ
  • --lock-all-tables
    • 全てのテーブルをロックしてバックアップを取得する
  • single-transaction
    • InnoDBがサポートしているトランザクションの仕組みを利用して、InnoDBテーブルに限り一貫性のとれたバックアップを取得する

4.3.2. mysqldumpによるバックアップ例

① 全てのテーブルをロックしてデータベース全体のバックアップを取得

$ mysqldump --user=root --password=root --master-data=2 \
            --socket=/usr/local/mysql/data/mysql.sock \
            --hex-bolb --default-character-set=utf8 --all-databases \
            --lock-all-tables > mysql_bkup_dump.sql

② InnoDBのトランザクションを使用してデータベース全体のバックアップを取得

$ mysqldump --user=root --password=root --master-data=2 \
            --socket=/usr/local/mysql/data/mysql.sock \
            --hex-bolb --default-character-set=utf8 --all-databases \
            --single-transaction > mysql_bkup_dump.sql
  • 注意事項
    • データの整合性を保つために、バックアップ取得中は、テーブルに関するDDL文（※）を実行しないこと
      • ※ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE

4.3.3. mysqldumpからのリストア例

① マシンやディスクが壊れている場合は復旧する
② MySQLサーバが起動している場合は停止する
③ 既存のデータベース領域を削除後、再作成する（必要に応じて事前にバックアップを取得）
④ バイナリの出力を停止（my.cnfからlog-binをコメントアウト）
⑤ 権限テーブルとネットワーク接続を無効化した状態でMySQLサーバを起動

$ mysqld --defaults-file=/usr/local/mysql/data/my.cnf --skip-networking --skip-grant-tables

⑥ バックアップファイルに記述されたSQL文を実行

$ mysql --default-character-set=utf8 --socket=/usr/local/mysql/data/mysql.sock < mysql_bkup_dump.sql

⑦ バイナリログの出力を再開して、正常に再起動

$ mysqladmin --user=root --password=root --socket=/usr/local/mysql/data/mysql.sock shutdown
$ mysqld --defaults-file=/usr/local/mysql/data/my.cnf &
  • 注意事項
    • MySQL5.5以上の場合、この手順では性能統計情報を格納するperformance_schemaが作成されず、エラーログにperformance_schemaが無いというエラーが出力される
    • 回避策
      • サーバ起動前に、performance_schemaディレクトリをdatadir配下にコピーする
      • MySQLサーバ再起動後、mysql_upgradeを実行する

4.3.4. バイナリログを使用したポイントインタイムリカバリ

① ネットワーク接続を無効化した状態でMySQLサーバを起動

$ mysqld_safe --defaults-file=/usr/local/mysql/data/my.cnf --skip-networking &

② バイナリログからロールフォワード用SQL文を生成し、生成したSQL文を実行する（ファイル名とポジションは適切なものを指定）

$ mysqlbinlog --disable-log-bin --start-position=1017 MySQL.000010 MySQL.00011 > recover.sql

③ 正常に再起動

$ mysqladmin --user=root --password=root --socket=/usr/local/mysql/data/mysql.sock shutdown
$ mysqld --defaults-file=/usr/local/mysql/data/my.cnf &
94
96
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
94
96

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?