Go to Qiita Advent Calendar 2024 Top
LoginSignup
38
36

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

mixiグループAdvent Calendar 2016

Day 20
@yoanin株式会社MIXI

ProxySQL触ってみた

Last updated at Posted at 2016-12-21

皆様、こんにちは！株式会社ミクシィ XFLAGスタジオにて主にサーバ開発/運用をしている@yoan_mtkと申します。
モンストの映画は観ていただけたでしょうか？
熱くなる展開がてんこ盛りで意外に大人も楽しめる映画になっておりますので、まだ観ていない方は是非とも映画館にてご鑑賞ください！

この記事はmixiグループ Advent Calendar 2016 20日目の記事です。

この記事ではProxySQLの紹介と簡単な使い方をまとめます。

ProxySQLとは

概要

  • サイト
  • github
  • 公式ドキュメント
  • mysqlのプロキシサーバ
  • ハイパフォーマンス
  • perconaが出した資料によると、mariadbが出しているmaxscaleよりも性能が良い
  • 類似ソフトウェア
    • mysql-proxy
    • maxscale

どんなことができるの？

  • mysql-proxyではバグのあるread-write分散が可能
  • クエリやIP、ポートによってアクセスするbackendDBの振り分け
  • backendDBの監視
  • ルータの運用にインスパイアを受けており、設定変更を無停止行える
  • proxyの操作はadminユーザを作成し、そちらから操作します

どんな用途で使えるの？

  • failover
    • masterの死亡を監視してslaveへ自動で切り替え
  • DBサーバのスケールイン/アウト

proxysqlでの用語

  • user
    • proxyに接続するmysqlユーザ
  • server
    • proxyするバックエンドのDBサーバ
  • hostgroup
    • バックエンドのDBサーバのグループ
    • 同じｈostgroupに登録するとmaster-slaveの関係があるとみなし、failover設定を行うと自動で切り替わってくれます
  • rule
    • マッチしたクエリを特定のhostgroupに送るルール
    • IPやportでも振り分けられる

コンフィグ構成

コンフィグ階層構造

図はwikiから拝借しました

+-------------------------+
|         RUNTIME         |
+-------------------------+
       /|\          |
        |           |
    [1] |       [2] |
        |          \|/
+-------------------------+
|         MEMORY          |
+-------------------------+ _
       /|\          |      |\
        |           |        \
    [3] |       [4] |         \ [5]
        |          \|/         \
+-------------------------+  +-------------------------+
|          DISK           |  |       CONFIG FILE       |
+-------------------------+  +-------------------------+

proxysqlでは設定の保存/参照データが3層の階層構造になっています

  • RUNTIME
    • 起動しているproxysqlプロセスが実際に従うコンフィグデータ
    • 再起動するとデータは消える
  • MEMORY(mainと呼ばれることもある)
    • 構成変更するときに主に利用するコンフィグデータ
      • ここで設定が完了したら、RUNTIME, DISKに設定を反映/保存する
    • 再起動するとデータは消える
  • DISK
    • MEMORYでの設定を永続的に保存するコンフィグデータ
    • SQLiteのdbに保存される
      • デフォルトは/var/lib/proxysql/proxysql.db
    • 再起動してもデータは残る
  • CONFIG FILE
    • テキスト形式で設定を記述する(proxysqlからは参照のみ)
      • デフォルトは/etc/proxysql.cnf
    • 再起動してもデータは残る

DISK vs CONFIG

DISKが優先的にロードされます。
CONFIGで設定した項目をMEMORYに反映したい場合はDISKを削除する必要があります。

  • DISKあり、CONFIGあり
    • DISKの設定をロード
  • DISKあり、CONFIGなし
    • DISKの設定をロード
  • DISKなし、CONFIGあり
    • CONFIGの設定をロード
  • DISKなし、CONFIGなし
    • DISKを初期化してロード

proxysqlの運用中、設定を変更しないであろうものはCONFIGに書くようにすれば良いと思います。

インストール

最新版をインストール

wget https://github.com/sysown/proxysql/releases/download/v1.3.1/proxysql_1.3.1-ubuntu14_amd64.deb
sudo dpkg -i proxysql_1.3.1-ubuntu14_amd64.deb
sudo service proxysql start

config反映

/etc/proxysql.cnfに以下のように設定します。

#file proxysql.cfg

# This config file is parsed using libconfig , and its grammar is described in:
# http://www.hyperrealm.com/libconfig/libconfig_manual.html#Configuration-File-Grammar
# Grammar is also copied at the end of this file

datadir="/var/lib/proxysql"

admin_variables =
{
        admin_credentials = "admin:hogehoge"
        mysql_ifaces      = "127.0.0.1:6032;/tmp/proxysql_admin.sock"
}

mysql_variables =
{
        interfaces                 = "0.0.0.0:3316;/tmp/proxysql.sock"
        default_schema             = "information_schema"
        monitor_username           = "root"
        monitor_password           = "hogehoge"
        commands_stats             = true
        sessions_sort              = true
}

mysql_users:
(
{
        username          = "root"
        password          = "hogehoge"
        default_hostgroup = 0
        active            = 1
}
)
  • Adminユーザとしてadmin:hogehoge
  • mysqlclientが接続するユーザとしてroot:hogehoge

を用意しました。

つかってみる

最初はRUNTIMEに反映されているproxyがなにもない状態です。

ubuntu@ip-172-31-8-193:~$ mysql -uadmin -phogehoge -P6032 --socket=/tmp/proxysql_admin.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)


Oracle is a registered trademark of Oracle Corporation and/or its


Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Admin> select * from runtime_mysql_servers;
Empty set (0.00 sec)

mainの設定もない状態です。

Admin> select * from mysql_servers;
Empty set (0.00 sec)

backendにRDSを登録する

下準備として2台のバックエンドDBにdatabaseを用意します。
それぞれbackend_1とbackend_2とします。

ubuntu@ip-172-31-8-193:~$ mysql -uroot -phogehoge -hbackend-1.cluster-chn1pjid8uwz.ap-northeast-1.rds.amazonaws.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3656
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database backend_1;
Query OK, 1 row affected (0.01 sec)

mysql> ^DBye
ubuntu@ip-172-31-8-193:~$ mysql -uroot -phogehoge -hbackend-2.cluster-chn1pjid8uwz.ap-northeast-1.rds.amazonaws.com
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database backend_2;
Query OK, 1 row affected (0.04 sec)

backendに接続する

backend_1に接続してみます。
今回はproxyと同じホストから接続を行います。

adminユーザでbackend_1のあるDBサーバを追加します。

Admin> insert into mysql_servers (hostgroup_id, hostname, status) values (0, 'backend-1.cluster-chn1pjid8uwz.ap-northeast-1.rds.amazonaws.com', 'ONLINE');
Query OK, 1 row affected (0.00 sec)

Admin> select * from mysql_servers;
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                                        | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | backend-1.cluster-chn1pjid8uwz.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
1 row in set (0.00 sec)

Admin> select * from mysql_servers;Admin> select * from runtime_mysql_servers;
Empty set (0.01 sec)

memory上にはRDSが反映されましたが、RUNTIME上には反映されていません。

Admin> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin> select * from runtime_mysql_servers;
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                                        | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | backend-1.cluster-chn1pjid8uwz.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
1 row in set (0.00 sec)

backend-1に接続していることを確認します。

ubuntu@ip-172-31-8-193:~$ mysql -uroot -phogehoge -P3316 --socket=/tmp/proxysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.47 (ProxySQL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backend_1          |
| mysql              |
| performance_schema |
| proxysql_test      |
+--------------------+
5 rows in set (0.00 sec)

backendサーバを切り替える

接続しているサーバをbackend_1からbackend_2に切り替えます。

ubuntu@ip-172-31-8-193:~$ mysql -uadmin -phogehoge -P6032 --socket=/tmp/proxysql_admin.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.47 (ProxySQL Admin Module)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from runtime_mysql_servers; # backend_1のみが有効になっている
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                                        | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | backend-1.cluster-chn1pjid8uwz.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
1 row in set (0.00 sec)

mysql> update mysql_servers set hostname='backend-2.cluster-chn1pjid8uwz.ap-northeast-1.rds.amazonaws.com' where hostgroup_id=0; # hostを更新
Query OK, 1 row affected (0.00 sec)

mysql> select * from mysql_servers; # mainではbackend_2が有効になっている
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                                        | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | backend-2.cluster-chn1pjid8uwz.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
1 row in set (0.01 sec)

mysql> select * from runtime_mysql_servers; # runtimeはまだbackend_1
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                                        | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | backend-1.cluster-chn1pjid8uwz.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
1 row in set (0.00 sec)

mysql> LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> select * from runtime_mysql_servers; # mainの設定が反映された
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname                                                        | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | backend-2.cluster-chn1pjid8uwz.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-----------------------------------------------------------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
1 row in set (0.00 sec)

mysql>

なお、localhostからつなげていたmysqlclientでは、以下のように切り替わっていることが確認できます。
(connectionはそのままでDBが切り替わっている！！)

ubuntu@ip-172-31-8-193:~$ mysql -uroot -phogehoge -P3316 --socket=/tmp/proxysql.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.47 (ProxySQL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backend_1          |
| mysql              |
| performance_schema |
| proxysql_test      |
+--------------------+
5 rows in set (0.00 sec)

## ここで切り替えを行った。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backend_2          |
| mysql              |
| performance_schema |
| proxysql_test      |
+--------------------+
5 rows in set (0.16 sec)

mysql>

終わりに

ProxySQLいかがでしたでしょうか？
無停止でサーバの切り替えが行えるため、こちらを利用してサーバのスケールアウトもメンテナンス無しで行えます。
本来運用で利用するのであれば、updateでhostを書き換えるのではなく、サーバレコードをinsertしてOFFLINEにした方が良いと思います。

以上になります。
明日からもよろしくお願いします！

38
36
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
38
36

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?