Mysql Configuration

配置文件的路径

refer to : https://dev.mysql.com/doc/refman/8.4/en/option-files.html

Table 6.2 Option Files Read on Unix and Unix-Like Systems

一般来说，mysql配置文件的路径，存在于/etc/my.cnf。可以通过以下命令，进行查询：

[root@vm132 ~]# whereis my.cnf
my: /etc/my.cnf

配置文件的语法和示例

Empty lines in option files are ignored. Nonempty lines can take any of the following forms:

#comment, ;comment

Comment lines start with # or ;. A # comment can start in the middle of a line as well.

[group]

group is the name of the program or group for which you want to set options. After a group line, any option-setting lines apply to the named group until the end of the option file or another group line is given. Option group names are not case-sensitive.

opt_name

This is equivalent to --opt_name on the command line.

opt_name=value

This is equivalent to --opt_name=value on the command line. In an option file, you can have spaces around the = character, something that is not true on the command line. The value optionally can be enclosed within single quotation marks or double quotation marks, which is useful if the value contains a # comment character.

If an option group name is the same as a program name, options in the group apply specifically to that program. For example, the [mysqld] and [mysql] groups apply to the mysqld server and the mysql client program, respectively.

The [client] option group is read by all client programs provided in MySQL distributions (but not by mysqld). To understand how third-party client programs that use the C API can use option files, see the C API documentation at mysql_options().

The [client] group enables you to specify options that apply to all clients. For example, [client] is the appropriate group to use to specify the password for connecting to the server. (But make sure that the option file is accessible only by yourself, so that other people cannot discover your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs that you use. Programs that do not understand the option quit after displaying an error message if you try to run them.

List more general option groups first and more specific groups later. For example, a [client] group is more general because it is read by all client programs, whereas a [mysqldump] group is read only by mysqldump. Options specified later override options specified earlier, so putting the option groups in the order [client], [mysqldump] enables mysqldump-specific options to override [client] options.

Here is a typical global option file:

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
socket=/tmp/mysql.sock
key_buffer_size=16M
max_allowed_packet=128M

[mysqldump]
quick

Here is a typical user option file:

[client]
# The following password is sent to all standard MySQL clients
password="my password"

[mysql]
no-auto-rehash
connect_timeout=2

配置项

max_connections

refer to : https://dev.mysql.com/doc/refman/8.4/en/server-system-variables.html#sysvar_max_connections

show variables like 'max_connections';

wait_timeout & interactive_timeout

show variables like 'wait_timeout';

show variables like 'interactive_timeout';

interactive_timeout参数，定义了对于交互式连接，服务端等待数据的最大时间。如果超过这个时间，服务端仍然没有收到数据，则会关闭连接。

所谓交互式client，是指调用mysql_real_connect()函数建立连接时，设置了CLIENT_INTERACTIVE选项。比较常用的就是命令行终端。

比如我们在终端上进入mysql管理，使用的就是交互的连接，这时候，如果没有操作的时间达到了interactive_time设置的时间，连接就会自动断开。

MySQL的配置中，有一个叫做“wait_timeout"的参数，这个参数大致的意思是这样：当一个客户端连接到MySQL数据库后，如果客户端不自己断开，也不做任何操作，MySQL数据库会将这个连接保留"wait_timeout"这么长时间（单位是s，默认是28800s，也就是8小时），超过这个时间之后，MySQL数据库为了节省资源，就会在数据库端断开这个连接；当然，在此过程中，如果客户端在这个连接上有任意的操作，MySQL数据库都会重新开始计算这个时间。

wait_timeout参数，定义对于非交互式连接，服务端等待数据的最长时间。如果超过这个时间，服务端仍然没有收到数据，则会关闭连接。比较常用的，类似于jdbc连接

完整配置如下：

[root@VM-0-13-rockylinux system]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

interactive_timeout=3600
wait_timeout=3600
max_connections=1000