ProxySQL
宗乐池
wget https://github.com/sysown/proxysql/releases/download/v2.0.13/proxysql-2.0.13-1-centos7.x86_64.rpm
[root@mysqldb2 ~]# rpm -ivh proxysql-2.0.13-1-centos7.x86_64.rpm
warning: proxysql-2.0.13-1-centos7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 79953b49: NOKEY
Preparing... ################################# [100%]
Updating / installing...
1:proxysql-2.0.13-1 warning: group proxysql does not exist - using root
warning: group proxysql does not exist - using root
################################# [100%]
Created symlink from /etc/systemd/system/multi-user.target.wants/proxysql.service to /etc/systemd/system/proxysql.service.
[root@mysqldb2 ~]# service proxysql start
Redirecting to /bin/systemctl start proxysql.service
[root@mysqldb2 ~]#
[root@mysqldb2 ~]# service proxysql status
Redirecting to /bin/systemctl status proxysql.service
● proxysql.service - High Performance Advanced Proxy for MySQL
Loaded: loaded (/etc/systemd/system/proxysql.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2020-07-21 14:49:07 CST; 2s ago
Process: 27843 ExecStart=/usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf $PROXYSQL_OPTS (code=exited, status=0/SUCCESS)
Main PID: 27847 (proxysql)
Tasks: 24
CGroup: /system.slice/proxysql.service
├─27847 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
└─27848 /usr/bin/proxysql --idle-threads -c /etc/proxysql.cnf
Jul 21 14:49:06 mysqldb2 systemd[1]: Starting High Performance Advanced Proxy for MySQL...
Jul 21 14:49:07 mysqldb2 proxysql[27843]: 2020-07-21 14:49:07 [INFO] Using config file /etc/proxysql.cnf
Jul 21 14:49:07 mysqldb2 proxysql[27843]: 2020-07-21 14:49:07 [INFO] Using OpenSSL version: OpenSSL 1.1.1d 10 Sep 2019
Jul 21 14:49:07 mysqldb2 proxysql[27843]: 2020-07-21 14:49:07 [INFO] No SSL keys/certificates found in datadir (/var/lib/proxysql). Generating new keys/certificates.
Jul 21 14:49:07 mysqldb2 systemd[1]: Started High Performance Advanced Proxy for MySQL.
[root@mysqldb2 ~]#
[root@mysqldb2 ~]# netstat -anlp |grep proxy
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 27848/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 27848/proxysql
unix 2 [ ACC ] STREAM LISTENING 45033 7281/master private/proxymap
unix 2 [ ACC ] STREAM LISTENING 45036 7281/master private/proxywrite
unix 2 [ ACC ] STREAM LISTENING 39637 6626/gssproxy /var/lib/gssproxy/default.sock
unix 2 [ ACC ] STREAM LISTENING 39638 6626/gssproxy /run/gssproxy.sock
unix 3 [ ] STREAM CONNECTED 38273 6626/gssproxy
unix 2 [ ] DGRAM 39562 6626/gssproxy
[root@mysqldb2 ~]# proxysql --version
ProxySQL version 2.0.13-107-g91737e0, codename Truls[root@mysqldb2 ~]# mysql -uadmin -padmin -h 127.0.0.1 -P 6032
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
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.
(admin@127.0.0.1_(none)) [14:50]> 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.00 sec)
(root@localhost_(none)) [14:56]> create user 'monitor'@'%' identified by 'monitor';
Query OK, 0 rows affected (0.03 sec)
(root@localhost_(none)) [14:57]> grant all privileges on *.* to 'monitor'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
(root@localhost_(none)) [14:57]> grant all privileges on *.* to 'zs'@'%' identified by 'zs' with grant option;
Query OK, 0 rows affected (0.00 sec)
(root@localhost_(none)) [14:58]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.168.1.179',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.168.1.178',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.168.1.200',3306);
load mysql servers to runtime;
save mysql servers to disk;
(admin@127.0.0.1_(none)) [15:38]> select * from mysql_servers;
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.168.1.179 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | 172.168.1.178 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | 172.168.1.180 | 3306 | 0 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='monitor';
Query OK, 1 row affected (0.00 sec)
Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='monitor';
Query OK, 1 row affected (0.00 sec)
load mysql variables to runtime;
save mysql variables to disk;
(admin@127.0.0.1_(none)) [15:40]> select * from monitor.mysql_server_connect_log;
+---------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+---------------+
| 172.168.1.179 | 3306 | 1595316817974397 | 1114 | NULL |
| 172.168.1.178 | 3306 | 1595316818408422 | 3217 | NULL |
| 172.168.1.180 | 3306 | 1595316818842502 | 3272 | NULL |
| 172.168.1.179 | 3306 | 1595316877974594 | 902 | NULL |
| 172.168.1.180 | 3306 | 1595316878751469 | 4164 | NULL |
| 172.168.1.178 | 3306 | 1595316879527801 | 2082 | NULL |
| 172.168.1.179 | 3306 | 1595316937975355 | 877 | NULL |
| 172.168.1.180 | 3306 | 1595316938556367 | 2327 | NULL |
| 172.168.1.178 | 3306 | 1595316939138158 | 2857 | NULL |
| 172.168.1.179 | 3306 | 1595316997976454 | 2417 | NULL |
| 172.168.1.178 | 3306 | 1595316998393443 | 2690 | NULL |
| 172.168.1.180 | 3306 | 1595316998810900 | 2462 | NULL |
| 172.168.1.179 | 3306 | 1595317057977485 | 3180 | NULL |
| 172.168.1.180 | 3306 | 1595317058539641 | 3932 | NULL |
| 172.168.1.178 | 3306 | 1595317059101778 | 1916 | NULL |
| 172.168.1.178 | 3306 | 1595317117977771 | 3182 | NULL |
| 172.168.1.180 | 3306 | 1595317118501166 | 3414 | NULL |
| 172.168.1.179 | 3306 | 1595317119024291 | 864 | NULL |
| 172.168.1.180 | 3306 | 1595317177977803 | 2216 | NULL |
| 172.168.1.179 | 3306 | 1595317178517579 | 710 | NULL |
| 172.168.1.178 | 3306 | 1595317179057067 | 1999 | NULL |
| 172.168.1.179 | 3306 | 1595317237978268 | 1384 | NULL |
| 172.168.1.178 | 3306 | 1595317238422315 | 3705 | NULL |
| 172.168.1.180 | 3306 | 1595317238866618 | 5674 | NULL |
| 172.168.1.180 | 3306 | 1595317297978795 | 2523 | NULL |
| 172.168.1.179 | 3306 | 1595317298423876 | 1637 | NULL |
| 172.168.1.178 | 3306 | 1595317298868780 | 1640 | NULL |
| 172.168.1.179 | 3306 | 1595317357978983 | 1272 | NULL |
| 172.168.1.178 | 3306 | 1595317358469230 | 2179 | NULL |
| 172.168.1.180 | 3306 | 1595317358959516 | 5136 | NULL |
+---------------+------+------------------+-------------------------+---------------+
30 rows in set (0.00 sec)
类似资料：