## repo 설정.
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
[root@proxysql yum.repos.d]# vi mariadb.repo
[mariadb-main]
name = MariaDB Server
baseurl = https://dlm.mariadb.com/repo/mariadb-server/10.7/yum/rhel/7/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Server-GPG-KEY
gpgcheck = 1
enabled = 1
[mariadb-maxscale]
# To use the latest stable release of MaxScale, use "latest" as the version
# To use the latest beta (or stable if no current beta) release of MaxScale, use "beta" as the version
name = MariaDB MaxScale
baseurl = https://dlm.mariadb.com/repo/maxscale/latest/yum/rhel/7/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-MaxScale-GPG-KEY
gpgcheck = 1
enabled = 1
[mariadb-tools]
name = MariaDB Tools
baseurl = https://downloads.mariadb.com/Tools/rhel/7/x86_64
gpgkey = file:///etc/pki/rpm-gpg/MariaDB-Enterprise-GPG-KEY
gpgcheck = 1
enabled = 1
[root@proxysql ~]# yum clean all
Loaded plugins: fastestmirror, langpacks
Cleaning repos: base epel extras mariadb updates
Cleaning up everything
Cleaning up list of fastest mirrors
[root@proxysql yum.repos.d]# sudo yum install MariaDB-client
■ ProxySQL DB 접속 (proxysql서버)
기본은 port가6032로 설정되있습니다.
또한 기본 사용자 ID와 Password는admin과admin입니다.
[root@proxysql ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name | file |
+-----+---------------+-------------------------------------+
| 0 | main | |
| 2 | disk | /var/lib/proxysql/proxysql.db |
| 3 | stats | |
| 4 | monitor | |
| 5 | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+
5 rows in set (0.000 sec)
MySQL [(none)]> select * from global_variables where variable_name like '%galera%';
+----------------------------------------------------+----------------+
| variable_name | variable_value |
+----------------------------------------------------+----------------+
| mysql-monitor_galera_healthcheck_interval | 5000 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
+----------------------------------------------------+----------------+
3 rows in set (0.002 sec)
■ Monitor 유저 생성 (galera1서버)
[root@proxysql ~]# vi /etc/proxysql.cnf
...
# make sure to configure monitor username and password
# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
monitor_username="monitor"
monitor_password="monitor"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
...
[root@jh-galera001 ~]# mysql -uroot -proot
MariaDB [(none)]> create user 'monitor'@'192.168.100.%' identified by 'monitor';
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> grant all on *.* to 'monitor'@'192.168.100.%';
Query OK, 0 rows affected (0.00 sec)
■ ProxySQL에 galera 정보 추가 (proxysql서버)
(1번 그룹 write/ 2번 그룹 read)
MySQL [(none)]> show create table mysql_servers\G
*************************** 1. row ***************************
table: mysql_servers
Create Table: CREATE TABLE mysql_servers (
hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0,
hostname VARCHAR NOT NULL,
port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306,
gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0,
status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1,
compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000,
max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0,
use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0,
max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0,
comment VARCHAR NOT NULL DEFAULT '',
PRIMARY KEY (hostgroup_id, hostname, port) )
1 row in set (0.000 sec)
## 1번 Write group
MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values (1,'192.168.100.40',3306),(1,'192.168.100.41',3306),(1,'192.168.100.42',3306);
Query OK, 3 rows affected (0.000 sec)
## 2번 Read group
MySQL [(none)]> insert into mysql_servers (hostgroup_id, hostname, port) values (2,'192.168.100.40',3306),(2,'192.168.100.41',3306),(2,'192.168.100.42',3306);
Query OK, 3 rows affected (0.000 sec)
MySQL [(none)]> 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 | 192.168.100.40 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.100.41 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 1 | 192.168.100.42 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.100.40 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.100.41 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | 192.168.100.42 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
6 rows in set (0.000 sec)
##QUERY를 날릴 User 생성 ( 해당 유저가 DB에도 있어야됩니다. )
<ProxySQL>
MySQL [(none)]> INSERT INTO mysql_users (username, password, active, default_hostgroup) VALUES ('jh','jh',1,1);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | attributes | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
| jh | jh | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | | |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+------------+---------+
1 row in set (0.000 sec)
<Galera1> DB에서도 유저 생성
MariaDB [(none)]> create user 'kim'@'192.168.100.%' identified by 'kim';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant all on *.* to 'kim'@'192.168.100.%';
Query OK, 0 rows affected (0.00 sec)
## Query rule에 따른 트랜잭션 분배설정 (1번 group-> write 2번 group -> read)
MySQL [(none)]> INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '.*@.*', 1, 1);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '^SELECT.*', 2, 0);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> INSERT INTO mysql_query_rules (active, match_pattern, destination_hostgroup, apply) VALUES (1, '^SELECT.*FOR UPDATE', 2, 1);
Query OK, 1 row affected (0.000 sec)
* match_pattern: https://proxysql.com/documentation/global-variables/mysql-variables/#mysql-query_processor_regex
MySQL [(none)]> select rule_id,active,match_pattern,destination_hostgroup from mysql_query_rules;
+---------+--------+---------------------+-----------------------+
| rule_id | active | match_pattern | destination_hostgroup |
+---------+--------+---------------------+-----------------------+
| 1 | 1 | .*@.* | 1 |
| 2 | 1 | ^SELECT.* | 2 |
| 3 | 1 | ^SELECT.*FOR UPDATE | 2 |
+---------+--------+---------------------+-----------------------+
3 rows in set (0.000 sec)
## 스케줄
MySQL [(none)]> show create table scheduler\G;
*************************** 1. row ***************************
table: scheduler
Create Table: CREATE TABLE scheduler (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL,
filename VARCHAR NOT NULL,
arg1 VARCHAR,
arg2 VARCHAR,
arg3 VARCHAR,
arg4 VARCHAR,
arg5 VARCHAR,
comment VARCHAR NOT NULL DEFAULT '')
1 row in set (0.000 sec)
MySQL [(none)]> INSERT INTO scheduler (id, active, interval_ms, filename, arg1, arg2, arg3, arg4, arg5) VALUES (1, 1, 1000, '/usr/share/proxysql/tools/proxysql_galera_checker.sh', 1, 2, 1, 1, '/var/lib/proxysql/proxysql_galera_checker.log');
Query OK, 1 row affected (0.000 sec)
- arg1 -> write group
- arg2 -> read group
* https://linuxglobe.com/centos/centos-7/proxysql-for-mariadb-galera-clustering-centos-7.html
MySQL [(none)]> select * from scheduler;
+----+--------+-------------+------------------------------------------------------+------+------+------+------+-----------------------------------------------+---------+
| id | active | interval_ms | filename | arg1 | arg2 | arg3 | arg4 | arg5 | comment |
+----+--------+-------------+------------------------------------------------------+------+------+------+------+-----------------------------------------------+---------+
| 1 | 1 | 1000 | /usr/share/proxysql/tools/proxysql_galera_checker.sh | 1 | 2 | 1 | 1 | /var/lib/proxysql/proxysql_galera_checker.log | |
+----+--------+-------------+------------------------------------------------------+------+------+------+------+-----------------------------------------------+---------+
1 row in set (0.000 sec)
MySQL [(none)]> show variables like '%mysql-query_retries_on_failure%';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| mysql-query_retries_on_failure | 1 |
+--------------------------------+-------+
1 row in set (0.000 sec)
MySQL [(none)]> SET mysql-query_retries_on_failure=10;
Query OK, 1 row affected (0.000 sec)
## 지금까지의 설정을 DISK단에 저장
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
LOAD SCHEDULER TO RUNTIME;
SAVE SCHEDULER TO DISK;
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
[root@proxysql ~]# vi /var/lib/proxysql/proxysql_galera_checker.log
Thu May 26 16:20:25 KST 2022 ###### proxysql_galera_checker.sh SUMMARY ######
Thu May 26 16:20:25 KST 2022 Hostgroup writers 1
Thu May 26 16:20:25 KST 2022 Hostgroup readers 2
Thu May 26 16:20:25 KST 2022 Number of writers 1
Thu May 26 16:20:25 KST 2022 Writers are readers 1
Thu May 26 16:20:25 KST 2022 log file /var/lib/proxysql/proxysql_galera_checker.log
Thu May 26 16:20:25 KST 2022 ###### HANDLE WRITER NODES ###### --> WRITE는 192.168.100.40번만 ONLINE이고 나머지는 OFFLINE
Thu May 26 16:20:25 KST 2022 --> Checking WRITE server 1:192.168.100.40:3306, current status ONLINE, wsrep_local_state 4
Thu May 26 16:20:25 KST 2022 server 1:192.168.100.40:3306 is already ONLINE: 1 of 1 write nodes
Thu May 26 16:20:25 KST 2022 --> Checking WRITE server 1:192.168.100.41:3306, current status ONLINE, wsrep_local_state 4
Thu May 26 16:20:25 KST 2022 Changing server 1:192.168.100.41:3306 to status OFFLINE_SOFT. Reason: max write nodes reached (1)
Thu May 26 16:20:25 KST 2022 --> Checking WRITE server 1:192.168.100.42:3306, current status ONLINE, wsrep_local_state 4
Thu May 26 16:20:25 KST 2022 Changing server 1:192.168.100.42:3306 to status OFFLINE_SOFT. Reason: max write nodes reached (1)
Thu May 26 16:20:25 KST 2022 ###### HANDLE READER NODES ###### --> READ는 모든 NODE가 ONLINE
Thu May 26 16:20:25 KST 2022 --> Checking READ server 2:192.168.100.40:3306, current status ONLINE, wsrep_local_state 4
Thu May 26 16:20:25 KST 2022 server 2:192.168.100.40:3306 is already ONLINE
Thu May 26 16:20:25 KST 2022 --> Checking READ server 2:192.168.100.41:3306, current status ONLINE, wsrep_local_state 4
Thu May 26 16:20:25 KST 2022 server 2:192.168.100.41:3306 is already ONLINE
Thu May 26 16:20:25 KST 2022 --> Checking READ server 2:192.168.100.42:3306, current status ONLINE, wsrep_local_state 4
Thu May 26 16:20:25 KST 2022 server 2:192.168.100.42:3306 is already ONLINE
Thu May 26 16:20:25 KST 2022 ###### SUMMARY ######
Thu May 26 16:20:25 KST 2022 --> Number of writers that are 'ONLINE': 1 : hostgroup: 1
Thu May 26 16:20:25 KST 2022 --> Number of readers that are 'ONLINE': 3 : hostgroup: 2
Thu May 26 16:20:25 KST 2022 ###### Loading mysql_servers config into runtime ######
Thu May 26 16:20:26 KST 2022 ###### proxysql_galera_checker.sh SUMMARY ######
Thu May 26 16:20:26 KST 2022 Hostgroup writers 1
Thu May 26 16:20:26 KST 2022 Hostgroup readers 2
Thu May 26 16:20:26 KST 2022 Number of writers 1
Thu May 26 16:20:26 KST 2022 Writers are readers 1
Thu May 26 16:20:26 KST 2022 log file /var/lib/proxysql/proxysql_galera_checker.log