MySQL9 docker容器配置SSL

Nov 10, 2024 in UNDEFINED
notion
4 min read

Table of Content

最近写个小东西用到了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 = '+08: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用户密码等。
1. 如果初始化时/var/lib/mysql 不为空，则会直接报错，所以不把ssl证书挂载在/var/lib/mysql中。
2. 如果是后续再执行该脚本，则不会执行初始化。这意味着如果/var/lib/mysql的关键数据在的话，不会重新创建数据库、root用户、也不会修改root密码。也就是说，后续你稍微改了脚本中的 MYSQL_DATABASE和 MYSQL_ROOT_PASSWORD 环境变量也不会生效。
ssl是否配置成功需要 docker run -it 查看启动日志来确定。
设置 default-time_zone = '+08:00' 是为了让mysql的时间和服务器时间一致，否则会有8小时的时差。

时区设置补充

这里有几个容易混淆的概念：

容器系统时区（date 命令、日志时间）
MySQL 全局时区（@@global.time_zone）
当前连接的会话时区（@@session.time_zone，很多函数比如 NOW() 会受它影响）

default-time_zone = '+08:00' 配置的是 mysqld 启动后的全局默认时区，新建连接会继承它。这里我建议使用 '+08:00' 这种固定偏移量写法，优点是不依赖 MySQL 时区表。

如果写成 Asia/Shanghai 这种命名时区，则需要先导入 MySQL 时区表，否则可能出现下面的问题：

SET time_zone = 'Asia/Shanghai' 报 Unknown or incorrect time zone
CONVERT_TZ(..., 'UTC', 'Asia/Shanghai') 返回 NULL

可以用下面命令确认实际时区配置是否生效：

docker exec -it mysql mysql -uroot -pYOUR_PASSWORD -e "
SHOW VARIABLES LIKE '%time_zone%';
SELECT
    CONNECTION_ID() AS conn_id,
    @@session.time_zone AS session_time_zone,
    @@global.time_zone AS global_time_zone,
    @@system_time_zone AS system_time_zone,
    NOW() AS now_ts,
    UTC_TIMESTAMP() AS utc_ts,
    TIMESTAMPDIFF(HOUR, UTC_TIMESTAMP(), NOW()) AS offset_hours
"

如果只是临时排查问题，也可以只修改当前会话：

SET time_zone = '+08:00';

还有一个很关键的区别：

DATETIME 不保存时区，写进去什么就存什么（但是 NOW()/CURRENT_TIMESTAMP 生成值时仍然受会话时区影响）
TIMESTAMP 会在写入/读取时按会话时区做转换（内部按 UTC 存储）

建表

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?;

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;

MySQL Daemon配置

时区设置补充

建表

macOS连接

Rust sqlx 连接：

Grafana配置数据源

参考文档