【MySQL】服务器配置和管理

本文使用的MySQL版本是8.0

MySQL服务器介绍

MySQL服务器通常说的是mysqld程序。

mysqld 是 MySQL 数据库服务器的核心程序，负责处理客户端的请求、管理数据库和执行数据库操作。管理员可以通过配置文件和各种工具来管理和监控 mysqld 服务器的运行

官方文档：MySQL :: MySQL 8.0 Reference Manual :: 7 MySQL Server Administration

 本文将介绍下面三个内容：

服务器启动选项：可以在命令行和配置文件中指定，用于配置服务器的行为和特性。

服务器系统变量：反映了启动选项的当前状态和值，其中一些变量可以在服务器运行时修改。

服务器状态变量：包含了关于运行时操作的计数器和统计信息。

选项

 每次启动mysqld的时候，可以通过命令行或者文件来提供选项，建议使用文件的方式来指定选项，这样就能确保每次运行时都是使用相同的选项。

官方文档：MySQL :: MySQL 8.0 Reference Manual :: 7.1.4 Server Option, System Variable, and Status Variable Reference

如果mysqld启动时如果没有用--defaults-file 来指定配置文件，那么就是按照表格由上至下依次查找配置文件。后面的配置文件会把前面的覆盖掉（如果有相同的配置）。

使用文件的方式读取选项时，mysqld是从 [mysqld] 和 [server] 这两个节点中读取选项的。

查看选项

mysqld --verbose --help

使用该命令，会把mysqld的选项和系统变量列出来。一般不使用这个命令，因为它把选项和系统变量都输出了，我们可以在官网查看这些选项。

 常用选项

使用选项

在指定的配置文件中修改或添加即可

系统变量

反映了启动选项的当前状态和值，其中一些变量可以在服务器运行时修改。

可以说，系统变量就是选项的表现，

查看系统变量

查看全部

# 在mysql命令行下
show variables;

或者用mysqladmin程序

mysqladmin variables

查看模糊 

show variables like "%xxx%";

查看指定

查看全局或者会话

show [global | session] variables;

下面将会讲到全局变量或者局部变量 

使用系统变量

• 设置系统变量的语法与命令选项的语法相同，指定变量名称时，破折号和下划线可以互换使用。例如，--general_log=ON 和 --general-log=ON 是等价的。
• 当使用选项设置一个数值的变量时，可以带有后缀 K、M 或 G（大小写不限）表示 1024、1024^2 或 1024^3；从MySQL 8.0.14 开始，后缀也可以用 T、P 和 E 来表示 1024^4、1024^5 或 1024^6。

示例：为服务器指定 256 KB 的排序缓冲区大小和 1 GB 的最大数据包大小

在命令行的指定：

mysqld --sort-buffer-size=256K

--max-allowed-packet=1G

在选项文件中指定：

[mysqld]
sort_buffer_size=256k
max_allowed_packet=1g

• 系统变量有两个作用域，分别是 Global（全局）和 Session（会话），Global 全局变量影响服务器的整体操作，Session 会话变量影响各个客户端连接的操作。给定的系统变量可以同时具有全局值和会话值，它们的关系如下： 

服务器启动时，会将每个全局变量初始化并设置默认值，具体的值可以通过命令行或选项文件更改。
服务器为每个客户端维护一组 Session 变量，在客户端连接时使用相应全局变量的当前值进行初始化。

范围：全局变量影响整个 MySQL 服务器实例，而会话变量只对当前连接有效。
生命周期：全局变量在服务器启动时初始化，持续存在于服务器的整个生命周期，而会话变量在客户端连接期间存在，当连接关闭时消失。
作用对象：全局变量影响所有连接和操作，而会话变量只影响当前连接。
设置方式：全局变量可以在配置文件或启动命令中设置，会话变量可以通过 SET 语句在连接时动态设置。

• 大部分系统变量是动态的，在服务器运行时可以通过 SET 语句动态更改，并且无需停止和重新启动服务器。在服务器运行时，使用 SET 语句设置系统变量，需要指定作用域（也可以在前面加上 @@ 修饰符），然后指定系统变量的名称，名称必须使用下划线而不是破折号，如下所示： 

设置全局系统变量最大连接数为1000：
mysql> SET GLOBAL max_connections = 1000;
mysql> SET @@GLOBAL.max_connections = 1000;

将全局系统变量持久化到 mysqld-auto.cnf 文件（同时设置运行时值）：
mysql> SET PERSIST max_connections = 1000;
mysql> SET @@PERSIST.max_connections = 1000;

将全局系统变量持久化到 mysqld-auto.cnf 文件（不设置运行时值）：
mysql> SET PERSIST_ONLY max_connections = 1000;
mysql> SET @@PERSIST_ONLY.max_connections = 1000;

删除持久化的系统变量可以使用语句：

RESET PERSIST IF EXISTS system_var_name;

设置 Session 系统变量，时区为 "+8:00"：

mysql> SET SESSION time_zone='+8:00';
mysql> SET @@SESSION.time_zone='+8:00';
mysql> SET @@time_zone='+8:00';

• 对于数值型的系统变量，用带有后缀的值指定时，只适用于选项方式，而不能用在 SET 方式中；SET 方式可以使用表达式为系统变量指定值，而在选项方式中不允许，如下所示：

选项形式：
root@guangchen-vm:~# mysqld --max_allowed_packet=16M # 允许
root@guangchen-vm:~# mysqld --max_allowed_packet=16*1024*1024 # 不允许
运行时 SET 形式：
mysql> SET GLOBAL max_allowed_packet=16M; # 不允许
mysql> SET GLOBAL max_allowed_packet=16*1024*1024; # 允许

• 在设置全局变量时需要指定 GLOBAL 关键字的原因是为了防止出现以下问题:

如果要删除的 SESSION 变量与 GLOBAL 变量名相同，那么具有修改全局变量权限的客户端可能会意外地更改 GLOBAL 变量，而不仅仅是只修改 SESSION 变量。
如果已经有一个 SESSION 变量而且与 GLOBAL 变量同名，那么本意是要修改 GLOBAL 变量，可能只是修改了 SESSION 变量的值。
所以没有明确指定 GLOBAL 和 SESSION 时，对于当前客户端来说 SESSION 的优先级更高。

状态变量

MySQL 中的状态变量是用来跟踪服务器状态和性能指标的变量。它们提供了有关 MySQL 服务器运行时状态的信息，可以帮助管理员进行监视、调优和故障排除。状态变量包括了各种性能指标、缓存使用情况、连接和查询统计等信息。

官方文档：MySQL :: MySQL 8.0 Reference Manual :: 7.1.10 Server Status Variables

查看状态变量

SHOW [GLOBAL | SESSION] STATUS [like status_name];

与查看系统变量基本一致。

常见状态标识

常见配置

# MySQL 服务节点
[mysqld]
 
# MySQL 服务启动后监听的端口号
port=3306
 
# 数据目录的路径，这里演示的是windows下的配置，注意分隔符用/，如果是\则需要转义为\\.
# 把原data目录整体复制到新路径
# datadir=写具体的实现目录
 
# MySQL 服务器的字符集与排序规则
character_set_server=utf8mb4
collation_server=utf8mb4_general_ci 
 
# 新建表时使用的存储引擎，windows下已默认配置
default_storage_engine=INNODB
 
# 时区
default-time-zone='+8:00'
 
# ################# 日志相关 #################
#日志文件
log_output=FILE,TABLE
#开启一般查询日志   
general_log=1     
#指定自定义的文件名   
general_log_file=/var/log/mysql/general.log 
 
#开启慢查询日志
slow_query_log=1     
#指定自定义的文件名
slow_query_log_file=/var/log/mysql/slow_query.log 
 
#错误日志的路径
log-error=/var/log/mysql/error.log