【MySQL】服务器管理与配置

MySQL服务器

服务器默认配置

查看服务器默认选项和系统变量

 mysqld --verbose --help

查看运行时的系统变量，可以通过like去指定自己要查询的内容

状态变量的查看 

系统变量和状态变量的作用域

• 全局作用域： 对于每个会话都会生效
• 当前会话：只在当前会话连接中生效

系统变量与选项

数据库服务器启动后配置参数以及区分选项和系统变量

• MySQL启动数据库服务器：两种方式，一种直接通过命令行的方式启动，另一种是通过配置文件中指定参数（my.cnf / my.ini）；推荐使用配置文件启动
• 选项文件读取：在配置文件中，通过识别[ mysqld ] [ server ] 组来读取启动参数，也就是在配置文件中通过这两部分启动指定启动时的选项
• 系统变量与选项区别
  • 系统变量是MySQL服务器运行的时候可以修改的参数，可以通过show variables 查看当前系统变量，然后通过set 去设置
  • 选型则是启动时候的指定参数，一旦MySQL启动后就无法修改，show variables 中不会显示这些选项

自我理解选项和系统变量

选项就是启动服务器后的配置，比如设置最大连接数端口号等，类似于启动汽车后，打开空调然后设置导航，启动的时候生效，但是过程中是无法更改的。

系统变量则像温度调节以及座椅调节，在运行过程中可以随时调整。

选项只可以启动的时候设置，而系统变量可以在MySQL运行期间动态调整。

常用选项分析

字符集相关

• --character-set-server（系统变量：character_set_server）：指定服务器的默认字符集，通常设置为utf8mb4来支持更多的字符
• --collation-server：指定服务器的默认排序规则，通常是与utf8mb4结合使用，一般是utf8b4_0900_ai_ci

基础设置

• --port：MySQL服务端监听端口号
• --datadir：指定MySQL数据的存储目录
• --default-storage-engine：设置默认的表存储索引引擎，一般是InnoDB

日志相关选项

• --log-output：执行文件输出位置
• --general-log：启动或者禁用一般查询日志，0关闭1开启
• general-log-file：指定一般查询日志文件的名称
• --slow-query-log-file：指定慢查询的文件名称

连接与缓存选项

• --max-connections：设置允许客户端同时连接的最大数量。
• --table-open-cache：设置同时可以打开表的最大数量。
• --innodb-buffer-pool-size：指定 InnoDB 的缓冲池大小，用于缓存表和索引数据，默认 128MB。
• --innodb-log-buffer-size：指定 InnoDB 的磁盘写入日志之前的缓冲区大小。

f服务器与性能调优

• --server-id）：用于指定 MySQL 服务器的唯一标识符，通常在主从复制时使用。
• --flush-time：指定将所有表同步到磁盘的时间间隔，单位为秒。
• --join-buffer-size：指定用于表关联操作的缓冲区大小，默认 256KB。
• --sort-buffer-size：为排序操作分配的会话内存缓冲区大小。
• --open-files-limit：设置操作系统可用的文件描述符数量，控制 MySQL 可同时打开的文件数量。

二进制日志和复制选项

• --log-error：指定 MySQL 错误日志文件的位置，记录错误和警告信息。
• --log-bin：指定用于二进制日志文件的基本名称，主要用于主从复制。
• --binlog-row-event-max-size：设置二进制日志中一行记录的最大事件大小

系统变量的使用

MySQL系统配置变量主要有两种方式，一种是命令行，也就是在启动MySQL的时候通过命令行直接指定其参数；另外一种是配置文件的方式，也就是通过编辑MySQL配置文件来设置系统变量。

数值后缀使用

为某些系统变量设置数值的时候，可以使用带有后缀的数值单位，这些后缀可以表示不同的字节数，基本上是遵循其内存的数值大小设定

• K/k：1024字节
• M/m：1024^2字节

配置使用

命令行方式，设置排序缓冲区的大小为256KB，设定允许最大的数据包大小为1GB

mysqld --sort-buffer-size=256K --max-allowed-packet=1G

配置文件方式，配置含义如上

[mysqld]
sort_buffer_size=256k
max_allowed_packet=1g

动态修改系统变量

SET GLOBAL sort_buffer_size = 256000;  -- 全局修改
SET SESSION sort_buffer_size = 256000; -- 当前会话修改

系统变量的两个作用域（session 和 global） 

Global全局作用域

全局作用域于整个MySQL服务器的配置参数，其影响服务器的整体操作，也就是当修改一个全局变量的时候，改变就会立即生效，同时会影响所有的客户端连接和服务器行为。

• 服务器启动时设置：通过命令行或者配置文件的方式进行设定，作为默认值，所有的全局变量都会初始化成默认值
• 运行的时候修改：如下通过SQL语句动态修改

SET GLOBAL max_connections = 500;

Session会话变量

简单来说就是针对于该客户端设置的一个变量内容个，客户端在连接生命周期中，可以根据需要动态的修改会话的值，不会影响其他客户端的操作。 

SET SESSION sort_buffer_size = 256000;

使用set语句来设置系统变量

需要注意修改系统变量的范围在哪里

两种方式设置最大连接数

全局系统变量持久化到mysqld-auto.cnf文件中

mysqld_auto.cnf文件本身是不存在的，只有在SET PRESIST语句执行的时候，该文件才会存在

 文件存在验证

删除该文件

 设置session系统变量（更改时区）

 注意上述只是对当前操作生效，因为作用域是session，下面验证

数值性系统变量设置原则

• 命令行选项方式：支持直接设置数值，允许带单位，不支持表达式
• SET方式：运行时设置，允许表达式计算具体的数值，但是不可以带单位

# 启动 MySQL 服务时，使用命令行选项的方式
mysqld --max_allowed_packet=16M  # 允许，带单位

# 如果尝试使用表达式，则不允许
mysqld --max_allowed_packet=16*1024*1024  # 不允许，表达式形式


-- 尝试在 MySQL 运行时直接使用带单位的数值会报错
SET GLOBAL max_allowed_packet = 16M;  -- 不允许，带单位的数值形式

-- 正确的用法是使用表达式计算具体数值
SET GLOBAL max_allowed_packet = 16*1024*1024;  -- 允许，表达式形式

查看seesion和global作用域的变量

 部分系统变量只有安装了插件或者组件才可以使用

服务器常用配置

例如设置MySQL中的my.cnf文件

[mysqld]
# 基本设置
user = mysql                     # 指定 MySQL 服务的运行用户
port = 3306                      # MySQL 服务运行端口
datadir = /var/lib/mysql          # 数据库文件存储目录
socket = /var/lib/mysql/mysql.sock # 进程 socket 文件路径
log-error = /var/log/mysql/error.log  # 错误日志文件路径
pid-file = /var/run/mysqld/mysqld.pid # 进程 ID 文件路径

# 连接限制
max_connections = 500             # 最大连接数，适用于高并发应用
max_allowed_packet = 64M          # 允许的最大数据包大小，防止大型查询失败
connect_timeout = 10              # 客户端连接超时时间，单位为秒

# 内存和缓存优化
innodb_buffer_pool_size = 1G      # InnoDB 缓冲池大小，通常设置为服务器物理内存的 70-80%
innodb_log_file_size = 256M       # InnoDB 日志文件大小，有助于提高写入性能
innodb_log_buffer_size = 16M      # InnoDB 日志缓冲区大小
sort_buffer_size = 4M             # 排序缓存大小，影响复杂排序的效率
read_buffer_size = 2M             # 读取缓存区大小，影响全表扫描的效率

# 查询缓存
query_cache_size = 64M            # 查询缓存大小
query_cache_type = 1              # 启用查询缓存

# 日志设置
slow_query_log = 1                # 启用慢查询日志
slow_query_log_file = /var/log/mysql/slow.log  # 慢查询日志文件
long_query_time = 2               # 记录执行超过 2 秒的查询

# 临时文件存放路径，提升性能
tmpdir = /tmp

# 字符集和排序规则
character-set-server = utf8mb4    # 设置服务器的默认字符集为 utf8mb4
collation-server = utf8mb4_unicode_ci  # 设置默认的排序规则

# 安全性设置
skip-symbolic-links               # 禁用符号链接，提升安全性

 查看状态变量

基本语法使用

直接通过SHOW[GLOBAL | SESSION] STATUS命令

SHOW GLOBAL STATUS;
SHOW SESSION STATUS;

使用LIKE子句筛选出特定的状态变量

SHOW GLOBAL STATUS LIKE 'Aborted%';

借助状态变量进行优化MySQL性能

• 优化连接管理
  • 可以通过监控Aborted_connects和Aborted_clients（已终止的客户端连接），可以发现连接问题，如连接过多或者连接的频繁
  • 如果Threads_created很高，那么就说明系统在频繁的创建爱和销毁线程，有可能会导致线程缓存不足，此时可以通过调整thread_cache_size来减少线程的创建和销毁，从而提高连接效率
• 监控服务器传输量
  • 通过Bytes_received和Bytes_sent来查看服务器的网络传输量，如果数据传输量异常增大的话，就需要对查询进行优化
• 监控临时表的使用
  • Created_tmp_tables和Created_tmp_disk_tables来监控服务器是否频繁创建临时表，过多的磁盘临时表创建也就以意味着查询语句不够优化

 

MySQL数据目录

基本认识

概念和存储位置

MySQL的数据目录就是一个文件系统的路径，MySQL中的所有数据都存储在这个文件中

 

数据库目录和文件 

 数据库目录重要文件夹分析

系统数据库：包含用户、权限等相关系统信息

 MySQL性能监控数据库：主要提供关于数据库性能的数据

 

常见文件类型

•  .frm文件：表结构文件，其中存储的是表的元数据（例如列定义、索引等）
• .ibd文件（InnoDb表）：这个就是InnoDb存储引擎独有的表空间文件，其中包含表的数据和索引
• .MYD文件：MyISAM表存储引擎的数据文件，存储表中的实际数据
• .MYI文件：MyISAM表存储引擎索引文件，存储表的索引信息

全局系统文件

• ibdata1：InnoDB的共享表空间文件，其中存储了InnoDB存储引擎的元数据、事务日志等，对于使用共享表空间的配置，InnoDB表的数据和索引也会存储在这个文件中
• ib_logfile0：这个是InnoDB的重做日志文件，主要用于事务恢复，这些日志文件存储了事务处理时更改的信息，用于的崩溃恢复，大小和数量可以通过配置文件调整

配置文件和运行文件

• my.cnf / my.ini：MySQL的主配置文件，里面存放着MySQL服务器的配置信息
• auto.cnf：服务器的唯一标识文件，主要就是用于生成MySQL实例的UUID

备份和数据文件目录

可以直接备份MySQL的数据目录来备份数据库

cp -r /var/lib/mysql /backup/mysql_backup

 工具

•  mysqldump：生成SQL文件，适合小数据库进行备份
• XtraBackup：适合大数据库备份

具体使用再拓展