ProxySQL 配置详解及读写分离(+GTID)等功能说明 (完整篇)

22 11月

作者:admin|分类:DBA运维

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"}

ProxySQL 配置详解及读写分离(+GTID)等功能说明 (完整篇)

更多文章推荐

历史上的今天

« 2024年11月 »
一	二	三	四	五	六	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30