【MySQL】服务器管理与配置

大家好，欢迎来到IT知识分享网。

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 = ; -- 全局修改 SET SESSION sort_buffer_size = ; -- 当前会话修改

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

Global全局作用域

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

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

SET GLOBAL max_connections = 500;

Session会话变量

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

SET SESSION sort_buffer_size = ;

使用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：适合大数据库备份

具体使用再拓展