1. 连接参数
1.1. activate_all_roles_on_login
| Property | Value |
|---|---|
| Command-Line Format | --activate-all-roles-on-login |
| Introduced | 8.0.2 |
| System Variable | [activate_all_roles_on_login](server-administration.html#sysvar_activate_all_roles_on_login) |
| Scope | Global |
| Dynamic | Yes |
| SET_VAR Hint Applies | No |
| Type | boolean |
| Default Value | OFF |
::: alert-warning
此参数在版本8.0.2引入，是一个可以动态调整的global级参数，默认值为OFF。此参数用于控制在账户登录时是否激活已经授予的角色，如果为ON则授予的角色会被激活，设置为OFF时只能通过SET DEFAULT ROLE显式激活用户角色。activate_all_roles_on_login设置只在账户登录或者开始执行存储过程时生效，如果想更改session的role需要执行SET ROLE语句。
:::
- 范例:
1.创建测试角色
mysql> create role test2_read;
Query OK, 0 rows affected (0.02 sec)
- 赋权角色
mysql> grant select on dsg.* to 'test2_read';
Query OK, 0 rows affected (0.01 sec)
3.创建测试用户
mysql> create user 'read2'@'%' identified by '1234';
Query OK, 0 rows affected (0.01 sec)
- 角色赋予用户
mysql> grant test2_read to 'read2'@'%';
Query OK, 0 rows affected (0.02 sec)
5.刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
6.检测是否能够查看DSG 数据库
[root@mysql2 ~]# mysql -u read2 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.15 MySQL Community Server - GPL
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.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
mysql>
mysql> show grants for 'read2'@'%';
+---------------------------------------+
| Grants for read2@% |
+---------------------------------------+
| GRANT USAGE ON *.* TO `read2`@`%` |
| GRANT `test2_read`@`%` TO `read2`@`%` |
+---------------------------------------+
2 rows in set (0.00 sec)
mysql> select current_role();
+----------------+
| current_role() |
+----------------+
| NONE |
+----------------+
- 设置set_default_role
set default role all to 'read2'@'%';`
mysql> select current_role();
+------------------+
| current_role() |
+------------------+
| `test2_read`@`%` |
+------------------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| dsg |
| information_schema |
+--------------------+
2 rows in set (0.00 sec)
::: alert-warning
此例说明:activate_all_roles_on_login 控制角色是否生效.
:::
1.2. automatic_sp_privileges
| Property | Value |
|---|---|
| System Variable | [automatic_sp_privileges](server-administration.html#sysvar_automatic_sp_privileges) |
| Scope | Global |
| Dynamic | Yes |
| SET_VAR Hint Applies | No |
| Type | boolean |
| Default Value | TRUE |
该参数控制着server是否自动分配execute和alter权限给创建routine的用户。 默认为1，自动赋权
1.3. auto_generate_certs
| Property | Value |
|---|---|
| Command-Line Format | --auto-generate-certs[={OFF|ON}] |
| System Variable | [auto_generate_certs](server-administration.html#sysvar_auto_generate_certs) |
| Scope | Global |
| Dynamic | No |
| SET_VAR Hint Applies | No |
| Type | boolean |
| Default Value | ON |
自动认证:当服务启动后服务程序会自动产生server ,client 认证文件,不需要再指定-ssl 选项
例如:一下插件为自动生成,不需要手动指定.
sha256_password_auto_generate_rsa_keys and caching_sha2_password_auto_generate_rsa_keys
系统变量是相关的，但它控制使用SSL进行安全连接所需的SSL证书和密钥文件的自动生成。
1.4. back_log
| Property | Value |
|---|---|
| System Variable | [back_log](server-administration.html#sysvar_back_log) |
| Scope | Global |
| Dynamic | No |
| SET_VAR Hint Applies | No |
| Type | integer |
| Default Value | -1 (autosized) |
| Minimum Value | 1 |
| Maximum Value | 65535 |
baklog 选项适用于在非常短的时间内有大量连接.进行指定有多少连接可以进来.
- 修改back_log参数值:由默认的50修改为500.（每个连接256kb, 占用：125M）
back_log=500
查看mysql 当前系统默认back_log值，命令：
show variables like 'back_log';
back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说，如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源。将会报：
unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时.
back_log值不能超过TCP/IP连接的侦听队列的大小。若超过则无效，查看当前系统的TCP/IP连接的侦听队列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog，目前系统为1024。对于Linux系统推荐设置为大于512的整数。
查看当前系统监听队列:
cat /proc/sys/net/ipv4/tcp_max_syn_backlog
修改系统内核参数，可以编辑/etc/sysctl.conf去调整它。
如：
net.ipv4.tcp_max_syn_backlog = 2048
生效
sysctl -p
1.5. bind_address
| Property | Value |
|---|---|
| Command-Line Format | --bind-address=addr |
| System Variable | [bind_address](server-administration.html#sysvar_bind_address) |
| Scope | Global |
| Dynamic | No |
| SET_VAR Hint Applies | No |
| Type | string |
| Default Value | * |
此选项为,是在哪个网络接口上进行网络监听.
- 如果是* ,服务监听在本机的所有网卡上.包括IPV4 ,IPV6.
- 如果是0.0.0.0,监听所有的IPV4.
- 如果是:: 服务监听所有的IPV4,IPV6
- 如果是IPV4 的映射地址,则要使用::ffff:127.0.0.1 host=127.0.0.1 or --host=::ffff:127.0.0.1 登录
- 如果是特殊的IPV4 地址和IPV6 地址,你只能从特定的地址进行连接.
1.6. block_encryption_mode
| Property | Value |
|---|---|
| Command-Line Format | --block-encryption-mode=# |
| System Variable | [block_encryption_mode](server-administration.html#sysvar_block_encryption_mode) |
| Scope | Global, Session |
| Dynamic | Yes |
| SET_VAR Hint Applies | No |
| Type | string |
| Default Value | aes-128-ecb |
连接的加密模式:
For OpenSSL, permitted mode values are: ECB, CBC, CFB1, CFB8, CFB128, OFB
For wolfSSL, permitted mode values are: ECB, CBC
1.7. character_set_client
| Property | Value |
|---|---|
| System Variable | [character_set_client](server-administration.html#sysvar_character_set_client) |
| Scope | Global, Session |
| Dynamic | Yes |
| SET_VAR Hint Applies | No |
| Type | string |
| Default Value (>= 8.0.1) | utf8mb4 |
| Default Value (8.0.0) | utf8 |
数据库客户端默认字符集
1.8. character_set_connection
| Property | Value |
|---|---|
| System Variable | [character_set_connection](server-administration.html#sysvar_character_set_connection) |
| Scope | Global, Session |
| Dynamic | Yes |
| SET_VAR Hint Applies | No |
| Type | string |
| Default Value (>= 8.0.1) | utf8mb4 |
| Default Value (8.0.0) | utf8 |
客户端默认字符集
1.9. character_set_results
| Property | Value |
|---|---|
| System Variable | [character_set_results](server-administration.html#sysvar_character_set_results) |
| Scope | Global, Session |
| Dynamic | Yes |
| SET_VAR Hint Applies | No |
| Type | string |
| Default Value (>= 8.0.1) | utf8mb4 |
| Default Value (8.0.0) | utf8 |
查询结果字符集
1.10. collation_connection
| Property | Value |
|---|---|
| System Variable | [collation_connection](server-administration.html#sysvar_collation_connection) |
| Scope | Global, Session |
| Dynamic | Yes |
| SET_VAR Hint Applies | No |
| Type | string |
连接字符集转换.
1.11. collation_database
| Property | Value |
|---|---|
| System Variable | [collation_database](server-administration.html#sysvar_collation_database) |
| Scope | Global, Session |
| Dynamic | Yes |
| SET_VAR Hint Applies | No |
| Type | string |
| Default Value (>= 8.0.1) | utf8mb4_0900_ai_ci |
| Default Value (8.0.0) | latin1_swedish_ci |
| Footnote | This option is dynamic, but only the server should set this information. You should not set the value of this variable manually. |
数据库字符集转换
1.12. connect_timeout
| Property | Value |
|---|---|
| Command-Line Format | --connect-timeout=# |
| System Variable | [connect_timeout](server-administration.html#sysvar_connect_timeout) |
| Scope | Global |
| Dynamic | Yes |
| SET_VAR Hint Applies | No |
| Type | integer |
| Default Value | 10 |
| Minimum Value | 2 |
| Maximum Value | 31536000 |
mysql客户端在尝试与mysql服务器建立连接时，mysql服务器返回错误握手协议前等待客户端数据包的最大时限。默认10秒。
增大这个参数可以减少 connection to MySQL server at 'XXX', system error: errno. 这样频繁的报错.
1.13. default_authentication_plugin
| Property | Value |
|---|---|
| Command-Line Format | --default-authentication-plugin=plugin_name |
| System Variable | [default_authentication_plugin](server-administration.html#sysvar_default_authentication_plugin) |
| Scope | Global |
| Dynamic | No |
| SET_VAR Hint Applies | No |
| Type | enumeration |
| Default Value (>= 8.0.4) | caching_sha2_password |
| Default Value (<= 8.0.3) | mysql_native_password |
| Valid Values (>= 8.0.3) | mysql_native_password``sha256_password``caching_sha2_password |
| Valid Values (<= 8.0.2) | mysql_native_password``sha256_password |
默认的数据库用户认证插件
更改用户加密方式:
CREATE USER ... IDENTIFIED BY 'cleartext password';
1.14. disconnect_on_expired_password
| Property | Value |
|---|---|
| Command-Line Format | --disconnect-on-expired-password[=#] |
| System Variable | [disconnect_on_expired_password](server-administration.html#sysvar_disconnect_on_expired_password) |
| Scope | Global |
| Dynamic | No |
| SET_VAR Hint Applies | No |
| Type | boolean |
| Default Value | ON |
对于密码过期的用户登录:
- 如果客户端表明了过期用户的过期密码,则允许连接到一个沙盒环境.
- 如果客户端没有标明过期用户的过期密码,如果
- case1:参数打开,则断开连接.
- case2:允许连接,但是连接到一个沙盒环境中.
7690
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
