[LINUX] PROXYSQL - fourslickz/notes GitHub Wiki

install galera

apt update
apt install mariadb-server galera-4 rsync -y

Konfigurasi Galera (di semua node)

Edit /etc/mysql/mariadb.conf.d/60-galera.cnf:

Node1 (10.0.0.11):

[mysqld]
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="my_galera_cluster"
wsrep_cluster_address="gcomm://10.0.0.11,10.0.0.12,10.0.0.13"

wsrep_node_address="10.0.0.11"
wsrep_node_name="node1"

wsrep_sst_method=rsync

NODE-2

[mysqld]
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="my_galera_cluster"
wsrep_cluster_address="gcomm://10.0.0.11,10.0.0.12,10.0.0.13"

wsrep_node_address="10.0.0.12"
wsrep_node_name="node2"

wsrep_sst_method=rsync

node-3

[mysqld]
binlog_format=ROW
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0

# Galera settings
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name="my_galera_cluster"
wsrep_cluster_address="gcomm://10.0.0.11,10.0.0.12,10.0.0.13"

wsrep_node_address="10.0.0.13"
wsrep_node_name="node3"

wsrep_sst_method=rsync

🚀 Jalankan cluster

1. Bootstrap cluster di Node1:

galera_new_cluster

2. Start service di Node2 & Node3:

systemctl start mariadb

3. Cek status cluster (dari salah satu node):

SHOW STATUS LIKE 'wsrep_cluster_size';
SHOW STATUS LIKE 'wsrep_cluster_status';

Install proxysql

wget -O - 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | apt-key add -

echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -cs)/ ./ \
    | tee /etc/apt/sources.list.d/proxysql.list

apt update
apt install proxysql -y

mysql -u admin -padmin -h 127.0.0.1 -P6032

Tambahkan node Galera ke ProxySQL

-- Writer group (10)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) 
VALUES (10, '10.0.0.11', 3306, 200);

-- Reader group (20)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) 
VALUES (20, '10.0.0.12', 3306, 200);
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) 
VALUES (20, '10.0.0.13', 3306, 200);

Tambahkan user DB

INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent) 
VALUES ('appuser', 'app_pass', 10, 1);

Atur query rules

-- Semua SELECT → reader group (20)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT', 20, 1);

-- Sisanya (INSERT/UPDATE/DELETE) → writer group (10)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '.*', 10, 1);

Commit konfigurasi

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Test

mysql -u appuser -papp_pass -h 127.0.0.1 -P6033 -e "SELECT @@hostname;"