目录
3.1 查看main库（默认登陆后即在此库）的global_variables表信息
在拓扑结构中，ProxySQL部署在应用程序和MySQL集群的中间位置。应用程序向ProxySQL发起SQL语句，ProxySQL分析收到的SQL语句，进行匹配、重写等操作，然后路由给后端MySQL集群中的实例。
ProxySQL
- 两个版本：官方版和percona版，
- percona版是基于官方版基础上修改，C++语言开发，轻量级但性能优异(支持处理千亿级数据)
- 具有中间件所需的绝大多数功能，包括：
多种方式的读/写分离
定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
缓存查询结果
后端节点监控
官方站点：https://proxysql.com/
官方手册：https://github.com/sysown/proxysql/wiki
拓扑图如下：
1、配置主从环境
参考文件：主从安装配置
2、配置ProxySQL
安装方式有两种：yum安装与rpm包方式安装
rpm包：下载地址
2.1 ProxySQL安装（需yum源）
[root@centos-03 ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
[root@centos-03 ~]# yum clean all
[root@centos-03 ~]# yum repolist
proxysql_repo/7 ProxySQL YUM repository 0
[root@centos-03 ~]# yum install -y proxysql
[root@centos-03 ~]# rpm -ql proxysql
/etc/logrotate.d/proxysql
/etc/proxysql.cnf
/etc/systemd/system/proxysql-initial.service
/etc/systemd/system/proxysql.service
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl2.1.1 启动ProxySQL
[root@centos-03 ~]# chkconfig proxysql on
[root@centos-03 ~]# systemctl start proxysql
[root@centos-03 ~]# systemctl status proxysql2.1.2 启动后会监听两个端口
默认为6032和6033。6032端口是ProxySQL的管理端口，6033是ProxySQL对外提供服务的端口 (即连接到转发后端的真正数据库的转发端口)。
[root@centos-03 ~]# netstat -lntp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 1538/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 1538/proxysql 2.1.3 安装mysql-clinet客户端
另外，需要在proxysql服务器上安装mysql客户端，用于在本机连接到ProxySQL的管理接口
[root@centos-03 ~]# cat <<EOF | tee /etc/yum.repos.d/MariaDB.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2.4/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
[root@centos-03 ~]# yum -y install MariaDB-clientProxySQL启动过程总结:
当proxysql启动时，首先读取配置文件CONFIG FILE(/etc/proxysql.cnf)，然后从该配置文件中获取datadir，datadir中配置的是sqlite的数据目录。如果该目录存在，且sqlite数据文件存在，那么正常启动，将sqlite中的配置项读进内存，并且加载进RUNTIME，用于初始化proxysql的运行。如果datadir目录下没有sqlite的数据文件，proxysql就会使用config file中的配置来初始化proxysql，并且将这些配置保存至数据库。sqlite数据文件可以不存在，/etc/proxysql.cnf文件也可以为空，但/etc/proxysql.cnf配置文件必须存在，否则，proxysql无法启动。
2.2 配置ProxySQL
ProxySQL有配置文件/etc/proxysql.cnf和配置数据库文件/var/lib/proxysql/proxysql.db。这里需要特别注意：如果存在如果存在"proxysql.db"文件(在/var/lib/proxysql目录下)，则ProxySQL服务只有在第一次启动时才会去读取proxysql.cnf文件并解析；后面启动会就不会读取proxysql.cnf文件了！如果想要让proxysql.cnf文件里的配置在重启proxysql服务后生效(即想要让proxysql重启时读取并解析proxysql.cnf配置文件)，则需要先删除/var/lib/proxysql/proxysql.db数据库文件，然后再重启proxysql服务。这样就相当于初始化启动proxysql服务了，会再次生产一个纯净的proxysql.db数据库文件(如果之前配置了proxysql相关路由规则等，则就会被抹掉)。 官方推荐用admin interface方式！(即在proxysql本机使用mysql客户端连接管理端口)
[root@centos-03 ~]# egrep -v "^(#|$)" /etc/proxysql.cnf
datadir="/var/lib/proxysql" #数据目录
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
admin_credentials="admin:admin" #连接管理端的用户名与密码
mysql_ifaces="0.0.0.0:6032" #管理端口，用来连接proxysql的管理数据库
}
mysql_variables=
{
threads=4 #指定转发端口开启的线程数量
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces="0.0.0.0:6033" #指定转发端口，用于连接后端mysql数据库的，相当于代理作用
default_schema="information_schema"
stacksize=1048576
server_version="5.7.22" #指定后端mysql的版本
connect_timeout_server=3000
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
}
mysql_servers =
(
)
mysql_users:
(
)
mysql_query_rules:
(
)
scheduler=
(
)
mysql_replication_hostgroups=
(
)
proxysql的数据目录
[root@centos-03 ~]# ll /var/lib/proxysql/
总用量 404
-rw-rw---- 1 proxysql proxysql 1078 8月 11 15:55 proxysql-ca.pem
-rw-rw---- 1 proxysql proxysql 1062 8月 11 15:55 proxysql-cert.pem
-rw------- 1 proxysql proxysql 196608 8月 11 15:55 proxysql.db
-rw-rw---- 1 proxysql proxysql 1675 8月 11 15:55 proxysql-key.pem
-rw------- 1 proxysql proxysql 4052 8月 11 15:55 proxysql.log
-rw-r--r-- 1 proxysql proxysql 5 8月 11 15:55 proxysql.pid
-rw------- 1 proxysql proxysql 163840 8月 11 16:09 proxysql_stats.db3、ProxySQL的库、表说明
（默认管理端口是6032，客户端服务端口是6033。默认的用户名密码都是 admin）
通过管理端口6032去连接的 (注意, 下面连接命令中后面的--prompt 'admin'字段可以不加，也是可以登录进去的)
[root@centos-03 yum.repos.d]# mysql -u admin -padmin -h 127.0.0.1 -P6032
或者
[root@centos-03 yum.repos.d]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin>'
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 4
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, 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>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)数据库说明：
ProxySQL提供了几个库，每个库都有各自的意义；
- - main 内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容，
- 不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。
- - disk 是持久化到硬盘的配置，sqlite数据文件。
- - stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
- - monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。
3.1 查看main库（默认登陆后即在此库）的global_variables表信息
admin>use main;
Database changed
admin>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 |
+----------------------------------------------------+
32 rows in set (0.00 sec)
admin> select * from global_variables;
+--------------------------------------------------------------+-----------------------------+
| variable_name | variable_value |
+--------------------------------------------------------------+-----------------------------+
| mysql-default_charset | utf8 |
| mysql-default_collation_connection | utf8_general_ci |
| mysql-shun_on_failures | 5 |
| mysql-shun_recovery_time_sec | 10 |
| mysql-query_retries_on_failure | 1 |
| mysql-client_multi_statements | true |
| mysql-connect_retries_delay | 1 |
| mysql-connection_delay_multiplex_ms | 0 |
| mysql-connection_max_age_ms | 0 |
| mysql-connect_timeout_server_max | 10000 |
| mysql-eventslog_filename | |
| mysql-eventslog_filesize | 104857600 |
| mysql-eventslog_default_log | 0 |
| mysql-eventslog_format | 1 |
| mysql-auditlog_filename | |
| mysql-auditlog_filesize | 104857600 |
| mysql-handle_unknown_charset | 1 |
| mysql-free_connections_pct | 10 |
| mysql-connection_warming | false |
| mysql-session_idle_ms | 1000 |
| mysql-have_ssl | false |
| mysql-client_found_rows | true |
| mysql-monitor_enabled | true |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 |
| mysql-monitor_groupreplication_max_transactions_behind_count | 3 |
| mysql-monitor_galera_healthcheck_interval | 5000 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_threads_min | 8 |
| mysql-monitor_threads_max | 128 |
| mysql-monitor_threads_queue_maxsize | 128 |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-max_allowed_packet | 67108864 |
| mysql-tcp_keepalive_time | 0 |
| mysql-use_tcp_keepalive | 0 |
| mysql-automatic_detect_sqli | 0 |
| mysql-firewall_whitelist_enabled | 0 |
| mysql-firewall_whitelist_errormsg | Firewall blocked this query |
| mysql-throttle_connections_per_sec_to_hostgroup | 1000000 |
| mysql-max_transaction_time | 14400000 |
| mysql-multiplexing | true |
| mysql-log_unhealthy_connections | true |
| mysql-forward_autocommit | false |
| mysql-enforce_autocommit_on_reads | false |
| mysql-autocommit_false_not_reusable | false |
| mysql-autocommit_false_is_transaction | false |
| mysql-verbose_query_error | false |
| mysql-hostgroup_manager_verbose | 1 |
| mysql-binlog_reader_connect_retry_msec | 3000 |
| mysql-threshold_query_length | 524288 |
| mysql-threshold_resultset_size | 4194304 |
| mysql-query_digests_max_digest_length | 2048 |
| mysql-query_digests_max_query_length | 65000 |
| mysql-wait_timeout | 28800000 |
| mysql-throttle_max_bytes_per_second_to_client | 0 |
| mysql-throttle_ratio_server_to_client | 0 |
| mysql-max_stmts_per_connection | 20 |
| mysql-max_stmts_cache | 10000 |
| mysql-mirror_max_concurrency | 16 |
| mysql-mirror_max_queue_length | 32000 |
| mysql-default_max_latency_ms | 1000 |
| mysql-query_processor_iterations | 0 |
| mysql-query_processor_regex | 1 |
| mysql-set_query_lock_on_hostgroup | 1 |
| mysql-reset_connection_algorithm | 2 |
| mysql-auto_increment_delay_multiplex | 5 |
| mysql-long_query_time | 1000 |
| mysql-query_cache_size_MB | 256 |
| mysql-poll_timeout_on_failure | 100 |
| mysql-server_capabilities | 569867 |
| mysql-keep_multiplexing_variables | tx_isolation,version |
| mysql-kill_backend_connection_when_disconnect | true |
| mysql-client_session_track_gtid | true |
| mysql-session_idle_show_processlist | true |
| mysql-show_processlist_extended | 0 |
| mysql-query_digests | true |
| mysql-query_digests_lowercase | false |
| mysql-query_digests_replace_null | false |
| mysql-query_digests_no_digits | false |
| mysql-query_digests_normalize_digest_text | false |
| mysql-query_digests_track_hostname | false |
| mysql-servers_stats | true |
| mysql-default_reconnect | true |
| mysql-ssl_p2s_ca | |
| mysql-ssl_p2s_cert | |
| mysql-ssl_p2s_key | |
| mysql-ssl_p2s_cipher | |
| mysql-init_connect | |
| mysql-ldap_user_variable | |
| mysql-add_ldap_user_comment | |
| mysql-default_tx_isolation | READ-COMMITTED |
| mysql-default_session_track_gtids | OFF |
| mysql-connpoll_reset_queue_length | 50 |
| mysql-min_num_servers_lantency_awareness | 1000 |
| mysql-aurora_max_lag_ms_only_read_from_replicas | 2 |
| mysql-stats_time_backend_query | false |
| mysql-stats_time_query_processor | false |
| mysql-query_cache_stores_empty_result | true |
| admin-stats_credentials | stats:stats |
| admin-stats_mysql_connections | 60 |
| admin-stats_mysql_connection_pool | 60 |
| admin-stats_mysql_query_cache | 60 |
| admin-stats_mysql_query_digest_to_disk | 0 |
| admin-stats_system_cpu | 60 |
| admin-stats_system_memory | 60 |
| admin-telnet_admin_ifaces | (null) |
| admin-telnet_stats_ifaces | (null) |
| admin-refresh_interval | 2000 |
| admin-read_only | false |
| admin-hash_passwords | true |
| admin-vacuum_stats | true |
| admin-version | 2.0.13-107-g91737e0 |
| admin-cluster_username | |
| admin-cluster_password | |
| admin-cluster_check_interval_ms | 1000 |
| admin-cluster_check_status_frequency | 10 |
| admin-cluster_mysql_query_rules_diffs_before_sync | 3 |
| admin-cluster_mysql_servers_diffs_before_sync | 3 |
| admin-cluster_mysql_users_diffs_before_sync | 3 |
| admin-cluster_proxysql_servers_diffs_before_sync | 3 |
| admin-cluster_mysql_query_rules_save_to_disk | true |
| admin-cluster_mysql_servers_save_to_disk | true |
| admin-cluster_mysql_users_save_to_disk | true |
| admin-cluster_proxysql_servers_save_to_disk | true |
| admin-checksum_mysql_query_rules | true |
| admin-checksum_mysql_servers | true |
| admin-checksum_mysql_users | true |
| admin-restapi_enabled | false |
| admin-restapi_port | 6070 |
| admin-web_enabled | false |
| admin-web_port | 6080 |
| admin-admin_credentials | admin:admin |
| admin-mysql_ifaces | 0.0.0.0:6032 |
| mysql-threads | 4 |
| mysql-max_connections | 2048 |
| mysql-default_query_delay | 0 |
| mysql-default_query_timeout | 36000000 |
| mysql-have_compress | true |
| mysql-poll_timeout | 2000 |
| mysql-interfaces | 0.0.0.0:6033 |
| mysql-default_schema | information_schema |
| mysql-stacksize | 1048576 |
| mysql-server_version | 5.5.30 |
| mysql-connect_timeout_server | 3000 |
| mysql-monitor_username | monitor |
| mysql-monitor_password | monitor |
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_read_only_interval | 1500 |
| mysql-monitor_read_only_timeout | 500 |
| mysql-ping_interval_server_msec | 120000 |
| mysql-ping_timeout_server | 500 |
| mysql-commands_stats | true |
| mysql-sessions_sort | true |
| mysql-connect_retries_on_failure | 10 |
+--------------------------------------------------------------+-----------------------------+
169 rows in set (0.01 sec)登陆成功后，可通过对main库（默认登陆后即在此库）的global_variables表中的"admin-admin_credentials" 和 "admin-mysql_ifaces"
两个变量进行更改来修改登录认证! 比如说修改密码或定义一个非admin的用户用于远程登录。
proxysql的6032端口是管理入口，账号密码是admin(可以动态修改),允许客户端连接；6033端口就是客户端入口，账号密码通过管理接口去设置。在proxysql本机使用mysql客户端连接到ProxySQL的管理接口(admin interface), 该接口的默认管理员用户和密码都是admin。
3.2 mysql_ifaces
也就是说proxysql有一个admin接口专门来做配置，相当于一个mysql shell可以通过sql来让配置实时生效。
mysql_ifaces配置了允许连接proxysql的ip和port
[root@centos-03 yum.repos.d]# nl /etc/proxysql.cnf |grep mysql_ifaces
# 将admin_variables中的mysql_ifaces修改成允许远程访问
36 # mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
37 mysql_ifaces="0.0.0.0:6032"
如果ip配置为0.0.0.0表示不限制ip，但是出于安全考虑，admin用户无论怎么设置都只能在本机登录!
3.3 admin_credentials
这个key保存所有可以操作proxysql的用户名和密码，格式为：user:pass;user1:pass1，这里可以修改密码或定义一个非admin的用户用于远程登录。 前提是保证想要管理proxysql的机器安装有mysql client客户端！
先在proxysql本机登录 (因为初始账号密码是admin:admin，只能在本机登录), 这里的proxysql本机地址是192.168.1.130
修改远程连接proxysql管理端口的账号和密码radmin:radmin.
[root@centos-03 yum.repos.d]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin>'
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 6
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, 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> update global_variables set variable_value = 'admin:admin;radmin:radmin' where variable_name = 'admin-admin_credentials';
Query OK, 1 row affected (0.00 sec)
admin> LOAD ADMIN VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.03 sec)
admin> SAVE ADMIN VARIABLES TO DISK;
Query OK, 35 rows affected (0.00 sec)
这样就可以使用下面的命令在其他机器上使用radmin用户登录（其他机器上需要有mysql client）
[root@centos-02 ~]# mysql -uradmin -pradmin -h192.168.1.130 -P6032
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 9
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, 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.
mysql> 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.01 sec)1) main 库 （disk库的表字段和main一样）
admin> show tables from main;
+----------------------------------------------------+
| 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 |
+----------------------------------------------------+
32 rows in set (0.00 sec)
admin>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.00 sec)常用的几个表介绍
===============================================
global_variables
设置变量，包括监听的端口、管理账号等。
mysql_collations
相关字符集和校验规则。
mysql_query_rules
定义查询路由规则。
mysql_replication_hostgroups
监视指定主机组中所有服务器的read_only值，并且根据read_only的值将服务器分配给写入器或读取器主机组。ProxySQL monitor模块会监控hostgroups
后端所有servers 的read_only 变量，如果发现从库的read_only变为0、主库变为1，则认为角色互换了，自动改写mysql_servers表里面 hostgroup关系，
达到自动 Failover 效果。
mysql_servers
设置后端MySQL的表
mysql_users
配置后端数据库的程序账号和监控账号。
scheduler
调度器是一个类似于cron的实现，集成在ProxySQL中，具有毫秒的粒度。通过脚本检测来设置ProxySQL。
2）stats库
admin> show tables from stats;
+--------------------------------------+
| tables |
+--------------------------------------+
| global_variables |
| stats_memory_metrics |
| stats_mysql_commands_counters |
| stats_mysql_connection_pool |
| stats_mysql_connection_pool_reset |
| stats_mysql_errors |
| stats_mysql_errors_reset |
| stats_mysql_free_connections |
| stats_mysql_global |
| stats_mysql_gtid_executed |
| stats_mysql_prepared_statements_info |
| stats_mysql_processlist |
| stats_mysql_query_digest |
| stats_mysql_query_digest_reset |
| stats_mysql_query_rules |
| stats_mysql_users |
| stats_proxysql_servers_checksums |
| stats_proxysql_servers_metrics |
| stats_proxysql_servers_status |
+--------------------------------------+
19 rows in set (0.00 sec)常用的几个表介绍
===============================================
stats_mysql_commands_counters
统计各种SQL类型的执行次数和时间，通过参数mysql-commands_stats控制开关，默认是ture。
stats_mysql_connection_pool
连接后端MySQL的连接信息。
stats_mysql_processlist
类似MySQL的show processlist的命令，查看各线程的状态。
stats_mysql_query_digest
表示SQL的执行次数、时间消耗等。通过变量mysql-query_digests控制开关，默认是开。
stats_mysql_query_rules
路由命中次数统计。
3）monitor库
admin> show tables from monitor;
+--------------------------------------+
| tables |
+--------------------------------------+
| mysql_server_aws_aurora_check_status |
| mysql_server_aws_aurora_failovers |
| mysql_server_aws_aurora_log |
| mysql_server_connect_log |
| mysql_server_galera_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+--------------------------------------+
9 rows in set (0.00 sec)常用的几个表介绍
===============================================
mysql_server_connect_log
连接到所有MySQL服务器以检查它们是否可用，该表用来存放检测连接的日志。
mysql_server_ping_log
使用mysql_ping API ping后端MySQL服务器，检查它们是否可用，该表用来存放ping的日志。
mysql_server_replication_lag_log
后端MySQL服务主从延迟的检测。
runtime_开头的是运行时的配置，这些是不能修改的。要修改ProxySQL的配置，需要修改了非runtime_表，
修改后必须执行"LOAD ... TO RUNTIME"才能加载到RUNTIME生效，执行save ... to disk才能将配置持久化保存到磁盘。
4、ProxySQL实现读写分离
4.1 向ProxySQL中添加MySQL节点
使用insert语句添加主机到mysql_servers表中，其中：hostgroup_id 为10表示写组，为20表示读组。
[root@centos-03 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin>'
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 11
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, 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> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.1.128',3306);
Query OK, 1 row affected (0.00 sec)
admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.1.129',3306);
Query OK, 1 row affected (0.00 sec)如果在插入过程中，出现报错：
ERROR 1045 (#2800): UNIQUE constraint failed: mysql_servers.hostgroup_id, mysql_servers.hostname, mysql_servers.port
说明可能之前就已经定义了其他配置，可以清空这张表 或者 删除对应host的配置admin> select * from mysql_servers;
admin> delete from mysql_servers;
Query OK, 6 rows affected (0.000 sec)
查看这2个节点是否插入成功，以及它们的状态
admin> 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 | 192.168.1.128 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | 192.168.1.129 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)说明：
- hostgroup_id: ProxySQL通过 hostgroup (下称HG) 的形式组织后端db实例。一个 HG 代表同属于一个角色
- 该表的主键是 (hostgroup_id, hostname, port)，可以看到一个 hostname:port 可以在多个hostgroup里面，如上面的 10.0.100.100:3307，这样可以避免 HG 1000 的从库全都不可用时，依然可以把读请求发到主库上。
- 一个 HG 可以有多个实例，即多个从库，可以通过 weight 分配权重
- hostgroup_id 0 是一个特殊的HG，路由查询的时候，没有匹配到规则则默认选择 HG 0
- status:
- ONLINE: 当前后端实例状态正常
- SHUNNED: 临时被剔除，可能因为后端 too many connections error，或者超过了可容忍延迟阀值 max_replication_lag
- OFFLINE_SOFT: “软离线”状态，不再接受新的连接，但已建立的连接会等待活跃事务完成。
- OFFLINE_HARD: “硬离线”状态，不再接受新的连接，已建立的连接或被强制中断。当后端实例宕机或网络不可达，会出现。
- max_connections: 允许连接到该后端mysql实例的最大连接数。不要大于MySQL设置的 max_connections，如果后端实例 hostname:port 在多个 hostgroup 里，以较大者为准，而不是各自独立允许的最大连接数。
- max_replication_lag: 允许的最大延迟，主库不受这个影响，默认0。如果 > 0， monitor 模块监控主从延迟大于阀值时，会临时把它变为 SHUNNED 。
- max_latency_ms: mysql_ping 响应时间，大于这个阀值会把它从连接池剔除（即使是ONLINE）
- comment: 备注，不建议留空。可以通过它的内容如json格式的数据，配合自己写的check脚本，完成一些自动化的工作。
如上修改后，加载到RUNTIME，并保存到disk
admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.02 sec)
admin> save mysql servers to disk;
Query OK, 0 rows affected (0.03 sec)4.2 监控后端MySQL节点
添加Mysql节点之后，还需要监控这些后端节点。对于后端是主从复制的环境来说，这是必须的，因为ProxySQL需要通过每个节点的read_only值来自动调整
它们是属于读组还是写组。首先在后端master主数据节点上创建一个用于监控的用户名(只需在master上创建即可，因为会复制到slave上)，这个用户名只需具有USAGE权限即可。如果还需
要监控复制结构中slave是否严重延迟于master(这个俗语叫做"拖后腿"，术语叫做"replication lag")，则还需具备replication client权限。
在mysql-master主数据库节点行执行：
[root@centos-01 ~]# mysql -uroot -p123456
......
mysql> create user monitor@'192.168.1.%' identified by '1q2w3e4r';
Query OK, 0 rows affected (0.15 sec)
mysql> grant replication client on *.* to monitor@'192.168.1.%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)然后回到mysql-proxy代理层节点上配置监控
[root@centos-03 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin>'
.........
admin> set mysql-monitor_username='monitor';
Query OK, 1 row affected (0.00 sec)
admin> set mysql-monitor_password='1q2w3e4r';
Query OK, 1 row affected (0.00 sec)
admin> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
admin> save mysql variables to disk;
Query OK, 134 rows affected (0.01 sec)
验证监控结果：ProxySQL监控模块的指标都保存在monitor库的log表中。
以下是连接是否正常的监控(对connect指标的监控)：
注意：可能会有很多connect_error，这是因为没有配置监控信息时的错误，配置后如果connect_error的结果为NULL则表示正常。
admin> select * from mysql_server_connect_log;
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
192.168.1.129 | 3306 | 1597203755579760 | 5449 | NULL |
| 192.168.1.128 | 3306 | 1597203757132535 | 7186 | NULL |
| 192.168.1.129 | 3306 | 1597203758162549 | 1554 | NULL |
| 192.168.1.128 | 3306 | 1597203817132683 | 1350 | NULL |
| 192.168.1.129 | 3306 | 1597203818037219 | 10335 | NULL |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)以下是对心跳信息的监控(对ping指标的监控)
admin> select * from mysql_server_ping_log;
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
| 192.168.1.129 | 3306 | 1597203754965639 | 25516 | NULL |
| 192.168.1.129 | 3306 | 1597203755641789 | 8559 | NULL |
| 192.168.1.128 | 3306 | 1597203755822015 | 27738 | NULL |
| 192.168.1.129 | 3306 | 1597203765642489 | 649 | NULL |
| 192.168.1.128 | 3306 | 1597203765798771 | 526 | NULL |
| 192.168.1.128 | 3306 | 1597203775642756 | 502 | NULL |
| 192.168.1.129 | 3306 | 1597203775839034 | 614 | NULL |
| 192.168.1.128 | 3306 | 1597203785643016 | 501 | NULL |
| 192.168.1.129 | 3306 | 1597203785788363 | 458 | NULL |
| 192.168.1.128 | 3306 | 1597203795643189 | 414 | NULL |
| 192.168.1.129 | 3306 | 1597203795808873 | 524 | NULL |
| 192.168.1.128 | 3306 | 1597203805643444 | 483 | NULL |
| 192.168.1.129 | 3306 | 1597203805774997 | 507 | NULL |
| 192.168.1.128 | 3306 | 1597203815643802 | 780 | NULL |
| 192.168.1.129 | 3306 | 1597203815794225 | 1311 | NULL |
| 192.168.1.129 | 3306 | 1597203825654344 | 5258 | NULL |
| 192.168.1.128 | 3306 | 1597203825809517 | 15436 | NULL |
| 192.168.1.129 | 3306 | 1597203835654454 | 775 | NULL |
| 192.168.1.128 | 3306 | 1597203835826912 | 474 | NULL |
| 192.168.1.129 | 3306 | 1597203845655478 | 489 | NULL |
| 192.168.1.128 | 3306 | 1597203845794034 | 1652 | NULL |
| 192.168.1.129 | 3306 | 1597203855656338 | 998 | NULL |
| 192.168.1.128 | 3306 | 1597203855852682 | 660 | NULL |
| 192.168.1.129 | 3306 | 1597203865657335 | 491 | NULL |
| 192.168.1.128 | 3306 | 1597203865781372 | 389 | NULL |
| 192.168.1.129 | 3306 | 1597203875658214 | 519 | NULL |
| 192.168.1.128 | 3306 | 1597203875826015 | 688 | NULL |
| 192.168.1.129 | 3306 | 1597203885659435 | 439 | NULL |
| 192.168.1.128 | 3306 | 1597203885857864 | 968 | NULL |
| 192.168.1.129 | 3306 | 1597203895659554 | 415 | NULL |
| 192.168.1.128 | 3306 | 1597203895859534 | 923 | NULL |
| 192.168.1.129 | 3306 | 1597203905660589 | 570 | NULL |
| 192.168.1.128 | 3306 | 1597203905803424 | 739 | NULL |
| 192.168.1.129 | 3306 | 1597203915660741 | 485 | NULL |
| 192.168.1.128 | 3306 | 1597203915801208 | 458 | NULL |
| 192.168.1.128 | 3306 | 1597203925661748 | 493 | NULL |
| 192.168.1.129 | 3306 | 1597203925832599 | 2219 | NULL |
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+
read_only日志此时也为空(正常来说，新环境配置时，这个只读日志是为空的)
admin>select * from mysql_server_read_only_log;
Empty set (0.00 sec)replication_lag的监控日志为空
admin>select * from mysql_server_replication_lag_log;
Empty set (0.00 sec)指定写组的id为10，读组的id为20
admin> insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values(10,20);
Query OK, 1 row affected (0.00 sec)在该配置加载到RUNTIME生效之前，先查看下各mysql server所在的组。
admin>select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.1.128 | 3306 | ONLINE | 1 |
| 10 | 192.168.1.129 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
2 rows in set (0.00 sec)2个节点都在hostgroup_id=10的组中。
现在，将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。
admin> load mysql servers to runtime;
Query OK, 0 rows affected (0.00 sec)
admin> save mysql servers to disk;
Query OK, 0 rows affected (0.02 sec)一加载，Monitor模块就会开始监控后端的read_only值，当监控到read_only值后，就会按照read_only的值将某些节点自动移动到读/写组。
例如，此处所有节点都在id=10的写组，slave1的read_only=1，这个节点将会移动到id=20的组。
现在看结果
admin> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.1.128 | 3306 | ONLINE | 1 |
| 20 | 192.168.1.129 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
2 rows in set (0.00 sec)
admin> select * from mysql_server_read_only_log;
+---------------+------+------------------+-----------------+-----------+-------+
| hostname | port | time_start_us | success_time_us | read_only | error |
+---------------+------+------------------+-----------------+-----------+-------+
| 192.168.1.129 | 3306 | 1597215109281816 | 11848 | 1 | NULL |
| 192.168.1.128 | 3306 | 1597215109310894 | 11839 | 0 | NULL |
| 192.168.1.129 | 3306 | 1597215110782890 | 878 | 1 | NULL |
| 192.168.1.128 | 3306 | 1597215110802814 | 715 | 0 | NULL |
| 192.168.1.128 | 3306 | 1597215112283130 | 475 | 0 | NULL |
| 192.168.1.129 | 3306 | 1597215112312026 | 685 | 1 | NULL |4.3 配置mysql_users
上面的所有配置都是关于后端MySQL节点的，现在可以配置关于SQL语句的，包括：发送SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、SQL语句的重写等等。本小节是SQL请求所使用的用户配置，例如root用户。这要求我们需要先在后端MySQL节点添加好相关用户。这里以root和sqlsender两个用户名为例
首先，在mysql-master主数据库节点上执行：(只需master执行即可，会复制给slave)
[root@centos-01 ~]# mysql -uroot -p123456
......
mysql> grant all on *.* to root@'192.168.1.%' identified by 'passwd';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> grant all on *.* to sqlsender@'192.168.1.%' identified by 'P@ssword1!';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)然后回到mysql-proxy代理层节点，配置mysql_users表，将刚才的两个用户添加到该表中。
[root@centos-03 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin>'
.............
admin> insert into mysql_users(username,password,default_hostgroup) values('root','passwd',10);
Query OK, 1 row affected (0.00 sec)
admin> insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10);
Query OK, 1 row affected (0.00 sec)
admin> load mysql users to runtime;
Query OK, 0 rows affected (0.00 sec)
admin> save mysql users to disk;
Query OK, 0 rows affected (0.00 sec)
mysql_users表有不少字段，最主要的三个字段为username、password和default_hostgroup：
- username：前端连接ProxySQL，以及ProxySQL将SQL语句路由给MySQL所使用的用户名。
- password：用户名对应的密码。可以是明文密码，也可以是hash密码。如果想使用hash密码，可以先在某个MySQL节点上执行
select password(PASSWORD)，然后将加密结果复制到该字段。
- default_hostgroup：该用户名默认的路由目标。例如，指定root用户的该字段值为10时，则使用root用户发送的SQL语句默认
情况下将路由到hostgroup_id=10组中的某个节点。
admin> select * from mysql_users;
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| myadmin | mypass | 1 | 0 | 0 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | |
| root | passwd | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | |
| sqlsender | P@ssword1! | 1 | 0 | 10 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | |
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
3 rows in set (0.00 sec)注意：active 与 transaction_persistent需引起注意，只有active=1的用户才是有效的用户。
至于transaction_persistent字段，当它的值为1时，表示事务持久化：当某连接使用该用户开启了一个事务后，那么在事务提交/回滚之前，
所有的语句都路由到同一个组中，避免语句分散到不同组。在以前的版本中，默认值为0，不知道从哪个版本开始，它的默认值为1。
我们期望的值为1，所以在继续下面的步骤之前，先查看下这个值，如果为0，则执行下面的语句修改为1。
admin> update mysql_users set transaction_persistent=1 where username='root';
Query OK, 1 row affected (0.00 sec)
admin> update mysql_users set transaction_persistent=1 where username='sqlsender';
Query OK, 1 row affected (0.00 sec)
admin> load mysql users to runtime;
Query OK, 0 rows affected (0.01 sec)
admin> save mysql users to disk;
Query OK, 0 rows affected (0.02 sec)然后，分别使用root用户和sqlsender用户测试下它们是否能路由到默认的hostgroup_id=10(它是一个写组)读、写数据。
下面是通过转发端口6033连接的，连接的是转发到后端真正的数据库!
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 128 |
+-------------+
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "create database proxy_test"
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| grade |
| mysql |
| performance_schema |
| proxy_test |
| sys |
+--------------------+
[root@centos-03 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'use proxy_test;create table t(id int);'
[root@centos-03 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show tables from proxy_test;'
+----------------------+
| Tables_in_proxy_test |
+----------------------+
| t |
+----------------------+
[root@centos-03 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| grade |
| mysql |
| performance_schema |
| proxy_test |
| sys |
+--------------------+
[root@centos-03 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'drop database proxy_test;'
[root@centos-03 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| grade |
| mysql |
| performance_schema |
| sys |
+--------------------+4.4 读写分离：配置路由规则
ProxySQL的路由规则非常灵活，可以基于用户、基于schema以及基于每个语句实现路由规则的定制。本案例作为一个入门配置，实现一个最简单的语句级路由规则，从而实现读写分离。
必须注意: 这只是实验，实际的路由规则绝不应该仅根据所谓的读、写操作进行分离，而是从各项指标中找出压力大、执行频繁的语句单独写规则、做缓存等等。和查询规则有关的表有两个:mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后才支持该快速路由表。本案例只介绍第一个表。插入两个规则，目的是将select语句分离到hostgroup_id=20的读组，但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，所以应该路由到hostgroup_id=10的写组.
[root@centos-03 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
:29: admin@127.0.0.1:[(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.00 sec)
:29: admin@127.0.0.1:[(none)]> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)
:30: admin@127.0.0.1:[(none)]> save mysql query rules to disk;
Query OK, 0 rows affected (0.01 sec)需要注意： select ... for update规则的rule_id必须要小于普通的select规则的rule_id，因为ProxySQL是根据rule_id的顺序进行规则匹配的。
再来测试下，读操作是否路由给了hostgroup_id=20的读组, 如下发现server_id为2的节点 (即slave从节点)在读组内
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id'
+-------------+
| @@server_id |
+-------------+
| 129 |
+-------------+读操作已经路由给读组，再看看写操作。这里以事务持久化进行测试。
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 128 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 129 |
+-------------+显然，一切都按照预期进行。最后，如果想查看路由的信息，可查询stats库中的stats_mysql_query_digest表。
以下是该表的一个输出格式示例(和本案例无关)
[root@centos-03 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin>'
...........
admin>use stats;
Database changed
admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+-------------------------------------------------------------------------------------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+-------------------------------------------------------------------------------------------------------------+
| 10 | 35417 | 1 | drop database proxy_test |
| 10 | 25534 | 1 | create table t(id int) |
| 10 | 25178 | 3 | select @@server_id |
| 10 | 21857 | 1 | create database proxy_test |
| 20 | 20384 | 5 | select @@server_id |
| 10 | 10014 | 1 | SELECT DATABASE() |
| 10 | 9694 | 2 | start transaction |
| 10 | 5960 | 2 | show databases |
| 20 | 4416 | 2 | SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC |
| 10 | 2982 | 1 | show tables from proxy_test |
| 10 | 2822 | 2 | show databases |
| 10 | 1857 | 2 | commit |
| 20 | 1215 | 1 | select @@server_id. |
| 10 | 1200 | 1 | show database |
| 10 | 0 | 1 | select USER() |
| 10 | 0 | 11 | select @@version_comment limit ? |
| 10 | 0 | 5 | select @@version_comment limit ? |
+----+----------+------------+-------------------------------------------------------------------------------------------------------------+
17 rows in set (0.01 sec)4.5 测试读写分离效果
由于读写操作都记录在proxysql的stats_mysql_query_digest表内。
为了测试读写分离的效果，可以先清空此表中之前的记录 (即之前在实现读写分配路由配置之前的记录)
下面这个命令是专门清空stats_mysql_query_digest表的 (使用"delete from stats_mysql_query_digest" 清空不掉!)
admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)
admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
Empty set (0.00 sec)
在mysql-proxy代理层节点，通过proxysql进行数据写入，并查看
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from grade.hi_tb;'
+------+----------+
| id | name |
+------+----------+
| 1 | bobu |
| 2 | Bertram |
| 21 | zhongguo |
| 22 | china |
| 23 | taiwan |
| 3 | English |
+------+----------+
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'delete from grade.hi_tb where id > 3;'
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'insert into grade.hi_tb values(5,"深圳"),(6,"北京"),(7,"上海");'
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'update grade.hi_tb set name="天津" where id=2;'
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from grade.hi_tb;'
+------+---------+
| id | name |
+------+---------+
| 1 | bobu |
| 2 | 天津 |
| 3 | English |
| 5 | 深圳 |
| 6 | 北京 |
| 7 | 上海 |
+------+---------+在mysql-master (centos-01)主数据库和mysql-slave (centos-02)从数据库上查看
[root@centos-01 ~]# mysql -uroot -p123456
......
mysql> select * from grade.hi_tb;
+------+---------+
| id | name |
+------+---------+
| 1 | bobu |
| 2 | 天津 |
| 3 | English |
| 5 | 深圳 |
| 6 | 北京 |
| 7 | 上海 |
+------+---------+
6 rows in set (0.00 sec)
[root@centos-02 ~]# mysql -uroot -p123456
.......
mysql> select * from grade.hi_tb;
+------+---------+
| id | name |
+------+---------+
| 1 | bobu |
| 2 | 天津 |
| 3 | English |
| 5 | 深圳 |
| 6 | 北京 |
| 7 | 上海 |
+------+---------+
6 rows in set (0.00 sec)
发现在客户端通过proxysql插件更新的数据，已经写到mysql-master主数据库上，并同步到mysql-slave从数据库上了！
最后在proxysql管理端查看读写分离
[root@centos-03 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
..............
:12: admin@127.0.0.1:[(none)]> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest;
+-----------+----------+-------------------------------------------------+------------+
| hostgroup | username | digest_text | count_star |
+-----------+----------+-------------------------------------------------+------------+
| 10 | root | update grade.hi_tb set name=? where id=? | 4 |
| 10 | root | insert into grade.hi_tb values(?,?) | 1 |
| 10 | root | insert into grade.hi_tb values(?,?),(?,?),(?,?) | 2 |
| 10 | root | insert into kevin.haha values(?,?),(?,?),(?,?) | 1 |
| 10 | root | insert into grade.hi_tb values(?,李四) | 1 |
| 10 | root | insert into grade.hi_tb values(?,English) | 1 |
| 20 | root | select * from grade.hi_tb | 9 |
| 10 | root | delete from grade.hi_tb where id > ? | 1 |
| 10 | root | insert into grade.hi_tb values(?,li) | 2 |
| 20 | root | select * from grade | 1 |
| 10 | root | select @@version_comment limit ? | 23 |
+-----------+----------+-------------------------------------------------+------------+
11 rows in set (0.01 sec)
从上述结果就可以看出proxysql实现的读写分离配置是成功的，读请求是转发到group20的读组内，写请求转发到group10的写组内!!
4.6 开启ProxySQL的Web统计功能
首先打开web功能
[root@centos-03 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
:20: admin@127.0.0.1:[(none)]> update global_variables set variable_value='true' where variable_name='admin-web_enabled';
Query OK, 1 row affected (0.00 sec)
:20: admin@127.0.0.1:[(none)]> LOAD ADMIN VARIABLES TO RUNTIME;
Query OK, 0 rows affected (0.07 sec)
:20: admin@127.0.0.1:[(none)]> SAVE ADMIN VARIABLES TO DISK;
Query OK, 35 rows affected (0.00 sec)然后查看端口和登录web界面的用户名和密码，用户名和密码与stat账户一致：
:20: admin@127.0.0.1:[(none)]> select * from global_variables where variable_name LIKE 'admin-web%' or variable_name LIKE 'admin-stats%';
+----------------------------------------+----------------+
| variable_name | variable_value |
+----------------------------------------+----------------+
| admin-stats_credentials | stats:stats | //账号密码
| admin-stats_mysql_connections | 60 |
| admin-stats_mysql_connection_pool | 60 |
| admin-stats_mysql_query_cache | 60 |
| admin-stats_mysql_query_digest_to_disk | 0 |
| admin-stats_system_cpu | 60 |
| admin-stats_system_memory | 60 |
| admin-web_enabled | true |
| admin-web_port | 6080 | //端口
+----------------------------------------+----------------+
9 rows in set (0.00 sec)查看web端口是否正常打开
[root@centos-03 ~]# lsof -i:6080
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
proxysql 3390 proxysql 44u IPv4 27649 0t0 TCP *:6080 (LISTEN)访问http://192.168.1.130:6080并使用stats:stats登录即可查看一些统计信息。
参考：文章
本文参考链接：https://bertram.blog.csdn.net/article/details/107936255
