MySQL 服务器配置与管理
MySQL 服务器简介
MySQL 服务器指的是 mysqld 程序,当运行 mysqld 后对外提供 MySQL 服务。
服务器配置和默认值
mysqld 有很多选项和系统变量可以在启动时进行配置。
本文介绍了 MySQL 服务器的基本配置与管理方法。内容包括查看系统变量与状态指示器,通过选项文件或命令行设置变量(如排序缓冲区、数据包大小),以及动态修改全局或会话级变量的语法。详细说明了 Linux 和 Windows 下的配置文件路径及常用配置项(端口、字符集、存储引擎)。此外,还涵盖了 MySQL 的数据目录管理,以及各类日志(错误日志、一般查询日志、慢查询日志、二进制日志、Redo Log、Undo Log)的作用、配置方式与优缺点分析。最后简述了在一台机器上运行多个 MySQL 实例的概念。

MySQL 服务器指的是 mysqld 程序,当运行 mysqld 后对外提供 MySQL 服务。
mysqld 有很多选项和系统变量可以在启动时进行配置。
查看所有 mysqld 选项和可配置的系统变量列表及默认值:
mysqld --verbose --help
查看服务器在运行时系统变量的值,连接到 MySQL 并执行以下语句:
SHOW VARIABLES;
查看服务器在运行时的一些统计和状态指示器:
SHOW STATUS;
注意: 系统变量、状态变量的作用域分为:Global (全局), Session (当前会话或连接), 或两者都支持。
当通过 mysqld 启动数据库服务器时,可以通过选项文件或命令行中提供选项,但是,在大多数情况下,为确保服务器每次运行时都使用相同的选项,最好的方法是在选项文件中指定相应的选项。
mysqld 从选项文件中的 [mysqld] 和 [server] 组 (节点) 中读取选项内容
常用选项

设置系统变量的语法与命令选项的语法相同,指定变量名称时,破折号和下划线可以互换使用。例如,--general_log=ON 和 --general-log=ON 是等价的。

示例:为服务器指定 256 KB 的排序缓冲区大小和 1 GB 的最大数据包大小
# 在命令行指定
mysqld --sort-buffer-size=256K --max-allowed-packet=1G
# 在选项文件中指定
[mysqld]
sort_buffer_size=256k
max_allowed_packet=1g

大部分系统变量是动态的,在服务器运行时可以通过 SET 语句动态更改,并且无需停止和重新启动服务器。在服务器运行时,使用 SET 语句设置系统变量,需要指定作用域 (也可以在前面加上@@ 修饰符),然后指定系统变量的名称,名称必须使用下划线而不是破折号,如下所示
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 文件(不设置运行时值):
# PERSIST 表示持久化的同时设置全局变量的值
mysql> SET PERSIST_ONLY max_connections = 1000;
mysql> SET @@PERSIST_ONLY.max_connections = 1000;
删除持久化的系统变量可以使用语句
RESET PERSIST IF EXISTS system_var_name;
Linux 系统下编辑 /etc/mysql/my.cnf
Windows 系统下打开 C:/ProgramData/MySQL/MySQL Server 8.0/my.ini

注意: • 编辑前先备份原始文件 • 如果要修改数据目录选项建议先停止 MySQL 服务,并把原 data 目录整体复制到新路径,配置完成后重启服务
在 [mysqld] 节点下添加以下内容:
# MySQL 服务节点
[mysqld]
# MySQL 服务启动后监听的端口号
port=3306
# 数据目录的路径,这里演示的是 windows 下的配置,注意分隔符用/,如果是\则需要转义为\\.
# 把原 data 目录整体复制到新路径
datadir=D:/database/MySQL/data8.0
# MySQL 服务器的字符集与排序规则
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
# 新建表时使用的存储引擎,windows 下已默认配置
default-storage-engine=INNODB

错误日志,一般查询日志,慢查询日志,二进制日志,中继日志,DDL 日志,回滚日志,重做日志

默认情况下,所有启用的日志将写入数据目录,可以通过刷新日志强制服务器关闭并重新打开日志文件 (通过 FLUSH LOGS 语句刷新日志来强制服务器关闭并重新打开日志文件).
如果启用一般查询日志和慢查询日志,日志的输出方式可以指定为日志文件或 mysql 系统库中的 general_log 和 slow_log 表,也可以两者同时指定。启动时的日志控制

[mysqld]
# 日志文件
log_output=FILE
# 开启一般查询日志
general_log=1
# 指定自定义的文件名
general_log_file=/var/lib/mysql/general.log
# 开启慢查询日志
slow_query_log=1
# 指定自定义的文件名
slow_query_log_file=/var/lib/mysql/slow_query.log
• 可以通过 SQL 语句的条件查询过滤日志内容,从而选择满足特定条件的日志记录。 • 可以通过客户端程序连接到服务器并查询表中的日志信息,无需登录服务器主机访问文件系统。 • 日志记录具有标准格式,可查看日志表的结构,可以使用以下语句:
SHOW CREATE TABLE mysql.general_log; # 一般查询日志
SHOW CREATE TABLE mysql.slow_log; # 慢查询日志
SHOW CREATE TABLE mysql.general_log;

实例



慢查询日志的产生条件:
一般来说执行时间包括:执行 sql 并返回结果的时间 + 释放锁的时间.
日志内容

日志文件的内容格式

日志表的表结构

错误日志一般会记录 mysqld 启动和关闭的次数,诊断消息以及服务器运行期间发生的错误;
配置错误日志
错误日志输出的位置,可以是控制台或指定文件,"控制台"表示 stderr 标准错误输出。



错误日志的核心字段
示例


• 如果使用 FLUSH ERROR LOGS 命令刷新错误日志,服务器会将正在写入的任何错误日志文件关闭并重新打开。 • 如果要手动重命名错误日志文件,可以在重命名操作之后执行刷新操作,服务器会以原文件名生成一个新的错误日志文件,例如日志文件名为 host_name.err,可以按以下步骤操作:

作用







查看二进制日志

二进制日志格式




微信公众号「极客日志」,在微信中扫描左侧二维码关注。展示文案:极客日志 zeeklog
在线格式化和美化您的 SQL 查询(它支持各种 SQL 方言)。 在线工具,SQL 美化和格式化在线工具,online
解析 INSERT 等受限 SQL,导出为 CSV、JSON、XML、YAML、HTML 表格(见页内语法说明)。 在线工具,SQL转CSV/JSON/XML在线工具,online
CSV 与 JSON/XML/HTML/TSV/SQL 等互转,单页多 Tab。 在线工具,CSV 工具包在线工具,online
将字符串编码和解码为其 Base64 格式表示形式即可。 在线工具,Base64 字符串编码/解码在线工具,online
将字符串、文件或图像转换为其 Base64 表示形式。 在线工具,Base64 文件转换器在线工具,online
将 Markdown(GFM)转为 HTML 片段,浏览器内 marked 解析;与 HTML转Markdown 互为补充。 在线工具,Markdown转HTML在线工具,online