前提

これから作るもの

MySQL Multi Masterといっているけれど、実際に作ろうとしているのは Percona XtraDB Cluster だ。NDB Clusterとは別物。Galera Clusterの系統だ。
ネットやQiitaを探せばいろんな人がやっている記録が出てくるけれど、自分でやってみることに価値があるってことで。

本来はスプリットブレインを避けるために3台orそれ以上の奇数での構成を推奨しているが、とりあえず動作を見たいだけなので2台で作る。

手順について

本手順ではコンソール作業を「$」プロンプトで書いているけれど、 全部root作業 だと思ってほしい。フォーマットの関係で#だとコメントアウトになってしまって読みにくいので・・・

試験環境の構築

とにもかくにもVagrantが楽ちんなのでVagrantで作る。

Vagrant セットアップ

$ vagrant box add centos7 https://github.com/holms/vagrant-centos7-box/releases/download/7.1.1503.001/CentOS-7.1.1503-x86_64-netboot.box
$ vagrant init centos7

$ vim VagrantFile

最後のendの前に、以下を追加する。
設定値中のメモリサイズとかは各環境で調整してほしい。

  ### server1の設定内容
  config.vm.define "s1" do |server|
    server.vm.hostname = config.vm.box + "-s1" + ".vagrant"
    server.vm.network "private_network", ip: "192.168.200.101", host: "52001"
    config.vm.provider "virtualbox" do |vb|
      ### virtualbox上で表示される名前
      vb.name = config.vm.box + "_s1"
      vb.customize ["modifyvm", :id, "--memory", "2048"]
      vb.customize ["modifyvm", :id, "--cpus", "2"]
    end
  end
  ### server2の設定内容
  config.vm.define "s2" do |server|
    server.vm.hostname = config.vm.box + "-s2" + ".vagrant"
    server.vm.network "private_network", ip: "192.168.200.102", host: "52002"
    config.vm.provider "virtualbox" do |vb|
      ### virtualbox上で表示される名前
      vb.name = config.vm.box + "_s2"
      vb.customize ["modifyvm", :id, "--memory", "2048"]
      vb.customize ["modifyvm", :id, "--cpus", "1"]
    end
  end

起動

$ vagrant up s1
$ vagrant up s2

以降はこんな感じでログインできる。(vagrant ssh使ってもいいけど)

$ ssh 192.168.200.101 -l root
$ ssh 192.168.200.102 -l root

どちらも、パスワードはvagrant

疎通NGのときはvagrant reload s1してみよう。

Percona XtraDB Clusterインストール

事前準備

入れても入れなくてもいいけど、minimalに含まれていないパッケージを入れる。

$ yum install net-tools
$ yum install vim

検証なのでFWを無効化する。(当然非推奨）

$ firewall-cmd --set-default-zone=trusted
$ firewall-cmd --get-zone-of-interface=eth1
## trustedになっていること

## trustedではない場合
$ firewall-cmd --zone=trusted --change-interface=eth1
$ firewall-cmd --get-zone-of-interface=eth1

パッケージインストール

オフィシャルの記載を参考に進める。

レポジトリの追加

$ yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
$ yum search Percona

インストール

サーバーをインストールすれば、依存関係でクライアントもインストールされる。

$ yum install Percona-XtraDB-Cluster-server-56

初期設定〜初回起動

ノード1の初期設定

オフィシャルドキュメントに基づいて進める。

設定ファイル作成

設定値は色々な考えがあって良いのだが、使用するwsrep_sst_methodの関連で必須パラメータがいくつかある。(まだ記載漏れがあるかもしれない)
パラメータが漏れていてもmysqldは自動的にデフォルト値を使って起動してくれるが、wsrep_sst_methodのxtrabackup-v2などのプログラムが正常動作せずに、2台目の追加時等にエラーを吐くことがある。しかもエラーが読みにくいので注意。私はここで少しハマった。

server_id
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
innodb_log_file_size
datadir

また、これは必須ではなさそうだが、安全性のために設定するのが良いようだ。

log_slave_updates

/etc/my.cnf

# Template my.cnf for PXC
# Edit to your requirements.

[mysqld]
user = mysql
datadir = /var/lib/mysql
log_bin
default_storage_engine  = InnoDB

# In order for Galera to work correctly binlog format should be ROW
binlog_format                  = ROW
log-bin=/var/lib/mysql/mysqld-bin
expire_logs_days=1

innodb_buffer_pool_size        = 100M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 64M
innodb_file_per_table          = 1
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode       = 2

explicit_defaults_for_timestamp = 0

# Cluster
log-slave-updates
wsrep_cluster_name             = TestCluster
wsrep_provider                 = /usr/lib64/galera3/libgalera_smm.so
wsrep_sst_method               = xtrabackup-v2
wsrep_slave_threads            = 8
wsrep_sst_auth                 = "sstuser:reppassword"
wsrep_cluster_address          = gcomm://192.168.200.101,192.168.200.102
wsrep_node_address             = 192.168.200.101
wsrep_node_name                = Node1
server_id = 1


[mysqld_safe]
pid-file = /run/mysqld/mysql.pid
syslog


!includedir /etc/my.cnf.d

ノード1初回起動

1台目のサーバーの場合は、参加するクラスターノードがいないので、最初のノード(bootstrap)を意味する@bootstrap.serviceを付与して起動する必要がある。

$ systemctl start mysql@bootstrap.service
$ journalctl -u mysql

rootユーザへのパスワード設定とユーザー作成

ノード2を作成する前に、オフィシャルにならってレプリケーション用にsstuserを作成する。また、rootユーザーにパスワードを設定しよう。
違和感を覚えるが、sstuserはlocalhostで良いようだ。

$ mysql -u root
mysql> UPDATE mysql.user SET password=PASSWORD("password") where user='root';

mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'reppassword';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';

mysql> FLUSH PRIVILEGES;

ノード2の初期設定

インストールと設定をする。インストール部分は割愛。

設定ファイル作成

基本的にはノード1から複製すれば良い。その後、以下のパラメータを変える。

wsrep_node_address
wsrep_node_name
server_id

下記にdiff形式で差分を載せる。

/etc/my.cnf

@@ -31,9 +31,9 @@
 wsrep_slave_threads            = 8
 wsrep_sst_auth                 = "sstuser:reppassword"
 wsrep_cluster_address          = gcomm://192.168.200.101,192.168.200.102
-wsrep_node_address             = 192.168.200.101
-wsrep_node_name                = Node1
-server_id = 1
+wsrep_node_address             = 192.168.200.102
+wsrep_node_name                = Node2
+server_id = 2

ノード2起動とクラスターへの参加

こちらは単に起動すればいい。

$ systemctl start mysql

自動的に初期同期が行われ、先ほど作ったユーザー情報も同期される。
ただし要注意情報として、これは 初回の動作 であることを覚えておきたい。

基本的にXtraDB Clusterは InnoDBのみを同期対象としている ので、MyISAMであるmysql.userテーブルは一部の操作以外は"正確に"同期されない。（DDLであるCREATE, ALTER, GRANT, TRUNCATE, DROPは同期されるが、UPDATE文などは同期されない）
mysql.userテーブルを操作していると、この辺りに「あれ？」って首を傾げることになることに注意だ。

補足：DBエンジンの確認方法

$ mysql -u root -ppassword

mysql> use information_schema;
mysql> select table_name, engine from tables where table_schema = 'mysql';

動作確認

期待通り同期されていることを順に確認してみる。

データベースの作成

ノード1で実施。

$ mysql -u root -ppassword

[rootユーザー]
mysql> show databases like 'newdb';
Empty set (0.00 sec)

mysql> create database newdb;

mysql> show databases like 'newdb';
+------------------+
| Database (newdb) |
+------------------+
| newdb            |
+------------------+
1 row in set (0.00 sec)

ノード2で確認

$ mysql -u root -ppassword

[rootユーザー]
mysql> show databases like 'newdb';
+------------------+
| Database (newdb) |
+------------------+
| newdb            |
+------------------+
1 row in set (0.00 sec)

同期されている。

ユーザー作成と削除

前述のとおり、mysql.userはMyISAMなので注意が必要であるが、CREATE文であれば同期が走る。
ノード1で実施。

$ mysql -u root -ppassword

[rootユーザー]
mysql> create user 'test_user'@'localhost' identified by 'password';
mysql> grant process on *.* to 'test_user'@'localhost';
mysql> flush privileges;
mysql> select user,host,password from mysql.user where user='test_user';
+-----------+-----------+-------------------------------------------+
| user      | host      | password                                  |
+-----------+-----------+-------------------------------------------+
| test_user | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

## このままセッションを維持。

前述の通り、CREATE文のためノード2でユーザーが複製されていることが確認できる。

$ mysql -u root -ppassword

[rootユーザー]
mysql> select user,host,password from mysql.user where user='test_user';
+-----------+-----------+-------------------------------------------+
| user      | host      | password                                  |
+-----------+-----------+-------------------------------------------+
| test_user | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> \q

ノード2でパスワードや権限が同期していることを確認。

$ mysql -u test_user -ppassword
## ローカルホストのtest_userとしてログインできる。


[test_userユーザー]
mysql> show processlist;
## プロセスリストが表示される。

mysql> \q

ノード2でユーザーを削除してみる。

$ mysql -u root -ppassword

[rootユーザー]
mysql> drop user 'test_user'@'localhost';

ノード1でつないだままのセッションを使って確認。

[rootユーザー]
mysql> select user,host,password from mysql.user where user='test_user';
Empty set (0.00 sec)

mysql> \q

もちろん改めてログインしようとしてもユーザーは削除されている。

$ mysql -u test_user -ppassword
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)

mysqlslap

ベンチマークツールのmysqlslapを使って、動作を確認する。

準備

こんな感じでShellスクリプトを作る。
ただのワンライナーなんだけど、繰り返しやすいようにって意味と、コマンドが長すぎるので。

domysqlslap.sh

#!/bin/bash
mysqlslap \
  --no-defaults \
  --concurrency=5 \
  --iterations=4 \
  --engine=innodb \
  --auto-generate-sql \
  --auto-generate-sql-add-autoincrement \
  --auto-generate-sql-load-type=mixed \
  --auto-generate-sql-write-number=1000 \
  --number-of-queries=100000 \
  --host=localhost \
  --port=3306 \
  --user=root \
  --password=password \
  -v

最後の-vの数を増やせば(-vvとか)情報量が増える。
詳しい例はこちらを見た方がいい。

実行(シングル)

まずはノード1にやってみる。

$ bash domysqlslap.sh

Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 18.045 seconds
    Minimum number of seconds to run all queries: 17.676 seconds
    Maximum number of seconds to run all queries: 18.441 seconds
    Number of clients running queries: 5
    Average number of queries per client: 20000

エラーなくテストが終わった。性能面は気にしないでほしい。
同様に後で起動したノード2でも試してみる。

$ bash domysqlslap.sh
Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 16.878 seconds
    Minimum number of seconds to run all queries: 16.517 seconds
    Maximum number of seconds to run all queries: 17.295 seconds
    Number of clients running queries: 5
    Average number of queries per client: 20000

この間、topなどを眺めていると、もう片方にも負荷がかかっていることが確認できる。

並行アクセス動作確認

HAProxyの準備

ロードバランサとしてそれぞれのホスト上にhaproxyを準備する。
性能面ではKeepalivedの方が望ましいが、第三者的ホストが必要となってしまうので、手抜きでご勘弁を。

インストールと設定

yumで入れる。

$ yum install haproxy

$ vim /etc/haproxy/haproxy.cfg

defaults以下のブロックを以下のように変える。
燦然と輝く balance roundrobin の1行が、Master-Slave構成ではないことの証明。
この検証ではhaproxyをmysqldノードに相乗りさせているので、haproxyのポートを53306に設定している。

defaults
    mode                    tcp
    log                     global
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout check           10s
    maxconn                 3000

listen MySQL 0.0.0.0:53306
    timeout connect 10s
    timeout client 1m
    timeout server 1m
    mode tcp
    balance roundrobin
    option mysql-check user haproxy
    server s1 192.168.200.101:3306 check
    server s2 192.168.200.102:3306 check

DBにhaproxy動作確認用ユーザと、mysqlslap用ユーザを作る。(今回はアクセス元を%にしている)

mysql> GRANT USAGE ON *.* TO 'haproxy'@'%';
mysql> CREATE USER 'test_user'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'%';
mysql> FLUSH PRIVILEGES;

動作確認

haproxyのステータス確認。（バックエンド指定のs1,s2がUPであること)

$ systemctl start haproxy

$ echo "show stat" | socat stdio /var/lib/haproxy/stats
# pxname,svname,qcur,qmax,scur,smax,slim,stot,bin,bout,dreq,dresp,ereq,econ,eresp,wretr,wredis,status,weight,act,bck,chkfail,chkdown,lastchg,downtime,qlimit,pid,iid,sid,throttle,lbtot,tracked,type,rate,rate_lim,rate_max,check_status,check_code,check_duration,hrsp_1xx,hrsp_2xx,hrsp_3xx,hrsp_4xx,hrsp_5xx,hrsp_other,hanafail,req_rate,req_rate_max,req_tot,cli_abrt,srv_abrt,comp_in,comp_out,comp_byp,comp_rsp,lastsess,last_chk,last_agt,qtime,ctime,rtime,ttime,
MySQL,FRONTEND,,,0,0,3000,0,0,0,0,0,0,,,,,OPEN,,,,,,,,,1,2,0,,,,0,0,0,0,,,,,,,,,,,0,0,0,,,0,0,0,0,,,,,,,,
MySQL,s1,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,1,0,0,0,2,0,,1,2,1,,0,,2,0,,0,L7OK,0,0,,,,,,,0,,,,0,0,,,,,-1,5.6.24-72.2-56-log,,0,0,0,0,
MySQL,s2,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,1,0,0,0,2,0,,1,2,2,,0,,2,0,,0,L7OK,0,1,,,,,,,0,,,,0,0,,,,,-1,5.6.24-72.2-56-log,,0,0,0,0,
MySQL,BACKEND,0,0,0,0,300,0,0,0,0,0,,0,0,0,0,UP,2,2,0,,0,2,0,,1,2,0,,0,,1,0,,0,,,,,,,,,,,,,,0,0,0,0,0,0,-1,,,0,0,0,0,

ロードバランサの動作確認。

$ mysql -u test_user -ppassword -h 192.168.200.101 -P 53306 -e'show variables like "server_id";'

繋ぐたびに異なるserveridが表示されたらOK。

負荷試験

試験用ファイル作成

先ほどの試験用ファイルをhaproxyを経由するように編集する。

$ cp -p domysqlslap.sh  domysqlslap_ha.sh
$ vim domysqlslap_ha.sh
## 接続ユーザー情報や接続先を変更する

$ cat domysqlslap_ha.sh
#!/bin/bash
mysqlslap \
  --no-defaults \
  --concurrency=5 \
  --iterations=4 \
  --engine=innodb \
  --auto-generate-sql \
  --auto-generate-sql-add-autoincrement \
  --auto-generate-sql-load-type=mixed \
  --auto-generate-sql-write-number=1000 \
  --number-of-queries=100000 \
  --host=192.168.200.101 \
  --port=53306 \
  --user=test_user \
  --password=password \
  -v


$ diff -u domysqlslap.sh domysqlslap_ha.sh
--- domysqlslap.sh  2015-08-17 11:28:40.133577790 +0000
+++ domysqlslap_ha.sh   2015-08-18 02:22:48.849237649 +0000
@@ -9,8 +9,8 @@
   --auto-generate-sql-load-type=mixed \
   --auto-generate-sql-write-number=1000 \
   --number-of-queries=100000 \
-  --host=localhost \
-  --port=3306 \
-  --user=root \
-  --password=password \
+  --host=192.168.200.101 \
+  --port=53306 \
+  --user=test_user \
+  --password=password \
   -v

接続試験

それぞれのノードで以下のコマンドを予め打っておき、ちゃんとhaproxyによって両ノードに接続が振られていることを確認する。

$ watch 'ss -natp | grep 3306'

ベンチマーク実行。

$ do domysqlslap_ha.sh

Benchmark
    Running for engine innodb
    Average number of seconds to run all queries: 25.393 seconds
    Minimum number of seconds to run all queries: 24.943 seconds
    Maximum number of seconds to run all queries: 26.290 seconds
    Number of clients running queries: 5
    Average number of queries per client: 20000

エラーなく実行された。

まとめ

今回は雑にVagrantでCentOS7を起動し、Percona XtraDB Clusterを動かすまでを確認した。本来は3ノード以上の奇数構成とし、ちゃんとクォーラムを意識した構成とすべきだろう。

性能面は置いておいて、Percona XtraDB Clusterは簡単にマルチマスター構成を取れるというのは本当のようだ。

CentOS7 + MySQL Multi Master 検証メモ

前提

これから作るもの

手順について

試験環境の構築

Vagrant セットアップ

Percona XtraDB Clusterインストール

事前準備

パッケージインストール

レポジトリの追加

インストール

初期設定〜初回起動

ノード1の初期設定

設定ファイル作成

ノード1初回起動

rootユーザへのパスワード設定とユーザー作成

ノード2の初期設定

設定ファイル作成

ノード2起動とクラスターへの参加

動作確認

データベースの作成

ユーザー作成と削除

mysqlslap

準備

実行(シングル)

並行アクセス動作確認

HAProxyの準備

インストールと設定

動作確認

負荷試験

試験用ファイル作成

接続試験

まとめ