前言
一周以前遇到一个的故障, 我靠着过去的经验去分析问题, 这次栽的挺深的, 查错了方向, 陷进了错误的方向苦思不得其解. 本来是一个很简单的问题, 明眼人一下就能看出来的, 结果硬是分析了好几天.
所以, 一句话送给大家成功有个副作用, 就是以为过去的做法, 同样适用于未来.
SERVER.xml 介绍
前面我们主要介绍了 schema.xml 配置和 rule.xml 中的分片算法. 那么还有一个很重要的配置文件叫 server.xml. 该文件提供了系统配置, 用户和权限配置, 黑白名单配置. 它主要有以下几个标签组成.
system 标签, 这个标签主要是系统层面的配置, 这个配置的模块很多. 比如可以控制最大连接数, 慢查询日志, load data 相关配置, Session 预留内存, XA 事务等等.
user 标签, 这个标签主要是用户方面的配置, 包括管理用户和业务用户的配置, 还有权限的配置.
firewall 标签, 这个标签主要是黑白名单的配置.
实战 SERVER.xml 配置
接下来我们来实战一下, 首先我们来配置个慢查询功能.
配置项 | 配置内容 | 默认值 |
---|---|---|
enableSlowLog | 慢查询日志开关 | 默认为 0,关闭 |
slowLogBaseDir | 慢查询日志存储文件夹 | dble 根目录 / slowlogs |
slowLogBaseName | 慢查询日志存储文件名前缀 | slow-query |
flushSlowLogPeriod | 日志写盘周期,每隔这个周期,会强制将内存数据写入磁盘 | 1 秒 |
flushSlowLogSize | 日志写盘的阈值数,内存中一旦慢 SQL 条数达到这个阈值,就会强制写盘 | 1000 |
sqlSlowTime | 慢日志时间阈值,单位毫秒,一旦查询时间超过这个阈值就会被记录 | 100 |
测试慢查询功能
修改 server.xml 中的下列内容. 然后重启 dble 软件. 这里需要注意的一点就是 SERVER.xml 中的 system 标签相关的配置修改之后, 无法使用 reload @@config 进行在线重置.
- <property name="enableSlowLog">
- 1
- </property>
- <property name="slowLogBaseDir">
- ./slowlogs
- </property>
- <property name="slowLogBaseName">
- slow-query
- </property>
- <property name="flushSlowLogPeriod">
- 1
- </property>
- <property name="flushSlowLogSize">
- 1000
- </property>
- <property name="sqlSlowTime">
- 100
- </property>
然后使用 shell 造 100 万数据量.
- for i in {
- 1..1000000
- }
- do
- echo $i'|name'$[i]''>>a1.txt
- done
使用 load data 将数据导入.
- create table hash_test(
- id int not null,
- name varchar(250),
- primary key(id)
- ) engine=innodb charset=utf8;
- load data local infile '/root/a1.txt' ignore into table hash_test character set 'utf8' fields terminated by '|' lines terminated by '\n';
执行查询, 整个查询耗时 0.20 秒. 由于大于 100 毫秒, 该 SQL 将会被慢查询记录下来
- MySQL> select * from hash_test where name='name58';
- ----+--------+
- | id | name |
- +----+--------+
- | 58 | name58 |
- +----+--------+
- 1 row in set (0.20 sec)
然后我们就可以检查 slowlogs 文件夹下的 slow-query.log 文件, 文件内容就和我们 MySQL 的慢查询日志差不多.
- [root@mycat slowlogs]# tail -200f slow-query.log
- /FAKE_PATH/mysqld, Version: FAKE_VERSION. started with:
- Tcp port: 3320 Unix socket: FAKE_SOCK
- Time Id Command Argument
- # Time: 2020-01-15T17:46:14.912000Z
- # User@Host: root[root] @ [192.168.56.181] Id: 1
- # Query_time: 0.204225 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 Read_SQL: 0.000109 Prepare_Push: 0.085666 dn3_First_Result_Fetch: 0.015016 dn4_First_Result_Fetch: 0.012640 dn1_First_Result_Fetch: 0.007918 dn2_First_Result_Fetch: 0.007671 dn5_First_Result_Fetch: 0.025433 dn3_Last_Result_Fetch: 0.055441 dn4_Last_Result_Fetch: 0.055493 dn1_Last_Result_Fetch: 0.110465 dn2_Last_Result_Fetch: 0.084747 dn5_Last_Result_Fetch: 0.027631 Write_Client: 0.110779
- SET timestamp=1579081574912;
- select * from hash_test where name='name58';
以上就是慢查询的功能, 要进一步的分析, 我们还可以使用 pt-query-digest 工具.
system 还有很多配置项. 例如我们的服务端口 serverPort:8066, 管理端口 managerPort:9066, 最大连接数 maxCon, 配置为 0 表示连接数不做限制, 当设置不为 0, 创建的连接数大于 maxCon 就会报连接失败. processors:NIO 前端处理器的数量, 默认为 java 虚拟机核数. backendProcessors:NIO 后端处理器的数量, 默认 java 虚拟机核数. 还有一系列的参数这里不一一介绍, 大家可以查看官方文档对这些参数的解释.
- <property name="bindIp">
- 0.0.0.0
- </property>
- <property name="serverPort">
- 8066
- </property>
- <property name="managerPort">
- 9066
- </property>
- <property name="maxCon">
- 0
- </property>
- <property name="processors">
- 4
- </property>
- <property name="backendProcessors">
- 12
- </property>
- <property name="processorExecutor">
- 4
- </property>
- <property name="backendProcessorExecutor">
- 12
- </property>
- <property name="complexExecutor">
- 8
- </property>
- <property name="writeToBackendExecutor">
- 4
- </property>
黑白名单配置
黑白名单在 firewall 标签中配置. whitehost 代表着白名单, 而 blacklist 代表着黑名单. 白名单的配置较为简单, 只有两个选项. 一个是可以连接的主机, 另外一个就是可以连接的用户. 如果配置了黑名单, 设置了 blacklist check=true, 就开启了黑名单校验规则. 它会针对白名单连接上来的用户在做进一步的权限限制. 这里有很多 property 属性可以配置. 例如这个 demo 中的规则 selelctAllow 就是是否允许 select 语句, 这里是 false, 就代表不允许. 还有 insertAllow,updateAllow,
deleteAllow 等等.
- <firewall>
- <whitehost>
- <host host="127.0.0.1" user="root"/>
- <host host="0:0:0:0:0:0:0:1" user="root"/>
- </whitehost>
- <blacklist check="true">
- <property name="selelctAllow">false</property>
- </blacklist>
- </firewall>
我们来配置一下, 让 192.168.56.181 上连接的 root 用户不能执行查询. 在白名单中增加
<host host="192.168.56.181" user="root"/>
然后在 181 上登录执行 SQL, 会提示 The statement is unsafe SQL, reject for user 'root'. 直接就拒绝我们执行 select.
- [root@mysql5 ~]# MySQL -uroot -p123456 -h192.168.56.185 -P8066
- MySQL> select * from hash_test where id=10000;
- ERROR 3012 (HY000): The statement is unsafe SQL, reject for user 'root'
通过黑名单, 我们可以做更进一步的权限控制. 这样做的好处是把权限的粒度分的更细. 例如: 针对个人账号, 就只允许它做查询操作, 其他的 DML 和 DDL 操作权限一律不给. 针对业务账号就放开 DML 和 DDL 的权限.
用户配置
最后一块是用户的配置. 首先需要配置的是一个管理账号, 用于通过管理端口 9066 登录. 管理账号可以执行一些管理端命令. 例如 reload @@config.
- <user name="man1">
- <property name="password">654321</property>
- <property name="manager">true</property>
- </user>
然后是业务用户的配置. 业务用户是可以配置多个的. 例如当前我们配置的业务用户是 root 和 buddy, 他们都能够访问 testdb 这个逻辑库. 同时对整个逻辑库具有的权限是 0110. 这四个数字的顺序分别代表 INSERT UPDATE SELECT DELETE. 当是 0 的时候就代表没有权限, 如果是 1 就代表这有这个权限. 因此 0110 代表了对这个逻辑库下所有的表拥有 update 和 select 的权限, 而没有 insert 和 delete 的权限. 虽然在 schema 级别设置了通用的权限, 但是我们仍然可以在 < table > 标签针对具体的表设置具体的权限, 实现覆盖. 例如 tb02 这个表的权限是 1111, 就代表能对该表做所有的增删改查.
需要注意的一点是, 虽然这里我们设置了可以增删改查, 但是 drop,truncate 这样的权限是设置不了的. 而这些权限只能通过黑名单来控制.
- <user name="root">
- <property name="password">123456</property>
- <property name="schemas">testdb</property>
- <!-- table's DML privileges INSERT/UPDATE/SELECT/DELETE -->
- <privileges check="true">
- <schema name="TESTDB" dml="0110">
- <table name="tb01" dml="0000"></table>
- <table name="tb02" dml="1111"></table>
- </schema>
- </privileges>
- </user>
- <user name="buddy">
- <property name="password">123456</property>
- <property name="schemas">testdb</property>
- <property name="readOnly">false</property>
- <!-- table's DML privileges INSERT/UPDATE/SELECT/DELETE -->
- <privileges check="true">
- <schema name="TESTDB" dml="0110">
- <table name="tb01" dml="0000"></table>
- <table name="tb02" dml="1111"></table>
- </schema>
- </privileges>
- </user>
如果我们的这个账号, 需要访问多个逻辑库, 配置上也很简单, 只需要用逗号分隔开就行了.
<property name="schemas">testdb,testdb2</property>
可能有人会担心, 这个密码这样写明文的, 不是很容易被人看到吗? 这个问题不大, 我们可以配置 usingDecrypt 选项来进行加密. 在这里我们需要使用 encrypt.sh 脚本.
该脚本运行方法: sh encrypt.sh 0:{user}:{password}
- [root@mycat conf]# sh encrypt.sh 0:root:123456
- /etc/alternatives/jre_1.8.0_openjdk/bin/java -cp /dble/lib/dble*.jar com.actiontech.dble.util.DecryptUtil password=******
- GO0bnFVWrAuFgr1JMuMZkvfDNyTpoiGU7n/Wlsa151CirHQnANVk3NzE3FErx8v6pAcO0ctX3xFecmSr+976QA==
我们把这一串加密字符串设置进去. 然后 reload @@config 就可以了.
- <user name="root">
- <property name="password">GO0bnFVWrAuFgr1JMuMZkvfDNyTpoiGU7n/Wlsa151CirHQnANVk3NzE3FErx8v6pAcO0ctX3xFecmSr+976QA==</property>
- <property name="schemas">testdb</property>
- <property name="usingDecrypt">1</property>
- </user>
此时使用 123456 能够正常登录, 但是后台 xml 配置文件的已经设置成了密文, 增强了密码安全性.
- [root@mysql5 ~]# MySQL -uroot -p123456 -h192.168.56.185 -P8066
- 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 3
- Server version: 5.6.29-dble-2.19.09.0-fd62e7a27a561169acabc11df32b2f0d13a0b922-20191121135714 dble Server (ActionTech)
- Copyright (c) 2000, 2019, 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.
后记
今天的 server.xml 我们只是介绍了一些基本常用的功能, 更多高级用法可以参考官方文档, 官方文档对每个参数都做了具体的说明. 日常在运行中遇到一些问题或者优化, 可以通过调整 dble 系统参数来解决. 同时黑白名单也进一步方便我们进行细粒度的权限控制.
来源: https://www.qcloud.com/developer/article/1575625