【MySQL】服务器配置和管理

码农世界 2024-06-19 后端 91 次浏览 0个评论

本文使用的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