参考文献

  • MySQL 8 Cookbook

使用mysql_config_editor进行无密码认证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql_config_editor --help
mysql_config_editor Ver 1.0 Distrib 5.7.24, for osx11.1 on x86_64
Copyright (c) 2012, 2018, 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.

MySQL Configuration Utility.
Usage: mysql_config_editor [program options] [command [command options]]
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  -?, --help          Display this help and exit.
  -v, --verbose       Write more information.
  -V, --version       Output version information and exit.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
verbose                           FALSE

Where command can be any one of the following :
       set [command options]     Sets user name/password/host name/socket/port
                                 for a given login path (section).
       remove [command options]  Remove a login path from the login file.
       print [command options]   Print all the options for a specified
                                 login path.
       reset [command options]   Deletes the contents of the login file.
       help                      Display this usage/help information.

  • 使用mysql_config_editor创建.mylogin.cnf文件

    1
    2
    3
    4
    # mysql_config_editor set --login-path=root_local --host=localhost --user=root --password
    Enter password:

    # mysql --login-path=root_local

使用X509设置加密连接

  • 验证数据目录中的文件,更新my.cnf,重启服务器并检查与SSL有关的变量

    1
    2
    3
    4
    5
    6
    7
    8
    9
    ➜ ll -htr |grep pem
    -rw-------    1 _mysql  _mysql   1.6K  9 17  2021 ca-key.pem
    -rw-r--r--    1 _mysql  _mysql   1.1K  9 17  2021 ca.pem
    -rw-------    1 _mysql  _mysql   1.6K  9 17  2021 server-key.pem
    -rw-r--r--    1 _mysql  _mysql   1.1K  9 17  2021 server-cert.pem
    -rw-------    1 _mysql  _mysql   1.6K  9 17  2021 client-key.pem
    -rw-r--r--    1 _mysql  _mysql   1.1K  9 17  2021 client-cert.pem
    -rw-------    1 _mysql  _mysql   1.6K  9 17  2021 private_key.pem
    -rw-r--r--    1 _mysql  _mysql   452B  9 17  2021 public_key.pem
    1
    2
    3
    4
    5
    sudo vim /etc/my.cnf
    [mysqld]
    ssl-ca=/var/lib/mysql/ca.pem
    ssl-cert=/var/lib/mysql/server-cert.pem
    ssl-key=/var/lib/mysql/server-key.pem
    1
    sudo systemctl restart mysqld
    1
    mysql> show variables like '%ssl%';

  • 将client-key.pem,client-cert.pem文件从服务器的数据目录复制到客户端

  • 通过传递--ssl-cert和--ssl-key选项连接到服务器

    1
    mysql --ssl-cert=client.pem --ssl-key=client-key.pem -h <hostname>

  • 强制用户只能通过X509连接

    1
    mysql> alter user 'username'@'%' require X509;