ProxySQL چیست

apt-get install -y lsb-release apt-transport-https
wget -O - 'https://repo.proxysql.com/ProxySQL/repo_pub_key' | apt-key add -
echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/$(lsb_release -sc)/ ./ \
| tee /etc/apt/sources.list.d/proxysql.list
apt-get update
apt-get install proxysql

service proxysql start
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

service proxysql-initial start

mysql -P6032 -uadmin -padmin -h 127.0.0.1

add writer nodes > INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'192.168.207.35',3360,100);
                   INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (2,'192.168.207.36',3360,100);
                   
add reader nodes > INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (3,'192.168.207.35',3360,100);
                   INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (3,'192.168.207.36',3360,100);
                   INSERT INTO mysql_servers(hostgroup_id,hostname,port,weight) VALUES (3,'192.168.207.37',3360,100);

writer_hostgroup=2, backup_writer_hostgroup=4, reader_hostgroup=3, max_writers=1, writer_is_also_reader=1

create mysql_galera_hostgroups > 
INSERT INTO mysql_galera_hostgroups (writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind) 
VALUES (2,4,3,1,1,1,1,100);

save to runtime > LOAD MYSQL SERVERS TO RUNTIME;
save to disk > SAVE MYSQL SERVERS TO DISK;

select * from mysql_servers;
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname       | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 2            | 192.168.207.35 | 3360 | 0         | ONLINE | 100    | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 192.168.207.36 | 3360 | 0         | ONLINE | 100    | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 192.168.207.35 | 3360 | 0         | ONLINE | 100    | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 192.168.207.36 | 3360 | 0         | ONLINE | 100    | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 192.168.207.37 | 3360 | 0         | ONLINE | 100    | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+----------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+

mysql> select * from mysql_galera_hostgroups;
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+
| 2                | 4                       | 3                | 1                 | 1      | 1           | 1                     | 100                     | NULL    |
+------------------+-------------------------+------------------+-------------------+--------+-------------+-----------------------+-------------------------+---------+

add query rule for read/write split:
> INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.*',3, 1);
> INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '^SELECT.* FOR UPDATE',2, 1);
> INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, '.*',2, 1);
> LOAD MYSQL QUERY RULES TO RUNTIME;
> SAVE MYSQL QUERY RULES TO DISK;

change admin password > SET admin-admin_credentials = 'admin:newpass'; 
                                           LOAD ADMIN VARIABLES TO RUNTIME; 
                                           SAVE ADMIN VARIABLES TO DISK; 

change port > set mysql-interfaces = "0.0.0.0:4006";
                SAVE MYSQL VARIABLES TO DISK;                        
                service proxysql restart

change monitor user and password > set mysql-monitor_username = "monitoring";
                                 > set mysql-monitor_password = "pass";
                                 > LOAD MYSQL VARIABLES TO RUNTIME;
                                 > SAVE MYSQL VARIABLES TO DISK;

CREATE USER 'monitoring'@'%' IDENTIFIED BY 'pass';
GRANT SELECT on sys.* to 'monitoring'@'%';

add zabbix user > INSERT INTO mysql_users(username,password) VALUES ('zabbix_user','PASSWORD');
                > LOAD MYSQL USERS TO RUNTIME;
                > SAVE MYSQL USERS TO DISK;

show tables;
+----------------------------------------------------+
| tables                                             |
+----------------------------------------------------+
| global_variables                                   |
| mysql_aws_aurora_hostgroups                        |
| mysql_collations                                   |
| mysql_firewall_whitelist_rules                     |
| mysql_firewall_whitelist_sqli_fingerprints         |
| mysql_firewall_whitelist_users                     |
| mysql_galera_hostgroups                            |
| mysql_group_replication_hostgroups                 |
| mysql_query_rules                                  |
| mysql_query_rules_fast_routing                     |
| mysql_replication_hostgroups                       |
| mysql_servers                                      |
| mysql_users                                        |
| proxysql_servers                                   |
| restapi_routes                                     |
| runtime_checksums_values                           |
| runtime_global_variables                           |
| runtime_mysql_aws_aurora_hostgroups                |
| runtime_mysql_firewall_whitelist_rules             |
| runtime_mysql_firewall_whitelist_sqli_fingerprints |
| runtime_mysql_firewall_whitelist_users             |
| runtime_mysql_galera_hostgroups                    |
| runtime_mysql_group_replication_hostgroups         |
| runtime_mysql_query_rules                          |
| runtime_mysql_query_rules_fast_routing             |
| runtime_mysql_replication_hostgroups               |
| runtime_mysql_servers                              |
| runtime_mysql_users                                |
| runtime_proxysql_servers                           |
| runtime_restapi_routes                             |
| runtime_scheduler                                  |
| scheduler                                          |
+----------------------------------------------------+

select * from mysql_server_galera_log order by time_start_us desc limit 3;
+----------------+------+------------------+-----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+----------------+-------+
| hostname       | port | time_start_us    | success_time_us | primary_partition | read_only | wsrep_local_recv_queue | wsrep_local_state | wsrep_desync | wsrep_reject_queries | wsrep_sst_donor_rejects_queries | pxc_maint_mode | error |
+----------------+------+------------------+-----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+----------------+-------+
| 192.168.207.37 | 3360 | 1596622329840835 | 3568            | YES               | NO        | 0                      | 4                 | NO           | NO                   | NO                              | NO             | NULL  |
| 192.168.207.36 | 3360 | 1596622329834547 | 3665            | YES               | NO        | 0                      | 4                 | NO           | NO                   | NO                              | NO             | NULL  |
| 192.168.207.35 | 3360 | 1596622329831493 | 9064            | YES               | NO        | 0                      | 4                 | NO           | NO                   | NO                              | NO             | NULL  |
+----------------+------+------------------+-----------------+-------------------+-----------+------------------------+-------------------+--------------+----------------------+---------------------------------+----------------+-------+

mysql> select hostgroup,srv_host,status,ConnUsed,MaxConnUsed,Queries,Latency_us from stats.stats_mysql_connection_pool order by srv_host;
+-----------+----------------+---------+----------+-------------+----------+------------+
| hostgroup | srv_host       | status  | ConnUsed | MaxConnUsed | Queries  | Latency_us |
+-----------+----------------+---------+----------+-------------+----------+------------+
| 2         | 192.168.207.35 | SHUNNED | 0        | 85          | 31894    | 647        |
| 3         | 192.168.207.35 | ONLINE  | 0        | 4           | 178634   | 647        |
| 4         | 192.168.207.35 | ONLINE  | 0        | 0           | 0        | 647        |
| 2         | 192.168.207.36 | SHUNNED | 0        | 936         | 23387827 | 509        |
| 3         | 192.168.207.36 | ONLINE  | 0        | 3           | 171814   | 509        |
| 4         | 192.168.207.36 | ONLINE  | 0        | 0           | 0        | 509        |
| 2         | 192.168.207.37 | ONLINE  | 0        | 16          | 122069   | 819        |
| 3         | 192.168.207.37 | ONLINE  | 0        | 3           | 84405    | 819        |
+-----------+----------------+---------+----------+-------------+----------+------------+

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 |
+-----------+----------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
| 2         | 192.168.207.37 | 3360     | ONLINE  | 0        | 17       | 17     | 0       | 17          | 10675   | 0                 | 1811061         | 15524           | 944        |
| 2         | 192.168.207.36 | 3360     | SHUNNED | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 459        |
| 2         | 192.168.207.35 | 3360     | SHUNNED | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 915        |
| 3         | 192.168.207.37 | 3360     | ONLINE  | 0        | 10       | 10     | 0       | 10          | 5619    | 0                 | 1344154         | 40544106        | 944        |
| 3         | 192.168.207.36 | 3360     | ONLINE  | 0        | 6        | 6      | 0       | 6           | 5489    | 0                 | 1327736         | 39501124        | 459        |
| 3         | 192.168.207.35 | 3360     | ONLINE  | 0        | 8        | 8      | 0       | 8           | 5769    | 0                 | 1400080         | 40226115        | 915        |
| 4         | 192.168.207.36 | 3360     | ONLINE  | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 459        |
| 4         | 192.168.207.35 | 3360     | ONLINE  | 0        | 0        | 0      | 0       | 0           | 0       | 0                 | 0               | 0               | 915        |
+-----------+----------------+----------+---------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+

datadir="/var/lib/proxysql"
 
admin_variables =
{
        admin_credentials="admin:pass;cluster:pass;admin:admin"
        mysql_ifaces="0.0.0.0:6032"
        cluster_username="cluster"
        cluster_password="pass"
        cluster_check_interval_ms=200
        cluster_check_status_frequency=100
        cluster_mysql_query_rules_save_to_disk=true
        cluster_mysql_servers_save_to_disk=true
        cluster_mysql_users_save_to_disk=true
        cluster_proxysql_servers_save_to_disk=true
        cluster_mysql_query_rules_diffs_before_sync=3
        cluster_mysql_servers_diffs_before_sync=3
        cluster_mysql_users_diffs_before_sync=3
        cluster_proxysql_servers_diffs_before_sync=3
}
 
proxysql_servers =
(
    {
        hostname="SERVER1"
        port=6032
        comment="proxysql1"
    },
    {
        hostname="SERVER2"
        port=6032
        comment="proxysql2"
    }
)

set admin-cluster_username > set admin-cluster_username = "myCluster1";
set admin-cluster_password > set admin-cluster_password = "yumyumCluster";
updating admin_credentials > SET admin-admin_credentials = 'admin:admin;myCluster1:yumyumCluster';
save changes > LOAD ADMIN VARIABLES TO RUNTIME;
               SAVE ADMIN VARIABLES TO DISK;

INSERT INTO proxysql_servers (hostname,port) VALUES ('192.168.207.110','6032');
LOAD PROXYSQL SERVERS TO RUNTIME;
SAVE PROXYSQL SERVERS TO DISK;

    LOAD MYSQL QUERY RULES TO RUNTIME: generates a new checksum if admin-checksum_mysql_query_rules is true
    LOAD MYSQL SERVERS TO RUNTIME: generates a new checksum if admin-checksum_mysql_servers is true
    LOAD MYSQL USERS TO RUNTIME: generates a new checksum if admin-checksum_mysql_users is true
    LOAD PROXYSQL SERVERS TO RUNTIME: generates a new checksum, always
    LOAD ADMIN VARIABLES TO RUNTIME: does NOT generate a checksum yet
    LOAD MYSQL VARIABLES TO RUNTIME: does NOT generate a checksum yet

select * from stats_mysql_query_digest order by count_star DESC limit 5\G
SELECT * FROM stats_mysql_query_digest ORDER BY sum_time DESC\G

INSERT INTO mysql_query_rules(active, digest, cache_ttl, apply) VALUES(1,'0x2F6029D9E19D4643',2000,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;