こんにちは、キエンです。
先日、ProxySqlの検証について以下のエントリを紹介しました。今回、いこレポの開発環境に実際にProxySQLを導入しましたので、ご紹介します。
開発環境構成の変更点
いこレポの開発環境はDockerで動かしています。変更前と変更後のコンテナはこちらです。
データベースの1台だけでproxysqlを導入しても意味がないので、master-slaveの構成も追加しました。
Dockerの設定ファイルの構造
mysqlのmaster-slave構成
まず、MYSQLのレプリケーションについてはこのサイトをおすすめです。
config/docker/mysql/master.cnf
masterノード用のmy.cnfファイルです。
# デフォルトの設定をそのままにする !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ # レプリケーション用の設定を追加する [mysqld] server-id = 1 # DBサーバごとにユニークな値を指定する log-bin = /var/log/mysql/bin-log # バイナリログの有効化及びそのファイル名 log-bin-index = /var/log/mysql/bin-log # バイナリログ一覧のファイル名指定 sync_binlog = 1 # バイナリログがトランザクションコミット時に同期書き込みされる
config/docker/mysql/slave.cnf
slaveノード用のmy.cnfファイルです。
# デフォルトの設定をそのままにする !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ # レプリケーション用の設定を追加する [mysqld] server-id = 2 relay-log = /var/log/mysql/relay-bin # リレーログの有効化及びそのファイル名 relay-log-index = /var/log/mysql/relay-bin # リレーログ一覧のファイル名指定 read_only = 1 # 読み取り専用を有効
docker-compose.yml
services: mysql-master: image: mysql:5.6 volumes: - mysql-master-data:/var/lib/mysql - ./config/docker/mysql/master.cnf:/etc/mysql/my.cnf environment: MYSQL_ALLOW_EMPTY_PASSWORD: "yes" ports: - '13306:3306' mysql-slave: image: mysql:5.6 volumes: - mysql-slave-data:/var/lib/mysql - ./config/docker/mysql/slave.cnf:/etc/mysql/my.cnf - ./config/docker/mysql/start-slave.sh:/docker-entrypoint-initdb.d/start-slave.sh environment: MYSQL_ALLOW_EMPTY_PASSWORD: "yes" ports: - '23306:3306' depends_on: - mysql-master volumes: mysql-master-data: driver: local mysql-slave-data: driver: local
docker の MySQL イメージ では /docker-entrypoint-initdb.d/ というディレクトリ内に初期化用のSQLやスクリプトを置くことで、最初に イメージ を起動したときにデータの初期化を自動的に行う仕組みがあります。mysqlの/docker-entrypoint-initdb.d に start-slave.sh を置くことで、slave起動時にslave開始のスクリプトを実行しています。
config/docker/mysql/start-slave.sh
slave開始のスクリプトです。
#!/bin/bash until mysqladmin ping -h mysql-master --silent; do sleep 1 done # masterに繋いで bin-logのファイル名とポジションを取得する log_file=`mysql -u root -h mysql-master -e 'SHOW MASTER STATUS \G' | grep File: | awk '{print $2}'` pos=`mysql -u root -h mysql-master -e 'SHOW MASTER STATUS \G' | grep Position: | awk '{print $2}'` # レプリケーション用の設定 mysql -u root -e "CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_USER='root', MASTER_PASSWORD='', MASTER_LOG_FILE='${log_file}', MASTER_LOG_POS=${pos}" # slaveの開始 mysql -u root -e 'START SLAVE'
master-slave構成の確認
コンテナを起動します。
$ docker-compose up mysql-master mysql-slave
masterノードでmaster statusを確認します。
mysql-master> SHOW MASTER STATUS\G *************************** 1. row *************************** File: bin-log.000004 Position: 120 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.01 sec)
slaveノードでslave statusを確認します。
mysql-slave> SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: mysql-master Master_User: root Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bin-log.000004 Read_Master_Log_Pos: 120 Relay_Log_File: relay-bin.000003 Relay_Log_Pos: 281 Relay_Master_Log_File: bin-log.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 120 Relay_Log_Space: 448 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: 5e82f323-e29e-11e9-a899-0242ac160002 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec)
master statusとslave statusの結果により、レプリケーションの設定が問題ないですね。念の為、実際にデータを変更することも確認します。
# データ変更前に、masterノードとslaveノードのデータを確認する。 mysql-master> select id, updated_at from articles order by id desc limit 1; +-------+---------------------+ | id | updated_at | +-------+---------------------+ | 14224 | 2019-08-23 08:55:47 | +-------+---------------------+ mysql-slave> select id, updated_at from articles order by id desc limit 1; +-------+---------------------+ | id | updated_at | +-------+---------------------+ | 14224 | 2019-08-23 08:55:47 | +-------+---------------------+ # masterノードでデータを変更する mysql-master> update articles set updated_at = now() where id = 14224; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 # データを変更後、masterノードとslaveノードのデータを再確認する mysql-master> select id, updated_at from articles where id = 14224; +-------+---------------------+ | id | updated_at | +-------+---------------------+ | 14224 | 2019-09-29 10:04:55 | +-------+---------------------+ mysql-slave> select id, updated_at from articles where id = 14224; +-------+---------------------+ | id | updated_at | +-------+---------------------+ | 14224 | 2019-09-29 10:04:55 | +-------+---------------------+
問題ないですね、master-slave構成の確認が完了しました。 次に、ProxySQLを導入しましょう。
ProxySQL導入
config/docker/proxysql/Dockerfile-proxysql
FROM centos:6 RUN echo $'[proxysql_repo]\n\ name= ProxySQL YUM repository\n\ baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever\n\ gpgcheck=1\n\ gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key\n' \ >> /etc/yum.repos.d/proxysql.repo RUN yum install -y proxysql mysql gettext && \ rm -rf /var/cache/yum/* && \ yum clean all
Dockerで設定ファイルに環境変数を埋め込むため、gettextもインストールしました。 インストール後、envsubstコマンドが使えるようになります。
ProxySQL設定は設定ファイルまたはMySQL互換の管理インターフェイスから設定できますので、今回は両方の方法も試しました。
設定ファイル使用の場合
config/docker/proxysql/proxysql-cnf.template
ProxySQL用の設定ファイルです。(/etc/proxysql.cnf)
datadir="/var/lib/proxysql" errorlog="/var/lib/proxysql/proxysql.log" admin_variables= { admin_credentials="admin:admin" mysql_ifaces="0.0.0.0:6032" } mysql_variables= { threads=4 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:6033" default_schema="information_schema" stacksize=1048576 server_version="5.5.30" connect_timeout_server=3000 monitor_username="${DB_USER}" monitor_password="${DB_PASSWORD}" monitor_history=600000 monitor_connect_interval=60000 monitor_ping_interval=10000 monitor_read_only_interval=1500 monitor_read_only_timeout=1000 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10 } # defines all the MySQL servers mysql_servers = ( { address = "${WRITER_HOST}" port = 3306 hostgroup = 1 }, { address = "${WRITER_HOST}" port = 3306 hostgroup = 2 weight = 1 }, { address = "${READER_HOST}" port = 3306 hostgroup = 2 weight = 2 }, ) # defines all the MySQL users mysql_users: ( { username = "${DB_USER}" password = "${DB_PASSWORD}" default_hostgroup = 1 }, ) # defines MySQL Query Rules mysql_query_rules: ( { rule_id = 1 active = 1 match_digest = "^SELECT .* FOR UPDATE$" destination_hostgroup = 1 apply = 1 }, { rule_id = 2 active = 1 match_digest = "^SELECT" destination_hostgroup = 2 apply = 1 } ) mysql_replication_hostgroups= ( { writer_hostgroup = 1 reader_hostgroup = 2 check_type = "${READ_ONLY_CHECK_TYPE}" } )
後で、埋め込む環境変数のは
- WRITER_HOST
- READER_HOST
- DB_USER
- DB_PASSWORD
- READ_ONLY_CHECK_TYPE
- Auroraを使わない場合read_onlyにする
- Auroraを使っている場合はinnodb_read_onlyにする
docker-compose.yml
service: proxysql: build: context: . dockerfile: ./config/docker/proxysql/Dockerfile-proxysql volumes: - ./config/docker/proxysql/proxysql-cnf.template:/var/lib/proxysql/proxysql-cnf.template environment: - WRITER_HOST=mysql-master - READER_HOST=mysql-slave - READ_ONLY_CHECK_TYPE=read_only - DB_USER=root - DB_PASSWORD= command: > /bin/sh -c "envsubst ' $$WRITER_HOST $$READER_HOST $$DB_USER $$DB_PASSWORD $$READ_ONLY_CHECK_TYPE '< /var/lib/proxysql/proxysql-cnf.template > /etc/proxysql.cnf && /etc/init.d/proxysql start && tail -f /var/lib/proxysql/proxysql.log" ports: - 6033:6033 depends_on: - mysql-slave
WRITER_HOSTとREADER_HOSTはmysql-masterとmysql-slaveを設定します。 ProxySQLを起動して、管理インタフェースで確認しましょう。
管理インタフェースで設定ファイルから設定が反映されるか確認します。
Admin> select * from mysql_servers; +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | mysql-master | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | mysql-master | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | mysql-slave | 3306 | 0 | ONLINE | 2 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ Admin> select * from mysql_replication_hostgroups; +------------------+------------------+------------+---------+ | writer_hostgroup | reader_hostgroup | check_type | comment | +------------------+------------------+------------+---------+ | 1 | 2 | read_only | | +------------------+------------------+------------+---------+ Admin> select * from mysql_users; +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+ | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+ | root | | 1 | 0 | 1 | | 0 | 1 | 0 | 1 | 1 | 10000 | | +----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+ Admin> SELECT rule_id,match_digest,destination_hostgroup FROM mysql_query_rules WHERE active = 1 ORDER BY rule_id; +---------+------------------------+-----------------------+ | rule_id | match_digest | destination_hostgroup | +---------+------------------------+-----------------------+ | 1 | ^SELECT .* FOR UPDATE$ | 1 | | 2 | ^SELECT | 2 | +---------+------------------------+-----------------------+
statsとmonitorから接続状況なども確認します。
Admin> select * from stats_mysql_connection_pool; +-----------+--------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +-----------+--------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 1 | mysql-master | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 634 | | 2 | mysql-master | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 634 | | 2 | mysql-slave | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 530 | +-----------+--------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ Admin> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 5; +--------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +--------------+------+------------------+-------------------------+---------------+ | mysql-master | 3306 | 1569783188211861 | 3048 | NULL | | mysql-slave | 3306 | 1569783187496602 | 2892 | NULL | | mysql-master | 3306 | 1569783128391268 | 3069 | NULL | | mysql-slave | 3306 | 1569783127564732 | 3223 | NULL | | mysql-slave | 3306 | 1569783068608753 | 2641 | NULL | +--------------+------+------------------+-------------------------+---------------+ 5 rows in set (0.00 sec) Admin> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 5; +--------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +--------------+------+------------------+----------------------+------------+ | mysql-master | 3306 | 1569783217668805 | 352 | NULL | | mysql-slave | 3306 | 1569783217485873 | 360 | NULL | | mysql-slave | 3306 | 1569783207587172 | 511 | NULL | | mysql-master | 3306 | 1569783207484382 | 354 | NULL | | mysql-slave | 3306 | 1569783197599818 | 515 | NULL | +--------------+------+------------------+----------------------+------------+ 5 rows in set (0.00 sec) Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 5; +--------------+------+------------------+-----------------+-----------+-------+ | hostname | port | time_start_us | success_time_us | read_only | error | +--------------+------+------------------+-----------------+-----------+-------+ | mysql-master | 3306 | 1569783259648349 | 310 | 0 | NULL | | mysql-slave | 3306 | 1569783259626402 | 434 | 1 | NULL | | mysql-master | 3306 | 1569783258146494 | 582 | 0 | NULL | | mysql-slave | 3306 | 1569783258126026 | 728 | 1 | NULL | | mysql-slave | 3306 | 1569783256650751 | 797 | 1 | NULL | +--------------+------+------------------+-----------------+-----------+-------+
設定ファイルから設定がうまく行っていますね。次は、管理インタフェースから設定方法を試します。
MySQL互換の管理インターフェイスの場合
config/docker/proxysql/init-sql.template
# 監視用ユーザーとパスワードの設定 SET mysql-monitor_username = "${DB_USER}"; SET mysql-monitor_password = "${DB_PASSWORD}"; # バックエンドの設定 INSERT INTO mysql_servers(hostgroup_id, hostname, port, weight) VALUES (1, "${WRITER_HOST}", 3306, 1), (2, "${WRITER_HOST}", 3306, 1), (2, "${READER_HOST}", 3306, 2); # MySQL replication hostgroupsの設定 INSERT INTO mysql_replication_hostgroups(writer_hostgroup, reader_hostgroup, check_type) VALUES (1, 2, "${READ_ONLY_CHECK_TYPE}"); # MYSQL User の設定 INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ("${DB_USER}", "${DB_PASSWORD}", 1); # MYSQL クエリルール INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (1, 1, '^SELECT.*FOR UPDATE$', 1, 1), (2, 1, '^SELECT', 2, 1); # RUNTIMEに反映 LOAD MYSQL VARIABLES TO RUNTIME; LOAD ADMIN VARIABLES TO RUNTIME; LOAD MYSQL SERVERS TO RUNTIME; LOAD MYSQL USERS TO RUNTIME; LOAD MYSQL QUERY RULES TO RUNTIME;
docker-compose.yml
上記の「設定ファイル使用の場合」で設定したdocker-compose.ymlはvolumesとcommandだけを変更します。
service: proxysql: volumes: - ./config/docker/proxysql/init-sql.template:/var/lib/proxysql/init-sql.template - ./config/docker/proxysql/start-proxysql.sh:/var/lib/proxysql/start-proxysql.sh command: > /bin/sh -c "envsubst ' $$WRITER_HOST $$READER_HOST $$DB_USER $$DB_PASSWORD $$READ_ONLY_CHECK_TYPE '< /var/lib/proxysql/init-sql.template > /var/lib/proxysql/init.sql && sh /var/lib/proxysql/start-proxysql.sh && tail -f /var/lib/proxysql/proxysql.log"
config/docker/proxysql/start-proxysql.sh
ProxySQLを起動と設定スクリプトです。
#!/bin/sh # ProxySQL起動する /etc/init.d/proxysql start until mysqladmin ping -h 127.0.0.1 -P6032 --silent; do sleep 1 done # ProxySQLを設定する mysql -h 127.0.0.1 -P6032 -uadmin -padmin < /var/lib/proxysql/init.sql
「設定ファイル使用の場合」と同様、ProxySQLの動作を確認しました。問題ないでした。
Railsのdatabase.yml設定の変更
# 変更前 development: <<: *default host: mysql ↓ # 変更後 development: <<: *default host: proxysql port: 6033
Railsを再起動してから、いこレポは無事に表示できました。
ProxySQLのWeb Serverを有効
ProxySQLの状況は毎回管理インタフェースで確認すると、時間がかかるので、ProxySQLのWeb Serverに有効しましょう。
config/docker/proxysql/init-sql.template
以下のWeb Server有効の設定を追加します。
# Web Serverを有効 SET admin-web_enabled = 'true';
Web Serverのデフォルトポートは6080です。デフォルトのアクセスアカウントはstats:statsです。
mysql> show variables like 'admin-stats_credentials'; +-------------------------+-------------+ | Variable_name | Value | +-------------------------+-------------+ | admin-stats_credentials | stats:stats | +-------------------------+-------------+ 1 row in set (0.01 sec) mysql> show variables like '%web_port %'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | admin-web_port | 6080 | +-------------------+-------+
docker-compose.ymlのproxysqlの設定
service: proxysql: ports: - 6080:6080
ProxySQLのWeb Serverの確認
こちらのURLをアクセスします。 http://0.0.0.0:6080/
以下の画面が確認できますよ。機能がまだ多くないですが、便利ですね。
最後に
いかがでしょうか。いこレポの開発環境にProxySQLを無事に導入しましたが、本番環境まで反映するのはまだまだいろんな検証がありますね。 さて、アクトインディではエンジニアを募集していますね、ぜひご応募してください。 actindi.net
