欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL 链接参数说明

程序员文章站 2022-03-10 21:48:32
...

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)
  1. 赋权角色
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)
  1. 角色赋予用户
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 [email protected]%                    |
+---------------------------------------+
| GRANT USAGE ON *.* TO `read2`@`%`     |
| GRANT `test2_read`@`%` TO `read2`@`%` |
+---------------------------------------+
2 rows in set (0.00 sec)
mysql>  select current_role();
+----------------+
| current_role() |
+----------------+
| NONE           |
+----------------+
  1. 设置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:允许连接,但是连接到一个沙盒环境中.