togatttiのエンジニアメモ

過度な期待はしないでください.

HAProxyで、MariaDBの負荷分散

サーバの中に、mysqld_multiを使い擬似的なMaste/Slave構成を用意する。

この構成に対して、HAProxyを使った負荷分散の動作を確認する。

検証サーバのOSは、CentOS7系を使用する。

MariaDBのインストール

# yum update
# yum install -y mariadb mariadb-server

mysqld_multiの設定

mysqld_multiで、MariaDBを3プロセス起動する。

DBデータ用のディレクトリを作成し、システムテーブルを初期化する。

# mysql_install_db --user=mysql --datadir=/var/lib/mysql1
# mysql_install_db --user=mysql --datadir=/var/lib/mysql2
# mysql_install_db --user=mysql --datadir=/var/lib/mysql3
# chgrp -R mysql /var/lib/mysql[123]

/etc/my.cnfを以下の様に変更する。

レプリケーションで、mysqld1は、Master、mysqld2とmysqld3はSlaveの役割を持つ。

# /etc/my.cnf
[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = multi_admin
password   = multipass

# master
[mysqld1]
server-id  = 1
log-bin=mysql-bin
socket     = /tmp/mysql.sock1
port       = 3307
pid-file   = /var/lib/mysql1/hostname.pid1
datadir    = /var/lib/mysql1
log-error    = /var/log/mariadb/mariadb1.log

# slave
[mysqld2]
server-id  = 2
socket     = /tmp/mysql.sock2
port       = 3308
pid-file   = /var/lib/mysql2/hostname.pid2
datadir    = /var/lib/mysql2
log-error    = /var/log/mariadb/mariadb2.log

# slave
[mysqld3]
server-id  = 3
socket     = /tmp/mysql.sock3
port       = 3309
pid-file   = /var/lib/mysql3/hostname.pid3
datadir    = /var/lib/mysql3
log-error    = /var/log/mariadb/mariadb3.log

MariaDBを起動する。

# mysqld_multi start
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running

mysqld_multi stopを実行するために、SHUTDOWN権限をmulti_adminに付与する。

# echo "GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass' WITH GRANT OPTION;" | mysql -h 127.0.0.1 -P 3307
# echo "GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass' WITH GRANT OPTION;" | mysql -h 127.0.0.1 -P 3308
# echo "GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass' WITH GRANT OPTION;" | mysql -h 127.0.0.1 -P 3309

レプリケーションの設定

my.cnfの内容は、前述したものを使う。

Master

# echo "GRANT REPLICATION SLAVE ON *.* to replicator@'127.0.0.1' IDENTIFIED BY 'secret'; FLUSH PRIVILEGES; SELECT SLEEP(3); SHOW MASTER STATUS;" | mysql -h 127.0.0.1 -P 3307
SLEEP(3)
0
File    Position        Binlog_Do_DB    Binlog_Ignore_DB
mysql-bin.000002        474

Slave

# echo "CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3307,MASTER_USER='replicator',MASTER_PASSWORD='secret',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=474; START SLAVE; SELECT SLEEP(3); SHOW SLAVE STATUS;" | mysql -h 127.0.0.1 -P 3308
# echo "CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3307,MASTER_USER='replicator',MASTER_PASSWORD='secret',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=474; START SLAVE; SELECT SLEEP(3); SHOW SLAVE STATUS;" | mysql -h 127.0.0.1 -P 3309

HAProxy用のMariaDBユーザ作成

Masterにhaproxy用のユーザを作成する。Slaveにもレプリケーションされていることを確認する。

# echo "INSERT INTO mysql.user (Host,User) values ('127.0.0.1','haproxy_check'); FLUSH PRIVILEGES; GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'127.0.0.1' IDENTIFIED BY 'secret' WITH GRANT OPTION; FLUSH PRIVILEGES;" | mysql -h 127.0.0.1 -P 3307
# echo "SELECT User,Host FROM mysql.user WHERE User LIKE 'haproxy%';" | mysql -h 127.0.0.1 -P 3307
User    Host
haproxy_check   127.0.0.1
haproxy_root    127.0.0.1
# echo "SELECT User,Host FROM mysql.user WHERE User LIKE 'haproxy%';" | mysql -h 127.0.0.1 -P 3308
User    Host
haproxy_check   127.0.0.1
haproxy_root    127.0.0.1
# echo "SELECT User,Host FROM mysql.user WHERE User LIKE 'haproxy%';" | mysql -h 127.0.0.1 -P 3309
User    Host
haproxy_check   127.0.0.1
haproxy_root    127.0.0.1

HAProxyのインストール

# yum install haproxy

haproxyの設定

haproxy.cfgを以下のように変更する。

# /etc/haproxy/haproxy.cfg
global
  log 127.0.0.1 local0 notice
  user haproxy
  group haproxy

defaults
  log global
  retries 2
  timeout connect 3000
  timeout server 5000
  timeout client 5000

listen mysql-cluster
  bind 127.0.0.1:3306
  mode tcp
  option mysql-check user haproxy_check
  balance roundrobin
  server mysql-1 127.0.0.1:3307 check
  server mysql-2 127.0.0.1:3308 check
  server mysql-3 127.0.0.1:3309 check

HAProxyがどのポートでもバインドできるようにSELinuxの設定を変更しておき、haproxyを起動する。

# setsebool -P haproxy_connect_any=1
# systemctl start haproxy

MariaDBに、何度か接続すると異なるserver_idに振り分けられ負荷分散されることが確認できる。

# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
# echo "SELECT @@server_id;" | mysql -h 127.0.0.1
@@server_id
2
# echo "SELECT @@server_id;" | mysql -h 127.0.0.1
@@server_id
3
# echo "SELECT @@server_id;" | mysql -h 127.0.0.1
@@server_id
1

試しにスレーブを落としてみるとマスターだけに接続されることが確認できる。

# mysqld_multi stop 2,3
# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
MySQL server from group: mysqld3 is not running
# echo "SELECT @@server_id;" | mysql -h 127.0.0.1
@@server_id
1
# echo "SELECT @@server_id;" | mysql -h 127.0.0.1
@@server_id
1
# echo "SELECT @@server_id;" | mysql -h 127.0.0.1
@@server_id
1

参考

MySQL :: Starting and Stopping MySQL :: 4.4 mysqld_multi — Manage Multiple MySQL Servers

MariaDB Load Balancing with HAProxy on Centos 7 - Pull Requests - Medium