MySQL-ProxySQL读写分离连接池负载均衡分库分表故障切换查询重写流量镜像SQL审计自动重连自动下线在线配置路由引擎Query Cache缓存主从复制架构高可用MySQL中间件ProxySQL

2018-11-22 830

市场分析

如题，我们首先分析市场上已有的同类产品：

MySQL Route：是现在MySQL官方Oracle公司发布出来的一个中间件。
Atlas：是由奇虎360公发的基于MySQL协议的数据库中间件产品，它在MySQL官方推出的MySQL-Proxy 0.8.2版本的基础上，修改了若干Bug，并增加了很多功能特性。目前该产品在360内部得到了广泛应用。
DBProxy：是由美团点评公司技术工程部DBA团队（北京）开发维护的一个基于MySQL协议的数据中间层。它在奇虎360公司开源的Atlas基础上，修改了部分bug，并且添加了很多特性。
Cobar：是阿里巴巴B2B开发的关系型分布式系统，管理将近3000个MySQL实例。在阿里经受住了考验，后面由于作者的走开的原因cobar没有人维护了，阿里也开发了tddl替代cobar。
MyCAT：是社区爱好者在阿里cobar基础上进行二次开发，解决了cobar当时存在的一些问题，并且加入了许多新的功能在其中。目前MyCAT社区活跃度很高，目前已经有一些公司在使用MyCAT。总体来说支持度比较高，也会一直维护下去。

ProxySQL

今天给大家介绍的一款是proxySQL，ProxySQL是使用C++语言开发的，强悍性能截图：

其官网有个ProxySQL1.3.2与MariaDB MaxScale 2.0.3、HAProxy1.7、NGINX1.11.8、MySQL Router 2.0等ProxySQL产品对比信息，可以看出其优秀值得使用。至少有很多大公司巨兽已经在使用ProxySQL，比如亚马逊数据库集群自动切换。

ProxySQL官网文档也是很齐全，MySQL5.7结合ProxySQL 案例讲述的也很清晰，现在先简单介绍下其特色功能点：

查询缓存
查询路由
故障转移
在线配置立刻生效无需重启
应用层代理
跨平台
高级拓展支持
防火墙

通过上述，我们可以看到ProxySQL可以做许多事情，已经不仅仅是纯粹的MySQL读写分离，其实我们通过后面所述结合业务发散，ProxySQL还可以支持以下高级功能：

读写分离
数据库集群、分片
分库分表
主从切换
SQL审计
连接池多路复用
负载均衡
查询重写
流量镜像
自动重连
自动下线
高可用
.........

高可用架构

ProxySQL实战

准备

要实现MySQL的读写分离，首先要准备好MySQL主从复制架构（请SA协助或者自己百度），比如：
主（写）服务器：10.0.16.1
一般都是两台从（读）服务器：10.0.16.2、10.0.17.2
除主从复制架构外，还需要准备一台布置中间件的主机，这里是10.0.16.88

一共4台机器，下面是我的主从配置，大家可以参考下：

10.0.16.1主服务器中/etc/my.cnf中[mysqld]配置段配置：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log_bin=/var/lib/mysql/binlog
server_id=1
innodb_file_per_table=ON
skip_name_resolve=ON
sync_binlog=1
innodb_flush_log_at_trx_commit=1
relay_log=/var/lib/mysql/slavelog

10.0.16.2从服务器中/etc/my.cnf中[mysqld]配置段配置：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=21
relay_log=/var/lib/mysql/slavelog
set @@global.read_only=ON
sync_binlog=1
innodb_flush_log_at_trx_commit=1

10.0.17.2从服务器中/etc/my.cnf中[mysqld]配置段配置：

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
innodb_file_per_table=ON
skip_name_resolve=ON
server_id=22
relay_log=/var/lib/mysql/slavelog
set @@global.read_only=ON
sync_binlog=1
innodb_flush_log_at_trx_commit=1

安装

wget https://github.com/sysown/proxysql/releases/download/v1.4.12/proxysql_1.4.12-ubuntu16_amd64.deb
sudo dpkg -i proxysql_1.4.12-ubuntu16_amd64.deb

操作

版本：sudo proxysql --version
启动：sudo service proxysql start
暂停：sudo service proxysql stop
重启：sudo service proxysql restart
状态：sudo service proxysql status

概念

客户端：6033端口
管理端：6032端口

配置文件

//不推荐
/etc/proxysql.cnf

控制台

上述之所以不推荐，是因为我们可以通过ProxySQL控制台在线修改配置，无需重启，立即生效。

mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> ' --default-auth=mysql_native_password

设置SQL日志记录【ProxySQL】

SET mysql-eventslog_filename='queries.log';

添加主从【ProxySQL】

insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(1,'10.0.16.1',3306,1,'主库');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'10.0.16.2',3306,9,'从库1');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(2,'10.0.17.2',3306,1,'从库2');

查看主从【ProxySQL】

Admin> select * from mysql_servers;
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| hostgroup_id | hostname    | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment               |
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
| 1            | 10.0.16.1 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | 主库                  |
| 2            | 10.0.16.2 | 3306 | ONLINE | 9      | 0           | 1000            | 0                   | 0       | 0              | 从库1                 |
| 2            | 10.0.17.2 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              | 从库2                 |
+--------------+-------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------------------+
4 rows in set (0.00 sec)

创建主从账号【MySQL】

CREATE USER 'proxysql'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' WITH GRANT OPTION;

添加主从账号【ProxySQL】

insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proxysql','123456',1,1);

查看主从账号【ProxySQL】

Admin> select * from mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
| proxysql | 123456   | 1      | 0       | 1                 | NULL           | 0             | 1                      | 0            | 1       | 1        | 10000           |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
1 row in set (0.00 sec)

创建监控账号【MySQL】

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT SELECT ON *.* TO 'monitor'@'%' WITH GRANT OPTION;

添加监控账号【ProxySQL】

set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor';

查看监控账号【ProxySQL】

SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';


//也可以这样快速定位
Admin> select @@mysql-monitor_username;
+--------------------------+
| @@mysql-monitor_username |
+--------------------------+
| monitor                  |
+--------------------------+
1 row in set (0.01 sec)

Admin> select @@mysql-monitor_password;
+--------------------------+
| @@mysql-monitor_password |
+--------------------------+
| monitor                  |
+--------------------------+
1 row in set (0.00 sec)

检测监控【ProxySQL】

检测上述配置是否正确：connect_error为NULL则正确。

Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+-------------+------+------------------+-------------------------+---------------+
| hostname    | port | time_start_us    | connect_success_time_us | connect_error |
+-------------+------+------------------+-------------------------+---------------+
| 10.0.16.1   | 3306 | 1543224623330044 | 1067                    | NULL          |
| 10.0.16.2   | 3306 | 1543224622707711 | 1094                    | NULL          |
| 10.0.17.2   | 3306 | 1543224563518239 | 1180                    | NULL          |
+-------------+------+------------------+-------------------------+---------------+
3 rows in set (0.01 sec)

Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+-------------+------+------------------+----------------------+------------+
| hostname    | port | time_start_us    | ping_success_time_us | ping_error |
+-------------+------+------------------+----------------------+------------+
| 10.0.16.1   | 3306 | 1543224683246221 | 169                  | NULL       |
| 10.0.16.2   | 3306 | 1543224683106758 | 194                  | NULL       |
| 10.0.17.2   | 3306 | 1543224673230502 | 268                  | NULL       |
+-------------+------+------------------+----------------------+------------+
3 rows in set (0.00 sec)

读写映射【ProxySQL】

这里配置主从自动切换：互为主从，自动切换，保证高可用。

如果你没有做到互为主从，请跳过此项。

insert into mysql_replication_hostgroups values(1,2,'高可用');

Admin> select * from mysql_replication_hostgroups;
+------------------+------------------+-----------+
| writer_hostgroup | reader_hostgroup | comment   |
+------------------+------------------+-----------+
| 1                | 2                | 高可用    |
+------------------+------------------+-----------+
1 row in set (0.00 sec)

读写路由【ProxySQL】

读写分离规则，正则写法，也支持全匹配SQL，同时支持按照MySQL账号、库名、表名、客户IP等特征自动识别DB，从而达到分库分表、读写分离。ProxySQL的核心！

insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,log,apply)values(1,1,'^UPDATE',1,1,1);
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,log,apply)values(2,1,'^SELECT',2,1,1);

SQL重写：分库

原来查询MySQL专业学生的SQL语句：

select * from it_db.stu where zhuanye='MySQL' and xxx;

分库后，该SQL语句需要重写为：

select * from MySQL.stu where 1=1 and xxx;

我们只需要插入一条SQL路由重写规则：

insert into mysql_query_rules(rule_id,active,apply,destination_hostgroup,match_pattern,replace_pattern) 
values (1,1,1,20,"^(select.*?from) it_db\.(.*?) where zhuanye=['""](.*?)['""] (.*)$","\1 \3.\2 where 1=1 \4");

大家可以看到，直接通过强大的正则配置SQL路由重写，后端程序小白无需任何感知即可实现数据库分库分片，另外集群等原理类似。

查看路由【ProxySQL】

检测上述配置的路由规则。

Admin> select * from mysql_query_rules \G;
*************************** 1. row ***************************
              rule_id: 1
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^UPDATE
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 1
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
                  log: 1
                apply: 1
              comment: NULL
*************************** 2. row ***************************
              rule_id: 2
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: ^SELECT
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 2
            cache_ttl: NULL
            reconnect: NULL
              timeout: NULL
              retries: NULL
                delay: NULL
    next_query_flagIN: NULL
       mirror_flagOUT: NULL
     mirror_hostgroup: NULL
            error_msg: NULL
               OK_msg: NULL
          sticky_conn: NULL
            multiplex: NULL
                  log: 1
                apply: 1
              comment: NULL
2 rows in set (0.01 sec)

配置生效【ProxySQL】

//加载到内存
load mysql users to runtime;
load mysql servers to runtime;
load mysql query rules to runtime;
load mysql variables to runtime;
load admin variables to runtime;

//永久生效
save mysql users to disk;
save mysql servers to disk;
save mysql query rules to disk;
save mysql variables to disk;
save admin variables to disk;

PHP、JAVA、CLI端使用

*********程序配置参数记得更改：弃用直连MYSQL改为ProxySQL代理连接*********

ProxySQL 6033端口正好是MySQL 3306端口的反转！

后端程序PDO直接连接此DSN，一切分发路由由ProxySQL来代理，对于后端小白来说无需关心如此复杂的DBA幕后故事也可以实现MYSQL读写分离、分库分表高可用！

mysql -uproxysql -p123456 -h127.0.0.1 -P6033 --default-auth=mysql_native_password

验证【ProxySQL】

Admin> select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 1       | 2    |
| 2       | 2160 |
+---------+------+
2 rows in set (0.00 sec)


Admin> select * from stats_mysql_query_digest;
+-----------+------------+----------+--------------------+-------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest             | digest_text                                                                         | count_star | first_seen | last_seen  | sum_time | min_time | max_time |
+-----------+------------+----------+--------------------+-------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
| 1         | master     | proxysql | 0x48407E0543261BAF | UPDATE `sys_user_base` SET last_login_time=?,pw_grade=? WHERE `user_id` = ?  | 1          | 1543218281 | 1543218281 | 269595   | 269595   | 269595   |
| 2         | master     | proxysql | 0x7B56979CCBF5FE63 | SELECT `value` FROM `ent_apiceshiqiye_config` WHERE `type` = ? LIMIT ?       | 4          | 1543220121 | 1543220171 | 1021     | 157      | 446      |
+-----------+------------+----------+--------------------+-------------------------------------------------------------------------------------+------------+------------+------------+----------+----------+----------+
2 rows in set (0.00 sec)



select * from stats_mysql_query_digest_reset;//清空日志


Admin> select * from stats_mysql_commands_counters where Total_cnt>0;
+--------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| Command      | Total_Time_us | Total_cnt | cnt_100us | cnt_500us | cnt_1ms | cnt_5ms | cnt_10ms | cnt_50ms | cnt_100ms | cnt_500ms | cnt_1s | cnt_5s | cnt_10s | cnt_INFs |
+--------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
| CREATE_TABLE | 177478        | 2         | 0         | 0         | 0       | 0       | 0        | 1        | 0         | 1         | 0      | 0      | 0       | 0        |
| SELECT       | 9537803       | 2175      | 15        | 1820      | 187     | 73      | 7        | 33       | 13        | 25        | 1      | 1      | 0       | 0        |
| UPDATE       | 445145        | 2         | 0         | 0         | 0       | 0       | 0        | 0        | 0         | 2         | 0      | 0      | 0       | 0        |
| SHOW         | 542898        | 124       | 0         | 0         | 22      | 97      | 2        | 0        | 1         | 2         | 0      | 0      | 0       | 0        |
+--------------+---------------+-----------+-----------+-----------+---------+---------+----------+----------+-----------+-----------+--------+--------+---------+----------+
4 rows in set (0.00 sec)

注意【ProxySQL】

//选表
show tables from stats;

显示所有数据库【ProxySQL】

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)

显示所有数据表：main【ProxySQL】

此库对比下面的disk库，除了多了runtime_开头的表意外，其余表数量、结构、内容是完全一样的，可以粗暴认为是其拷贝，其实这是由ProxySQL三层架构达到修改配置在线生效无需重启决定的：

disk库是永久保存到磁盘中的信息；
main库是内存中运行的信息，其中runtime_系列表代表当前系统中正在使用的配置信息。

下述所有表的CRUD和使用MYSQL感觉一样，无需重复介绍，见名知意。

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.00 sec)

显示所有数据表：disk【ProxySQL】

用于将配置持久化到磁盘上。配置持久化后，下次重启ProxySQL时就会读取这些已被持久化的配置。

Admin> show tables from disk;
+------------------------------------+
| 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                   |
| scheduler                          |
+------------------------------------+
10 rows in set (0.01 sec)

显示所有数据表：stats【ProxySQL】

这个数据库包含了ProxySQL收集的关于其内部功能的指标。通过这个数据库，你可以知道触发某个计数器的频率，途经ProxySQL的查询执行次数等等。

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_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.00 sec)

显示所有数据表：monitor【ProxySQL】

Admin> show tables from monitor;
+------------------------------------+
| tables                             |
+------------------------------------+
| mysql_server_connect_log           |//MySQL表连接日志，connect_error为NULL则成功
| mysql_server_group_replication_log |//MySQL主从复制日志
| mysql_server_ping_log              |//MySQL表PING日志，connect_error为NULL则成功
| mysql_server_read_only_log         |//MySQL从库read_only值监控日志，以备自动切换主从状态
| mysql_server_replication_lag_log   |//MySQL服务主从延迟的检测
+------------------------------------+
5 rows in set (0.00 sec)

示所有数据表：stats_history【ProxySQL】

Admin> show tables from stats_history;
+------------------------+
| tables                 |
+------------------------+
| mysql_connections      |
| mysql_connections_day  |
| mysql_connections_hour |
| mysql_query_cache      |
| mysql_query_cache_day  |
| mysql_query_cache_hour |
| system_cpu             |
| system_cpu_day         |
| system_cpu_hour        |
| system_memory          |
| system_memory_day      |
| system_memory_hour     |
+------------------------+
12 rows in set (0.02 sec)

ProxySQL教程

ProxySQL库、表介绍

https://github.com/sysown/proxysql/wiki/Main-(runtime)

微信关注我们

原文链接：https://my.oschina.net/cart/blog/2907232

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

对 React 组件进行单元测试（unit testing）

在这里说一下前端开发的一个特点是更多的会涉及用户界面，当开发规模达到一定程度时，几乎注定了其复杂度会成倍的增长。无论是在代码的初始搭建过程中，还是之后难以避免的重构和修正bug过程中，常常会陷入逻辑难以梳理、无法掌握全局关联的境地。而单元测试作为一种“提纲挈领、保驾护航”的基础手段，为开发提供了“围墙和脚手架”，可以有效的改善这些问题。作为一种经典的开发和重构手段，单元测试在软件开发领域被广泛认可和采用；前端领域也逐渐积累起了丰富的测试框架和最佳实践。本文将按如下顺序进行说明: I. 单元测试简介 II. React 单元测试中用到的工具 III. 用测试驱动 React 组件重构 IV. React 单元测试常见案例 I. 单元测试简介单元测试（unit testing），是指对软件中的最小可测试单元进行检查和验证。简单来说，单元就是人为规定的最小的被测功能模块。单元测试是在软件开发过程中要进行的最低级别的测试活动，软件的独立单元将在与程序的其他部分相隔离的情况下进行测试。测试框架测试框架的作用是提供一些方便的语法来描述测试用例，以及对用例进行分组。断言(asser...

2018-11-22

704

可能看到这个标题，读者会问：要修改日志的级别，不是直接修改log.xxx就好了吗？为何要搞那么复杂呢？所以，先说一下场景，为什么要通过TurboFilter去动态的修改日志级别。我们在使用Java开发各种项目的时候必然的会引入很多框架，这些框架通过堆叠的方式完成所要提供的业务服务（一个服务请求在进入后会在这些框架中兜一圈，然后返回结果），当一个比较底层的框架在处理过程中抛出了异常之后，这个异常会不断的向上传递。这个时候，有的框架直接throw，继续向上抛，而有的在throw之前还会自己打印一下error日志，这就导致了当出现异常的时候，往往会出现一连串类似的错误日志记录。如果对接了错误日志告警，就会出现重复告警的现象。为了解决类似这样的问题，修改源码重新编译最直接，但是不可取。所以希望可以有更好的手段去控制这些已经被编码固化的日志打印信息。当我们使用Logback的时候，TurboFilter就是解决该问题的工具之一。 TurboFIlter不同于之前在[《Logback中如何自定义灵活的日志过滤规则》]一文中介绍的那些通过ch.qos.logback.core.filter.Filt...