Skip to main content
 首页 » 数据库

Mysql中间件 ProxySQL 进行数据库读写分离

2022年08月12日172duanxz

目录

        ProxySQL

1、配置主从环境

2、配置ProxySQL

2.1 ProxySQL安装(需yum源)

2.2 配置ProxySQL

3、ProxySQL的库、表说明 

3.1 查看main库(默认登陆后即在此库)的global_variables表信息

3.2 mysql_ifaces

3.3 admin_credentials

 4、ProxySQL实现读写分离

4.1 向ProxySQL中添加MySQL节点

4.2 监控后端MySQL节点

4.3 配置mysql_users

4.4 读写分离:配置路由规则

4.5 测试读写分离效果

4.6  开启ProxySQL的Web统计功能


在拓扑结构中,ProxySQL部署在应用程序和MySQL集群的中间位置。应用程序向ProxySQL发起SQL语句,ProxySQL分析收到的SQL语句,进行匹配、重写等操作,然后路由给后端MySQL集群中的实例。

ProxySQL

  • 两个版本:官方版和percona版,
  • percona版是基于官方版基础上修改,C++语言开发,轻量级但性能优异(支持处理千亿级数据)
  • 具有中间件所需的绝大多数功能,包括:

多种方式的读/写分离
定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
缓存查询结果
后端节点监控
官方站点:https://proxysql.com/
官方手册:https://github.com/sysown/proxysql/wiki

 拓扑图如下:

1、配置主从环境

 参考文件:主从安装配置

2、配置ProxySQL

安装方式有两种:yum安装与rpm包方式安装

rpm包:下载地址

2.1 ProxySQL安装(需yum源)

[root@centos-03 ~]# cat <<EOF | tee /etc/yum.repos.d/proxysql.repo 
 [proxysql_repo] 
 name= ProxySQL YUM repository 
 baseurl=http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever 
 gpgcheck=1 
 gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key 
 EOF 
[proxysql_repo] 
name= ProxySQL YUM repository 
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever 
gpgcheck=1 
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key 
 
[root@centos-03 ~]# yum clean all 
[root@centos-03 ~]# yum repolist 
proxysql_repo/7               ProxySQL YUM repository          0 
 
[root@centos-03 ~]# yum install -y proxysql 
[root@centos-03 ~]# rpm -ql proxysql 
/etc/logrotate.d/proxysql 
/etc/proxysql.cnf 
/etc/systemd/system/proxysql-initial.service 
/etc/systemd/system/proxysql.service 
/usr/bin/proxysql 
/usr/share/proxysql/tools/proxysql_galera_checker.sh 
/usr/share/proxysql/tools/proxysql_galera_writer.pl

2.1.1 启动ProxySQL 

[root@centos-03 ~]# chkconfig proxysql on 
[root@centos-03 ~]# systemctl start proxysql 
[root@centos-03 ~]# systemctl status proxysql

2.1.2 启动后会监听两个端口 

默认为6032和6033。6032端口是ProxySQL的管理端口,6033是ProxySQL对外提供服务的端口 (即连接到转发后端的真正数据库的转发端口)。

[root@centos-03 ~]# netstat -lntp 
Active Internet connections (only servers) 
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name     
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      1538/proxysql        
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      1538/proxysql    

2.1.3 安装mysql-clinet客户端

 另外,需要在proxysql服务器上安装mysql客户端,用于在本机连接到ProxySQL的管理接口

[root@centos-03 ~]# cat <<EOF | tee /etc/yum.repos.d/MariaDB.repo 
[mariadb] 
name = MariaDB 
baseurl = http://yum.mariadb.org/10.2.4/centos7-amd64 
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB 
gpgcheck=1 
EOF 
[mariadb] 
name = MariaDB 
baseurl = http://yum.mariadb.org/10.2.4/centos7-amd64 
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB 
gpgcheck=1 
[root@centos-03 ~]# yum -y install  MariaDB-client

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无法启动。

2.2 配置ProxySQL

ProxySQL有配置文件/etc/proxysql.cnf和配置数据库文件/var/lib/proxysql/proxysql.db。这里需要特别注意:如果存在如果存在"proxysql.db"文件(在/var/lib/proxysql目录下),则ProxySQL服务只有在第一次启动时才会去读取proxysql.cnf文件并解析;后面启动会就不会读取proxysql.cnf文件了!如果想要让proxysql.cnf文件里的配置在重启proxysql服务后生效(即想要让proxysql重启时读取并解析proxysql.cnf配置文件),则需要先删除/var/lib/proxysql/proxysql.db数据库文件,然后再重启proxysql服务。这样就相当于初始化启动proxysql服务了,会再次生产一个纯净的proxysql.db数据库文件(如果之前配置了proxysql相关路由规则等,则就会被抹掉)。 官方推荐用admin interface方式!(即在proxysql本机使用mysql客户端连接管理端口)

[root@centos-03 ~]# egrep -v "^(#|$)" /etc/proxysql.cnf 
datadir="/var/lib/proxysql"                              #数据目录 
errorlog="/var/lib/proxysql/proxysql.log" 
admin_variables= 
{ 
	admin_credentials="admin:admin"                       #连接管理端的用户名与密码 
	mysql_ifaces="0.0.0.0:6032"                           #管理端口,用来连接proxysql的管理数据库 
} 
mysql_variables= 
{ 
	threads=4                                             #指定转发端口开启的线程数量 
	max_connections=2048 
	default_query_delay=0 
	default_query_timeout=36000000 
	have_compress=true 
	poll_timeout=2000 
	interfaces="0.0.0.0:6033"                              #指定转发端口,用于连接后端mysql数据库的,相当于代理作用 
	default_schema="information_schema" 
	stacksize=1048576 
	server_version="5.7.22"                                 #指定后端mysql的版本 
	connect_timeout_server=3000 
	monitor_username="monitor" 
	monitor_password="monitor" 
	monitor_history=600000 
	monitor_connect_interval=60000 
	monitor_ping_interval=10000 
	monitor_read_only_interval=1500 
	monitor_read_only_timeout=500 
	ping_interval_server_msec=120000 
	ping_timeout_server=500 
	commands_stats=true 
	sessions_sort=true 
	connect_retries_on_failure=10 
} 
mysql_servers = 
( 
) 
mysql_users: 
( 
) 
mysql_query_rules: 
( 
) 
scheduler= 
( 
) 
mysql_replication_hostgroups= 
( 
) 

 proxysql的数据目录

[root@centos-03 ~]# ll /var/lib/proxysql/ 
总用量 404 
-rw-rw---- 1 proxysql proxysql   1078 8月  11 15:55 proxysql-ca.pem 
-rw-rw---- 1 proxysql proxysql   1062 8月  11 15:55 proxysql-cert.pem 
-rw------- 1 proxysql proxysql 196608 8月  11 15:55 proxysql.db 
-rw-rw---- 1 proxysql proxysql   1675 8月  11 15:55 proxysql-key.pem 
-rw------- 1 proxysql proxysql   4052 8月  11 15:55 proxysql.log 
-rw-r--r-- 1 proxysql proxysql      5 8月  11 15:55 proxysql.pid 
-rw------- 1 proxysql proxysql 163840 8月  11 16:09 proxysql_stats.db

3、ProxySQL的库、表说明 

(默认管理端口是6032,客户端服务端口是6033。默认的用户名密码都是 admin)

通过管理端口6032去连接的 (注意, 下面连接命令中后面的--prompt 'admin'字段可以不加,也是可以登录进去的)

[root@centos-03 yum.repos.d]# mysql -u admin -padmin -h 127.0.0.1 -P6032 
或者 
[root@centos-03 yum.repos.d]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin>' 
mysql: [Warning] Using a password on the command line interface can be insecure. 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 4 
Server version: 5.5.30 (ProxySQL Admin Module) 
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
admin>show databases; 
+-----+---------------+-------------------------------------+ 
| seq | name          | file                                | 
+-----+---------------+-------------------------------------+ 
| 0   | main          |                                     | 
| 2   | disk          | /var/lib/proxysql/proxysql.db       | 
| 3   | stats         |                                     | 
| 4   | monitor       |                                     | 
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db | 
+-----+---------------+-------------------------------------+ 
5 rows in set (0.00 sec)

数据库说明:

ProxySQL提供了几个库,每个库都有各自的意义;

  • -  main 内存配置数据库,表里存放后端db实例、用户验证、路由规则等信息。表名以 runtime_开头的表示proxysql当前运行的配置内容,
  • 不能通过dml语句修改,只能修改对应的不以 runtime_ 开头的(在内存)里的表,然后 LOAD 使其生效, SAVE 使其存到硬盘以供下次重启加载。
  • -  disk 是持久化到硬盘的配置,sqlite数据文件。
  • -  stats 是proxysql运行抓取的统计信息,包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间等等。
  • -  monitor 库存储 monitor 模块收集的信息,主要是对后端db的健康/延迟检查。

3.1 查看main库(默认登陆后即在此库)的global_variables表信息

admin>use main; 
Database changed 
admin>show tables; 
+----------------------------------------------------+ 
| tables                                             | 
+----------------------------------------------------+ 
| global_variables                                   | 
| mysql_aws_aurora_hostgroups                        | 
| mysql_collations                                   | 
| mysql_firewall_whitelist_rules                     | 
| mysql_firewall_whitelist_sqli_fingerprints         | 
| mysql_firewall_whitelist_users                     | 
| mysql_galera_hostgroups                            | 
| mysql_group_replication_hostgroups                 | 
| mysql_query_rules                                  | 
| mysql_query_rules_fast_routing                     | 
| mysql_replication_hostgroups                       | 
| mysql_servers                                      | 
| mysql_users                                        | 
| proxysql_servers                                   | 
| restapi_routes                                     | 
| runtime_checksums_values                           | 
| runtime_global_variables                           | 
| runtime_mysql_aws_aurora_hostgroups                | 
| runtime_mysql_firewall_whitelist_rules             | 
| runtime_mysql_firewall_whitelist_sqli_fingerprints | 
| runtime_mysql_firewall_whitelist_users             | 
| runtime_mysql_galera_hostgroups                    | 
| runtime_mysql_group_replication_hostgroups         | 
| runtime_mysql_query_rules                          | 
| runtime_mysql_query_rules_fast_routing             | 
| runtime_mysql_replication_hostgroups               | 
| runtime_mysql_servers                              | 
| runtime_mysql_users                                | 
| runtime_proxysql_servers                           | 
| runtime_restapi_routes                             | 
| runtime_scheduler                                  | 
| scheduler                                          | 
+----------------------------------------------------+ 
32 rows in set (0.00 sec) 
 
admin> select * from global_variables; 
+--------------------------------------------------------------+-----------------------------+ 
| variable_name                                                | variable_value              | 
+--------------------------------------------------------------+-----------------------------+ 
| mysql-default_charset                                        | utf8                        | 
| mysql-default_collation_connection                           | utf8_general_ci             | 
| mysql-shun_on_failures                                       | 5                           | 
| mysql-shun_recovery_time_sec                                 | 10                          | 
| mysql-query_retries_on_failure                               | 1                           | 
| mysql-client_multi_statements                                | true                        | 
| mysql-connect_retries_delay                                  | 1                           | 
| mysql-connection_delay_multiplex_ms                          | 0                           | 
| mysql-connection_max_age_ms                                  | 0                           | 
| mysql-connect_timeout_server_max                             | 10000                       | 
| mysql-eventslog_filename                                     |                             | 
| mysql-eventslog_filesize                                     | 104857600                   | 
| mysql-eventslog_default_log                                  | 0                           | 
| mysql-eventslog_format                                       | 1                           | 
| mysql-auditlog_filename                                      |                             | 
| mysql-auditlog_filesize                                      | 104857600                   | 
| mysql-handle_unknown_charset                                 | 1                           | 
| mysql-free_connections_pct                                   | 10                          | 
| mysql-connection_warming                                     | false                       | 
| mysql-session_idle_ms                                        | 1000                        | 
| mysql-have_ssl                                               | false                       | 
| mysql-client_found_rows                                      | true                        | 
| mysql-monitor_enabled                                        | true                        | 
| mysql-monitor_connect_timeout                                | 600                         | 
| mysql-monitor_ping_max_failures                              | 3                           | 
| mysql-monitor_ping_timeout                                   | 1000                        | 
| mysql-monitor_read_only_max_timeout_count                    | 3                           | 
| mysql-monitor_replication_lag_interval                       | 10000                       | 
| mysql-monitor_replication_lag_timeout                        | 1000                        | 
| mysql-monitor_groupreplication_healthcheck_interval          | 5000                        | 
| mysql-monitor_groupreplication_healthcheck_timeout           | 800                         | 
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3                           | 
| mysql-monitor_groupreplication_max_transactions_behind_count | 3                           | 
| mysql-monitor_galera_healthcheck_interval                    | 5000                        | 
| mysql-monitor_galera_healthcheck_timeout                     | 800                         | 
| mysql-monitor_galera_healthcheck_max_timeout_count           | 3                           | 
| mysql-monitor_replication_lag_use_percona_heartbeat          |                             | 
| mysql-monitor_query_interval                                 | 60000                       | 
| mysql-monitor_query_timeout                                  | 100                         | 
| mysql-monitor_slave_lag_when_null                            | 60                          | 
| mysql-monitor_threads_min                                    | 8                           | 
| mysql-monitor_threads_max                                    | 128                         | 
| mysql-monitor_threads_queue_maxsize                          | 128                         | 
| mysql-monitor_wait_timeout                                   | true                        | 
| mysql-monitor_writer_is_also_reader                          | true                        | 
| mysql-max_allowed_packet                                     | 67108864                    | 
| mysql-tcp_keepalive_time                                     | 0                           | 
| mysql-use_tcp_keepalive                                      | 0                           | 
| mysql-automatic_detect_sqli                                  | 0                           | 
| mysql-firewall_whitelist_enabled                             | 0                           | 
| mysql-firewall_whitelist_errormsg                            | Firewall blocked this query | 
| mysql-throttle_connections_per_sec_to_hostgroup              | 1000000                     | 
| mysql-max_transaction_time                                   | 14400000                    | 
| mysql-multiplexing                                           | true                        | 
| mysql-log_unhealthy_connections                              | true                        | 
| mysql-forward_autocommit                                     | false                       | 
| mysql-enforce_autocommit_on_reads                            | false                       | 
| mysql-autocommit_false_not_reusable                          | false                       | 
| mysql-autocommit_false_is_transaction                        | false                       | 
| mysql-verbose_query_error                                    | false                       | 
| mysql-hostgroup_manager_verbose                              | 1                           | 
| mysql-binlog_reader_connect_retry_msec                       | 3000                        | 
| mysql-threshold_query_length                                 | 524288                      | 
| mysql-threshold_resultset_size                               | 4194304                     | 
| mysql-query_digests_max_digest_length                        | 2048                        | 
| mysql-query_digests_max_query_length                         | 65000                       | 
| mysql-wait_timeout                                           | 28800000                    | 
| mysql-throttle_max_bytes_per_second_to_client                | 0                           | 
| mysql-throttle_ratio_server_to_client                        | 0                           | 
| mysql-max_stmts_per_connection                               | 20                          | 
| mysql-max_stmts_cache                                        | 10000                       | 
| mysql-mirror_max_concurrency                                 | 16                          | 
| mysql-mirror_max_queue_length                                | 32000                       | 
| mysql-default_max_latency_ms                                 | 1000                        | 
| mysql-query_processor_iterations                             | 0                           | 
| mysql-query_processor_regex                                  | 1                           | 
| mysql-set_query_lock_on_hostgroup                            | 1                           | 
| mysql-reset_connection_algorithm                             | 2                           | 
| mysql-auto_increment_delay_multiplex                         | 5                           | 
| mysql-long_query_time                                        | 1000                        | 
| mysql-query_cache_size_MB                                    | 256                         | 
| mysql-poll_timeout_on_failure                                | 100                         | 
| mysql-server_capabilities                                    | 569867                      | 
| mysql-keep_multiplexing_variables                            | tx_isolation,version        | 
| mysql-kill_backend_connection_when_disconnect                | true                        | 
| mysql-client_session_track_gtid                              | true                        | 
| mysql-session_idle_show_processlist                          | true                        | 
| mysql-show_processlist_extended                              | 0                           | 
| mysql-query_digests                                          | true                        | 
| mysql-query_digests_lowercase                                | false                       | 
| mysql-query_digests_replace_null                             | false                       | 
| mysql-query_digests_no_digits                                | false                       | 
| mysql-query_digests_normalize_digest_text                    | false                       | 
| mysql-query_digests_track_hostname                           | false                       | 
| mysql-servers_stats                                          | true                        | 
| mysql-default_reconnect                                      | true                        | 
| mysql-ssl_p2s_ca                                             |                             | 
| mysql-ssl_p2s_cert                                           |                             | 
| mysql-ssl_p2s_key                                            |                             | 
| mysql-ssl_p2s_cipher                                         |                             | 
| mysql-init_connect                                           |                             | 
| mysql-ldap_user_variable                                     |                             | 
| mysql-add_ldap_user_comment                                  |                             | 
| mysql-default_tx_isolation                                   | READ-COMMITTED              | 
| mysql-default_session_track_gtids                            | OFF                         | 
| mysql-connpoll_reset_queue_length                            | 50                          | 
| mysql-min_num_servers_lantency_awareness                     | 1000                        | 
| mysql-aurora_max_lag_ms_only_read_from_replicas              | 2                           | 
| mysql-stats_time_backend_query                               | false                       | 
| mysql-stats_time_query_processor                             | false                       | 
| mysql-query_cache_stores_empty_result                        | true                        | 
| admin-stats_credentials                                      | stats:stats                 | 
| admin-stats_mysql_connections                                | 60                          | 
| admin-stats_mysql_connection_pool                            | 60                          | 
| admin-stats_mysql_query_cache                                | 60                          | 
| admin-stats_mysql_query_digest_to_disk                       | 0                           | 
| admin-stats_system_cpu                                       | 60                          | 
| admin-stats_system_memory                                    | 60                          | 
| admin-telnet_admin_ifaces                                    | (null)                      | 
| admin-telnet_stats_ifaces                                    | (null)                      | 
| admin-refresh_interval                                       | 2000                        | 
| admin-read_only                                              | false                       | 
| admin-hash_passwords                                         | true                        | 
| admin-vacuum_stats                                           | true                        | 
| admin-version                                                | 2.0.13-107-g91737e0         | 
| admin-cluster_username                                       |                             | 
| admin-cluster_password                                       |                             | 
| admin-cluster_check_interval_ms                              | 1000                        | 
| admin-cluster_check_status_frequency                         | 10                          | 
| admin-cluster_mysql_query_rules_diffs_before_sync            | 3                           | 
| admin-cluster_mysql_servers_diffs_before_sync                | 3                           | 
| admin-cluster_mysql_users_diffs_before_sync                  | 3                           | 
| admin-cluster_proxysql_servers_diffs_before_sync             | 3                           | 
| admin-cluster_mysql_query_rules_save_to_disk                 | true                        | 
| admin-cluster_mysql_servers_save_to_disk                     | true                        | 
| admin-cluster_mysql_users_save_to_disk                       | true                        | 
| admin-cluster_proxysql_servers_save_to_disk                  | true                        | 
| admin-checksum_mysql_query_rules                             | true                        | 
| admin-checksum_mysql_servers                                 | true                        | 
| admin-checksum_mysql_users                                   | true                        | 
| admin-restapi_enabled                                        | false                       | 
| admin-restapi_port                                           | 6070                        | 
| admin-web_enabled                                            | false                       | 
| admin-web_port                                               | 6080                        | 
| admin-admin_credentials                                      | admin:admin                 | 
| admin-mysql_ifaces                                           | 0.0.0.0:6032                | 
| mysql-threads                                                | 4                           | 
| mysql-max_connections                                        | 2048                        | 
| mysql-default_query_delay                                    | 0                           | 
| mysql-default_query_timeout                                  | 36000000                    | 
| mysql-have_compress                                          | true                        | 
| mysql-poll_timeout                                           | 2000                        | 
| mysql-interfaces                                             | 0.0.0.0:6033                | 
| mysql-default_schema                                         | information_schema          | 
| mysql-stacksize                                              | 1048576                     | 
| mysql-server_version                                         | 5.5.30                      | 
| mysql-connect_timeout_server                                 | 3000                        | 
| mysql-monitor_username                                       | monitor                     | 
| mysql-monitor_password                                       | monitor                     | 
| mysql-monitor_history                                        | 600000                      | 
| mysql-monitor_connect_interval                               | 60000                       | 
| mysql-monitor_ping_interval                                  | 10000                       | 
| mysql-monitor_read_only_interval                             | 1500                        | 
| mysql-monitor_read_only_timeout                              | 500                         | 
| mysql-ping_interval_server_msec                              | 120000                      | 
| mysql-ping_timeout_server                                    | 500                         | 
| mysql-commands_stats                                         | true                        | 
| mysql-sessions_sort                                          | true                        | 
| mysql-connect_retries_on_failure                             | 10                          | 
+--------------------------------------------------------------+-----------------------------+ 
169 rows in set (0.01 sec)

登陆成功后,可通过对main库(默认登陆后即在此库)的global_variables表中的"admin-admin_credentials" 和 "admin-mysql_ifaces"

两个变量进行更改来修改登录认证! 比如说修改密码或定义一个非admin的用户用于远程登录。

proxysql的6032端口是管理入口,账号密码是admin(可以动态修改),允许客户端连接;6033端口就是客户端入口,账号密码通过管理接口去设置。在proxysql本机使用mysql客户端连接到ProxySQL的管理接口(admin interface), 该接口的默认管理员用户和密码都是admin。 

3.2 mysql_ifaces


也就是说proxysql有一个admin接口专门来做配置,相当于一个mysql shell可以通过sql来让配置实时生效。
mysql_ifaces配置了允许连接proxysql的ip和port

[root@centos-03 yum.repos.d]# nl /etc/proxysql.cnf |grep mysql_ifaces 
#  将admin_variables中的mysql_ifaces修改成允许远程访问 
    36	#	mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" 
    37		mysql_ifaces="0.0.0.0:6032" 

如果ip配置为0.0.0.0表示不限制ip,但是出于安全考虑,admin用户无论怎么设置都只能在本机登录!

3.3 admin_credentials


这个key保存所有可以操作proxysql的用户名和密码,格式为:user:pass;user1:pass1,这里可以修改密码或定义一个非admin的用户用于远程登录。 前提是保证想要管理proxysql的机器安装有mysql client客户端!

先在proxysql本机登录 (因为初始账号密码是admin:admin,只能在本机登录), 这里的proxysql本机地址是192.168.1.130

修改远程连接proxysql管理端口的账号和密码radmin:radmin.

[root@centos-03 yum.repos.d]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin>' 
mysql: [Warning] Using a password on the command line interface can be insecure. 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 6 
Server version: 5.5.30 (ProxySQL Admin Module) 
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
admin> update global_variables set variable_value = 'admin:admin;radmin:radmin' where variable_name = 'admin-admin_credentials'; 
Query OK, 1 row affected (0.00 sec) 
 
admin> LOAD ADMIN VARIABLES TO RUNTIME; 
Query OK, 0 rows affected (0.03 sec) 
 
admin> SAVE ADMIN VARIABLES TO DISK; 
Query OK, 35 rows affected (0.00 sec) 

这样就可以使用下面的命令在其他机器上使用radmin用户登录(其他机器上需要有mysql client)

[root@centos-02 ~]# mysql -uradmin -pradmin -h192.168.1.130 -P6032 
mysql: [Warning] Using a password on the command line interface can be insecure. 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 9 
Server version: 5.5.30 (ProxySQL Admin Module) 
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
mysql> show databases; 
+-----+---------------+-------------------------------------+ 
| seq | name          | file                                | 
+-----+---------------+-------------------------------------+ 
| 0   | main          |                                     | 
| 2   | disk          | /var/lib/proxysql/proxysql.db       | 
| 3   | stats         |                                     | 
| 4   | monitor       |                                     | 
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db | 
+-----+---------------+-------------------------------------+ 
5 rows in set (0.01 sec)

1) main 库 (disk库的表字段和main一样)

admin> show tables from main; 
+----------------------------------------------------+ 
| tables                                             | 
+----------------------------------------------------+ 
| global_variables                                   | 
| mysql_aws_aurora_hostgroups                        | 
| mysql_collations                                   | 
| mysql_firewall_whitelist_rules                     | 
| mysql_firewall_whitelist_sqli_fingerprints         | 
| mysql_firewall_whitelist_users                     | 
| mysql_galera_hostgroups                            | 
| mysql_group_replication_hostgroups                 | 
| mysql_query_rules                                  | 
| mysql_query_rules_fast_routing                     | 
| mysql_replication_hostgroups                       | 
| mysql_servers                                      | 
| mysql_users                                        | 
| proxysql_servers                                   | 
| restapi_routes                                     | 
| runtime_checksums_values                           | 
| runtime_global_variables                           | 
| runtime_mysql_aws_aurora_hostgroups                | 
| runtime_mysql_firewall_whitelist_rules             | 
| runtime_mysql_firewall_whitelist_sqli_fingerprints | 
| runtime_mysql_firewall_whitelist_users             | 
| runtime_mysql_galera_hostgroups                    | 
| runtime_mysql_group_replication_hostgroups         | 
| runtime_mysql_query_rules                          | 
| runtime_mysql_query_rules_fast_routing             | 
| runtime_mysql_replication_hostgroups               | 
| runtime_mysql_servers                              | 
| runtime_mysql_users                                | 
| runtime_proxysql_servers                           | 
| runtime_restapi_routes                             | 
| runtime_scheduler                                  | 
| scheduler                                          | 
+----------------------------------------------------+ 
32 rows in set (0.00 sec) 
 
admin>show create table mysql_servers\G; 
*************************** 1. row *************************** 
       table: mysql_servers 
Create Table: CREATE TABLE mysql_servers ( 
    hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0, 
    hostname VARCHAR NOT NULL, 
    port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306, 
    gtid_port INT CHECK ((gtid_port <> port OR gtid_port=0) AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0, 
    status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE', 
    weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1, 
    compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0, 
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000, 
    max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0, 
    use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0, 
    max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0, 
    comment VARCHAR NOT NULL DEFAULT '', 
    PRIMARY KEY (hostgroup_id, hostname, port) ) 
1 row in set (0.00 sec)

常用的几个表介绍
===============================================
global_variables      
设置变量,包括监听的端口、管理账号等。
 
mysql_collations      
相关字符集和校验规则。
 
mysql_query_rules     
定义查询路由规则。
 
mysql_replication_hostgroups  
监视指定主机组中所有服务器的read_only值,并且根据read_only的值将服务器分配给写入器或读取器主机组。ProxySQL monitor模块会监控hostgroups
后端所有servers 的read_only 变量,如果发现从库的read_only变为0、主库变为1,则认为角色互换了,自动改写mysql_servers表里面 hostgroup关系,
达到自动 Failover 效果。
 
mysql_servers
设置后端MySQL的表
 
mysql_users
配置后端数据库的程序账号和监控账号。
 
scheduler
调度器是一个类似于cron的实现,集成在ProxySQL中,具有毫秒的粒度。通过脚本检测来设置ProxySQL。

2)stats库 

admin> show tables from stats; 
+--------------------------------------+ 
| tables                               | 
+--------------------------------------+ 
| global_variables                     | 
| stats_memory_metrics                 | 
| stats_mysql_commands_counters        | 
| stats_mysql_connection_pool          | 
| stats_mysql_connection_pool_reset    | 
| stats_mysql_errors                   | 
| stats_mysql_errors_reset             | 
| stats_mysql_free_connections         | 
| stats_mysql_global                   | 
| stats_mysql_gtid_executed            | 
| stats_mysql_prepared_statements_info | 
| stats_mysql_processlist              | 
| stats_mysql_query_digest             | 
| stats_mysql_query_digest_reset       | 
| stats_mysql_query_rules              | 
| stats_mysql_users                    | 
| stats_proxysql_servers_checksums     | 
| stats_proxysql_servers_metrics       | 
| stats_proxysql_servers_status        | 
+--------------------------------------+ 
19 rows in set (0.00 sec)

常用的几个表介绍
===============================================
stats_mysql_commands_counters
统计各种SQL类型的执行次数和时间,通过参数mysql-commands_stats控制开关,默认是ture。
 
stats_mysql_connection_pool
连接后端MySQL的连接信息。
 
stats_mysql_processlist
类似MySQL的show processlist的命令,查看各线程的状态。
 
stats_mysql_query_digest
表示SQL的执行次数、时间消耗等。通过变量mysql-query_digests控制开关,默认是开。
 
stats_mysql_query_rules
路由命中次数统计。

3)monitor库

admin> show tables from monitor;  
+--------------------------------------+ 
| tables                               | 
+--------------------------------------+ 
| mysql_server_aws_aurora_check_status | 
| mysql_server_aws_aurora_failovers    | 
| mysql_server_aws_aurora_log          | 
| mysql_server_connect_log             | 
| mysql_server_galera_log              | 
| mysql_server_group_replication_log   | 
| mysql_server_ping_log                | 
| mysql_server_read_only_log           | 
| mysql_server_replication_lag_log     | 
+--------------------------------------+ 
9 rows in set (0.00 sec)

 常用的几个表介绍
===============================================
mysql_server_connect_log
连接到所有MySQL服务器以检查它们是否可用,该表用来存放检测连接的日志。
 
mysql_server_ping_log
使用mysql_ping API ping后端MySQL服务器,检查它们是否可用,该表用来存放ping的日志。
 
mysql_server_replication_lag_log
后端MySQL服务主从延迟的检测。
 
runtime_开头的是运行时的配置,这些是不能修改的。要修改ProxySQL的配置,需要修改了非runtime_表,
修改后必须执行"LOAD ... TO RUNTIME"才能加载到RUNTIME生效,执行save ... to disk才能将配置持久化保存到磁盘。

 4、ProxySQL实现读写分离

4.1 向ProxySQL中添加MySQL节点

使用insert语句添加主机到mysql_servers表中,其中:hostgroup_id 为10表示写组,为20表示读组。

[root@centos-03 ~]#  mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin>' 
mysql: [Warning] Using a password on the command line interface can be insecure. 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 11 
Server version: 5.5.30 (ProxySQL Admin Module) 
 
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.1.128',3306); 
Query OK, 1 row affected (0.00 sec) 
 
admin> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.1.129',3306); 
Query OK, 1 row affected (0.00 sec)

如果在插入过程中,出现报错:
ERROR 1045 (#2800): UNIQUE constraint failed: mysql_servers.hostgroup_id, mysql_servers.hostname, mysql_servers.port
 
说明可能之前就已经定义了其他配置,可以清空这张表 或者 删除对应host的配置

admin> select * from mysql_servers;

admin> delete from mysql_servers;
Query OK, 6 rows affected (0.000 sec)

查看这2个节点是否插入成功,以及它们的状态

admin> select * from mysql_servers; 
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 
| hostgroup_id | hostname      | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | 
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 
| 10           | 192.168.1.128 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         | 
| 10           | 192.168.1.129 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         | 
+--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 
2 rows in set (0.00 sec)

 说明:

-  hostgroup_id: ProxySQL通过 hostgroup (下称HG) 的形式组织后端db实例。一个 HG 代表同属于一个角色
-  该表的主键是 (hostgroup_id, hostname, port),可以看到一个 hostname:port 可以在多个hostgroup里面,如上面的 10.0.100.100:3307,这样可以避免 HG 1000 的从库全都不可用时,依然可以把读请求发到主库上。
-  一个 HG 可以有多个实例,即多个从库,可以通过 weight 分配权重
-  hostgroup_id 0 是一个特殊的HG,路由查询的时候,没有匹配到规则则默认选择 HG 0
-  status:
-  ONLINE: 当前后端实例状态正常
   -  SHUNNED: 临时被剔除,可能因为后端 too many connections error,或者超过了可容忍延迟阀值 max_replication_lag
   -  OFFLINE_SOFT: “软离线”状态,不再接受新的连接,但已建立的连接会等待活跃事务完成。
   -  OFFLINE_HARD: “硬离线”状态,不再接受新的连接,已建立的连接或被强制中断。当后端实例宕机或网络不可达,会出现。
-  max_connections: 允许连接到该后端mysql实例的最大连接数。不要大于MySQL设置的 max_connections,如果后端实例 hostname:port 在多个 hostgroup 里,以较大者为准,而不是各自独立允许的最大连接数。
-  max_replication_lag: 允许的最大延迟,主库不受这个影响,默认0。如果 > 0, monitor 模块监控主从延迟大于阀值时,会临时把它变为 SHUNNED 。
-  max_latency_ms: mysql_ping 响应时间,大于这个阀值会把它从连接池剔除(即使是ONLINE)
-  comment: 备注,不建议留空。可以通过它的内容如json格式的数据,配合自己写的check脚本,完成一些自动化的工作。

如上修改后,加载到RUNTIME,并保存到disk 

admin> load mysql servers to runtime; 
Query OK, 0 rows affected (0.02 sec) 
 
admin> save mysql servers to disk; 
Query OK, 0 rows affected (0.03 sec)

4.2 监控后端MySQL节点

添加Mysql节点之后,还需要监控这些后端节点。对于后端是主从复制的环境来说,这是必须的,因为ProxySQL需要通过每个节点的read_only值来自动调整
它们是属于读组还是写组。

首先在后端master主数据节点上创建一个用于监控的用户名(只需在master上创建即可,因为会复制到slave上),这个用户名只需具有USAGE权限即可。如果还需
要监控复制结构中slave是否严重延迟于master(这个俗语叫做"拖后腿",术语叫做"replication lag"),则还需具备replication client权限。

mysql-master主数据库节点行执行: 

[root@centos-01 ~]# mysql -uroot -p123456 
...... 
 
mysql> create user monitor@'192.168.1.%' identified by '1q2w3e4r'; 
Query OK, 0 rows affected (0.15 sec) 
 
mysql> grant replication client on *.* to monitor@'192.168.1.%'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> flush privileges; 
Query OK, 0 rows affected (0.04 sec)

然后回到mysql-proxy代理层节点上配置监控

[root@centos-03 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin>' 
......... 
 
admin> set mysql-monitor_username='monitor'; 
Query OK, 1 row affected (0.00 sec) 
 
admin> set mysql-monitor_password='1q2w3e4r'; 
Query OK, 1 row affected (0.00 sec) 
 
admin> load mysql variables to runtime; 
Query OK, 0 rows affected (0.00 sec) 
 
admin> save mysql variables to disk; 
Query OK, 134 rows affected (0.01 sec) 

验证监控结果:ProxySQL监控模块的指标都保存在monitor库的log表中。

以下是连接是否正常的监控(对connect指标的监控):
注意:可能会有很多connect_error,这是因为没有配置监控信息时的错误,配置后如果connect_error的结果为NULL则表示正常。

admin> select * from mysql_server_connect_log; 
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+ 
| hostname      | port | time_start_us    | connect_success_time_us | connect_error                                                          | 
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+ 
 192.168.1.129 | 3306 | 1597203755579760 | 5449                    | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203757132535 | 7186                    | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203758162549 | 1554                    | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203817132683 | 1350                    | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203818037219 | 10335                   | NULL                                                                   | 
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+ 
5 rows in set (0.00 sec)

以下是对心跳信息的监控(对ping指标的监控)

admin> select * from mysql_server_ping_log; 
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+ 
| hostname      | port | time_start_us    | ping_success_time_us | ping_error                                                             | 
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+ 
| 192.168.1.129 | 3306 | 1597203754965639 | 25516                | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203755641789 | 8559                 | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203755822015 | 27738                | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203765642489 | 649                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203765798771 | 526                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203775642756 | 502                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203775839034 | 614                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203785643016 | 501                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203785788363 | 458                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203795643189 | 414                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203795808873 | 524                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203805643444 | 483                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203805774997 | 507                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203815643802 | 780                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203815794225 | 1311                 | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203825654344 | 5258                 | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203825809517 | 15436                | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203835654454 | 775                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203835826912 | 474                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203845655478 | 489                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203845794034 | 1652                 | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203855656338 | 998                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203855852682 | 660                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203865657335 | 491                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203865781372 | 389                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203875658214 | 519                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203875826015 | 688                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203885659435 | 439                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203885857864 | 968                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203895659554 | 415                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203895859534 | 923                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203905660589 | 570                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203905803424 | 739                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203915660741 | 485                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203915801208 | 458                  | NULL                                                                   | 
| 192.168.1.128 | 3306 | 1597203925661748 | 493                  | NULL                                                                   | 
| 192.168.1.129 | 3306 | 1597203925832599 | 2219                 | NULL                                                                   | 
+---------------+------+------------------+----------------------+------------------------------------------------------------------------+ 

read_only日志此时也为空(正常来说,新环境配置时,这个只读日志是为空的)

admin>select * from mysql_server_read_only_log; 
Empty set (0.00 sec)

replication_lag的监控日志为空

admin>select * from mysql_server_replication_lag_log; 
Empty set (0.00 sec)

指定写组的id为10,读组的id为20

admin>  insert into mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup) values(10,20); 
Query OK, 1 row affected (0.00 sec)

在该配置加载到RUNTIME生效之前,先查看下各mysql server所在的组。

admin>select hostgroup_id,hostname,port,status,weight from mysql_servers; 
+--------------+---------------+------+--------+--------+ 
| hostgroup_id | hostname      | port | status | weight | 
+--------------+---------------+------+--------+--------+ 
| 10           | 192.168.1.128 | 3306 | ONLINE | 1      | 
| 10           | 192.168.1.129 | 3306 | ONLINE | 1      | 
+--------------+---------------+------+--------+--------+ 
2 rows in set (0.00 sec)

2个节点都在hostgroup_id=10的组中。
现在,将刚才mysql_replication_hostgroups表的修改加载到RUNTIME生效。

admin> load mysql servers to runtime; 
Query OK, 0 rows affected (0.00 sec) 
 
admin> save mysql servers to disk; 
Query OK, 0 rows affected (0.02 sec)

一加载,Monitor模块就会开始监控后端的read_only值,当监控到read_only值后,就会按照read_only的值将某些节点自动移动到读/写组。
例如,此处所有节点都在id=10的写组,slave1的read_only=1,这个节点将会移动到id=20的组。

现在看结果

admin> select hostgroup_id,hostname,port,status,weight from mysql_servers; 
+--------------+---------------+------+--------+--------+ 
| hostgroup_id | hostname      | port | status | weight | 
+--------------+---------------+------+--------+--------+ 
| 10           | 192.168.1.128 | 3306 | ONLINE | 1      | 
| 20           | 192.168.1.129 | 3306 | ONLINE | 1      | 
+--------------+---------------+------+--------+--------+ 
2 rows in set (0.00 sec) 
 
admin> select * from mysql_server_read_only_log; 
+---------------+------+------------------+-----------------+-----------+-------+ 
| hostname      | port | time_start_us    | success_time_us | read_only | error | 
+---------------+------+------------------+-----------------+-----------+-------+ 
| 192.168.1.129 | 3306 | 1597215109281816 | 11848           | 1         | NULL  | 
| 192.168.1.128 | 3306 | 1597215109310894 | 11839           | 0         | NULL  | 
| 192.168.1.129 | 3306 | 1597215110782890 | 878             | 1         | NULL  | 
| 192.168.1.128 | 3306 | 1597215110802814 | 715             | 0         | NULL  | 
| 192.168.1.128 | 3306 | 1597215112283130 | 475             | 0         | NULL  | 
| 192.168.1.129 | 3306 | 1597215112312026 | 685             | 1         | NULL  |

4.3 配置mysql_users

上面的所有配置都是关于后端MySQL节点的,现在可以配置关于SQL语句的,包括:发送SQL语句的用户、SQL语句的路由规则、SQL查询的缓存、SQL语句的重写等等。本小节是SQL请求所使用的用户配置,例如root用户。这要求我们需要先在后端MySQL节点添加好相关用户。这里以root和sqlsender两个用户名为例

首先,在mysql-master主数据库节点上执行:(只需master执行即可,会复制给slave)

[root@centos-01 ~]# mysql -uroot -p123456 
...... 
 
mysql> grant all on *.* to root@'192.168.1.%' identified by 'passwd'; 
Query OK, 0 rows affected, 1 warning (0.01 sec) 
 
mysql> grant all on *.* to sqlsender@'192.168.1.%' identified by 'P@ssword1!'; 
Query OK, 0 rows affected, 1 warning (0.01 sec) 
 
mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

然后回到mysql-proxy代理层节点,配置mysql_users表,将刚才的两个用户添加到该表中。

[root@centos-03 ~]#  mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin>' 
............. 
 
admin> insert into mysql_users(username,password,default_hostgroup) values('root','passwd',10); 
Query OK, 1 row affected (0.00 sec) 
 
admin> insert into mysql_users(username,password,default_hostgroup) values('sqlsender','P@ssword1!',10); 
Query OK, 1 row affected (0.00 sec) 
 
admin> load mysql users to runtime; 
Query OK, 0 rows affected (0.00 sec) 
 
admin> save mysql users to disk; 
Query OK, 0 rows affected (0.00 sec) 
 

mysql_users表有不少字段,最主要的三个字段为username、password和default_hostgroup:

-  username:前端连接ProxySQL,以及ProxySQL将SQL语句路由给MySQL所使用的用户名。
-  password:用户名对应的密码。可以是明文密码,也可以是hash密码。如果想使用hash密码,可以先在某个MySQL节点上执行
   select password(PASSWORD),然后将加密结果复制到该字段。
-  default_hostgroup:该用户名默认的路由目标。例如,指定root用户的该字段值为10时,则使用root用户发送的SQL语句默认
   情况下将路由到hostgroup_id=10组中的某个节点。

admin> select * from mysql_users; 
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+ 
| username  | password   | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment | 
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+ 
| myadmin   | mypass     | 1      | 0       | 0                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         | 
| root      | passwd     | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         | 
| sqlsender | P@ssword1! | 1      | 0       | 10                | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |         | 
+-----------+------------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+ 
3 rows in set (0.00 sec)

 注意:active 与 transaction_persistent需引起注意,只有active=1的用户才是有效的用户。
至于transaction_persistent字段,当它的值为1时,表示事务持久化:当某连接使用该用户开启了一个事务后,那么在事务提交/回滚之前,
所有的语句都路由到同一个组中,避免语句分散到不同组。在以前的版本中,默认值为0,不知道从哪个版本开始,它的默认值为1。
我们期望的值为1,所以在继续下面的步骤之前,先查看下这个值,如果为0,则执行下面的语句修改为1。

admin> update mysql_users set transaction_persistent=1 where username='root'; 
Query OK, 1 row affected (0.00 sec) 
 
admin>  update mysql_users set transaction_persistent=1 where username='sqlsender'; 
Query OK, 1 row affected (0.00 sec) 
 
admin> load mysql users to runtime; 
Query OK, 0 rows affected (0.01 sec) 
 
admin> save mysql users to disk; 
Query OK, 0 rows affected (0.02 sec)

然后,分别使用root用户和sqlsender用户测试下它们是否能路由到默认的hostgroup_id=10(它是一个写组)读、写数据。
下面是通过转发端口6033连接的,连接的是转发到后端真正的数据库!

[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "select @@server_id" 
+-------------+ 
| @@server_id | 
+-------------+ 
|         128 | 
+-------------+ 
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "create database proxy_test" 
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e "show databases;" 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| grade              | 
| mysql              | 
| performance_schema | 
| proxy_test         | 
| sys                | 
+--------------------+ 
[root@centos-03 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'use proxy_test;create table t(id int);' 
[root@centos-03 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show tables from proxy_test;' 
+----------------------+ 
| Tables_in_proxy_test | 
+----------------------+ 
| t                    | 
+----------------------+ 
[root@centos-03 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show databases;' 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| grade              | 
| mysql              | 
| performance_schema | 
| proxy_test         | 
| sys                | 
+--------------------+ 
[root@centos-03 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'drop database proxy_test;' 
[root@centos-03 ~]# mysql -usqlsender -pP@ssword1! -P6033 -h127.0.0.1 -e 'show databases;' 
+--------------------+ 
| Database           | 
+--------------------+ 
| information_schema | 
| grade              | 
| mysql              | 
| performance_schema | 
| sys                | 
+--------------------+

4.4 读写分离:配置路由规则

ProxySQL的路由规则非常灵活,可以基于用户、基于schema以及基于每个语句实现路由规则的定制。本案例作为一个入门配置,实现一个最简单的语句级路由规则,从而实现读写分离。

必须注意: 这只是实验,实际的路由规则绝不应该仅根据所谓的读、写操作进行分离,而是从各项指标中找出压力大、执行频繁的语句单独写规则、做缓存等等。和查询规则有关的表有两个:mysql_query_rules和mysql_query_rules_fast_routing,后者是前者的扩展表,1.4.7之后才支持该快速路由表。本案例只介绍第一个表。插入两个规则,目的是将select语句分离到hostgroup_id=20的读组,但由于select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁,所以应该路由到hostgroup_id=10的写组.

[root@centos-03 ~]# mysql -uadmin -padmin -P6032 -h127.0.0.1  
Welcome to the MariaDB monitor.  Commands end with ; or \g. 
Your MySQL connection id is 11 
Server version: 5.5.30 (ProxySQL Admin Module) 
 
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
:29:  admin@127.0.0.1:[(none)]> insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1), (2,1,'^SELECT',20,1); 
Query OK, 2 rows affected (0.00 sec) 
 
:29:  admin@127.0.0.1:[(none)]> load mysql query rules to runtime; 
Query OK, 0 rows affected (0.00 sec) 
 
:30:  admin@127.0.0.1:[(none)]> save mysql query rules to disk; 
Query OK, 0 rows affected (0.01 sec)

需要注意: select ... for update规则的rule_id必须要小于普通的select规则的rule_id,因为ProxySQL是根据rule_id的顺序进行规则匹配的。
    
再来测试下,读操作是否路由给了hostgroup_id=20的读组, 如下发现server_id为2的节点 (即slave从节点)在读组内

[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select @@server_id' 
+-------------+ 
| @@server_id | 
+-------------+ 
|         129 | 
+-------------+

读操作已经路由给读组,再看看写操作。这里以事务持久化进行测试。

[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;' 
+-------------+ 
| @@server_id | 
+-------------+ 
|         128 | 
+-------------+ 
+-------------+ 
| @@server_id | 
+-------------+ 
|         129 | 
+-------------+

显然,一切都按照预期进行。最后,如果想查看路由的信息,可查询stats库中的stats_mysql_query_digest表。
以下是该表的一个输出格式示例(和本案例无关)

[root@centos-03 ~]#  mysql -uadmin -padmin -P6032 -h127.0.0.1 --prompt 'admin>' 
........... 
 
admin>use stats; 
Database changed 
admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC; 
+----+----------+------------+-------------------------------------------------------------------------------------------------------------+ 
| hg | sum_time | count_star | digest_text                                                                                                 | 
+----+----------+------------+-------------------------------------------------------------------------------------------------------------+ 
| 10 | 35417    | 1          | drop database proxy_test                                                                                    | 
| 10 | 25534    | 1          | create table t(id int)                                                                                      | 
| 10 | 25178    | 3          | select @@server_id                                                                                          | 
| 10 | 21857    | 1          | create database proxy_test                                                                                  | 
| 20 | 20384    | 5          | select @@server_id                                                                                          | 
| 10 | 10014    | 1          | SELECT DATABASE()                                                                                           | 
| 10 | 9694     | 2          | start transaction                                                                                           | 
| 10 | 5960     | 2          | show databases                                                                                              | 
| 20 | 4416     | 2          | SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC | 
| 10 | 2982     | 1          | show tables from proxy_test                                                                                 | 
| 10 | 2822     | 2          | show databases                                                                                              | 
| 10 | 1857     | 2          | commit                                                                                                      | 
| 20 | 1215     | 1          | select @@server_id.                                                                                         | 
| 10 | 1200     | 1          | show database                                                                                               | 
| 10 | 0        | 1          | select USER()                                                                                               | 
| 10 | 0        | 11         | select @@version_comment limit ?                                                                            | 
| 10 | 0        | 5          | select @@version_comment limit ?                                                                            | 
+----+----------+------------+-------------------------------------------------------------------------------------------------------------+ 
17 rows in set (0.01 sec)

4.5 测试读写分离效果

由于读写操作都记录在proxysql的stats_mysql_query_digest表内。
为了测试读写分离的效果,可以先清空此表中之前的记录 (即之前在实现读写分配路由配置之前的记录)


下面这个命令是专门清空stats_mysql_query_digest表的  (使用"delete from stats_mysql_query_digest"  清空不掉!)

admin> SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1; 
+---+ 
| 1 | 
+---+ 
| 1 | 
+---+ 
1 row in set (0.01 sec) 
 
admin> SELECT hostgroup hg, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC; 
Empty set (0.00 sec) 
 

在mysql-proxy代理层节点,通过proxysql进行数据写入,并查看

[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from grade.hi_tb;' 
+------+----------+ 
| id   | name     | 
+------+----------+ 
|    1 | bobu     | 
|    2 | Bertram  | 
|   21 | zhongguo | 
|   22 | china    | 
|   23 | taiwan   | 
|    3 | English  | 
+------+----------+ 
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'delete from grade.hi_tb where id > 3;' 
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'insert into grade.hi_tb  values(5,"深圳"),(6,"北京"),(7,"上海");' 
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'update grade.hi_tb set name="天津" where id=2;' 
[root@centos-03 ~]# mysql -uroot -ppasswd -P6033 -h127.0.0.1 -e 'select * from grade.hi_tb;' 
+------+---------+ 
| id   | name    | 
+------+---------+ 
|    1 | bobu    | 
|    2 | 天津    | 
|    3 | English | 
|    5 | 深圳    | 
|    6 | 北京    | 
|    7 | 上海    | 
+------+---------+

在mysql-master (centos-01)主数据库和mysql-slave (centos-02)从数据库上查看

[root@centos-01 ~]# mysql -uroot -p123456 
...... 
 
mysql> select * from grade.hi_tb; 
+------+---------+ 
| id   | name    | 
+------+---------+ 
|    1 | bobu    | 
|    2 | 天津    | 
|    3 | English | 
|    5 | 深圳    | 
|    6 | 北京    | 
|    7 | 上海    | 
+------+---------+ 
6 rows in set (0.00 sec) 
 
[root@centos-02 ~]# mysql -uroot -p123456 
....... 
 
mysql> select * from grade.hi_tb; 
+------+---------+ 
| id   | name    | 
+------+---------+ 
|    1 | bobu    | 
|    2 | 天津    | 
|    3 | English | 
|    5 | 深圳    | 
|    6 | 北京    | 
|    7 | 上海    | 
+------+---------+ 
6 rows in set (0.00 sec) 
 

发现在客户端通过proxysql插件更新的数据,已经写到mysql-master主数据库上,并同步到mysql-slave从数据库上了!

最后在proxysql管理端查看读写分离

[root@centos-03 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032 
.............. 
 
:12:  admin@127.0.0.1:[(none)]> select hostgroup,username,digest_text,count_star from stats_mysql_query_digest; 
+-----------+----------+-------------------------------------------------+------------+ 
| hostgroup | username | digest_text                                     | count_star | 
+-----------+----------+-------------------------------------------------+------------+ 
| 10        | root     | update grade.hi_tb set name=? where id=?        | 4          | 
| 10        | root     | insert into grade.hi_tb values(?,?)             | 1          | 
| 10        | root     | insert into grade.hi_tb values(?,?),(?,?),(?,?) | 2          | 
| 10        | root     | insert into kevin.haha values(?,?),(?,?),(?,?)  | 1          | 
| 10        | root     | insert into grade.hi_tb values(?,李四)          | 1          | 
| 10        | root     | insert into grade.hi_tb values(?,English)       | 1          | 
| 20        | root     | select * from grade.hi_tb                       | 9          | 
| 10        | root     | delete from grade.hi_tb where id > ?            | 1          | 
| 10        | root     | insert into grade.hi_tb values(?,li)            | 2          | 
| 20        | root     | select * from grade                             | 1          | 
| 10        | root     | select @@version_comment limit ?                | 23         | 
+-----------+----------+-------------------------------------------------+------------+ 
11 rows in set (0.01 sec) 

从上述结果就可以看出proxysql实现的读写分离配置是成功的,读请求是转发到group20的读组内,写请求转发到group10的写组内!!

4.6  开启ProxySQL的Web统计功能

首先打开web功能

[root@centos-03 ~]# mysql -uadmin -padmin -h127.0.0.1 -P6032  
Welcome to the MariaDB monitor.  Commands end with ; or \g. 
Your MySQL connection id is 56 
Server version: 5.5.30 (ProxySQL Admin Module) 
 
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
:20:  admin@127.0.0.1:[(none)]> update global_variables set variable_value='true' where variable_name='admin-web_enabled'; 
Query OK, 1 row affected (0.00 sec) 
 
:20:  admin@127.0.0.1:[(none)]> LOAD ADMIN VARIABLES TO RUNTIME; 
Query OK, 0 rows affected (0.07 sec) 
 
:20:  admin@127.0.0.1:[(none)]> SAVE ADMIN VARIABLES TO DISK; 
Query OK, 35 rows affected (0.00 sec)

然后查看端口和登录web界面的用户名和密码,用户名和密码与stat账户一致:

:20:  admin@127.0.0.1:[(none)]> select * from global_variables where variable_name LIKE 'admin-web%' or variable_name LIKE 'admin-stats%'; 
+----------------------------------------+----------------+ 
| variable_name                          | variable_value | 
+----------------------------------------+----------------+ 
| admin-stats_credentials                | stats:stats    |  //账号密码 
| admin-stats_mysql_connections          | 60             | 
| admin-stats_mysql_connection_pool      | 60             | 
| admin-stats_mysql_query_cache          | 60             | 
| admin-stats_mysql_query_digest_to_disk | 0              | 
| admin-stats_system_cpu                 | 60             | 
| admin-stats_system_memory              | 60             | 
| admin-web_enabled                      | true           | 
| admin-web_port                         | 6080           |    //端口 
+----------------------------------------+----------------+ 
9 rows in set (0.00 sec)

查看web端口是否正常打开

[root@centos-03 ~]# lsof -i:6080 
COMMAND   PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME 
proxysql 3390 proxysql   44u  IPv4  27649      0t0  TCP *:6080 (LISTEN)

访问http://192.168.1.130:6080并使用stats:stats登录即可查看一些统计信息。

参考:文章


本文参考链接:https://bertram.blog.csdn.net/article/details/107936255