[LINUX] PROXY‐SQL ERP - fourslickz/notes GitHub Wiki

[Frappe Server]
     │
     ▼
[ProxySQL]
     │
 ┌───────────────┬───────────────┐
 ▼               ▼               ▼
[Node-1]       [Node-2]       [Node-3]
 (WRITE)        (READ)         (READ)

INSTALL

apt install -y wget lsb-release ca-certificates apt-transport-https
wget -O - https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/repo_pub_key | sudo apt-key add -
echo "deb https://repo.proxysql.com/ProxySQL/proxysql-3.0.x/$(lsb_release -sc)/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list
apt update
apt install proxysql mariadb-client
systemctl enable proxysql
systemctl start proxysql
systemctl status proxysql

LOGIN INTO PROXYSQL CONSOLE

mysql -u admin -padmin -h 127.0.0.1 -P6032

CHECK host on the table mysql_servers

MySQL [(none)]> select * from mysql_servers;
Empty set (0.000 sec)

MySQL [(none)]>

INSERT CONFIG HOST DB

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections) VALUES
(10, '10.130.249.230', 3306, 1000),
(20, '10.130.249.231', 3306, 1000),
(20, '10.130.249.232', 3306, 1000);

INSERT CONFIG USERS

INSERT INTO mysql_users (username, password, default_hostgroup, transaction_persistent)
VALUES ('frappeadmin', 'your-password-db', 10, 1);

INSERT CONFIG QUERY

  • WRITE
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(1, 1, '^.*INSERT.*$', 10, 1),
(2, 1, '^.*UPDATE.*$', 10, 1),
(3, 1, '^.*DELETE.*$', 10, 1),
(4, 1, '^.*REPLACE.*$', 10, 1),
(5, 1, '^.*CREATE.*$', 10, 1),
(6, 1, '^.*DROP.*$', 10, 1),
(7, 1, '^.*ALTER.*$', 10, 1);
  • READ
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) VALUES
(100, 1, '^SELECT.*$', 20, 1);
  • LOAD CONFIG
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;