MySQLdを冗長構成にする

anonymous@undisclosed.example.com (Anonymous) — Mon, 14 May 2018 04:36:12 +0000

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のはず。

ほほほのほ - serverapp:database:mysqld

MySQLdを冗長構成にする