市场分析
如题,我们首先分析市场上已有的同类产品:
- 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教程
- MySQL中间件之ProxySQL(1):简介和安装
- MySQL中间件之ProxySQL(2):初试读写分离
- MySQL中间件之ProxySQL(3):Admin管理接口
- MySQL中间件之ProxySQL(4):多层配置系统
- MySQL中间件之ProxySQL(5):线程、线程池、连接池
- MySQL中间件之ProxySQL(6):管理后端节点
- MySQL中间件之ProxySQL(7):详述ProxySQL的路由规则
- MySQL中间件之ProxySQL(8):SQL语句的重写规则
- MySQL中间件之ProxySQL(9):ProxySQL的查询缓存功能
- MySQL中间件之ProxySQL(10):读写分离方法论
- MySQL中间件之ProxySQL(11):链式规则( flagIN 和 flagOUT )
- MySQL中间件之ProxySQL(12):禁止多路路由
- MySQL中间件之ProxySQL(13):ProxySQL集群
- MySQL中间件之ProxySQL(14):ProxySQL+PXC
- MySQL中间件之ProxySQL(15):ProxySQL代理MySQL组复制
ProxySQL库、表介绍
https://github.com/sysown/proxysql/wiki/Main-(runtime)