MySQLのリストアがめっちゃ速いXtraBackupのご紹介です。
MySQLの運用としてmysqldumpはバックアップは良いものの、リストア時はとても遅くなり問題になります。不安になってきた方は１度検証環境で現在のデータをリストアして復旧時間を計測してみると面白いかもしれません。
システムの要件や規模によりますが、秒単位で利益が発生しすぐに復旧する必要があるWEBサービス、データ容量が多くなってきた場合は、XtraBackupに切り替えた方が良いでしょう。また逆に、業務が発生していない夜間や早朝は止めて良くて、データ量も少ないシステムならmysqldumpで十分です。
目次
環境
- 1CPU 3.30GHz
- メモリ8GB
- バックアップ対象DB容量：3.5GB
- CentOS7
先に結論
mysqldumpがSQLに対して、xtrabackupは解凍なので当たり前っちゃ当たり前ですが恐ろしい差がでますね。レプリケーションとかしているとリストアしている間の時間はシビアなので速いにこしたことはないのです。
MySQLで普通にmysqldumpする
普通にmysqldumpします。
|
1 2 3 4 5 6 7 |
# time mysqldump -u root -p --all-databases --single-transaction --master-data=2 --flush-logs > alldump20171129.sql Enter password: real 0m48.273s user 0m17.952s sys 0m2.024s |
48秒で出来ました。
リストア
|
1 2 3 4 5 6 7 |
# time mysql -u root -p < alldump20171129.sql Enter password: real 3m23.844s user 0m19.109s sys 0m1.608s |
3分23秒
Xtrabackupのインストール
|
1 2 3 4 5 6 7 |
# yum install https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.8/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm インストール: percona-xtrabackup-24.x86_64 0:2.4.8-1.el7 依存性関連をインストールしました: libev.x86_64 0:4.15-7.el7 perl-Digest.noarch 0:1.17-245.el7 perl-Digest-MD5.x86_64 0:2.52-3.el7 |
バックアップディレクトリの作成
|
1 |
# mkdir -p /backupdb/xtrabackup |
バックアップ
|
1 2 3 4 5 6 7 8 |
# time /usr/bin/innobackupex --user root --password パスワード --no-lock /backupdb/xtrabackup xtrabackup: Transaction log of lsn (61580269) to (61580278) was copied. 171129 18:59:34 completed OK! real 0m29.636s user 0m0.241s sys 0m2.765s |
29秒
いきなり！ロールフォワード
XtrraBackupがめちゃ速！という結論は出ていますし、会社記事作成用とはいえ、せっかくDBのバックアップとリストアする作業を設けたので、ロールフォワードの訓練もしておくことにします。
ロールフォワード(前進復帰)
例えば、夕方17時に障害が発生し、朝5時にバックアップした復旧ポイントにデータをリストアすることをロールバック。ロールバックした地点から夕方17時まで、更新処理を改めて行うことで復旧することをロールフォワードといいます。
ロールフォワードのシナリオ
- データ入力する作業中に誤ってデータの削除をしてしまっている事に後で気付いた！
- そこでロールバック&ロールフォワードによる復旧！
茶番にお付き合い下さい。
大切なデータの追加1
|
1 2 3 |
INSERT INTO `msg` (`msg_id`, `msg`) VALUES ('999991', '遅いじゃないか・・ミッターマイヤー。'); INSERT INTO `msg` (`msg_id`, `msg`) VALUES ('999992', '卿がくるまで、生きていようと思ったのに、間に・合わないじゃないか・・。'); |
大切なデータの追加2
|
1 |
DELETE FROM `msg` WHERE `msg`.`msg_id` = 2 |
大切なデータの追加3
|
1 |
INSERT INTO `msg` (`msg_id`, `msg`) VALUES ('999993', '疾風ウォルフの名に恥じるじゃないか。。'); |
…!!!
サンプルデータの追加したと思ったら消してしまっています。
|
1 |
DELETE FROM `msg` WHERE `msg`.`msg_id` = 2 |
大変ですね。
緊急メンテナンスモードにしましょう。
サンプルデータの追加2をなかったことにしつつ、追加１と追加３は反映させた状態で復旧させなくてはいけません。
メンテナンスモードにして復旧するというシナリオです。
リストア
MySQLを止めます。
|
1 |
# systemctl stop mysqld |
旧ファイル退避。mysql.oldにバイナリログも退避される
|
1 2 3 |
# mv /var/lib/mysql /var/lib/mysql.old # mkdir /var/lib/mysql |
バックアップディレクトリの確認
|
1 2 3 4 5 6 |
# ls -laht /backupdb/xtrabackup 合計 0 drwxr-x--- 8 root root 267 11月 29 19:16 2017-11-29_19-15-51 drwxr-xr-x 3 root root 33 11月 29 19:15 . drwxr-xr-x 3 root root 24 11月 29 19:07 .. |
バイナリのポジションの把握
|
1 2 3 |
# cat /backupdb/xtrabackup/2017-11-29_19-15-51/xtrabackup_binlog_info mysql-bin.000001 154 |
mysql-bin.000001のファイルの154から見ればいいことがわかります。
リストアします。
|
1 2 3 4 5 6 7 |
# time /usr/bin/innobackupex --copy-back /backupdb/xtrabackup/2017-11-29_19-15-51 171129 17:53:07 completed OK! real 0m25.178s user 0m0.022s sys 0m2.795s |
25秒。
速いですね！
感動している場合じゃありません、復旧しなければ！！
binlogの具合を見てみよう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
# mysqlbinlog /var/lib/mysql.old/mysql-bin.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #171129 19:14:18 server id 1 end_log_pos 123 CRC32 0xea09430a Start: binlog v 4, server v 5.7.20-log created 171129 19:14:18 at startup ROLLBACK/*!*/; BINLOG ' eogeWg8BAAAAdwAAAHsAAAAAAAQANS43LjIwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA AAAAAAAAAAAAAAAAAAB6iB5aEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA AQpDCeo= '/*!*/; # at 123 #171129 19:14:18 server id 1 end_log_pos 154 CRC32 0x657305ad Previous-GTIDs # [empty] # at 154 #171129 19:24:50 server id 1 end_log_pos 219 CRC32 0x8a369a1e Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 219 #171129 19:24:50 server id 1 end_log_pos 310 CRC32 0x4e906b8a Query thread_id=63 exec_time=1 error_code=0 SET TIMESTAMP=1511951090/*!*/; SET @@session.pseudo_thread_id=63/*!*/; SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/; SET @@session.sql_mode=1436549152/*!*/; SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/; /*!\C utf8mb4 *//*!*/; SET @@session.character_set_client=45,@@session.collation_connection=224,@@session.collation_server=33/*!*/; SET @@session.lc_time_names=0/*!*/; SET @@session.collation_database=DEFAULT/*!*/; BEGIN /*!*/; # at 310 #171129 19:24:50 server id 1 end_log_pos 629 CRC32 0xc383e379 Query thread_id=63 exec_time=1 error_code=0 use `test_db`/*!*/; SET TIMESTAMP=1511951090/*!*/; INSERT INTO `msg` (`msg_id`, `msg`) VALUES ('999991', '遅いじゃないか・・ミッターマイヤー。') /*!*/; # at 629 #171129 19:24:50 server id 1 end_log_pos 660 CRC32 0xdf4fc2f8 Xid = 575 COMMIT/*!*/; # at 660 #171129 19:25:57 server id 1 end_log_pos 725 CRC32 0x82b81ec8 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 725 #171129 19:25:57 server id 1 end_log_pos 816 CRC32 0x72fd2605 Query thread_id=73 exec_time=0 error_code=0 SET TIMESTAMP=1511951157/*!*/; BEGIN /*!*/; # at 816 #171129 19:25:57 server id 1 end_log_pos 1186 CRC32 0x08b147d5 Query thread_id=73 exec_time=0 error_code=0 SET TIMESTAMP=1511951157/*!*/; INSERT INTO `msg` (`msg_id`, `msg`) VALUES ('999992', '卿がくるまで、生きていようと思ったのに、間に・合わないじゃないか・・。') /*!*/; # at 1186 #171129 19:25:57 server id 1 end_log_pos 1217 CRC32 0xeed258fe Xid = 661 COMMIT/*!*/; # at 1217 #171129 19:26:25 server id 1 end_log_pos 1282 CRC32 0xf66f5e54 Anonymous_GTID last_committed=2 sequence_number=3 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1282 #171129 19:26:25 server id 1 end_log_pos 1373 CRC32 0x70ac6c88 Query thread_id=89 exec_time=0 error_code=0 SET TIMESTAMP=1511951185/*!*/; BEGIN /*!*/; # at 1373 #171129 19:26:25 server id 1 end_log_pos 1501 CRC32 0xbc5036a8 Query thread_id=89 exec_time=0 error_code=0 SET TIMESTAMP=1511951185/*!*/; DELETE FROM `msg` WHERE `msg`.`msg_id` = 2 /*!*/; # at 1501 #171129 19:26:25 server id 1 end_log_pos 1532 CRC32 0xd54ce32e Xid = 978 COMMIT/*!*/; # at 1532 #171129 19:27:09 server id 1 end_log_pos 1597 CRC32 0x143bba6e Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; # at 1597 #171129 19:27:09 server id 1 end_log_pos 1688 CRC32 0xa554ccdb Query thread_id=99 exec_time=0 error_code=0 SET TIMESTAMP=1511951229/*!*/; BEGIN /*!*/; # at 1688 #171129 19:27:09 server id 1 end_log_pos 2010 CRC32 0xcff8cf32 Query thread_id=99 exec_time=0 error_code=0 SET TIMESTAMP=1511951229/*!*/; INSERT INTO `msg` (`msg_id`, `msg`) VALUES ('999993', '疾風ウォルフの名に恥じるじゃないか。。') /*!*/; # at 2010 #171129 19:27:09 server id 1 end_log_pos 2041 CRC32 0xeca4c678 Xid = 1078 COMMIT/*!*/; # at 2041 #171129 19:27:37 server id 1 end_log_pos 2064 CRC32 0xce9d6960 Stop SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/; DELIMITER ; # End of log file /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; |
わけがわからない。文字が多すぎですよね。事前知識がないと意味わからんです。
書いてある内容の解説
ヒント1.
|
1 2 3 |
# at 154 #171129 19:24:50 server id 1 end_log_pos 219 CRC32 0x8a369a1e Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/; |
バイナリポジションの把握から154から見ていけばいいことがわかっています。
ヒント2.
#171129 19:26:25
上記は2017年11月29日19時26分25秒をあらわします。
ヒント3.
|
1 2 3 4 5 6 7 |
# at 310 #171129 19:24:50 server id 1 end_log_pos 629 CRC32 0xc383e379 Query thread_id=63 exec_time=1 error_code=0 use `test_db`/*!*/; SET TIMESTAMP=1511951090/*!*/; INSERT INTO `msg` (`msg_id`, `msg`) VALUES ('999991', '遅いじゃないか・・ミッターマイヤー。') /*!*/; # at 629 |
# at 数字はファイルの場所を指定していると考えてください。ポジション310と629の間にINSERTのクエリがあることがわかります。
また、事故のクエリはDELETEです、DELETEで検索すれば良いかもしれません！
DELETEで前後５行を検索します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# mysqlbinlog /var/lib/mysql.old/mysql-bin.000001 | grep -A 5 -B 5 "DELETE" BEGIN /*!*/; # at 1373 #171129 19:26:25 server id 1 end_log_pos 1501 CRC32 0xbc5036a8 Query thread_id=89 exec_time=0 error_code=0 SET TIMESTAMP=1511951185/*!*/; DELETE FROM `msg` WHERE `msg`.`msg_id` = 2 /*!*/; # at 1501 #171129 19:26:25 server id 1 end_log_pos 1532 CRC32 0xd54ce32e Xid = 978 COMMIT/*!*/; # at 1532 |
あっいいですね。
ポジション1373と1501の間に問題のクエリがあることがわかります。
もしくは、事故った時間がわかれば話は早いです。
|
1 |
# mysqlbinlog --start-datetime="2017-11-29 17:30:00" /var/lib/mysql.old/mysql-bin.000001 |
時間を指定することでそこの時点からファイルを参照することが出来ます。
復旧用クエリ抽出
|
1 2 3 |
# mysqlbinlog --database="test_db" --start-position=154 --stop-position=1373 /var/lib/mysql.old/mysql-bin.000001 > /root/recovery_1.sql # mysqlbinlog --database="test_db" --start-position=1501 --stop-position=2041 /var/lib/mysql.old/mysql-bin.000001 > /root/recovery_2.sql |
事故ったクエリを迂回するように指定することで復旧用クエリを抽出することが出来ます。
MySQLの起動
|
1 2 3 |
# chown -R mysql:mysql /var/lib/mysql # systemctl restart mysqld |
復旧用クエリによるロールフォワード
|
1 2 3 |
# mysql -u root -p < /root/recovery_1.sql # mysql -u root -p < /root/recovery_2.sql |
データの確認
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
mysql> SELECT * FROM `msg` WHERE msg_id = 2; +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+----------+---------+---------------------+ | msg_id | msg | img | girl_flg | del_flg | regdate | +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+----------+---------+---------------------+ | 2 | テスト | | 1 | 0 | 2013-06-29 21:03:59 | +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+----------+---------+---------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM `msg` WHERE msg_id = 999991; +--------+-------------------------------------------------------- | msg_id | msg +--------+-------------------------------------------------------- | 999991 | 遅いじゃないか・・ミッターマイヤー。 +--------+-------------------------------------------------------- 1 row in set (0.00 sec) mysql> SELECT * FROM `msg` WHERE msg_id = 999992; +--------+---------+-----------------------------------------------------------------------------------------------------------+ | msg_id | user_cd | msg | +--------+---------+-----------------------------------------------------------------------------------------------------------+ | 999992 | 999992 | 卿がくるまで、生きていようと思ったのに、間に・合わないじゃないか・・。 | +--------+---------+-----------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM `msg` WHERE msg_id = 999993; +--------+-----------------------------------------------------------+ | msg_id | msg | +--------+-----------------------------------------------------------+ | 999993 | 疾風ウォルフの名に恥じるじゃないか。。 | +--------+-----------------------------------------------------------+ 1 row in set (0.00 sec) |
大丈夫そうですね。
無事復旧できましたね。
お疲れ様です。
