MySQLのレプリケーションを構築してみる

概要

最近クラウド化でアプリサイドのインフラ知識が無いエンジニアでも、
インフラ周りの構築の敷居が下がってきているので、DBサーバーくらいは立てられるようになっておこうというハナシ。

そもそも冗長化とは

冗長化（じょうちょうか）とは、システムの一部に何らかの障害が発生した場合に備えて、障害発生後でもシステム全体の機能を維持し続けられるように、予備装置を平常時からバックアップとして配置し運用しておくこと。 冗長化によって得られる安全性は冗長性と呼ばれ、英語ではredundancyと呼ぶ。
wikipedia

サービスを運用するにあたりDBは当然不可欠ですし、故障などのトラブルが発生した場合に速やかにサービスが復旧できる必要があります。
この為、DBに限らずサービスに関わるシステムは基本的に冗長化構成をしておきます。

DBの冗長化方式

DBの冗長化にはいくつかの方式があります

アクティブ・スタンバイ方式

• 2つ以上のDBを用意し、メインで稼働するDBに対してレプリケーション機能を使いデータ同期してスタンバイしておく方法
• スタンバイしているDBはデータ同期のみされサービスでは利用されない

Master/slave方式

• 「アクティブ・スタンバイ方式」のスタンバイしたDBを"参照用(slave)"としてサービスINする方式
• slaveはレプリケーション以外の書き込み不可としておく
• Masterに障害が発生した場合に、slaveをmasterに昇格する（フェールオーバー）
• 1つのmasterに対して複数のslaveを複数用意することで負荷分散が可能となる
• 負荷分散にはロードバランサー(haproxyなど)が必要となる
• デメリットとして、Masterに対してslaveが追いついていない状態で参照が行われると「slave遅延」が発生しうる
  

マルチマスタ構成

• 全てのDBが書き込み・読み込みできる構成
• 同一エントリの追加や削除を行った場合に動作が不安定になりがち

実際に作ってみる

Master/Slaveの2台のサーバーを立てる

割愛(AWSでもなんでもよいです)

Mysqlのインストール

#mysqlをインストールする
yum install mysql
#まっさらなDBを作成する
mysql_install_db 
#mysqlプロセスの起動
/etc/init.d/mysql start 

MasterDB想定の設定

# my.confの設定
vim /etc/my.conf
##下記設定にする
log-bin                 = mysqld-bin
binlog-format           = MIXED
expire_logs_days        = 1
log-slave-updates
max_binlog_size         = 268435456

--レプリケーション用のuserを追加する
GRANT REPLICATION SLAVE ON *.* TO [e-mail] IDENTIFIED BY 'PASSWORD';
--適当なテーブルの作成
CREATE TABLE IF NOT EXISTS `test` (
  `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `nickname` varchar(256) DEFAULT 'player' COMMENT 'ニックネーム',
  `delete_flg` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '削除フラグ',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='ユーザー情報' AUTO_INCREMENT=1 ;

#slave構築用にDBをdump
mysqldump -u root --all-databases --events --single-transaction --flush-logs --master-data=2 --hex-blob --default-character-set=utf8 > master_db.sql
#slave構築するサーバーにコピー
scp /master_db.sql [e-mail]:/tmp/

SlaveDB想定の設定

# my.confの設定
vim /etc/my.conf
##下記設定にする
relay-log               = mysqld-relay-bin
slave-skip-errors       = 1053, 1205
low-priority-updates   = 1
concurrent_insert      = 2
read-only
skip-slave-start
# dumpデータの流し込み
mysql -u root < master_db_sql
# Masterのバイナリログファイル名(MASTER_LOG_FILE)と位置(MASTER_LOG_POS)を確認する
head -n 100 master_db.sql | grep CHANGE
>-- CHANGE MASTER TO MASTER_LOG_FILE='mysqld-bin.000002', MASTER_LOG_POS=107;

-- DBをslaveとして動くように設定する
CHANGE MASTER TO
 MASTER_HOST='172.26.x.xx', #master相当のDBのIPを入れる
 MASTER_PORT=3306,
 MASTER_USER='repl',
 MASTER_PASSWORD='PASSWORD',
 MASTER_LOG_FILE='mysqld-bin.000xxx', #↑で取得したMASTER_LOG_FILE
 MASTER_LOG_POS=xxx; #↑で取得したMASTER_LOG_POSが入る
-- レプリケーションの開始
slave start;
show slave status\G; --behindMasterが0になればOK。NULLはレプリケーション失敗状態

レプリケーション挙動の確認

masterDBでinsert

INSERT INTO test_table VALUES(NULL, "test_user", NOW(), NOW(), 0); 

slaveDBでselect

SELECT * FROM test_table;

slaveDBにMasteDBで流したデータが存在していればレプリケーション成功

ハマりどころ

start slave; --レプリケーション開始したが・・
show slave status\G;
Last_IO_Error: error connecting to master '[e-mail]:3306' - retry-time: 60  retries: 86400 --接続できてない

接続確認

ping 172.26.x.xx #pingは通るか？
telnet 172.26.x.xx 3306 #3306ポートはどうだ？
Host '172.26.9.61' is not allowed to connect to this MySQL serverConnection closed by foreign host.

Master側に外部接続可能なレプリケーションユーザーがいなかった・・・

GRANT REPLICATION SLAVE ON *.* TO [e-mail] IDENTIFIED BY 'PASSWORD';
select host, user FROM mysql.user
+----------------+-------------+
| host           | user        |
+----------------+-------------+
| 172.26.x.x     | replication | --これがいないとダメ。
| 127.0.0.1      | root        |
| ::1            | root        |
| localhost      |             |
| localhost      | root        |
+----------------+-------------+

SQLはサービスの中でも一番ボトルになりやすい部分なので、
冗長化を理解しておくとサービスで何か起きた時の安心感があると思います。
とはいえアプリサイドのエンジニアは冗長化より先にExplainする癖つけましょう。