MaxScaleのインストールメモ

maxscaleをインストールする際にハマったので手順メモ

##環境
AWS　RDS
mariadb 10.0.24
db.t2.micro ssd 5GB マスター - スレーブ 構成
リードレプリカを１台コンソール上で追加
AWS EC2 t2.micro RHEL-7.3 maxscale構築用
maxscale 2.1.3
※AWS上のセキュリティグループ設定等は割愛

##maxscale インストール（EC2）

curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash

yum install maxscale

##マスターサーバー上での権限設定

CREATE USER 'maxscale'@'maxscalehost' IDENTIFIED BY 'maxscalepassword';

GRANT SELECT ON mysql.user TO 'maxscale'@'maxscaleのhost';
GRANT SELECT ON mysql.db TO 'maxscale'@'maxscaleのhost';
GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'maxscaleのhost';
GRANT SHOW DATABASES ON *.* TO 'maxscale'@'maxscaleのhost';
GRANT REPLICATION CLIENT ON *.* TO 'maxscale'@'maxscaleのhost';

##確認用SQL

SELECT DISTINCT
    user.user AS user,
    user.host AS host,
    user.password AS password,
    concat(user.user,user.host,user.password,
      IF((user.Select_priv+0)||find_in_set('Select',Coalesce(tp.Table_priv,0)),'Y','N') ,
      COALESCE( db.db,tp.db, '')) AS userdata,
    user.Select_priv AS anydb,
    COALESCE( db.db,tp.db, NULL) AS db
    FROM
    mysql.user LEFT JOIN
    mysql.db ON user.user=db.user AND user.host=db.host LEFT JOIN
    mysql.tables_priv tp ON user.user=tp.user AND user.host=tp.host
    WHERE user.user IS NOT NULL AND user.user <> ''

#maxscale.cnfの設定
/etc/maxscale.cnf.template から修正し /etc/maxscale.cnfに配置
サンプル

##接続テスト用ユーザー作成

CREATE USER 'test-user'@'接続元host' IDENTIFIED BY 'password';
GRANT SELECT ON *.* TO 'test-user'@'接続元host';

##起動

service maxscale start

##エラー確認

tail -f /var/log/maxscale/maxscale.log

もし設定で問題があればこのlogにエラー情報が出力される。
例(権限足りないケース)

2017-06-27 03:34:02   error  : [MySQL Monitor] Failed to execute query 'SHOW SLAVE STATUS' with user 'maxscale'. MySQL error message: Access denied; you need (at least one o
f) the SUPER, REPLICATION CLIENT privilege(s) for this operation

##接続確認（EC２上で実行）

mysql -u test-user -p -h 127.0.0.1 -P 4008

##参考にさせていただいたURL
http://qiita.com/bringer1092/items/03012ff4e046eb29dad4

##maxscale.cnfサンプル
public dns、ユーザー、パスワードは任意で設定

maxscale.cnf

# MaxScale documentation on GitHub:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Documentation-Contents.md

# Global parameters
#
# Complete list of configuration options:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Getting-Started/Configuration-Guide.md

[maxscale]
threads=1

# Server definitions
#
# Set the address of the server to the network
# address of a MySQL server.
#

[server1]
type=server
address=***rds(master)のpublic dnsをセット***
port=3306
protocol=MySQLBackend

[server2]
type=server
address=***rds(slave)のpublic dnsをセット***
port=3306
protocol=MySQLBackend


# Monitor for the servers
#
# This will keep MaxScale aware of the state of the servers.
# MySQL Monitor documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Monitors/MySQL-Monitor.md

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1,server2
user=maxscalepassword
passwd=maxscale password
monitor_interval=10000

# Service definitions
#
# Service Definition for a read-only service and
# a read/write splitting service.
#

# ReadConnRoute documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadConnRoute.md

[Read-Only Service]
type=service
router=readconnroute
servers=server2
user=maxscale
passwd=maxscalepassword
router_options=slave

# ReadWriteSplit documentation:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Routers/ReadWriteSplit.md

[Read-Write Service]
type=service
router=readwritesplit
#router=readconnroute
servers=server1
user=maxscale
passwd=maxscalepassword
max_slave_connections=100%

# This service enables the use of the MaxAdmin interface
# MaxScale administration guide:
# https://github.com/mariadb-corporation/MaxScale/blob/2.1/Documentation/Reference/MaxAdmin.md

[MaxAdmin Service]
type=service
router=cli

# Listener definitions for the services
#
# These listeners represent the ports the
# services will listen on.
#

[Read-Only Listener]
type=listener
service=Read-Only Service
protocol=MySQLClient
port=4008
address=0.0.0.0

[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
address=0.0.0.0

[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default