このページでは、mysql レプリケーション(replication) の復旧手順を示します。mysqld_multiを用いた簡単に構築できるサーバ構成で、mysql レプリケーション(replication) の復旧シナリオを数パターン示します。
読者の方にこのシナリオを読んで頂き、
- replication復旧の難しさ
- 唯一絶対の”正しい手順”は存在しない事
を感じ取って頂ければ幸いです。MySQL replication構成によって復旧手順は難しくも簡単にもなります。復旧作業を行なう際は、データロス期間vsダウンタイムのようなトレードオフを判断する必要があります。レプリケーションの復旧はDBAの単調作業ではなく、事業としての意思決定である事をご理解頂けると幸いです。
mysql レプリケーション 基本概念
mysql レプリケーション 基本概念 – binary log, relay log
mysql replicationの設定方法で説明した内容と重複しますが、MySQLのreplication (レプリケーション)を設定するには、以下4つのキーワードを抑える事が重要です。
- Binary log ( バイナリログ)
- Relay log ( リレーログ )
- I/O thread
- SQL thread
これら4つのキーワードについて、簡単なMySQL replication (レプリケーション) の動作を図示すると以下のようになります。
MySQL replication ( レプリケーション ) の処理の概要を説明すると次のようになります。
- Masterサーバに書き込まれた変更処理をBinary logに書き出します。
- SlaveサーバのI/O threadはBinary logを読み取り、Relay logに変更内容を書き出します。
- SlaveサーバのSQL threadはRelay logを読み取り、読み取った内容をデータベースに反映させます。
mysql レプリケーション 基本概念 – ログ ポジション
mysql レプリケーションの復旧作業を行なうには、バイナリログ(Binary log), リレーログ(relay log)がどこまで処理されたかを把握する事が大事です。mysqlの文脈で語られるポジションという用語は、ログがどこまで処理されたかを表す概念です。ポジションを確認するには、show master status, show master infoというコマンドを用います。
このページで示す復旧シナリオでは、ポジションの値を見て操作するシナリオは存在しません。しかし、想定外の壊れ方をした場合やreplication復旧手順として問題ないかどうかレビューする場合は、必須となる知識ですので、必ず会得しておきましょう。
mysql レプリケーション 基本概念 – ログ ポジション – show master status
Masterサーバにて、バイナリログがどのポジションまで出力されたのかを確認できるコマンドがshow master statusです。show master statusの出力例は以下のようになります。
mysql> show master status \G ;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 293
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
mysql レプリケーション 基本概念 – ログ ポジション – show slave status ;
Slaveサーバのポジションの読み方はかなり複雑です。Master_Log_File, Read_Master_Log_Pos, Relay_Log_File, Relay_Log_Pos, Relay_Master_Log_File, Exec_Master_Log_Posの6つの用語の意味を正確に把握する必要があります。
ポジションを表示させるには、以下のように”show slave status”コマンドを使用して下さい。
[root@localhost ~]# mysql -uroot -e "show slave status \G; " | egrep "Pos:|File:"
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1058
Relay_Log_File: mysql-relay.000004
Relay_Log_Pos: 829
Relay_Master_Log_File: mysql-bin.000003
Exec_Master_Log_Pos: 839
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_CA_File:
Master_Info_File: /var/lib/mysql3/master.info
各ポジションの意味は以下の通りです。
| 項目 | 意味 |
|---|---|
| Master_Log_File | I/O threadは、Masterサーバのバイナリログを読み取ります。今、バイナリログのどのファイルを読んでいるかを表します。 |
| Read_Master_Log_Pos | I/O threadは、Masterサーバのバイナリログを読み取ります。今、バイナリログのどのポジションを読んでいるかを表します。 |
| Relay_Log_File | SQL threadは、Slaveサーバのレプリケーションログを読み取ります。今、レプリケーションログのどのファイルを読んでいるかを表します。 |
| Relay_Log_Pos | SQL threadは、Slaveサーバのレプリケーションログを読み取ります。今、レプリケーションログのどのポジションを読んでいるかを表します。 |
| Relay_Master_Log_File | SQL threadは、Slaveサーバのレプリケーションログを読み取ります。今、読み取られているログが、Masterサーバ バイナリログのどのファイルに相当するかを表します。 |
| Exec_Master_Log_Pos | SQL threadは、Slaveサーバのレプリケーションログを読み取ります。今、読み取られているログが、Masterサーバ バイナリログのどのポジションに相当するかを表します。 |
Master_Log_File, Relay_Master_Log_Fileの意味は混同しやすいので、細心の注意を払ってください。また、それぞれのポジションの実態は、master.info, relay.infoというファイルです。
mysql レプリケーション 復旧シナリオ 事前準備
mysql レプリケーション 復旧シナリオ 事前準備 – mysqld_multi環境の構築
mysqld_multiを用いて、3つのmysqld(MySQLサーバ)を起動させます。動作確認のシナリオで使用する/etc/my.cnfの設定は以下の通りです。mysqld_multiの使い方は、mysqld_multiの設定方法を参照ください。
[mysqld] log-bin=mysql-bin relay-log=mysql-relay log-slave-updates master_info_repository=FILE relay_log_info_repository=FILE [mysqld_multi] mysqld=/usr/bin/mysqld_safe mysqladmin=/usr/bin/mysqladmin [mysqld1] datadir=/var/lib/mysql1 socket=/tmp/mysql1.sock tmpdir=/var/tmp/mysql1 port=13301 server-id=1 [mysqld2] datadir=/var/lib/mysql2 socket=/tmp/mysql2.sock tmpdir=/var/tmp/mysql2 port=13302 server-id=2 [mysqld3] datadir=/var/lib/mysql3 socket=/tmp/mysql3.sock tmpdir=/var/tmp/mysql3 port=13303 server-id=3
mysql レプリケーション 復旧シナリオ 事前準備 – レプリケーションの設定
3つのmysqld(MySQLサーバ)に対してレプリケーション(replication)の設定を行います。具体的な設定方法は、mysql replication 設定方法を参照ください。
mysql レプリケーション 復旧シナリオ 事前準備 – 免責事項
以下の復旧シナリオでは、mysqld停止とcpコマンドによるデータコピーが基本的なオペレーションになっています。しかし、実務では、mysqldの停止は確認事項が多く、非常に難しい作業です。データコピーの方法もcpコマンド以外に多数存在します。
このページでは、実務ではなく概要を把握してもらう事を念頭においておりますので、細かな考慮点は検討の対象外としております。実務で必要になる確認項目や勘所は最終章のTipsにまとめております。
mysql レプリケーション 復旧シナリオ 01 – スレーブサーバの障害
mysql レプリケーション 復旧シナリオ 01 – スレーブサーバの障害 – 障害シナリオの再現
以下のような構成になるようMySQLレプリケーション環境を構築します。
この環境において、mysqld_multi 3の障害を発生させます。
# mysqld_multi stop 3
mysql レプリケーション 復旧シナリオ 01 – スレーブサーバの障害 – 障害復旧
コピー元となる、mysqld_mutli 2をI/O thread, SQL threadを停止させます。これはコピーを行なっている最中にファイルの書き込みを避ける意図です。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql2.sock << EOF > STOP SLAVE > EOF [root@localhost ~]#
mysql_multi 2からmysqld_multi 3へのデータコピーを行ないます。レプリケーションを行なう環境において、server-idとuuidの重複は許されません。uuidはauto.cnfに記載されているので、auto.cnfの削除も忘れずに行ないましょう。auto.cnfが存在しない場合は、自動的にuuidが付与されます。
# rm -rf /var/lib/mysql3 # cp -rp /var/lib/mysql2 /var/lib/mysql3 # rm /var/lib/mysql3/auto.cnf
mysql レプリケーション 復旧シナリオ 01 – スレーブサーバの障害 – 動作確認
mysqld_multi 2,3を起動させます。
[root@localhost ~]# mysqld_multi start 2,3
それぞれ、レプリケーションが正常に動作している事を確認します。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql2.sock << EOF | grep -i Running:
> SHOW SLAVE STATUS \G ;
> EOF
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql3.sock << EOF | grep -i Running:
> SHOW SLAVE STATUS \G ;
> EOF
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@localhost ~]#
mysql レプリケーション 復旧シナリオ 02 – マスターサーバの障害 ログ転送保証あり
mysql レプリケーション 復旧シナリオ 02 – マスターサーバの障害 ログ転送保証あり – 障害シナリオの再現
以下のような構成になるようMySQLレプリケーション環境を構築します。
この環境において、マスターサーバ mysqld_multi 1の障害を発生させます。
# mysqld_multi stop 1
mysql レプリケーション 復旧シナリオ 02 – マスターサーバの障害 ログ転送保証あり – 障害復旧
マスターに昇格するサーバ側のバイナリログのポジションを確認します。mysqld_multi 2で、”SHOW MASTER STATUS”コマンドを発行し、バイナリログのポジションを確認します。
必須とは言い切れませんが、マスターに昇格する前には”RESET MASTER”コマンドを発行し、マスターサーバとしての情報を一度削除する事をお勧めします。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql2.sock
mysql> reset master ;
mysql> show master status \G ;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
スレーブサーバ側で、レプリケーションの向き先を変更します。mysqld_multi 3において、レプリケーションの向き先をmysqld_multi 1からmysqld_multi 2へ変更します。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql3.sock
mysql> stop slave ;
mysql> CHANGE MASTER TO
-> MASTER_HOST = 'localhost',
-> MASTER_USER = 'repl',
-> MASTER_PASSWORD = '',
-> MASTER_PORT = 13302,
-> MASTER_LOG_FILE = 'mysql-bin.000001',
-> MASTER_LOG_POS = 120 ;
mysql> start slave ;
mysql レプリケーション 復旧シナリオ 02 – マスターサーバの障害 ログ転送保証あり – 動作確認
mysqld_multi 2からmysqld_multi 3へのレプリケーションが行われている事を確認します。スレーブサーバ mysqld_multi 2側で、”SHOW SLAVE STATUS”コマンドを発行し、Slave_IO_Running, Slave_SQL_RunningがともにYesになっている事を確認します。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql3.sock
mysql> show slave status \G ;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 13302
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
<omitted>
mysql レプリケーション 復旧シナリオ 03 – マスターサーバの障害 ログ転送保証なし
mysql レプリケーション 復旧シナリオ 03 – マスターサーバの障害 ログ転送保証なし – 障害シナリオの再現
以下のような構成になるようMySQLレプリケーション環境を構築します。
前述の復旧シナリオ02との違いは、リレーログの転送に遅延が発生している事です。ネットワークの帯域が枯渇している場合やLOAD DATA文のようなトラフィックのバーストが発生しうる場合は、リレーログ転送の遅延が発生します。
リレーログの遅延はかなりの負荷をかけないと再現できませんので、ここでは”STOP SLAVE”コマンドを用いて疑似的に転送遅延を再現させます。まず、mysqld_multi 3で”STOP SLAVE”コマンドを入力し、意図的にリレーログが転送させないようにします。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql3.sock mysql> stop slave ;
マスターサーバ mysqld_multi 1において、バイナリログを発生させます。何らかの更新を伴うクエリをmysqld_multi 1に対して発行します。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql1.sock mysql> DROP DATABASE test ; mysql> CREATE DATABASE test ; mysql> CREATE TABLE test.sample ( id int ) ; mysql> INSERT INTO test.sample VALUES (1),(2),(3) ; mysql>
以上の手順で、リレーログの転送遅延を再現できました。この状態で、マスターサーバmysqld_multi 1の障害を発生させます。
# mysqld_multi stop 1
mysql レプリケーション 復旧シナリオ 03 – マスターサーバの障害 ログ転送保証なし – データ復旧
mysqld_multi 3はリレーログの転送が遅れてしまったため、mysqld_multi 2, 3の間でデータの差異が存在します。mysql レプリケーションはマスター/スレーブ間のデータが一致している事を前提としておりますので、まずmysqld_multi 3のデータを復旧させる必要があります。
mysqld_multi 2に存在するリレーログを用いてデータを復旧させます。復旧させるには、何分何秒のデータから何分何秒までのデータが破損しているかを明らかにする必要があります。
mysqld_multi 3上のリレーログを読み取り、どこまでのリレーログがデータベースに反映させたかを確認します。リレーログはバイナリ形式で保存されているので、読取にはmysqlbinlogコマンドが必要となります。ログを全て出力させると膨大な量になりますので、–start-datetimeオプションやtailを用いて必要な分だけ表示させると良いでしょう。
以下のような出力の場合は、一番最後に実行されたクエリは時刻21:43:14, タイムスタンプ1412944994である事が分かります。
[root@localhost ~]# mysqlbinlog --start-datetime="2010-10-10 21:30:00" /var/lib/mysql3/mysql-relay.000004 | tail -n 10 # at 712 #141010 21:43:14 server id 1 end_log_pos 641 CRC32 0x32dac26d Query thread_id=3 exec_time=1 error_code=0 SET TIMESTAMP=1412944994/*!*/; drop database TEST /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
次に遅延が発生してないスレーブであるmysqld_multi 2上のリレーログを観察します。すると、遅延が発生しているmysqld_multi 3はポジション712まで転送済であるものの、ポジション804以降が未転送である事が分かります。
[root@localhost ~]# mysqlbinlog --start-datetime="2010-10-10 21:43:00" /var/lib/mysql2/mysql-relay.000003 | less <omitted> # at 681 #141010 21:43:01 server id 1 end_log_pos 549 CRC32 0xabe7543d Xid = 31 COMMIT/*!*/; # at 712 #141010 21:43:14 server id 1 end_log_pos 641 CRC32 0x32dac26d Query thread_id=3 exec_time=1 error_code=0 SET TIMESTAMP=1412944994/*!*/; drop database TEST /*!*/; # at 804 #141010 21:43:57 server id 1 end_log_pos 733 CRC32 0x1dd0908e Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1412945037/*!*/; DROP DATABASE test /*!*/; # at 896 #141010 21:44:00 server id 1 end_log_pos 827 CRC32 0xb0bc1873 Query thread_id=5 exec_time=0 error_code=0 SET TIMESTAMP=1412945040/*!*/; CREATE DATABASE test
ポジション804以降のリレーログを、遅延が発生しているmysqld_multi 3 に反映させる事によって、mysqld_multi 2, 3間のデータ差異が埋まります。リレーログの差異を埋めるコマンド例は以下の通りです。
# mysqlbinlog --start-position=804 /var/lib/mysql2/mysql-relay.000003 > /tmp/diff.sql # mysql -uroot --socket=/tmp/mysql3.sock < /tmp/diff.sql
最後に、mysqld_multi 2, 3間のレプリケーションを設定し、復旧作業完了です。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql2.sock
mysql> stop slave ;
mysql> reset slave all ;
mysql> reset master ;
mysql> show master status \G ;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql>
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql3.sock
mysql> stop slave ;
mysql> CHANGE MASTER TO
-> MASTER_HOST = 'localhost',
-> MASTER_USER = 'repl',
-> MASTER_PASSWORD = '',
-> MASTER_PORT = 13302,
-> MASTER_LOG_FILE = 'mysql-bin.000001',
-> MASTER_LOG_POS = 120 ;
mysql> start slave ;
mysql> show slave status \G ;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: repl
Master_Port: 13302
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql レプリケーション 復旧シナリオ 04 – チェーン構成 マスターサーバの障害
mysql レプリケーション 復旧シナリオ 04 – チェーン構成 マスターサーバの障害 – 障害シナリオの再現
以下のような構成になるようMySQLレプリケーション環境を構築します。
この環境において、マスターサーバ mysqld_multi 1の障害を発生させます。
# mysqld_multi stop 1
mysql レプリケーション 復旧シナリオ 04 – チェーン構成 マスターサーバの障害 – 障害復旧
チェーン構成におけるマスターサーバの復旧は非常に簡単です。孫スレーブサーバの構築と同様の手順で復旧する事ができます。
まず、孫スレーブのレプリケーション元となるスレーブサーバmysqld_mutli 3において、”stop slave”コマンドでレプリケーションを停止させ、ディスクへの書き込みがない状態にします。また、このディスクへの書き込みがない状態のマスターポジションをメモに控えておきます。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql3.sock
mysql> stop slave ;
mysql> show master status \G ;
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 120
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
スレーブサーバmysqld_multi 3から孫スレーブmysqld_multi 1へのデータコピーを行います。この時、UUIDが記載されたauto.cnfを削除し、UUIDがバッティングしないように注意して下さい。また、設定によっては、PIDファイルの削除も必要になるかもしれません。
# rm -rf /var/lib/mysql1/* # cp -rp /var/lib/mysql3/* /var/lib/mysql1/ # rm -f /var/lib/mysql1/auto.cnf # rm -f /var/lib/mysql1/localhost.localdomain.pid
次の手順が一番の注意ポイントです。孫スレーブmysqld_multi 1をそのまま起動してしまうと、マスターに対してレプリケーションを行ってしまいます。これでは、想定外のデータが書き込まれてしまいますので、起動と同時にレプリケーションが行われないよう、–skip-slave-startオプションを指定します。
以下のように/etc/my.cnfの[mysqld1]セクションにskip-slave-startオプションを一時的に加筆します。
--- my.cnf.org 2014-10-12 16:35:04.734807372 +0900 +++ my.cnf 2014-10-13 12:14:01.657527555 +0900 @@ -10,6 +10,7 @@ mysqladmin=/usr/bin/mysqladmin [mysqld1] +skip-slave-start datadir=/var/lib/mysql1 socket=/tmp/mysql1.sock tmpdir=/var/tmp/mysql1
孫スレーブであるmysqld_multi 1を起動させます。”CHANGE MASTER TO”コマンドにより、レプリケーションを設定します。
[root@localhost ~]# mysqld_multi start 1 ;
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql1.sock
mysql> reset slave all ;
mysql> CHANGE MASTER TO
-> MASTER_HOST = 'localhost',
-> MASTER_USER = 'root',
-> MASTER_PASSWORD = '',
-> MASTER_PORT = 13303,
-> MASTER_LOG_FILE = 'mysql-bin.000005',
-> MASTER_LOG_POS = 120 ;
mysql レプリケーション 復旧シナリオ 04 -チェーン構成 マスターサーバの障害 – 動作確認
レプリケーションが正常に行われている事を確認します。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql1.sock
mysql> show slave status \G ;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 13303
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 120
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
<omitted>
mysql レプリケーション 復旧シナリオ 05 – チェーン構成 スレーブサーバの障害
mysql レプリケーション 復旧シナリオ 05 – チェーン構成 スレーブサーバの障害 – 障害シナリオの再現
以下のような構成になるようMySQLレプリケーション環境を構築します。
説明の都合上、他シナリオに比べて、やや実践的な環境を構築します。マスターサーバに定期的な書き込みを行います。以下は、定期的な書き込みを行う操作例です。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql1.sock
mysql> drop database test ;
mysql> create database test ;
mysql> create table test.sample ( i int primary key) ;
[root@localhost ~]# for i in `seq 1000`
> do
> echo "insert into test.sample values (${i});" | mysql -uroot --socket=/tmp/mysql1.sock ;
> sleep 10 ;
> done
この状態で、スレーブサーバ mysqld_multi 2の障害を発生させます。
# mysqld_multi stop 2
mysql レプリケーション 復旧シナリオ 05 – チェーン構成 スレーブサーバの障害 – 障害復旧
スレーブサーバの復旧はやや難しいです。孫スレーブであったmysqld_multi 3のポジションを”SHOW SLAVE STATUS”コマンドで確認しても、どこからレプリケーションを再開させれば良いかかは判断できないためです。レプリケーションを再開させるポジションは、リレーログ, バイナリログを目視で読み取って判断するしかありません。
まず、孫スレーブであったmysqld_multi 3のリレーログを確認します。すると、”insert into test.sample values (20)”, “COMMIT”まで実行された事が分かります。
[root@localhost ~]# mysqlbinlog --start-datetime="2010-10-13 12:50:00" /var/lib/mysql3/mysql-relay.000002 | tail -n 15 BEGIN /*!*/; # at 4667 #141013 12:53:08 server id 1 end_log_pos 5045 CRC32 0x8f87745d Query thread_id=32 exec_time=0 error_code=0 SET TIMESTAMP=1413172388/*!*/; insert into test.sample values (20) /*!*/; # at 4772 #141013 12:53:08 server id 1 end_log_pos 5076 CRC32 0xa9cf1f3a Xid = 79 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
次にマスターサーバであるmysqld_multi 1のバイナリログを観察します。すると、”insert into test.sample values (20)”, “COMMIT”の次のクエリであるポジション5076より再開すれば良い事が分かります。
[root@localhost ~]# mysqlbinlog --start-datetime="2010-10-13 12:52:00" /var/lib/mysql1/mysql-bin.000002 | less #141013 12:53:08 server id 1 end_log_pos 5045 CRC32 0x8f87745d Query thread_id=32 exec_time=0 error_code=0 SET TIMESTAMP=1413172388/*!*/; insert into test.sample values (20) /*!*/; # at 5045 #141013 12:53:08 server id 1 end_log_pos 5076 CRC32 0x50b97d6c Xid = 11 1 COMMIT/*!*/; # at 5076 #141013 12:53:18 server id 1 end_log_pos 5151 CRC32 0x479d02f9 Query thread_id=33 exec_time=0 error_code=0 SET TIMESTAMP=1413172398/*!*/; BEGIN /*!*/; # at 5151 #141013 12:53:18 server id 1 end_log_pos 5256 CRC32 0x476afd18 Query thread_id=33 exec_time=0 error_code=0 SET TIMESTAMP=1413172398/*!*/; insert into test.sample values (21)
mysqld_multi 1からmysqld_multi 3へのレプリケーションを行います。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql3.sock
mysql> stop slave ;
mysql> reset slave all ;
mysql> CHANGE MASTER TO
-> MASTER_HOST = 'localhost',
-> MASTER_USER = 'root',
-> MASTER_PASSWORD = '',
-> MASTER_PORT = 13301,
-> MASTER_LOG_FILE = 'mysql-bin.000002',
-> MASTER_LOG_POS = 5076
-> ;
mysql> start slave ;
mysql レプリケーション 復旧シナリオ 05 – チェーン構成 スレーブサーバの障害 – 動作確認
スレーブサーバmysqld_multi 3において、レプリケーションが動作している事を確認します。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql3.sock
mysql> show slave status \G ;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 13301
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 22169
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 17376
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
<omitted>
Tips
mysql server データコピー時の注意点
このページ紹介した手順は事前確認なしにデータコピーを行ってますが、実践では丁寧な確認作業が必要となります。cpコマンド、LVM Snapshot, DRBDのようなクラスタリング機能, AWSが提供するスナップショット機能などコピーの手段は様々ですが、これらの手段はディスク上のデータをコピーする事に注意して下さい。つまり、メモリ上のデータはコピーされません。mysqlの複製を作成する時はメモリ上にデータが存在しない事を確認する必要があります。
mysql server データコピー時の注意点 – temporary table
mysqlのデータコピーで、運用者をよく泣かせる仕様のひとつがtemporary tableです。temporary tableはメモリ上に展開されるデータですので、temporary tableが存在する場合はmysqlの複製を行ってはいけません。temporaryテーブルの有無は、”show status”コマンドのSlave_open_temp_tablesで確認する事ができます。
以下のようにSlave_open_temp_tablesが1以上の値となっている場合は、MySQLの複製を控える事をお勧めします。
mysql> stop slave io_thread ; mysql> show status like "Slave_open_temp_tables" ; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Slave_open_temp_tables | 1 | +------------------------+-------+ 1 row in set (0.00 sec) mysql>
mysql server データコピー時の注意点 – ログ先行書き込み(Write Ahead Log)
innodbにはログ先行書き込みという仕組みがあります。ログ先行書き込みとは、「まずは高速なメモリ上にデータを書き込み、後に非同期でファイルに書き込む」高速化のための仕組みです。
メモリ上に書き込まれていても、まだファイルには書き込まれていないデータが存在する場合は、MySQLの複製を控える事をお勧めします。”show engine innodb status”コマンドでLog sequence number, Last checkpoint atなどの値が全て同じになっている場合は、メモリ上のデータが全てディスクに反映されたと判断できます。
[root@localhost ~]$ mysql -uroot --socket=/tmp/mysql3.sock mysql> stop slave io_thread ; mysql> show engine innodb status ; <omitted> --- LOG --- Log sequence number 1680113 Log flushed up to 1680113 Pages flushed up to 1680113 Last checkpoint at 1680113
mysql レプリケーション遅延
mysqlの復旧手順を考える際は、遅延が定常的に発生するのかどうかも含めて手順化する必要があります。私が勝手にネーミングした概念ではありますが、mysqlの遅延は”ネットワーク遅延”と”リレーログ反映待ち”の2種類が存在します。
多くの場合は”リレーログ反映待ち”が実際に起きている遅延です。しかし、遅延に関する誤認識をもっているエンジニアが多いせいか、私は「ネットワークが遅い」「mysqlの遅延が発生している」との問い合わせを数多く対応しました。ネットワークエンジニアの幸せのため、声を大にして誤解を解く説明を行いたいと思います。
mysql レプリケーション遅延 – ネットワーク遅延
ネットワーク帯域が小さい場合やネットワークレイテンシが大きい場合は、マスター スレーブ間でログ転送の遅延が発生しうる場合があります。ネットワークがボトルネックとなり遅延が発生するのは、以下のような場合です。どのようなケースでネットワーク起因の遅延が発生するかを理解するだけで、無駄な切り分け工数や無駄な説明工数を削減する事ができるでしょう。
- 広域網越しでmysql レプリケーションを行なう場合
- fusion io のような高速ストレージを使用し、「ディスク > ネットワーク」 のような関係が成り立つ場合
- BLACKHOLEエンジンのように、メモリ上にデータベースを作成する場合 ( BLACKHOLEの説明は省略します )
- 通信速度が大幅に劣化するようなレアケースのネットワークの障害が発生した場合 ( 例 : STP状態異常、MACアドレスバッティング、スイッチ機器が大量パケットを送付するような残念な壊れ方をした場合 )
それでは、マスター スレーブ間の遅延について確認方法について考えてみましょう。マスタースレーブ間の遅延を確認するには、マスターとスレーブの両方で同時にshowコマンドを実行する必要があります。スレーブサーバのみの情報では、ネットワークの遅延有無は判断できません。
動作確認例は以下の通りです。マスター側のバイナリログのポジションが1280になっているのに対し、スレーブ側のRead_Master_Log_Posが1058になっています。1058から1280がネットワークに起因した遅延分となります。
[master側 動作確認]
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql1.sock -e "show master status \
G" ;
*************************** 1. row ***************************
File: mysql-bin.000003
Position: 1280
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
[root@localhost ~]#
[slave側 動作確認]
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql3.sock -e "show slave status \G; " | egrep " Master_Log_File|Read_Master_Log_Pos:"
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1058
[root@localhost ~]#
mysql レプリケーション 基本概念 – 遅延確認 – リレーログ反映待ち
MySQLはマスターと実行したクエリと同様のクエリがスレーブで実行されます。スレーブサーバではリレーログに書かれたSQLが順次実行されますので、処理に時間がかかるSQLが発行されれば、リレーログ反映待ちの遅延になります。
“ネットワーク遅延”と”リレーログ反映待ち”の2パターンの遅延を紹介しましたが、私が実務で経験した遅延は全て”リレーログ反映待ち”です。 mysqlの遅延と言えば多くの場合は、リレーログ反映待ちを指すと思って下さい。
リレーログ反映待ちが発生しうる状況としては以下をあげる事ができます。
- マスター, スレーブ間でI/O性能に大きな差がある ( BLACKHOLEエンジンもI/O差が発生するデザインパターンの一種)
- LOAD DATA, bulk insert等の大量更新処理が行なわれる
- 何らかの理由によりスレーブサーバにロックがかけられる
動作確認例は以下の通りです。スレーブサーバで”show slave status”を実行します。Read_Master_Log_PosとExec_Master_Log_Posに差が生じている場合は、リレーログ反映待ちの遅延が発生していると判断できます。以下出力例の場合は、839から1058までが遅延となります。
[root@localhost ~]$ mysql -uroot --socket=/tmp/mysql3.sock -e "show slave status \G;" | egrep "Master_Log_File:|_Master_Log_Pos"
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1058
Relay_Master_Log_File: mysql-bin.000003
Exec_Master_Log_Pos: 839
[root@localhost ~]$
“show slave status”のSeconds_Behind_Masterからリレーログ反映待ちを判断する事ができます。Seconds_Behind_Masterはリレーログ反映にかかる推定時間(秒)です。Seconds_Behind_Masterが0より大きい値を示しているならば、遅延が発生していると思って下さい。
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql3.sock -e "show slave status \G;" | grep "Sec"
Seconds_Behind_Master: 3
[root@localhost ~]#
リレーログ反映待ちのクエリを抽出するには、MySQLマスターサーバのバイナリログに対してmysqlbinlogコマンドを実行します。Exec_Master_Log_PosからRead_Master_Log_Posまでのポジションを指定して下さい。
[master側 バイナリログ抽出] [root@localhost ~]# mysqlbinlog --start-position=839 --stop-position=1058 /var/lib/mysql1/mysql-bin.000003 | grep ^insert insert into test values (7),(8),(9) [root@localhost ~]#
リレーログ反映待ちのクエリは、MySQLスレーブのリレーログからも抽出する事ができます。 Relay_Log_Posから末尾までのログを抽出する事でも同様の結果を得る事ができます。
[slave側 リレーログ抽出]
[root@localhost ~]# mysql -uroot --socket=/tmp/mysql3.sock -e "show slave status \G;" | egrep "Relay_Log_File:|Relay_Log_Pos:"
Relay_Log_File: mysql-relay.000004
Relay_Log_Pos: 829
[root@localhost ~]#
[root@localhost ~]#
[root@localhost ~]# mysqlbinlog --start-position=829 /var/lib/mysql3/mysql-relay.000004 | grep ^insert
insert into test values (7),(8),(9)
[root@localhost ~]#
