MySQLdを冗長構成にする
Last Update: 2014/10/14
近年インターネットに公開されている様々なシステムは、LAMP(Linux/Apache/MySQL/PHP)を利用しているものが多い。 その上、最近では高可用性が要求されるような状況が増えてきている。
このようなシステムを構築する際に問題になるのが、システムの冗長性を確保して、サービスシステム内の一部が停止してもサービス自体は継続できるようにする手法である。
このような時に、Web側システムはLoadBalancer等をうまく使うことで冗長性を確保できる「場合もある」が、DBはそう簡単に問屋がおろしてくれない。 これは、DBが扱うデータがatomicでなければならず、その状態を保持することが難しいからである。
ここでは、MySQL 5.5.31を利用した冗長構成作成に関して記録しておく。
なお、MySQLはOracleに買収されたので、MariaDBがForkされた。MariaDB5.5では以下の手法は利用できた。
参考URL
手法検討
MySQLdを冗長構成にする方法としては、一般に以下の3つがある。
手法 | 同期 | 備考(個人的なイメージとも云う) |
---|---|---|
Replication | async | 標準で利用できる。比較的簡単。障害時にデータがLostする可能性がある |
Cluster | sync | 詳しく知らない。Cluster用のMySQLdが必要。チューニングが難しい。システムが大きくなる。 |
semi-sync Replication | semi-sync | 標準で利用できるが、Pluginが必要。比較的簡単。障害時のData lostの可能性は低いが、性能も低下する |
Replication
Replicationは、最も設定が簡単でその割に比較的よく動作するので、従来(5.3以前)は非常によく利用されていた。 Replicationの問題は、Master-Slave間で同期する際に、同期にタイムラグがあることである。
client master slave --(SQL)---> 処理 <--(返事)-- --(binlog)--> 処理 <---(同期)---
SlaveがMasterからバイナリLOGを取得し、更新を実行することで同期するしくみ。 このような処理の流れなので、同期はasyncとなる。その結果、タイミングによってデータに不整合が起きる可能性がある。
障害 | Service継続 | Connection継続 | Transaction継続 | 同期 |
---|---|---|---|---|
Master停止 | ◯ | × | × | 停止 |
Slave停止 | ◯ | ◯ | ◯ | 停止 |
障害機を復帰させる際には、一度サービスを停止し、DBの同期をとる必要がある。 復帰させる際の停止を避けるには、Slaveを2台準備し、1台の停止では同期が外れないようにするなどの対策をとる必要がある。
Cluster
Clusterには詳しくないが、Clusterは、Replicationと異なり、システム側で同期を保障する構成である。 MySQLでClusterを構築するには、少なくとも
- Data node (データ/レコードの保存場所)
- SQL node (SQL文の構文解析等を担当するノード)
- Management node (管理ノード)
の3機能が必要になる。縮退すれば最低3台から構築できるが、冗長化を考えるなら、一般には最低で5台のServer(Data x2/SQL x2/Mgmt x1)を準備すべきであろう。(Clusterの実験をするだけなら3台から構築できる)
Web ApplicationのためにMySQL Clusterを利用するなら、サーバー停止などの場合の運用も考慮するとWebServerにhttpd+SQL nodeを入れ、WebServerのApplicationからlocalhostのSQL nodeを参照するようにするのが比較的良い構成と考えられる。こうすれば、WebServerとSQL nodeの通信の部分を非常に単純化して考えられる。近年しばしば利用されるFastCGIを利用するなら、FastCGI ServerにSQL nodeを入れればよい。
障害 | Service継続 | Connection継続 | Transaction継続 | 同期 |
---|---|---|---|---|
SQL node停止 | ◯ | △ | △ | 継続 |
Data node停止 | ◯ | ◯ | ◯ | 継続 |
Mgmt node停止 | ◯ | ◯ | ◯ | 継続 |
SQL-Data間障害 | ◯ | ◯ | × | 継続 |
Data-Data間障害 | ◯ | ◯ | × | 状況による |
もちろん、常識的に考えて、全Data nodeが非同期に死んだ場合など、同期を継続できない状況は考え得る。
なお、Clusterを構築する場合、ネットワークの設計だけでなく、データ構造などもしっかり検討する必要がある。
Semi-Synchronous Replication
Semi-Synchronous Replication(以下Semi-Sync)は、(いわゆる標準の)Replicationの(同期が外れやすい)問題に対する解決策の一つとして利用できる。
client master slave --(SQL)---> 処理 --(同期)--> 前処理 (binlogのcacheing) <--(返事)-- <--(返事)-- 後処理 (DBファイルへの変更のCommit)
Semi-Syncでは、上記のような流れで処理が行われるため、データの破損は非常に起こりにくい形になっている。実際、データの破損が起こる条件を想定することは難しい。 その代わりに、この構成の場合、ClientがSQL処理終了を受けとるまでの時間が長くなる。
masterがslaveに送った同期処理は、slave側で一度記録された段階でslaveからmasterに返事が返る。その後、slaveではゆっくり同期処理をする。 その為、「完全な同期状態ではない」瞬間が存在することになる。Semi-Syncと呼ばれるのはその為である。
実際には、slaveが「返事を行った後、同期処理を終了するまで」の段階で障害が発生したとしても、単に同期処理前までRollbackし、再度同期処理を行うことで同期状態を維持する。その為、実質上masterとslaveが非同期な状態になるシチュエーションはほとんど無いと考えられる。
- 但し、実装上の問題や誤解がある可能性はあるので、気になるかたは十分に調査することをお勧めする。
障害 | Service継続 | Connection継続 | Transaction継続 | 同期 |
---|---|---|---|---|
Master停止 | ◯ | × | × | 継続 |
Slave停止 | ◯ | ◯ | ◯ | 継続 |
障害機を復帰させる場合、通常はサービスを停止する必要はない。復帰時点で再度同期される。つまり、非同期のReplicationでどうしても必要になるFailbackを考慮する必要は無くなる事が期待できる。
注意点
上記説明の際に、DBプログラミングを行う際に必要な注意事項は記載していない。 DBプログラミングは、データのAtom性を確保するために、DB側だけでなくプログラム側でも注意すべき点があるが、そこには触れないものとする。
手法
上記及びその他の理由(割愛)により、Semi-Sync ReplicationとKeepalivedを用いて、MySQLdを冗長化する。
Keepalived
Semi-Sync ReplicationでMultiMaster Replicationを組むことで、いわゆるMultiHeadでありかつFailback操作が不要のDBシステムが組める。
しかし、MultiHead DBで非同期処理をする場合、SQL Queryを出すApplicationの側で考慮すべき事項が非常に多い。特に、データのAtomic性に関しては非常に短時間であっても保障されない場合がある。Slave側がMasterにAckを帰した直後からData操作が終了するまでの間は、MasterとSlaveのDataは同期していない(つまりSlave側のDataは古い)状態になる。これは、特にトランザクションの多いDBで問題が発生する。 このような用途ならばClusterやOracle/DB2等を利用するべきであろう。
Semi-Sync ReplicationでMultihead DBを組むのが(実用上)ベストだと筆者が思う状況は、
- 既存のWebアプリでMySQLを利用しており
- サービス停止にセンシティブな状況
である。つまり、
- 本来MySQL Serverとしては1台を想定している「DBの冗長化を考えていないWebアプリケーション」を
- DB側で何とかして冗長化して使う
場合である。要するに、
- SQLクエリを飛ばす側からはMySQLが1台に見えていて欲しい
- MySQLサーバーは冗長にしたい
という場合である。
このような要望に答えるための実装にはいくつかあるが、今回はKeepalivedを利用する。
Keepalivedは「VRRP」を利用して、「あるサービスが死んだ」ら「それをトリガーとしてVRRP的切替を行う」daemonである。
これを利用することで、アプリケーションからは『冗長化されたMySQLサーバー』を『いつも通り』に利用できるようになる。
技術的には半端なかき方をしてますので、気持ちだけわかってください
構成
HW | OS | MySQL | keepalived | その他 |
---|---|---|---|---|
XCP上のVM | CentOS 6.4 | 5.5.31 | 1.2.7 | MySQLdはremiで公開されているもの |
上記VMを2台(以下 my01, my02とする)構築し、冗長系を構築した。
構築
CentOSのInstall等は全て割愛。注意点は
- remi repositoryからMySQL関連のpackageをyumで投入
- compat-mysql51.x86_64
- mysql.x86_64
- mysql-libs.x86_64
- mysql-server.x86_64
- keepalivedをyumで投入
MySQL 初期構成
- /etc/my.cnfを修正(my01/my02)
... [mysqld] ... skip-external-locking character-set-server=utf8 skip-character-set-client-handshake skip-name-resolve # Replication関連 log-bin = /some/where/mysql-bin <- 必須。どこかにbin-logを保存すること relay-log = /some/where/mysqld-relay-bin server-id = 1 <- マシン毎に変えること。(my01は1、my02は2を設定した) binlog_format=mixed max_binlog_size=100M expire_logs_days=7 sync_binlog=1 log_slave_updates # SemiSync関連設定 #rpl_semi_sync_master_enabled =1 <- 当初はcomment outすること #rpl_semi_sync_slave_enabled = 1 #rpl_semi_sync_master_timeout=10
- my01(Master側)でmysqldを起動
- service mysqld start
- 通常の最小限の設定をする。(rootアカウントにパスワードをつけるなど)
- Replication用のアカウントを作成する
my01# mysql -u root -p mysql> GRANT REPLICATION SLAVE ON *.* TO replication@'my01' IDENTIFIED BY '****----****'; <- Account作成 mysql> GRANT REPLICATION SLAVE ON *.* TO replication@'my02' IDENTIFIED BY '****----****'; <- Account作成 mysql> select user,host,password from mysql.user; <- 確認
- 今回は、初期構築段階からの設定なので割愛するが、ここでMasterとSlaveの手動での同期を取っておく。
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 1222 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> ^D my01# cd /var/lib/mysql/ <-- MySQLのデータディレクトリへ移動 my01# tar czf ~/xxxxxDB.tgz xxxxxDB <-- データベースをバックアップ my01# scp xxxxxDB.tgz my02: my01# mysql -u root -p mysql> UNLOCK TABLES; mysql> ^D ===== my02# my02# cd /var/lib/mysql/ <-- MySQLのデータディレクトリへ移動 my02# tar xzf ~/xxxxxDB.tgz <-- Databaseファイルを展開
- my02(slave側)でmysqldを起動
- service mysqld start
- Replication設定を行う
mysql> CHANGE MASTER TO MASTER_HOST=my01, MASTER_USER='replication', MASTER_PASSWORD='****----****', MASTER_LOG_FILE='mysql-bin.000003', <- master statusで表示されたファイル名を投入 MASTER_LOG_POS=1222; <- master statusで表示されたPositionを投入
- 今回はMultimaster構成にするので、以下も実施
my02# mysql -u root -p mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 750 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> ^D my02# ===== my01# mysql -u root -p mysql> CHANGE MASTER TO MASTER_HOST=my02, MASTER_USER='replication', MASTER_PASSWORD='****----****', MASTER_LOG_FILE='mysql-bin.000003', <- master statusで表示されたファイル名を投入 MASTER_LOG_POS=750; <- master statusで表示されたPositionを投入 mysql> ^D my01#
- ここまで終わった段階で、両方のマシンをslaveとして動かす。
mysql> slave start; mysql> show slave status\G <- 確認 .... Slave_IO_Running: Yes Slave_SQL_Running: Yes ....
- 両方がYesであることを確認
- これで Multimaster Async Replicationとして動作開始
Semi-Synchronous Replication
MariaDB 5.5.31でも動作する可能性がありますが未検証です。しました。
ここまでくればあと少し。一気に行きます。
- my01/02で以下を実行
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'; mysql> show plugins; +--------------------------+----------+--------------------+--------------------+---------+ | Name | Status | Type | Library | License | +--------------------------+----------+--------------------+--------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | DISABLED | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | GPL | <-- ここ | rpl_semi_sync_slave | ACTIVE | REPLICATION | semisync_slave.so | GPL | <-- ここ +--------------------------+----------+--------------------+--------------------+---------+ 25 rows in set (0.00 sec)
- my.cnfを書き換える
- Semi-Sync関連の設定を生かす
- mysqldの操作
- service mysqld stop
- service mysqld start
- mysqldの状態を確認
mysql> show variables like '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | ON | <=== | rpl_semi_sync_master_timeout | 10 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | | rpl_semi_sync_slave_enabled | ON | <=== | rpl_semi_sync_slave_trace_level | 32 | +------------------------------------+-------+ 6 rows in set (0.01 sec) mysql> SHOW STATUS LIKE "rpl%"; +--------------------------------------------+-------------+ | Variable_name | Value | +--------------------------------------------+-------------+ | Rpl_semi_sync_master_clients | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 0 | | Rpl_semi_sync_master_no_times | 0 | | Rpl_semi_sync_master_no_tx | 0 | | Rpl_semi_sync_master_status | ON | <=== | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | | Rpl_semi_sync_slave_status | ON | <=== | Rpl_status | AUTH_MASTER | +--------------------------------------------+-------------+ 16 rows in set (0.00 sec)
落ち穂拾い
今の状態で、mysqldはMultimaster Semi-Synchronous Replicationが組めている状態になる。 あとは、keepalivedでClientからの通信を受けるIPアドレスを生かすだけ。
- keepalived
# cd /etc/keepalived # mv keepalived.conf keepalived.conf.dist # vi keepalived.conf ===== ここから ===== # # Keepalived configuration file for # WPSd01 MySQL Semi-Sync Replica Server # # $ID:$ # global_defs { notification_email { foo@example.com } notification_email_from keepalived@my01.example.com smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id my01 } vrrp_instance my01 { state BACKUP interface eth0 garp_master_delay 1 virtual_router_id 1 priority 100 advert_int 1 smtp_alert authentication { auth_type PASS auth_pass ----****---- } virtual_ipaddress { 192.0.2.1/29 dev eth1 } } virtual_server 192.0.2.1 3306 { delay_loop 3 lvs_method DR protocol TCP real_server 192.0.2.2 3306 { TCP_CHECK { connect_port 3306 connect_timeout 10 } notify_down "/etc/keepalived/shutdown_keepalived.sh down" } } ===== ここまで ===== # vi shutdown_keepalived.sh ===== ここから ===== #! /bin/bash # daemon shutdown script called from Keepalived. # # $ID:$ # if [ $# -ne 0 ]; then # Notify fault if [ $1 = "fault" ]; then /etc/rc.d/init.d/keepalived stop /etc/rc.d/init.d/mysqld stop fi # Notify down if [ $1 = "down" ]; then sleep 5 mysqlans=`/usr/bin/mysql -u slave -p"WPS Replica" -h 127.0.0.1 < "/etc/keepalived/showstat.sql" | /bin/grep Bytes_received` if [ "" = "$mysqlans" ]; then /etc/rc.d/init.d/mysqld restart /etc/rc.d/init.d/mysqld status if [ $? -ne 0 ]; then /bin/bash /etc/keepalived/shutdown_keepalived.sh fault fi fi fi fi ===== ここまで ===== # vi showstat.sql ===== ここから ===== show status; ===== ここまで =====
これでOKのはず。