MySQL/バックアップ

• MySQL関連のWikiへのリンク
  • MySQL
  • MySQL 5.1
  • MySQL 5.4
  • MySQL/InnoDB
  • MySQL/エラー
  • MySQL/クラスタリング
  • MySQL/サーバ移動
  • MySQL/チューニング
  • MySQL/バージョン
  • MySQL/バックアップ
  • MySQL/プロファイリング
  • MySQL/ベンチマーク
  • MySQL/レプリケーション
  • MySQL/ロック

• MySQLデータのバックアップ方法
• 【MySQLウォッチ】第23回　押さえておきたいバックアップとリストアのポイント
• スナップショットでバックアップ

テキストファイル出力 †

SELECT INTO OUTFILE †

• MYSQL (テキストファイルへの書き出しと読み込み）

  mysql> select [列名, …] into outfile '[ファイル名]' fields terminated by '[区切り文字]' enclosed by '[囲み文字]' lines terminated by '[改行コード]' from [テーブル名] [whereなど条件文];

mysqldumpで出力 †

• Using mysqldump to save data to CSV files

  $ mysqldump -p --skip-lock-tables -T ./ --fields-enclosed-by=\" --fields-terminated-by=, -t [データベース] [テーブル]

mysqldump †

• mysqldumpとは？解説と実行方法

mysqldump  --no-tablespaces --single-transaction -u [ユーザ] -p[パスワード] [オプション] [データベース] [テーブル1] [テーブル2] ... > [ダンプファイル]

• [テーブル *] は特定テーブルのみにしたい場合に指定

使い方 †

• 定義のみ、テーブルロックなし

  mysqldump -d --skip-lock-tables ...

• データのみ、テーブルロックなし

  mysqldump -t --skip-lock-tables ...

デフォルトで --opt オプションが有効になっている。--optオプションの内容は以下の通り。

• --add-drop-table
• --add-locks ... LOCK TABLESとUNLOCK TABLESステートメントで各テーブルダンプを囲む。これによりダンプファイルを再ロードする際のインサートの速度が向上する。
• --create-options
• --disable-keys
• --extended-insert
• --lock-tables
• --quick
• --set-charset

データベース内で確実に整合を取りたい場合は、以下のオプションも付ける。

• -x ... --lock-all-tablesと同じ。全てのデータベースの全てのテーブルをロック。これは全ダンプの期間、グローバルリードロックを取得することで達成される。全ての処理を停止した状態でやらないとえらいことになる。

その他よく使うオプションを以下に列挙

• --no-tablespaces ... テーブルスペース情報が不要な場合は付ける
• --single-transaction ... InnobDB メインな DBでデータの整合性を保つために付ける

• --skip-lock-tables ... テーブルをロックしないでダンプする。当然、このオプションを指定する場合はダンプ中にデータを更新しないようにする。
• --databases db_name1 [db_name2 ...] ... ダンプするDBを指定
• --ignore-table=db_name.tbl_name ... ダンプしないテーブルを指定
• --no-data ... データはダンプしない。CREATE TABLE文のみダンプしたい場合に使用
• --where="[条件句]" ... ダンプの条件指定。　例）--where="sales>10000"
  • AUTO_INCREMENT の値は現在値＋１になっているので、必要であれば修正する。

• MySQLのデータベース作成、ユーザー作成、アクセス権限設定

豆知識 †

• mysqldump と repair with keycache

mysqldump は効率良く import ができるように、以下の順番のSQLを出力してくれる

  1. lock tables
  2. disable keys
  3. insert, insert, insert, ...
  4. enable keys
  5. unlock tables

トラブルシューティング †

unknown variable †

• MySQLのバックアップ（mysqldump）で”mysqldump: unknown variable” そのときの解決方法
• mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4' と言われないためのTIPS

Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces †

• MySQLDump で tablespaces の PROCESS privilege エラーが出だした時の対処方法
• mysqldumpで「Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces」となる場合の対処

• テーブルスペース情報をダンプする必要がないユーザーは、-no-tablespacesオプションを指定してmysqldumpを呼び出すことにより、前述の要件を回避できます。

mysqlhotcopy †

• MyISAMとARCHIVEテーブルのみバックアップ可能

AutoMySQLBackup †

• MySQLの全DBを自動バックアップする

MySQL Enterprise Backup †

• 商用版のMySQL Edition で利用可能

InnoDB Hot Backup †

• ソフトエイジェンシーのページ
• 本家
• InnoDBのホットバックアップに関する質疑応答
• LVMスナップショット vs InnoDB Hot Backup

Percona XtraBackup †

• tcpdump & xtrabackup [slideshare]
• xtrabackupをちょっと便利に使う

• 基本的な仕組み
  • InnoDBのWAL(Write Ahead Log)を複製するプロセスを起動 –-データファイルをコピー –-データファイルコピーが終わったら、WALを適応(適応はInnoDBのクラッシュリカバリを利用)
  • コピー終了時点のバックアップ完了

Zmanda Recovery Manager for MySQL †

mylvmbackup †

LVMスナップショット †

• LVM + XFSで高速簡単 MySQLバックアップ
• LVMスナップショットバックアップのためのシェルスクリプトを作った