ProxySQL 配置详解及读写分离(+GTID)等功能说明 (完整篇)
ProxySQL是灵活强大的MySQL代理层, 是一个能实实在在用在生产环境的MySQL中间件，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行 cache，支持动态加载配置、故障切换和一些 SQL的过滤功能。还有一些同类产品比如 DBproxy、MyCAT、OneProxy 等。但经过反复对比和测试之后，还是觉得ProxySQL是一款性能不谙，靠谱稳定的MySQL 中间件产品 ！
ProxySQL的亮点所在
- 几乎所有的配置均可在线更改（其配置数据基于SQLite存储），无需重启proxysql
- 基于正则和client_addr的强大和灵活的路由规则
- 详细的状态统计，统计结果和pt-query-digest对慢日志的分析结果类似，相当于有了统一的查看sql性能和sql语句统计的入口（Designed by a DBA for DBAs）
- 自动重连和重新执行机制(auto-reconnect and automatic re-execution of queries using it’s Connections Pool ): 若一个请求在链接或执行过程中意外中断，proxysql会根据其内部机制重新执行该操作
- query cache功能：比mysql自带QC更灵活，可在mysql_query_rules表中依据digest,match_pattern,client_addr等维度控制哪类语句可以缓存
- 支持连接池（connection pool）并且支持multiplexing,区别于atlas之流的连接池实现。
ProxySQL的特点
ProxySQL是一个高性能的MySQL中间件，拥有强大的规则引擎。具有以下特性：
- 连接池，而且是 multiplexing；
- 主机和用户的最大连接数限制；
- 自动下线后端DB；
- 延迟超过阀值
- ping 延迟超过阀值
- 网络不通或宕机
- 强大的规则路由引擎；
- 实现读写分离
- 查询重写
- sql流量镜像
- 支持prepared statement；
- 支持Query Cache；
- 支持负载均衡，与gelera结合自动failover；
- 将所有配置保存写入到SQLit表中。
- 支持动态加载配置，即一般可以在线修改配置，但有少部分参数还是需要重启来生效。
- 支持query cache。
- 支持对query的路由，可以针对某个语句进行分配去哪个实例执行。
- 不支持分表，可以分库，但是利用规则配置实现分表。
如上可知，ProxySQL集合了很多优秀特性于一身，那么它的缺点呢就是项目不够成熟，好在官方网站一直在及时更新，并且受到 Percona 官方的支持。
ProxySQL多层管理配置设计 (有三层配置)
- runtime：运行中使用的配置文件
- memory：提供用户动态修改配置文件
- disk：将修改的配置保存到磁盘SQLit表中（即：proxysql.db）
- config：一般不使用它（即：proxysql.cnf）
ProxySQL运行机制草图如下：
ProxySQL有一个完备的配置系统，配置ProxySQL是基于sql命令的方式完成的。ProxySQL支持配置修改之后的在线保存、应用，不需要重启即可生效。整个配置系统分三层设计。
整个配置系统分为三层，如下图所示:
ProxySQL配置系统分为三层的目的：
1) 自动更新;
2) 尽可能的不重启proxysql就可以修改配置;
3) 方便回滚错误配置;
简单说就是配置proxysql分为三个级别，RUNTIME是即时生效的，MEMORY是保存在内存中但并不立即生效的，DISK|CONFIG FILE是持久化或写在配置文件中的。
这三个级别的配置文件互不干扰，在某个层级修改了配置文件，想要加载或保存到另一个层级，需要额外的LOAD或SAVE操作："LOAD xx_config FROM xx_level | LOAD xx_config TO xx_level | SAVE xx_config TO xx_level | SAVE xx_config FROM xx_level"，达到加载配置或者持久化配置的目的。这三层中每层的功能与含义如下：
- RUNTIME层
代表的是ProxySQL当前生效的配置，包括 global_variables, mysql_servers, mysql_users, mysql_query_rules。无法直接修改这里的配置，必须要从下一层load进来。该层级的配置时在proxysql管理库(sqlite)的main库中以runtime_开头的表，这些表的数据库无法直接修改，只能从其他层级加载；该层代表的是ProxySQL当前生效的正在使用的配置，包括global_variables, mysql_servers, mysql_users, mysql_query_rules表。无法直接修改这里的配置，必须要从下一层load进来。也就是说RUNTIME这个顶级层，是proxysql运行过程中实际使用的那一份配置，这一份配置会直接影响到生产环境的，所以要将配置加载进RUNTIME层时需要三思而行。
- MEMORY层
是平时在mysql命令行修改的 main 里头配置，可以认为是SQLite数据库在内存的镜像。该层级的配置在main库中以mysql_开头的表以及global_variables表，这些表的数据可以直接修改；用户可以通过MySQL客户端连接到此接口（admin接口），然后可以在mysql命令行查询不同的表和数据库，并修改各种配置，可以认为是SQLite数据库在内存的镜像。也就是说MEMORY这个中间层，上面接着生产环境层RUNTIME，下面接着持久化层DISK和CONFIG FILE。MEMORY层是我们修改proxysql的唯一正常入口。一般来说在修改一个配置时，首先修改Memory层，确认无误后再接入RUNTIME层，最后持久化到DISK和CONFIG FILE层。也就是说memeory层里面的配置随便改，不影响生产，也不影响磁盘中保存的数据。通过admin接口可以修改mysql_servers、mysql_users、mysql_query_rules、global_variables等表的数据。
- DISK|CONFIG FILR层
持久存储的那份配置，一般在$(DATADIR)/proxysql.db，在重启的时候会从硬盘里加载。 /etc/proxysql.cnf文件只在第一次初始化的时候用到，完了后，如果要修改监听端口，还是需要在管理命令行里修改，再 save 到硬盘。该层级的配置在磁盘上的sqlite库或配置文件里。DISK/CONFIG FILE层表示持久存储的那份配置，持久层对应的磁盘文件是$(DATADIR)/proxysql.db，在重启ProxySQL的时候，会从proxysql.db文件中加载信息。而 /etc/proxysql.cnf文件只在第一次初始化的时候使用，之后如果要修改配置，就需要在管理端口的SQL命令行里进行修改，然后再save到硬盘。 也就是说DISK和CONFIG FILE这一层是持久化层，我们做的任何配置更改，如果不持久化下来，重启后，配置都将丢失。
需要注意
1) ProxySQL每一个配置项在三层中都存在，但是这三层是互相独立的，也就是说proxysql可以同时拥有三份配置，每层都是独立的，可能三份配置都不一样，也可能三份都一样。
2) RUNTIME层代表 ProxySQL 当前生效的正在使用的配置，无法直接修改这里的配置，必须要从下一层 "load" 进来。
3) MEMORY这一层上面连接 RUNTIME 层，下面连接持久化层。在这层可以正常操作 ProxySQL 配置，随便修改，不会影响生产环境。修改一个配置一般都是先在 MEMORY 层完成，然后确认正常之后再加载到 RUNTIME 和持久化到磁盘上。
4) DISK 和 CONFIG FILE层持久化配置信息，重启后内存中的配置信息会丢失，所以需要将配置信息保留在磁盘中。重启时，可以从磁盘快速加载回来。
ProxySQL配置文件的修改流程一般是：
- 启动时：先修改必要的CONFIG FILE配置，比如管理端口，然后启动；
- 其他配置：修改MEMORY中的表，然后加载到RUNTIME并持久化。
ProxySQL具有一个复杂但易于使用的配置系统，可以满足以下需求：
- 允许轻松动态更新配置（这是为了让ProxySQL用户可以在需要零宕机时间配置的大型基础架构中使用它）。与MySQL兼容的管理界面可用于此目的。
- 允许尽可能多的配置项目动态修改，而不需要重新启动ProxySQL进程
- 可以毫不费力地回滚无效配置
- 这是通过多级配置系统实现的，其中设置从运行时移到内存，并根据需要持久保存到磁盘。
一般，修改的配置都是在memory层。可以load到runtime，使配置在不用重启proxysql的情况下也可以生效，也可以save到disk，将对配置的修改持久化！
需要修改配置时，直接操作的是 MEMORAY，以下命令可用于加载或保存 users (mysql_users): (序号对应上图“运行机制”草图)
[1]: LOAD MYSQL USERS TO RUNTIME / LOAD MYSQL USERS FROM MEMORY #常用。将修改后的配置(在memory层)用到实际生产 [2]: SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME #将生产配置拉一份到memory中 [3]: LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK #将磁盘中持久化的配置拉一份到memory中来 [4]: SAVE MYSQL USERS TO DISK / SAVE MYSQL USERS FROM MEMORY #常用。将memoery中的配置保存到磁盘中去 [5]: LOAD MYSQL USERS FROM CONFIG #将配置文件中的配置加载到memeory中
个人还是比较习惯用 TO，记住往上层是 LOAD，往下层是 SAVE。以下命令加载或保存servers (mysql_servers):
[1]: LOAD MYSQL SERVERS TO RUNTIME #常用，让修改的配置生效 [2]: SAVE MYSQL SERVERS TO MEMORY [3]: LOAD MYSQL SERVERS TO MEMORY [4]: SAVE MYSQL SERVERS TO DISK #常用，将修改的配置持久化 [5]: LOAD MYSQL SERVERS FROM CONFIG
后面的使用方法也基本相同，一并列出。以下命令加载或保存query rules (mysql_query_rules):
[1]: load mysql query rules to run #常用 [2]: save mysql query rules to mem [3]: load mysql query rules to mem [4]: save mysql query rules to disk #常用 [5]: load mysql query rules from config
以下命令加载或保存 mysql variables (global_variables):
[1]: load mysql variables to runtime [2]: save mysql variables to memory [3]: load mysql variables to memory [4]: save mysql variables to disk [5]: load mysql variables from config
以下命令加载或保存admin variables (select * from global_variables where variable_name like 'admin-%'):
[1]: load admin variables to runtime [2]: save admin variables to memory [3]: load admin variables to memory [4]: save admin variables to disk [5]: load admin variables from config
ProxySQL启动过程总结:
当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无法启动。
一、 ProxySQL 安装 (两种方式)
1) 采用yum方式安装
[root@mysql-proxy ~]# vim /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
执行安装
[root@mysql-proxy ~]# yum clean all
[root@mysql-proxy ~]# yum makecache
[root@mysql-proxy ~]# yum -y install proxysql
[root@mysql-proxy ~]# proxysql --version
ProxySQL version 1.4.13-15-g69d4207, codename Truls
启动ProxySQL
[root@mysql-proxy ~]# chkconfig proxysql on
[root@mysql-proxy ~]# systemctl start proxysql
[root@mysql-proxy ~]# systemctl status proxysql
启动后会监听两个端口，
默认为6032和6033。6032端口是ProxySQL的管理端口，6033是ProxySQL对外提供服务的端口 (即连接到转发后端的真正数据库的转发端口)。
[root@mysql-proxy ~]# netstat -tunlp
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 23940/proxysql
tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 23940/proxysql
2）采用rpm包方式安装
proxysql的rpm包下载地址: https://pan.baidu.com/s/1S1_b5DKVCpZSOUNmtCXrrg
提取密码: 5t1c
[root@mysql-proxy ~]# wget https://github.com/sysown/proxysql/releases/download/v1.4.8/proxysql-1.4.8-1-centos7.x86_64.rpm
[root@mysql-proxy ~]# rpm -ivh proxysql-1.4.8-1-centos7.x86_64.rpm --force
[root@mysql-proxy ~]# /etc/init.d/proxysql start
Starting ProxySQL: DONE!
[root@mysql-proxy ~]# ss -lntup|grep proxy
tcp LISTEN 0 128 *:6032 *:* users:(("proxysql",pid=2943,fd=24))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=22))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=21))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=20))
tcp LISTEN 0 128 *:6033 *:* users:(("proxysql",pid=2943,fd=19))
如上可以看出转发端口6033是启动了四个线程
==============================================================
以上两种方式采用任何一种都可以顺利安装proxysql插件。
另外，记得在proxysql服务器上安装mysql客户端，用于在本机连接到ProxySQL的管理接口
[root@mysql-proxy ~]# vim /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
安装mysql-clinet客户端
[root@mysql-proxy ~]# yum install -y MariaDB-client
--------------------------------------------------------------------------------------------------------------------------------------------------------
如果遇到报错：
Error: MariaDB-compat conflicts with 1:mariadb-libs-5.5.60-1.el7_5.x86_64
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest
解决办法：
[root@mysql-proxy ~]# rpm -qa|grep mariadb*
mariadb-libs-5.5.56-2.el7.x86_64
[root@mysql-proxy ~]# rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
[root@mysql-proxy ~]# yum install -y MariaDB-client
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@mysql-proxy ~]# egrep -v "^#|^$" /etc/proxysql.cnf
datadir="/var/lib/proxysql" #数据目录
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.5.30" #指定后端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@mysql-proxy ~]# ll /var/lib/proxysql/
total 1014052
-rw------- 1 root root 122880 Jan 25 14:33 proxysql.db
-rw------- 1 root root 1023288179 Jan 28 12:30 proxysql.log
-rw-r--r-- 1 root root 6 Jan 25 14:20 proxysql.pid
-rw------- 1 root root 1736704 Jan 28 12:29 proxysql_stats.db
查看main库（默认登陆后即在此库）的global_variables表信息
MySQL [(none)]> 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.000 sec)
MySQL [(none)]> use main;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MySQL [main]> show tables;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| 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_scheduler |
| scheduler |
+--------------------------------------------+
20 rows in set (0.000 sec)
MySQL [main]> select * from global_variables;
+-----------------------------------------------------+---------------------------+
| variable_name | variable_value |
+-----------------------------------------------------+---------------------------+
| mysql-shun_on_failures | 5 |
| mysql-shun_recovery_time_sec | 10 |
| mysql-query_retries_on_failure | 1 |
| 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-default_charset | utf8 |
| mysql-free_connections_pct | 10 |
| mysql-session_idle_ms | 1000 |
| 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_replication_lag_use_percona_heartbeat | |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-max_allowed_packet | 4194304 |
| mysql-throttle_connections_per_sec_to_hostgroup | 1000000 |
| mysql-max_transaction_time | 14400000 |
| mysql-multiplexing | 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-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 | 2147483647 |
| 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-long_query_time | 1000 |
| mysql-query_cache_size_MB | 256 |
| mysql-poll_timeout_on_failure | 100 |
| mysql-server_capabilities | 45578 |
| mysql-session_idle_show_processlist | true |
| mysql-query_digests | true |
| mysql-query_digests_lowercase | 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-default_sql_mode | |
| mysql-default_time_zone | SYSTEM |
| mysql-connpoll_reset_queue_length | 50 |
| mysql-stats_time_backend_query | false |
| mysql-stats_time_query_processor | false |
| 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 | proxysql |
| mysql-monitor_password | proxysql |
| 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 |
| admin-stats_credentials | stats:stats |
| admin-stats_mysql_connections | 60 |
| admin-stats_mysql_connection_pool | 60 |
| admin-stats_mysql_query_cache | 60 |
| 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-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-web_enabled | false |
| admin-web_port | 6080 |
| admin-admin_credentials | admin:admin|
| admin-mysql_ifaces | 0.0.0.0:6032 |
| admin-version | 1.4.8-32-g669c149 |
+-----------------------------------------------------+---------------------------+
125 rows in set (0.003 sec)
登陆成功后，可通过对main库（默认登陆后即在此库）的global_variables表中的"admin-admin_credentials" 和 "admin-mysql_ifaces"
两个变量进行更改来修改登录认证! 比如说修改密码或定义一个非admin的用户用于远程登录(下面会说到)。
proxysql的6032端口是管理入口，账号密码是admin(可以动态修改),允许客户端连接；6033端口就是客户端入口，账号密码通过管理接口去设置。在proxysql本机使用mysql客户端连接到ProxySQL的管理接口(admin interface), 该接口的默认管理员用户和密码都是admin。
mysql_ifaces
也就是说proxysql有一个admin接口专门来做配置，相当于一个mysql shell可以通过sql来让配置实时生效。
mysql_ifaces配置了允许连接proxysql的ip和port
[root@mysql-proxy ~]# vim /etc/proxysql.cnf
........
# 将admin_variables中的mysql_ifaces修改成允许远程访问
# mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock"
mysql_ifaces="0.0.0.0:6032"
如果ip配置为0.0.0.0表示不限制ip，但是出于安全考虑，admin用户无论怎么设置都只能在本机登录!!!
admin_credentials
这个key保存所有可以操作proxysql的用户名和密码，格式为：user:pass;user1:pass1，这里可以修改密码或定义一个非admin的用户用于远程登录。 前提是保证想要管理proxysql的机器安装有mysql client客户端！
先在proxysql本机登录 (因为初始账号密码是admin:admin，只能在本机登录), 这里的proxysql本机地址是172.16.60.214 修改远程连接proxysql管理端口的账号和密码radmin:radmin. [root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [(none)]> update global_variables set variable_value = 'admin:admin;radmin:radmin' where variable_name = 'admin-admin_credentials'; Query OK, 1 row affected (0.002 sec) MySQL [(none)]> LOAD ADMIN VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.000 sec) MySQL [(none)]> SAVE ADMIN VARIABLES TO DISK; Query OK, 31 rows affected (0.077 sec) 这样就可以使用下面的命令在其他机器上使用radmin用户登录（其他机器上需要有mysql client） [root@MGR-node3 ~]# mysql -uradmin -pradmin -h172.16.60.214 -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 35 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.00 sec)
ProxySQL的库、表说明 （默认管理端口是6032，客户端服务端口是6033。默认的用户名密码都是 admin）
通过管理端口6032去连接的 (注意, 下面连接命令中后面的--prompt 'admin'字段可以不加，也是可以登录进去的) [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 或者 [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin> ' Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 33 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 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.000 sec) ProxySQL提供了几个库，每个库都有各自的意义； - main 内存配置数据库，表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容， 不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载。 - disk 是持久化到硬盘的配置，sqlite数据文件。 - stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。 - monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查。 1) main 库 （disk库的表字段和main一样） admin> show tables from main; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_collations | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | runtime_checksums_values | | runtime_global_variables | | 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_scheduler | | scheduler | +--------------------------------------------+ 20 rows in set (0.001 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库 MySQL [(none)]> 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_global | | 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 | +--------------------------------------+ 15 rows in set (0.001 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库 MySQL [(none)]> show tables from monitor; +------------------------------------+ | tables | +------------------------------------+ | mysql_server_connect_log | | mysql_server_group_replication_log | | mysql_server_ping_log | | mysql_server_read_only_log | | mysql_server_replication_lag_log | +------------------------------------+ 5 rows in set (0.000 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才能将配置持久化保存到磁盘。
global_variables 有80多个变量可以设置，其中就包括监听的端口、管理账号、禁用monitor等
(admin@127.0.0.1:6032) [(none)]> show tables;
+--------------------------------------------+
| tables |
+--------------------------------------------+
| global_variables |
| mysql_collations |
| mysql_group_replication_hostgroups |
| mysql_query_rules |
| mysql_query_rules_fast_routing |
| mysql_replication_hostgroups |
| mysql_servers |
| mysql_users |
| proxysql_servers |
| runtime_checksums_values |
| runtime_global_variables |
| 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_scheduler |
| scheduler |
+--------------------------------------------+
20 rows in set (0.001 sec)
(admin@127.0.0.1:6032) [(none)]> 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_global |
| 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 |
+--------------------------------------+
15 rows in set (0.000 sec)
(admin@127.0.0.1:6032) [(none)]> 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 NOT NULL DEFAULT 3306,
status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE',
weight INT CHECK (weight >= 0) NOT NULL DEFAULT 1,
compression INT CHECK (compression >=0 AND compression <= 102400) 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.000 sec)
ERROR: No query specified
(admin@127.0.0.1:6032) [(none)]> select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 172.16.60.211 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 172.16.60.212 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 20 | 172.16.60.213 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.000 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脚本，完成一些自动化的工作。
表 mysql_users
MySQL [(none)]> show create table mysql_users\G;
*************************** 1. row ***************************
table: mysql_users
Create Table: CREATE TABLE mysql_users (
username VARCHAR NOT NULL,
password VARCHAR,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
default_hostgroup INT NOT NULL DEFAULT 0,
default_schema VARCHAR,
schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
PRIMARY KEY (username, backend),
UNIQUE (username, frontend))
1 row in set (0.000 sec)
ERROR: No query specified
MySQL [(none)]> select * from mysql_users;
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxysql | proxysql | 1 | 0 | 2 | 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.000 sec)
MySQL [(none)]> select username,password,transaction_persistent,active,backend,frontend,max_connections from runtime_mysql_users;
+-----------+-------------------------------------------+------------------------+--------+---------+----------+-----------------+
| username | password | transaction_persistent | active | backend | frontend | max_connections |
+-----------+-------------------------------------------+------------------------+--------+---------+----------+-----------------+
| proxysql | *BF27B4C7AAD278126E228AA8427806E870F64F39 | 1 | 1 | 0 | 1 | 10000 |
| root | *59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0 | 1 | 1 | 0 | 1 | 10000 |
| sqlsender | *50572A5FABC7DA9CEE5EB5977EDDE59E38967422 | 1 | 1 | 0 | 1 | 10000 |
| proxysql | *BF27B4C7AAD278126E228AA8427806E870F64F39 | 1 | 1 | 1 | 0 | 10000 |
| root | *59C70DA2F3E3A5BDF46B68F5C8B8F25762BCCEF0 | 1 | 1 | 1 | 0 | 10000 |
| sqlsender | *50572A5FABC7DA9CEE5EB5977EDDE59E38967422 | 1 | 1 | 1 | 0 | 10000 |
+-----------+-------------------------------------------+------------------------+--------+---------+----------+-----------------+
6 rows in set (0.001 sec)
- username, password: 连接后端db的用户密码。
这个密码你可以插入明文，也可以插入hash加密后的密文，proxysql会检查你插入的时候密码是否以 * 开头来判断，而且密文要在其它地方使用 PASSWORD()生成。但到 runtime_mysql_users 里，都统一变成了密文，所以可以明文插入，再 SAVE MYSQL USERS TO MEM，此时看到的也是HASH密文。
- active: 是否生效该用户。
- default_hostgroup: 这个用户的请求没有匹配到规则时，默认发到这个 hostgroup，默认0
- default_schema: 这个用户连接时没有指定 database name 时，默认使用的schema
注意表面上看默认为NULL，但实际上受到变量 mysql-default_schema 的影响，默认为 information_schema。关于这个参考我所提的 issue #988
- transaction_persistent: 如果设置为1，连接上ProxySQL的会话后，如果在一个hostgroup上开启了事务，那么后续的sql都继续维持在这个hostgroup上，不伦是否会匹配上其它路由规则，直到事务结束。
虽然默认是0，但我建议还是设成1，虽然一般来说由于前段应用的空值，为0出问题的情况几乎很小。作者也在考虑默认设成 1，refer this issue #793
- frontend, backend: 目前版本这两个都需要使用默认的1，将来有可能会把 Client -> ProxySQL (frontend) 与 ProxySQL -> BackendDB (backend)的认证分开。从 runtime_mysql_users 表内容看到，记录数比 mysql_users 多了一倍，就是把前端认证与后端认证独立出来的结果。
- fast_forward: 忽略查询重写/缓存层，直接把这个用户的请求透传到后端DB。相当于只用它的连接池功能，一般不用，路由规则 .* 就行了。
表 mysql_replication_hostgroups
MySQL [(none)]> show create table mysql_replication_hostgroups\G;
*************************** 1. row ***************************
table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>0),
comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup))
1 row in set (0.001 sec)
ERROR: No query specified
MySQL [(none)]> select * from mysql_replication_hostgroups;
+------------------+------------------+---------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+---------+
| 10 | 20 | 1 |
+------------------+------------------+---------+
1 row in set (0.000 sec)
定义 hostgroup 的主从关系。ProxySQL monitor 模块会监控 HG 后端所有servers 的 read_only 变量，如果发现从库的 read_only 变为0、主库变为1，则认为角色互换了，自动改写 mysql_servers 表里面 hostgroup 关系，达到自动 Failover 效果。
表 mysql_query_rules
mysql_query_rules 是ProxySQL非常核心一个表，定义查询路由规则
MySQL [(none)]> show create table mysql_query_rules\G;
*************************** 1. row ***************************
table: mysql_query_rules
Create Table: CREATE TABLE mysql_query_rules (
rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0,
username VARCHAR,
schemaname VARCHAR,
flagIN INT NOT NULL DEFAULT 0,
client_addr VARCHAR,
proxy_addr VARCHAR,
proxy_port INT,
digest VARCHAR,
match_digest VARCHAR,
match_pattern VARCHAR,
negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
re_modifiers VARCHAR DEFAULT 'CASELESS',
flagOUT INT,
replace_pattern VARCHAR,
destination_hostgroup INT DEFAULT NULL,
cache_ttl INT CHECK(cache_ttl > 0),
reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL,
timeout INT UNSIGNED,
retries INT CHECK (retries>=0 AND retries <=1000),
delay INT UNSIGNED,
next_query_flagIN INT UNSIGNED,
mirror_flagOUT INT UNSIGNED,
mirror_hostgroup INT UNSIGNED,
error_msg VARCHAR,
OK_msg VARCHAR,
sticky_conn INT CHECK (sticky_conn IN (0,1)),
multiplex INT CHECK (multiplex IN (0,1,2)),
log INT CHECK (log IN (0,1)),
apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0,
comment VARCHAR)
1 row in set (0.001 sec)
ERROR: No query specified
MySQL [(none)]> select * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | NULL | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT | NULL | 0 | CASELESS | NULL | NULL | 20 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
2 rows in set (0.000 sec)
- rule_id: 表主键，自增。规则处理是以 rule_id 的顺序进行。
- active: 只有 active=1 的规则才会参与匹配。
- username: 如果非 NULL，只有连接用户是 username 的值才会匹配。
- schemaname: 如果非 NULL，只有查询连接使用的db是 schemaname 的值才会匹配。
注意如果是 NULL，不代表连接没有使用schema，而是不伦任何schema都进一步匹配。
- flagIN, flagOUT, apply: 用来定义路由链 chains of rules。
- 首先会检查 flagIN=0 的规则，以rule_id的顺序；如果都没匹配上，则走这个用户的 default_hostgroup。
- 当匹配一条规则后，会检查 flagOUT。
- 如果不为NULL，并且 flagIN != flagOUT ，则进入以flagIN为上一个flagOUT值的新规则链。
- 如果不为NULL，并且 flagIN = flagOUT，则应用这条规则。
- 如果为NULL，或者 apply=1，则结束，应用这条规则。
- 如果最终没有匹配到，则找到这个用户的 default_hostgroup。
- client_addr: 匹配客户端来源IP
- proxy_addr, proxy_port: 匹配本地proxysql的IP、端口。我目前没有想到它的应用场景，可能是把proxysql监听在多个接口上，分发到不同的业务？
- digest: 精确的匹配一类查询。
- match_digest: 正则匹配一类查询。query digest 是指对查询去掉具体值后进行“模糊化”后的查询，类似 pt-fingerprint / pt-query-digest 的效果。
- match_pattern: 正则匹配查询。
以上都是匹配查询的规则，1.3.5版本使用的正则引擎只有 RE2 ，1.4版本可以通过变量 mysql-query_processor_regex 设置 RE2 或者 PCRE，且1.4开始默认是PCRE。
推荐用 match_digest 。关于每条查询都会计算digest对性能的影响，计算query digest确实会有性能损失，但是这却是proxysql里面非常重要的特性，主要是两点：
- proxysql无法知道连接复用(multipexing)是否必须被自动禁用，比如连接里面有variables/tmp tables/lock table等特殊命令，是不能复用的。
- 完整的查询去匹配正则的效率，一般没有参数化后的查询匹配效率高，因为有很长的字符串内容需要处理。再者，SELECT * FROM randomtable WHERE comment LIKE ‘%INTO sbtest1 % FROM sbtest2 %’字符串里有类似这样的语句，很难排除误匹配。
- negate_match_pattern: 反向匹配，相当于对 match_digest/match_pattern 的匹配取反。
- re_modifiers: 修改正则匹配的参数，比如默认的：忽略大小写CASELESS、禁用GLOBAL.
上面都是匹配规则，下面是匹配后的行为
- replace_pattern: 查询重写，默认为空，不rewrite。
- rewrite规则要遵守 RE2::Replace 。
destination_hostgroup: 路由查询到这个 hostgroup。当然如果用户显式 start transaction 且 transaction_persistent=1，那么即使匹配到了，也依然按照事务里第一条sql的路由规则去走。
- cache_ttl: 查询结果缓存的毫秒数。
proxysql这个 Query Cache 与 MySQL 自带的query cache不是同一个。proxysql query cache也不会关心后端数据是否被修改，它所做的就是针对某些特定种类的查询结果进行缓存，比如一些历史数据的count结果。一般不设。
- timeout: 这一类查询执行的最大时间（毫秒），超时则自动kill。
这是对后端DB的保护机制，相当于阿里云RDS loose_max_statement_time 变量的功能，但是注意不同的是，阿里云这个变量的时间时不包括DML操作出现InnoDB行锁等待的时间，而ProxySQL的这个 timeout 是计算从发送sql到等待响应的时间。默认mysql-default_query_timeout给的是 10h .
- retries: 语句在执行时失败时，重试次数。默认由 mysql-query_retries_on_failure变量指定，为1 。
个人建议把它设成0，即不重试。因为执行失败，对select而言很少见，主要是dml，但自己重试对数据不放心。
- delay: 查询延迟执行，这是ProxySQL提供的限流机制，会让其它的查询优先执行。
默认值 mysql-default_query_delay，为0。我们一般不用，其实还是要配合应用端使用，比如这边延迟执行，但上层等待你返回，那前端不就堵住了，没准出现雪崩效应。
- mirror_flagOUT,mirror_hostgroup
这两个高级了，目前这部分文档不全，功能是SQL镜像。顾名思义，就是把匹配到的SQL除了发送到 destination_hostgroup，同时镜像一份到这里的hostgroup，比如我们的测试库。比如这种场景，数据库要从5.6升级到5.7，要验证现有查询语句对5.7的适用情况，就可以把生产流量镜像到5.7新库上验证。
- error_msg: 默认为NULL，如果指定了则这个查询直接被 block 掉，马上返回这个错误信息。
这个功能也很实用，比如线上突然冒出一个 “坏查询”，应用端不方便马上发版解决，我们就可以在这配置一个规则，把查询屏蔽掉，想正常的mysql报错那样抛异常。下一篇文章有演示。
- multiplex: 连接是否复用。
- log: 是否记录查询日志。可以看到log是否记录的对象是根据规则。
要开启日志记录，需要设置变量 mysql-eventslog_filename 来指定文件名，然后这个 log 标记为1。但是目前proxysql记录的日志是二进制格式，需要特定的工具才能读取： eventslog_reader_sample 。这个工具在源码目录 tools下面。
proxysql对后端server健康检查
MySQL [monitor]> show variables like "mysql-monitor%"; +-----------------------------------------------------+------------+ | Variable_name | Value | +-----------------------------------------------------+------------+ | 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_replication_lag_use_percona_heartbeat | | | mysql-monitor_query_interval | 60000 | | mysql-monitor_query_timeout | 100 | | mysql-monitor_slave_lag_when_null | 60 | | mysql-monitor_wait_timeout | true | | mysql-monitor_writer_is_also_reader | true | | mysql-monitor_username | monitor | | mysql-monitor_password | P@ssword1! | | 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 | +-----------------------------------------------------+------------+ 22 rows in set (0.001 sec)
ProxySQL配置后端DB server
两种方式，区别在于
1) 一种是在往mysql_servers表中添加server时就为其划分好hostgroup_id（例如0表示写组，1表示读组）
2) 另一种往mysql_servers表中添加server时不区分hostgroup_id（例如全部设为0），然后通过mysql_replication_hostgroups表中的值，
根据proxysql检测到的各server的read_only变量值来自动为后端server设置hostgroup_id
这里强烈推荐用第一种方式
因为第一种是完全由我们控制的;而第二种假如我们误将读server的read_only属性设置为0，则proxysql会将其重新分配到写组，这绝对是不期望的。
ProxySQL下添加与修改配置
1) 添加配置
需要添加配置时，直接操作的是MEMORAY，例如：添加一个程序用户，在mysql_users表中执行一个插入操作：
MySQL [(none)]> insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('myadmin','mypass',1,0,1);
这样就完成了一个用户的添加。要让这个insert生效，还需要执行如下操作：
MySQL [(none)]>load mysql users to runtime;
表示将修改后的配置(MEMORY层)用到实际生产环境（RUNTIME层）
如果想保存这个设置永久生效，还需要执行如下操作：
MySQL [(none)]>save mysql users to disk;
表示将memoery中的配置保存到磁盘中去。
除了上面两个操作，还可以执行如下操作：
MySQL [(none)]>load mysql users to memory;
表示将磁盘中持久化的配置拉一份到memory中来。
MySQL [(none)]>load mysql users from config;
表示将配置文件中的配置加载到memeory中。
2) 持久化配置
以上SQL命令是对mysql_users进行的操作，同理，还可以对mysql_servers表、mysql_query_rules表、global_variables表等执行类似的操作。
如对mysql_servers表插入完成数据后，要执行保存和加载操作，可执行如下SQL命令：
MySQL [(none)]> load mysql servers to runtime;
MySQL [(none)]> save mysql servers to disk;
对mysql_query_rules表插入完成数据后，要执行保存和加载操作，可执行如下SQL命令：
MySQL [(none)]> load mysql query rules to runtime;
MySQL [(none)]> save mysql query rules to disk;
对global_variables表插入完成数据后，要执行保存和加载操作，可执行如下SQL命令：
以下命令加载或保存mysql variables（global_variables）:
MySQL [(none)]>load mysql variables to runtime;
MySQL [(none)]>save mysql variables to disk;
以下命令加载或保存admin variables（select * from global_variables where variable_name like 'admin-%'）:
MySQL [(none)]> load admin variables to runtime;
MySQL [(none)]>save admin variables to disk;
二、ProxySQL功能验证 （针对GTID模式的主从同步，另两个从库都要设置read_only=on）
接下来通过实战操作来全面了解一下 ProxySQL 的特性和使用场景。
1. 实验环境
172.16.60.211 mysql-master 安装Mysql5.7 172.16.60.212 mysql-slave1 安装Mysql5.7 172.16.60.213 mysql-slave2 安装Mysql5.7 172.16.60.214 mysql-proxy 安装ProxySQL，Mysql-client 系统都是CentOS7.5，MySQL版本是5.7，准备一主两从架构(基于GTID的同步,两个从库都要开启read_only=on)来配合ProxySQL。 [root@mysql-master ~]# cat /etc/redhat-release CentOS Linux release 7.5.1804 (Core) 1) 三个节点各自设置主机名 [root@mysql-master ~]# hostnamectl --static set-hostname mysql-master [root@mysql-master ~]# hostname mysql-master [root@mysql-slave1 ~]# hostnamectl --static set-hostname mysql-slave1 [root@mysql-slave1 ~]# hostname mysql-slave [root@mysql-slave2 ~]# hostnamectl --static set-hostname mysql-slave2 [root@mysql-slave2 ~]# hostname mysql-slave [root@mysql-proxy ~]# hostnamectl --static set-hostname mysql-proxy [root@mysql-proxy ~]# hostname mysql-proxy 2) 所有节点关闭selinux和iptables防火墙 [root@mysql-master ~]# setenforce 0 [root@mysql-master ~]# cat /etc/sysconfig/selinux |grep "SELINUX=disabled" SELINUX=disabled [root@mysql-master ~]# iptables -F [root@mysql-master ~]# systemctl disable firewalld [root@mysql-master ~]# systemctl stop firewalld [root@mysql-master ~]# firewall-cmd --state not running
2. 安装Mysql 5.7 (在三个mysql节点上安装)
在三个mysql节点机上使用yum方式安装Mysql5.7，参考：https://www.cnblogs.com/kevingrace/p/8340690.html
安装MySQL yum资源库
[root@mysql-master ~]# yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm
安装MySQL 5.7
[root@mysql-master ~]# yum install -y mysql-community-server
启动MySQL服务器和MySQL的自动启动
[root@mysql-master ~]# systemctl start mysqld.service
[root@mysql-master ~]# systemctl enable mysqld.service
设置登录密码
由于MySQL从5.7开始不允许首次安装后使用空密码进行登录！为了加强安全性，系统会随机生成一个密码以供管理员首次登录使用，
这个密码记录在/var/log/mysqld.log文件中，使用下面的命令可以查看此密码：
[root@mysql-master ~]# cat /var/log/mysqld.log|grep 'A temporary password'
2019-01-11T05:53:17.824073Z 1 [Note] A temporary password is generated for root@localhost: TaN.k:*Qw2xs
使用上面查看的密码TaN.k:*Qw2xs 登录mysql，并重置密码为123456
[root@mysql-master ~]# mysql -p #输入默认的密码：TaN.k:*Qw2xs
.............
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set password=password("123456");
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看mysql版本
[root@mysql-master ~]# mysql -p123456
........
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.24 |
+-----------+
1 row in set (0.00 sec)
=====================================================================
温馨提示
mysql5.7通过上面默认安装后，执行语句可能会报错：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
这个报错与Mysql 密码安全策略validate_password_policy的值有关，validate_password_policy可以取0、1、2三个值：
解决办法：
set global validate_password_policy=0;
set global validate_password_length=1;
3. 配置Mysql基于GTID的主从同步 (在mysql-master 和 mysql-slave1、mysql-slave2节点上)
1) 主数据库mysql-master (172.16.60.211)的配置操作
[root@mysql-master ~]# >/etc/my.cnf
[root@mysql-master ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = master-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
配置完成之后，别忘了重启Mysql
[root@mysql-master ~]# systemctl restart mysqld
登录mysql，查看一下master状态， 发现多了一项"Executed_Gtid_Set "
[root@mysql-master ~]# mysql -p123456
.........
mysql> show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+------------------------------------------+
| master-bin.000002 | 550 | | | fc39b161-22ca-11e9-a638-005056ac6820:1-2 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | fc39b161-22ca-11e9-a638-005056ac6820 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | fc39b161-22ca-11e9-a638-005056ac6820:1-2 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------+
8 rows in set (0.00 sec)
主库执行从库复制授权
mysql> grant replication slave,replication client on *.* to slave@'172.16.60.212' identified by "slave@123";
Query OK, 0 rows affected, 1 warning (0.09 sec)
mysql> grant replication slave,replication client on *.* to slave@'172.16.60.213' identified by "slave@123";
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
mysql> show grants for slave@'172.16.60.212';
+-------------------------------------------------------------------------------+
| Grants for slave@172.16.60.212 |
+-------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.60.212' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show grants for slave@'172.16.60.213';
+-------------------------------------------------------------------------------+
| Grants for slave@172.16.60.213 |
+-------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.60.213' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
在主数据库机器上创建一个测试库kevin（为了测试效果）
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.02 sec)
mysql> use kevin;
Database changed
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.17 sec)
mysql> insert into kevin.haha values(1,"congcong"),(2,"huihui"),(3,"grace");
Query OK, 3 rows affected (0.16 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from kevin.haha;
+----+----------+
| id | name |
+----+----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
+----+----------+
3 rows in set (0.00 sec)
2) 从数据库mysql-slave1 (172.16.60.212)的配置操作
与主服务器配置大概一致，除了server_id不一致外，从服务器还可以在配置文件里面添加："read_only＝on" ,
使从服务器只能进行读取操作，此参数对超级用户无效，并且不会影响从服务器的复制；
[root@mysql-slave1 ~]# >/etc/my.cnf
[root@mysql-slave1 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = master-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
read_only = on
配置完成之后，别忘了重启Mysql
[root@mysql-slave1 ~]# systemctl restart mysqld
接着登录mysql，做主从同步
[root@mysql-slave1 ~]# mysql -p123456
........
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
在从数据库里，使用change master 配置主从复制
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.24 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.60.211
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 2069
Relay_Log_File: mysql-slave1-relay-bin.000002
Relay_Log_Pos: 2284
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
............
............
Retrieved_Gtid_Set: fc39b161-22ca-11e9-a638-005056ac6820:1-8
Executed_Gtid_Set: 2afbc2f5-22cb-11e9-b9c0-00505688047c:1-2,
fc39b161-22ca-11e9-a638-005056ac6820:1-8
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
查看从库的gtid
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 2afbc2f5-22cb-11e9-b9c0-00505688047c:1-2,
fc39b161-22ca-11e9-a638-005056ac6820:1-8 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 2afbc2f5-22cb-11e9-b9c0-00505688047c:1-2 |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
接着查看从数据库的数据，发现kevin库已经同步过来了!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kevin |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from kevin.haha;
+----+----------+
| id | name |
+----+----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
+----+----------+
3 rows in set (0.00 sec)
3) 从数据库mysql-slave2 (172.16.60.213)的配置操作
[root@mysql-slave2 ~]# >/etc/my.cnf
[root@mysql-slave2 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 3
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = master-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
read_only = on
重启mysqld
[root@mysql-slave2 ~]# systemctl restart mysqld
登录mysql，做主从复制
[root@mysql-slave2 ~]# mysql -p123456
.........
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.17 sec)
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.60.211
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 2069
Relay_Log_File: mysql-slave2-relay-bin.000002
Relay_Log_Pos: 2284
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
..........
..........
Retrieved_Gtid_Set: fc39b161-22ca-11e9-a638-005056ac6820:1-8
Executed_Gtid_Set: 26e410b4-22cb-11e9-be44-005056880888:1-2,
fc39b161-22ca-11e9-a638-005056ac6820:1-8
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
查看从库的gtid
mysql> show global variables like '%gtid%';
+----------------------------------+------------------------------------------------------------------------------------+
| Variable_name | Value |
+----------------------------------+------------------------------------------------------------------------------------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | 26e410b4-22cb-11e9-be44-005056880888:1-2,
fc39b161-22ca-11e9-a638-005056ac6820:1-8 |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | 26e410b4-22cb-11e9-be44-005056880888:1-2 |
| session_track_gtids | OFF |
+----------------------------------+------------------------------------------------------------------------------------+
8 rows in set (0.01 sec)
接着查看从数据库的数据，发现kevin库已经同步过来了!
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kevin |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from kevin.haha;
+----+----------+
| id | name |
+----+----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
+----+----------+
3 rows in set (0.00 sec)
4）再回到主数据库mysql-master (172.16.60.211)上
查看master状态，发现已经有两个slave节点正常存在同步关系了
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 3 | | 3306 | 1 | 26e410b4-22cb-11e9-be44-005056880888 |
| 2 | | 3306 | 1 | 2afbc2f5-22cb-11e9-b9c0-00505688047c |
+-----------+------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)
5）测试数据同步
在主数据库mysql-master (172.16.60.211)上更新数据
mysql> insert into kevin.haha values(10,"heifei"),(11,"huoqiu"),(12,"chengxihu");
Query OK, 3 rows affected (0.05 sec)
Records: 3 Duplicates: 0 Warnings: 0
然后在两个slave从数据库上查看，发现已正常同步过来了
mysql> select * from kevin.haha;
+----+-----------+
| id | name |
+----+-----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
| 10 | heifei |
| 11 | huoqiu |
| 12 | chengxihu |
+----+-----------+
6 rows in set (0.00 sec)
4. 安装配置ProxySQL
已经在上面第一步中介绍了安装方法，这里采用rpm包方式安装，安装过程省略........
4.1 ProxySQL实现读写分离
向ProxySQL中添加MySQL节点
使用insert语句添加主机到mysql_servers表中，其中：hostgroup_id 为10表示写组，为20表示读组。
[root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1
............
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.211',3306);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.212',3306);
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'172.16.60.213',3306);
Query OK, 1 row affected (0.000 sec)
==========================================================================================================
如果在插入过程中，出现报错：
ERROR 1045 (#2800): UNIQUE constraint failed: mysql_servers.hostgroup_id, mysql_servers.hostname, mysql_servers.port
说明可能之前就已经定义了其他配置，可以清空这张表 或者 删除对应host的配置
MySQL [(none)]> select * from mysql_servers;
MySQL [(none)]> delete from mysql_servers;
Query OK, 6 rows affected (0.000 sec)
=========================================================================================================
查看这3个节点是否插入成功，以及它们的状态。
MySQL [(none)]> select * from mysql_servers\G;
*************************** 1. row ***************************
hostgroup_id: 10
hostname: 172.16.60.211
port: 3306
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 2. row ***************************
hostgroup_id: 10
hostname: 172.16.60.212
port: 3306
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
*************************** 3. row ***************************
hostgroup_id: 10
hostname: 172.16.60.213
port: 3306
status: ONLINE
weight: 1
compression: 0
max_connections: 1000
max_replication_lag: 0
use_ssl: 0
max_latency_ms: 0
comment:
6 rows in set (0.000 sec)
ERROR: No query specified
如上修改后，加载到RUNTIME，并保存到disk
MySQL [(none)]> load mysql servers to runtime;
Query OK, 0 rows affected (0.006 sec)
MySQL [(none)]> save mysql servers to disk;
Query OK, 0 rows affected (0.348 sec)
监控后端MySQL节点
添加Mysql节点之后，还需要监控这些后端节点。对于后端是主从复制的环境来说，这是必须的，因为ProxySQL需要通过每个节点的read_only值来自动调整
它们是属于读组还是写组。
首先在后端master主数据节点上创建一个用于监控的用户名(只需在master上创建即可，因为会复制到slave上)，这个用户名只需具有USAGE权限即可。如果还需
要监控复制结构中slave是否严重延迟于master(这个俗语叫做"拖后腿"，术语叫做"replication lag")，则还需具备replication client权限。
在mysql-master主数据库节点行执行： [root@mysql-master ~]# mysql -p123456 .......... mysql> create user monitor@'172.16.60.%' identified by 'P@ssword1!'; Query OK, 0 rows affected (0.03 sec) mysql> grant replication client on *.* to monitor@'172.16.60.%'; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec) 然后回到mysql-proxy代理层节点上配置监控 [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 .......... MySQL [(none)]> set mysql-monitor_username='monitor'; Query OK, 1 row affected (0.000 sec) MySQL [(none)]> set mysql-monitor_password='P@ssword1!'; Query OK, 1 row affected (0.000 sec) 修改后，加载到RUNTIME，并保存到disk MySQL [(none)]> load mysql variables to runtime; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> save mysql variables to disk; Query OK, 94 rows affected (0.079 sec) 验证监控结果：ProxySQL监控模块的指标都保存在monitor库的log表中。 以下是连接是否正常的监控(对connect指标的监控)： 注意：可能会有很多connect_error，这是因为没有配置监控信息时的错误，配置后如果connect_error的结果为NULL则表示正常。 MySQL [(none)]> select * from mysql_server_connect_log; +---------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +---------------+------+------------------+-------------------------+---------------+ | 172.16.60.211 | 3306 | 1548665195883957 | 762 | NULL | | 172.16.60.212 | 3306 | 1548665195894099 | 399 | NULL | | 172.16.60.213 | 3306 | 1548665195904266 | 483 | NULL | | 172.16.60.211 | 3306 | 1548665255883715 | 824 | NULL | | 172.16.60.212 | 3306 | 1548665255893942 | 656 | NULL | | 172.16.60.211 | 3306 | 1548665495884125 | 615 | NULL | | 172.16.60.212 | 3306 | 1548665495894254 | 441 | NULL | | 172.16.60.213 | 3306 | 1548665495904479 | 638 | NULL | | 172.16.60.211 | 3306 | 1548665512917846 | 487 | NULL | | 172.16.60.212 | 3306 | 1548665512928071 | 994 | NULL | | 172.16.60.213 | 3306 | 1548665512938268 | 613 | NULL | +---------------+------+------------------+-------------------------+---------------+ 20 rows in set (0.000 sec) 以下是对心跳信息的监控(对ping指标的监控) MySQL [(none)]> select * from mysql_server_ping_log; +---------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +---------------+------+------------------+----------------------+------------+ | 172.16.60.211 | 3306 | 1548665195883407 | 98 | NULL | | 172.16.60.212 | 3306 | 1548665195885128 | 119 | NULL | ........... | 172.16.60.213 | 3306 | 1548665415889362 | 106 | NULL | | 172.16.60.213 | 3306 | 1548665562898295 | 97 | NULL | +---------------+------+------------------+----------------------+------------+ 110 rows in set (0.001 sec) read_only日志此时也为空(正常来说，新环境配置时，这个只读日志是为空的) MySQL [(none)]> select * from mysql_server_read_only_log; Empty set (0.000 sec) replication_lag的监控日志为空 MySQL [(none)]> select * from mysql_server_replication_lag_log; Empty set (0.000 sec) 指定写组的id为10，读组的id为20。 MySQL [(none)]> insert into mysql_replication_hostgroups values(10,20,1); Query OK, 1 row affected (0.000 sec) 在该配置加载到RUNTIME生效之前，先查看下各mysql server所在的组。 MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers; +--------------+---------------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+---------------+------+--------+--------+ | 10 | 172.16.60.211 | 3306 | ONLINE | 1 | | 10 | 172.16.60.212 | 3306 | ONLINE | 1 | | 10 | 172.16.60.213 | 3306 | ONLINE | 1 | +--------------+---------------+------+--------+--------+ 3 rows in set (0.000 sec) 3个节点都在hostgroup_id=10的组中。 现在，将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。 MySQL [(none)]> load mysql servers to runtime; Query OK, 0 rows affected (0.003 sec) MySQL [(none)]> save mysql servers to disk; Query OK, 0 rows affected (0.361 sec) 一加载，Monitor模块就会开始监控后端的read_only值，当监控到read_only值后，就会按照read_only的值将某些节点自动移动到读/写组。 例如，此处所有节点都在id=10的写组，slave1和slave2都是slave，它们的read_only=1，这两个节点将会移动到id=20的组。 如果一开始这3节点都在id=20的读组，那么移动的将是Master节点，会移动到id=10的写组。 现在看结果 MySQL [(none)]> select hostgroup_id,hostname,port,status,weight from mysql_servers; +--------------+---------------+------+--------+--------+ | hostgroup_id | hostname | port | status | weight | +--------------+---------------+------+--------+--------+ | 10 | 172.16.60.211 | 3306 | ONLINE | 1 | | 20 | 172.16.60.212 | 3306 | ONLINE | 1 | | 20 | 172.16.60.213 | 3306 | ONLINE | 1 | +--------------+---------------+------+--------+--------+ 3 rows in set (0.000 sec) MySQL [(none)]> select * from mysql_server_read_only_log; +---------------+------+------------------+-----------------+-----------+-------+ | hostname | port | time_start_us | success_time_us | read_only | error | +---------------+------+------------------+-----------------+-----------+-------+ | 172.16.60.212 | 3306 | 1548665728919212 | 1684 | 1 | NULL | | 172.16.60.211 | 3306 | 1548665728918753 | 3538 | 0 | NULL | | 172.16.60.213 | 3306 | 1548665728919782 | 3071 | 1 | NULL |
配置mysql_users
上面的所有配置都是关于后端MySQL节点的，现在可以配置关于SQL语句的，包括：发送SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、SQL语句的重写等等。本小节是SQL请求所使用的用户配置，例如root用户。这要求我们需要先在后端MySQL节点添加好相关用户。这里以root和sqlsender两个用户名为例.
首先，在mysql-master主数据库节点上执行：(只需master执行即可，会复制给两个slave)
[root@mysql-master ~]# mysql -p123456
.........
mysql> grant all on *.* to root@'172.16.60.%' identified by 'passwd';
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> grant all on *.* to sqlsender@'172.16.60.%' identified by 'P@ssword1!';
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
然后回到mysql-proxy代理层节点，配置mysql_users表，将刚才的两个用户添加到该表中。
admin> insert into mysql_users(username,password,default_hostgroup) values('root','passwd',10);
Query OK, 1 row affected (0.001 sec)
admin> insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10);
Query OK, 1 row affected (0.000 sec)
admin> load mysql users to runtime;
Query OK, 0 rows affected (0.001 sec)
admin> save mysql users to disk;
Query OK, 0 rows affected (0.108 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\G
*************************** 1. row ***************************
username: root
password: passwd
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
*************************** 2. row ***************************
username: sqlsender
password: P@ssword1!
active: 1
use_ssl: 0
default_hostgroup: 10
default_schema: NULL
schema_locked: 0
transaction_persistent: 1
fast_forward: 0
backend: 1
frontend: 1
max_connections: 10000
2 rows in set (0.000 sec)
虽然这里没有详细介绍mysql_users表，但上面标注了"注意本行"的两个字段必须要引起注意。只有active=1的用户才是有效的用户。
至于transaction_persistent字段，当它的值为1时，表示事务持久化：当某连接使用该用户开启了一个事务后，那么在事务提交/回滚之前，
所有的语句都路由到同一个组中，避免语句分散到不同组。在以前的版本中，默认值为0，不知道从哪个版本开始，它的默认值为1。
我们期望的值为1，所以在继续下面的步骤之前，先查看下这个值，如果为0，则执行下面的语句修改为1。
MySQL [(none)]> update mysql_users set transaction_persistent=1 where username='root';
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> update mysql_users set transaction_persistent=1 where username='sqlsender';
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> load mysql users to runtime;
Query OK, 0 rows affected (0.001 sec)
MySQL [(none)]> save mysql users to disk;
Query OK, 0 rows affected (0.123 sec)
然后，分别使用root用户和sqlsender用户测试下它们是否能路由到默认的hostgroup_id=10(它是一个写组)读、写数据。
下面是通过转发端口6033连接的，连接的是转发到后端真正的数据库!
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "create database proxy_test"
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| kevin |
| mysql |
| performance_schema |
| proxy_test |
| sys |
+--------------------+
[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'use proxy_test;create table t(id int);'
[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show tables from proxy_test;'
+----------------------+
| Tables_in_proxy_test |
+----------------------+
| t |
+----------------------+
[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| kevin |
| mysql |
| performance_schema |
| proxy_test |
| sys |
+--------------------+
然后再删除上面这个测试库
[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'drop database proxy_test;'
[root@mysql-proxy ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show databases;'
+--------------------+
| Database |
+--------------------+
| information_schema |
| kevin |
| mysql |
| performance_schema |
| sys |
+--------------------+
读写分离：配置路由规则
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@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 ............ MySQL [(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.000 sec) MySQL [(none)]> load mysql query rules to runtime; Query OK, 0 rows affected (0.000 sec) MySQL [(none)]> save mysql query rules to disk; Query OK, 0 rows affected (0.272 sec) 需要注意： select ... for update规则的rule_id必须要小于普通的select规则的rule_id，因为ProxySQL是根据rule_id的顺序进行规则匹配的。 再来测试下，读操作是否路由给了hostgroup_id=20的读组, 如下发现server_id为2和3的节点 (即slave从节点)在读组内 [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' +-------------+ | @@server_id | +-------------+ | 3 | +-------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' +-------------+ | @@server_id | +-------------+ | 3 | +-------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' +-------------+ | @@server_id | +-------------+ | 2 | +-------------+ 读操作已经路由给读组，再看看写操作。这里以事务持久化进行测试。 [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;' +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ +-------------+ | @@server_id | +-------------+ | 3 | +-------------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;' +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ +-------------+ | @@server_id | +-------------+ | 2 | 显然，一切都按照预期进行。最后，如果想查看路由的信息，可查询stats库中的stats_mysql_query_digest表。 以下是该表的一个输出格式示例(和本案例无关)。 [root@mysql-proxy ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 ............ MySQL [(none)]> 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 | 283841 | 1 | drop database proxy_test | | 10 | 161020 | 1 | create table t(id int) | | 10 | 36002 | 1 | create database proxy_test | | 20 | 2719 | 5 | select @@server_id | | 10 | 1250 | 3 | select @@server_id | | 10 | 1102 | 2 | show databases | | 10 | 789 | 2 | start transaction | | 10 | 655 | 1 | SELECT DATABASE() | | 10 | 629 | 1 | show databases | | 10 | 564 | 1 | show tables from proxy_test | | 10 | 286 | 2 | commit | | 10 | 0 | 8 | select @@version_comment limit ? | | 10 | 0 | 5 | select @@version_comment limit ? | +----+----------+------------+----------------------------------+ 13 rows in set (0.002 sec)
测试读写分离效果
由于读写操作都记录在proxysql的stats_mysql_query_digest表内。 为了测试读写分离的效果，可以先清空此表中之前的记录 (即之前在实现读写分配路由配置之前的记录) 下面这个命令是专门清空stats_mysql_query_digest表的 (使用"delete from stats_mysql_query_digest" 清空不掉!) MySQL [(none)]> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.002 sec) MySQL [(none)]> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest; Empty set (0.001 sec) 在mysql-proxy代理层节点，通过proxysql进行数据写入，并查看 [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from kevin.haha;' +----+-----------+ | id | name | +----+-----------+ | 1 | congcong | | 2 | huihui | | 3 | grace | | 11 | huoqiu | | 12 | chengxihu | | 21 | zhongguo | +----+-----------+ [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'delete from kevin.haha where id > 3;' [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'insert into kevin.haha values(21,"zhongguo"),(22,"xianggang"),(23,"taiwan");' [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'update kevin.haha set name="hangzhou" where id=22 ;' [root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from kevin.haha;' +----+----------+ | id | name | +----+----------+ | 1 | congcong | | 2 | huihui | | 3 | grace | | 21 | zhongguo | | 22 | hangzhou | | 23 | taiwan | +----+----------+ 在mysql-master主数据库和mysql-slave1、mysql-slave2从数据上查看 [root@mysql-master ~]# mysql -p123456 ......... mysql> select * from kevin.haha; +----+----------+ | id | name | +----+----------+ | 1 | congcong | | 2 | huihui | | 3 | grace | | 21 | zhongguo | | 22 | hangzhou | | 23 | taiwan | +----+----------+ 6 rows in set (0.00 sec) 发现在客户端通过proxysql插件更新的数据，已经写到mysql-master主数据库上，并同步到mysql-slave1和mysql-slave2两个从数据库上了！ 最后在proxysql管理端查看读写分离 [root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 ............ ............ MySQL [(none)]> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest; +-----------+----------+------------------------------------------------+------------+ | hostgroup | username | digest_text | count_star | +-----------+----------+------------------------------------------------+------------+ | 10 | root | insert into kevin.haha values(?,?),(?,?),(?,?) | 1 | | 10 | root | delete from kevin.haha where id > ? | 1 | | 10 | root | update kevin.haha set name=? where id=? | 1 | | 20 | root | select * from kevin.haha | 2 | | 10 | root | select @@version_comment limit ? | 5 | +-----------+----------+------------------------------------------------+------------+ 5 rows in set (0.001 sec) 从上述结果就可以看出proxysql实现的读写分离配置是成功的，读请求是转发到group20的读组内，写请求转发到group10的写组内!!
4.2 负载均衡测试 (加权轮询)
如上已经配置好一主(mysql-master，在hostgroup10写组内)、两从(mysql-slave1和mysql-slave2，在hostgroup20读组内) ，并且已经在"mysql_query_rules"表中配置了路由规则，即写操作转发到hostgroup10组，读操作转发到hostgroup20组.
MySQL [(none)]> select * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
| 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | NULL | 0 | CASELESS | NULL | NULL | 10 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT | NULL | 0 | CASELESS | NULL | NULL | 20 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+-----+-------+---------+
2 rows in set (0.000 sec)
由于hostgroup10写组内只要一个节点(mysql-master节点)，hostgroup20读组内有两个节点(mysql-slave1、mysql-slave2)
所以这里只能测试读节点的负载均衡
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
再实验下mysql -e跟多条语句，看看如何
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave1 |
+--------------+
[root@mysql-proxy ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;select @@hostname;select @@hostname"
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
+--------------+
| @@hostname |
+--------------+
| mysql-slave2 |
+--------------+
由以上结果可能会猜想并可印证：
在一个client的一个链接周期内，所有query路由到同一台后端!
即在同一个client的链接周期内，query路由不会转发到同组内的不同后端节点机上，只能转发到同一台后端节点机上!
但是这只是个假象!!! 是因为正好用到了select @ 语句。
如官网所介绍: sends a query that implicitly disables multiplexing. For example, if you run “SELECT @a” , ProxySQL will disable
multiplexing for that client and will always use the same backend connection
最后可以知道: proxysql的负载方式目前仅为加权轮询一种（经验证所确认），并无其他机制!
===============================================================================
可以编写一个负载均衡的shell测试脚本:
[root@mysql-proxy ~]# which mysql
/usr/bin/mysql
[root@mysql-proxy ~]# vim /opt/test_proxysql_lb.sh
#!/bin/bash
i=0
while(($i<200))
do
/usr/bin/mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@hostname;" >> /tmp/test_proxy_sql_lb.txt
let "i++"
echo "$i"
sleep 0.1
done
执行测试脚本:
[root@mysql-proxy ~]# sh -x /opt/test_proxysql_lb.sh > /dev/null 2>&1
执行后检查结果
[root@mysql-proxy ~]# grep "mysql-slave1" /tmp/test_proxy_sql_lb.txt|wc -l
86
[root@mysql-proxy ~]# grep "mysql-slave2" /tmp/test_proxy_sql_lb.txt|wc -l
114
以上查询结果符合预期
4.3 开启ProxySQL的Web统计功能
首先打开web功能 [root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 ............ ............ MySQL [(none)]> update global_variables set variable_value='true' where variable_name='admin-web_enabled'; Query OK, 1 row affected (0.001 sec) MySQL [(none)]> LOAD ADMIN VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.001 sec) MySQL [(none)]> SAVE ADMIN VARIABLES TO DISK; Query OK, 31 rows affected (0.070 sec) 然后查看端口和登录web界面的用户名和密码，用户名和密码与stat账户一致： MySQL [(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_system_cpu | 60 | | admin-stats_system_memory | 60 | | admin-web_enabled | true | | admin-web_port | 6080 | #端口 +-----------------------------------+----------------+ 8 rows in set (0.003 sec)
查看web端口是否正常打开
[root@mysql-proxy ~]# lsof -i:6080 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME proxysql 22324 root 27u IPv4 23010645 0t0 TCP *:6080 (LISTEN)
访问http://172.16.60.214:6080并使用stats:stats登录即可查看一些统计信息。
4.4 scheduler打印proxysql状态到日志
[root@mysql-proxy ~]# mkdir -p /opt/proxysql/log
[root@mysql-proxy ~]# vim /opt/proxysql/log/status.sh
#!/bin/bash
DATE=`date "+%Y-%m-%d %H:%M:%S"`
echo "{\"dateTime\":\"$DATE\",\"status\":\"running\"}" >> /opt/proxysql/log/status_log
[root@mysql-proxy ~]# chmod 777 /opt/proxysql/log/status.sh
然后在proxysql插入一条scheduler (定义每分钟打印一次，即60000毫秒)
[root@mysql-proxy ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032
............
............
MySQL [(none)]> insert into scheduler(active,interval_ms,filename) values (1,60000,'/opt/proxysql/log/status.sh');
Query OK, 1 row affected (0.000 sec)
MySQL [(none)]> LOAD SCHEDULER TO RUNTIME;
Query OK, 0 rows affected (0.001 sec)
MySQL [(none)]> SAVE SCHEDULER TO DISK;
Query OK, 0 rows affected (0.105 sec)
然后查看日志就可以看到proxysql 的运行结果了：
[root@mysql-proxy ~]# tail -f /opt/proxysql/log/status_log
{"dateTime":"2019-02-19 14:24:03","status":"running"}
{"dateTime":"2019-02-19 14:25:03","status":"running"}
{"dateTime":"2019-02-19 14:26:03","status":"running"}
{"dateTime":"2019-02-19 14:27:03","status":"running"}
*************** 当你发现自己的才华撑不起野心时，就请安静下来学习吧！***************
