最近写个小东西用到了MySQL,折腾下怎么给MySQL配置SSL
MySQL Daemon配置
#rm -rf /var/lib/mysql
docker stop mysql
mkdir -p /var/lib/mysql /etc/mysql/conf.d
cat > /etc/mysql/conf.d/ssl.cnf <<EOF
[mysqld]
default-time_zone = '+8:00'
ssl_ca=/etc/mysql/ssl/ca.cer
ssl_cert=/etc/mysql/ssl/arloor.dev.cer
ssl_key=/etc/mysql/ssl/arloor.dev.key
require_secure_transport=ON
EOF
docker run -d --rm --name mysql \
--network host \
-v /var/lib/mysql:/var/lib/mysql \
-v /etc/mysql/conf.d:/etc/mysql/conf.d \
-v /root/.acme.sh/arloor.dev:/etc/mysql/ssl \
-e MYSQL_DATABASE=test \
-e MYSQL_ROOT_PASSWORD=xxxxxx \
docker.io/library/mysql:9.1
解释:
- MySQL 数据文件在
/var/lib/mysql
- 在
/etc/mysql/conf.d/ssl.cnf
中指定了 acme.sh 生成的ssl证书,并强制要求客户端ssl连接 - 如果想让mysqld自己生成自签名的证书,不指定
ssl_ca
、ssl_cert
和ssl_key
即可。参见Automatic SSL and RSA File Generation。记得保留require_secure_transport=ON
一些坑点:
- MySQL docker容器只能访问特定的文件夹(SELinux和ApkArmor机制),例如
/var/lib/mysql
和/etc/mysql
。如果将SSL证书放在别的地方,会报错Unable to get private key from xxx
。这个脚本把ssl证书放在了/etc/mysql
。 - 挂载了宿主机的
/var/lib/mysql
。在第一次运行本脚本时,该文件夹为空,此时dockerfile会执行初始化操作,例如创建数据库、创建root用户、设置root用户密码等。- 如果初始化时
/var/lib/mysql
不为空,则会直接报错,所以不把ssl证书挂载在/var/lib/mysql
中。 - 如果是后续再执行该脚本,则不会执行初始化。这意味着如果
/var/lib/mysql
的关键数据在的话,不会重新创建数据库、root用户、也不会修改root密码。也就是说,后续你稍微改了脚本中的MYSQL_DATABASE
和MYSQL_ROOT_PASSWORD
环境变量也不会生效。
- 如果初始化时
- ssl是否配置成功需要
docker run -it
查看启动日志来确定。 - 设置
default-time_zone = '+8:00'
是为了让mysql的时间和服务器时间一致,否则会有8小时的时差。
建表
docker exec mysql sh -c '
mysql -pYOUR_PASSWORD -e "
use test
DROP TABLE IF EXISTS rank_record;
CREATE TABLE rank_record (
id BIGINT NOT NULL AUTO_INCREMENT,
hot_rank_score DOUBLE NOT NULL,
inner_code VARCHAR(255) NOT NULL,
his_rank_change_rank INT NOT NULL,
market_all_count INT NOT NULL,
calc_time DATETIME NOT NULL,
his_rank_change INT NOT NULL,
src_security_code VARCHAR(255) NOT NULL,
\`rank\` INT NOT NULL,
hour_rank_change INT NOT NULL,
rank_change INT DEFAULT NULL,
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE INDEX idx_inner_code_calc_time (inner_code, calc_time)
);
show tables
"
'
macOS连接
brew install mysql-client
echo 'export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"' >> ~/.zshrc
export PATH="/opt/homebrew/opt/mysql-client/bin:$PATH"
mysql test -h xxxx.com -u root --password=xxxxxx --ssl-mode=REQUIRED
## 执行 SHOW SESSION STATUS LIKE 'Ssl_cipher'; 或 \s; 确认ssl已激活
Rust sqlx 连接:
let pool: sqlx::Pool<sqlx::MySql> = MySqlPoolOptions::new()
.max_connections(20)
// .connect("mysql://root:xxxxxxx@xxxxxx.com:3306/test?ssl-mode=Required&timezone=%2B08:00") // timezone参数见https://github.com/launchbadge/sqlx/pull/3418/files。
.connect_with(
MySqlConnectOptions::new()
.host("xxxx.com")
.username("root")
.password("xxxxxxx")
.database("test")
.ssl_mode(MySqlSslMode::Required)
.timezone(Some(String::from("+08:00"))), // 默认是UTC,这里改成+08:00
)
.await?;
用chrono的什么时间类型表示MySQL的DATETIME呢?这个问题在sqlx的文档中并没有明确说明。我的结论是使用NaiveDateTime。
首先mysql的DATETIME是时区无关的,就是说我insert什么,写进去和查出来的结果就是什么,这和chrono的NaiveDateTime就一致了。
如果我们使用 DateTime<Local>
映射MySQL的DATETIME,会遇到一个问题:
可以看到它做了一个很大胆的假设:DateTime<Local>
需要转换成 DateTime<Utc>
再存到数据库中。对北京时区来说,就是减了8小时,具体表现就是我插入一个 14:00:00
的 DateTime<Local>
, 数据库里村存的是 06:00:00
。
相同的事情在decode时候也会发生,sqlx会将数据库中的时间当作 DateTime<Utc>
,转换成 DateTime<Local>
,这样就会多加8小时。——这样一来一回,只在sqlx中使用是没问题的,但是在多个服务共同使用,特别是技术栈不同的系统中就容易出问题了。
我们的解决办法是,不要使用 DateTime<Local>
而是使用 NaiveDatetime
,它和mysql的Datetime一样都是没有时区的,所以也不存在转换。只需要在代码中调用下 chrono::Local::now().naive_local()
就可以了。
Grafana配置数据源
核心是要打开 With CA Cert
,并把 ssl_ca=
指定的ca证书内容贴在下面,否则Grafana不会尝试使用ssl连接,就会被mysql服务端拒绝。
查询语句:
SELECT
AVG(`rank`) as value,
src_security_code as metric,
$__timeGroupAlias(calc_time, '10m')
FROM
rank_record
WHERE
$__timeFilter(calc_time)
and src_security_code in (
select
distinct src_security_code
from
(
select
src_security_code,
max(`rank`) - min(`rank`) as rank_change
from
rank_record
WHERE
$__timeFilter(calc_time)
group by
src_security_code
order by
rank_change desc
limit
10
) as stock_top_n
)
GROUP BY
metric,
time
ORDER BY
time
对应的sql语句:
SELECT AVG(`rank`) as value,
src_security_code as metric,
CONVERT_TZ(from_unixtime(cast(cast(UNIX_TIMESTAMP(calc_time) / (600) as signed) * 600 as signed)), 'UTC',
'+08:00') as time
FROM rank_record
WHERE calc_time BETWEEN now() - interval 24 hour AND now()
and src_security_code in (select distinct src_security_code
from (select src_security_code, max(`rank`) - min(`rank`) as rank_change
from rank_record
WHERE calc_time BETWEEN now() - interval 24 hour AND now()
group by src_security_code
order by rank_change desc
limit 10) as stock_top_n)
GROUP BY metric, time
ORDER BY time;