zaco muraです。
最近データベースのシステムを運用しておりますが、非常に奥が深く苦労しています。
そんな中、DBサーバの予備機を構築するためにいろいろと調べたのでまとめておきます。
背景
数ヶ月ほど運用したDBの予備機を構築することに。その時に頭を悩ませたのはDBのコピーでした。
このサーバは商用であるため、以下の制約がありました。
・DBのサイズが比較的大きい(1テーブルが約3億カラム、サイズが130GBくらい)
・商用なので止められる時間が限られる(1時間程度)
これまでDBの複製にはmysqldumpを使っていましたが、これはDBの中身をSQL文で出力するらしく非常に時間がかかるとのこと。
詳しい人に聞くと1時間どころか1日でも終わらない可能性が高いとの見解のため、今回は却下。
そこでいろいろ考えたのですが、「DBと言えども実体はファイルなんだから、ファイルコピーで行けるんじゃね？」的なことを思いつきました。
mysqlhotcopyというコマンドがあるらしい
調べてみたところ、mysqlhotcopyというコマンドが引っ掛かりました。
簡単に言うと、データベースのロックとコピーを実行するためのコマンドです。
単純にcpコマンドなどでDBをコピーしても良いですが、トランザクションの途中などでコピーしちゃうとDBが不整合を起こすリスクがあります。
そのため、このコマンドでDBをロック(更新できない状態)にしつつコピーすることでキレイなデータをコピーできるということです。
実際に試してみたところ、とりあえず使えたので手順などをメモです。
※調べた所ところ、ムリヤリ使えるようにしている手順なので不具合の可能性もあります。
※実際に使う場合は自己責任でお願いします。
前提：環境
環境はこんな感じです。
・CentOS release 6.6 (Final)
・Server version: 5.6.22 MySQL Community Server (GPL)
・InnoDB
※ググった情報ではmysqlhotcopyはMyISAMのみ対応と書いていましたが、InnoDBでもできました。
実行手順1.コピーの取得
こちらのページを参考に行いました。
[root@db_real ~]# mkdir -p /var/lib/backup_20160310/ [root@db_real ~]# mysqlhotcopy "データベース名" -u root --password="パスワード" /var/lib/backup_20160310/ Warning: /usr/bin/mysqlhotcopy is deprecated and will be removed in a future version. Flushed 14 tables with read lock (`scheme1`.`table1`, `scheme1`.`table2`, `scheme1`.`table3`, .........., `scheme1`.`table14`) in 0 seconds. Locked 0 views () in 0 seconds. Copying 41 files... Copying indices for 0 files... Unlocked tables. mysqlhotcopy copied 14 tables (41 files) in 1474 seconds (1474 seconds overall).
こんな感じでできました。
これで/var/lib/backup_20160310/配下には “データベース名” のディレクトリができていて、DBの実体であるファイルがコピーされています。
このディレクトリごと予備機に転送しておき、/var/lib/mysql/配下に置いておきます。
実行手順2.コピーからのリストア
次に、コピーしたファイルからのリストア手順です。こちらは構築したい予備機での作業です。
なお重要な前提条件ですが、予備機は商用機と同じテーブル構成を事前に作っておきます。
※後述しますが、作ってないとtable doesn’t exist というエラーが出ました。
各テーブルの表領域(テーブルの実体)を一度消します
この時、キーの制約によって消せない場合があるので一時的にチェックしないようにします。
[root@db_yobi ~]# mysql -uroot -p scheme1 mysql> SET FOREIGN_KEY_CHECKS = 0; mysql> ALTER TABLE テーブル名1 DISCARD TABLESPACE; mysql> ALTER TABLE テーブル名2 DISCARD TABLESPACE; ....... # 全テーブルに実施 元々あったファイルを全削除し、コピーしたファイルを展開 [root@db_yobi ~]# cd /var/lib/mysql/ [root@db_yobi ~]# rm -rf ./scheme1/* #<== 元々あったファイルを全削除 [root@db_yobi ~]# mv ./backup_20160310/mysql/scheme1/* ./scheme1/. #<== コピーしたファイルを展開
各テーブルの表領域を、展開した.idbファイルからインポートします
またキーの制約も元に戻します。
[root@db_yobi ~]# mysql -uroot -p mysql> ALTER TABLE テーブル名1 IMPORT TABLESPACE; mysql> ALTER TABLE テーブル名2 IMPORT TABLESPACE; ....... # 全テーブルに実施 mysql> SET FOREIGN_KEY_CHECKS = 1;
この状態でテーブル情報は参照できるようになります。
しかしshow table status; を実行した時にcolumnが0に見えて気持ち悪いので以下も実行します。
[root@db_yobi ~]# mysqlcheck -u root -p -a -A
これで使えるようになりました！
ちなみに、表領域の削除＆インポートを行わないと、以下の様なエラーになります。
(mysqlcheckやmysql_upgradeを行っても改善されません。)
mysql> select * from table1 limit 5; ERROR 1146 (42S02): Table 'scheme1.table1' doesn't exist
以上が手順になります。
データベースは本当に奥が深くてまだまだだなーと思いました。
