目次
ProxySQLとは
ProxySQLは、SQLクエリを仲介して、特定のデータベースに振り分けるロードバランサーになります。
クエリでフィルターをかけて、読み込みと書き込みで分散も行うことができます。
インストール
sudo cat <<EOF | sudo tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name=ProxySQL repository
baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/almalinux/8
gpgcheck=1
gpgkey=https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key
EOF
sudo yum install proxysql-2.5.5
設定ファイルの設定を行います。
vi /etc/proxysql.cnf
----------------------------------------
datadir="/var/lib/proxysql"
errorlog="/var/log/proxysql/proxysql.log"
admin_variables=
{
admin_credentials="admin:admin"
mysql_ifaces="0.0.0.0:6032"
refresh_interval=2000
}
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;/tmp/proxysql.sock"
default_schema="information_schema"
stacksize=1048576
server_version="5.1.30"
connect_timeout_server=10000
monitor_history=60000
monitor_connect_interval=200000
monitor_ping_interval=200000
ping_interval_server_msec=10000
ping_timeout_server=200
commands_stats=true
sessions_sort=true
monitor_username="proxysql"
monitor_password="pass"
}
mysql_replication_hostgroups =
(
{
writer_hostgroup=0
reader_hostgroup=1
comment="host groups"
}
)
mysql_servers =
(
{ address="192.168.3.17", port=3306, hostgroup=0, max_connections=1000, max_replication_lag = 5 },
{ address="192.168.3.17", port=3306, hostgroup=1, max_connections=1000, max_replication_lag = 5 }
)
# MySQL へ Proxy 経由で接続するユーザ記載
mysql_users =
(
{ username = "ec2-user", password = "Servers", default_hostgroup = 0, max_connections=1000, active = 1 }
)
mysql_query_rules =
(
{
rule_id=1
active=1
match_pattern="^SELECT .* FOR UPDATE"
destination_hostgroup=0
apply=1
},
{
rule_id=2
active=1
match_pattern="^SELECT .*"
destination_hostgroup=1
apply=1
},
{
rule_id=3
active=1
match_pattern=".*"
destination_hostgroup=0
apply=1
}
)
接続確認を行い接続できたら完了
mysql -u ec2-user -pServers -P 6033 -h 127.0.0.1
------
[root@localhost ec2-user]# mysql -u admin -p -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '
Enter password:admin
ERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'127.0.0.1' (using password: YES)
[root@localhost ec2-user]# mysql -u admin -p -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
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.
ProxySQLAdmin> SELECT hostgroup,srv_host,status,ConnOK,ConnERR,Queries,Bytes_data_sent FROM stats_mysql_connection_pool;
+-----------+--------------+--------+--------+---------+---------+-----------------+
| hostgroup | srv_host | status | ConnOK | ConnERR | Queries | Bytes_data_sent |
+-----------+--------------+--------+--------+---------+---------+-----------------+
| 1 | 192.168.3.17 | ONLINE | 1 | 0 | 3 | 63 |
+-----------+--------------+--------+--------+---------+---------+-----------------+
1 row in set (0.01 sec)