MySQL高阶篇之配置文件详解与各类客户端工具- 惊觉

本文系统梳理：服务端配置项与系统变量的对应关系、客户端公共选项、配置文件的加载顺序与优先级、配置文件的编写方法，以及 mysqlcheck、mysqldump、mysqlimport、mysqladmin、mysqlshow、mysqldumpslow、mysqlbinlog、mysqlslap 八大客户端工具的用法与经典实例。

一、服务端配置项与系统变量

1.1 基本对应关系

MySQL 的每一个配置项都对应一个系统变量：启动选项 --xxx 中的连字符写法，对应系统变量 xxx 的下划线写法。例如：

--character-set-server  ──►  character_set_server
--default-storage-engine ──►  default_storage_engine

在配置文件中修改，相当于在服务器启动时直接设置 GLOBAL 变量；之后每个会话建立连接时，会从 GLOBAL 变量拷贝出自己的 SESSION 副本，这就是"改配置文件能影响所有新连接"的原理。

1.2 字符集与校对规则

启动选项	系统变量	默认值
`--character-set-server`	`character_set_server`	`utf8mb4`
`--collation-server`	`collation_server`	`utf8mb4_0900_ai_ci`

1.3 数据目录与存储引擎

启动选项	系统变量	说明
`--datadir`	`datadir`	数据文件目录，Ubuntu 默认 `/var/lib/mysql`
`--default-storage-engine`	`default_storage_engine`	表默认引擎，默认 InnoDB

1.4 日志相关配置

启动选项	系统变量	说明
`--log-output`	`log_output`	日志输出目的地：`FILE`/`TABLE`/`NONE`，默认 FILE
`--general-log`	`general_log`	一般查询日志开关，默认关闭，测试时使用
`--general-log-file`	`general_log_file`	一般日志文件名，默认 `host_name.log`
`--slow-query-log`	`slow_query_log`	慢查询日志开关，0/1 或 OFF/ON
`--slow-query-log-file`	`slow_query_log_file`	慢日志文件名，默认 `host_name-slow.log`
`--long-query-time`	`long_query_time`	慢查询阈值（秒），默认 10
`--log-error`	`log_error`	错误日志目标，控制台为 `stderr`，否则为文件名
`--log-bin`	N/A	binlog 基本名

1.5 连接与表缓存

启动选项	系统变量	说明
`--server-id`	`server_id`	服务器 ID，默认 1，主从必须唯一
`--max-connections`	`max_connections`	最大并发连接数
`--table-open-cache`	`table_open_cache`	所有线程可打开的表数量
`--max-connect-errors`	`max_connect_errors`	主机连续失败达到该值后被阻止连接
`--open-files-limit`	`open_files_limit`	mysqld 可用的文件描述符数量
`--max-allowed-packet`	`max_allowed_packet`	单个数据包最大值，默认 64MB

1.6 InnoDB 相关

启动选项	系统变量	说明
`--innodb-buffer-pool-size`	`innodb_buffer_pool_size`	缓冲池大小，默认 128MB
`--innodb-log-buffer-size`	`innodb_log_buffer_size`	日志缓冲区，默认 16MB
`--innodb-redo-log-capacity`	`innodb_redo_log_capacity`	重做日志占用磁盘空间
`--innodb-thread-concurrency`	`innodb_thread_concurrency`	InnoDB 内最大线程数，默认 0（不限制）
`--innodb-autoextend-increment`	`innodb_autoextend_increment`	表空间自动扩展增量（MB），默认 64

1.7 其他常用变量

启动选项	系统变量	说明
`--flush-time`	`flush_time`	非 0 时每隔该秒数关闭所有表并刷盘
`--join-buffer-size`	`join_buffer_size`	无索引 JOIN 的最小缓冲，默认 256KB
`--sort-buffer-size`	`sort_buffer_size`	每个需排序会话的缓冲大小
`--binlog-row-event-max-size`	`binlog_row_event_max_size`	ROW 事件最大值，须为 256 的倍数，默认 8192 字节

1.8 动态变量与静态变量

动态变量：可在运行时用 SET GLOBAL/SESSION 修改，无需重启；
静态变量：只能在启动时设置（如 datadir）。
完整动态变量列表见官方文档：[https://dev.mysql.com/doc/refman/8.0/en/dynamic-system-variables.html](https://dev.mysql.com/doc/refman/8.0/en/dynamic-system-variables.html)。

二、客户端公共选项

MySQL 客户端程序包括：mysql、mysqlcheck、mysqldump、mysqlimport、mysqladmin、mysqlshow、mysqldumpslow、mysqlbinlog、mysqlslap 等。

2.1 连接选项

选项	说明
`--host / -h`	服务器主机地址
`--port / -P`	服务器端口号
`--user / -u`	连接用户名
`--password / -p`	密码

-p 的特殊规则：如果在命令行直接写密码，必须紧跟选项、中间不能有空格：

mysql -uroot -p123456        # 正确
mysql -uroot -p 123456       # 此时 123456 会被当成数据库名

不写密码时，服务器会返回报文要求你输入密码。

2.2 其他常用选项

选项	说明
`--defaults-file`	只读取指定配置文件（必须是第一个选项，客户端/服务器程序通用）
`--database / -D`	指定要使用的数据库
`--compress / -C`	压缩客户端与服务器之间的传输
`--reconnect`	连接断开后自动重连
`--quick / -q`	不缓存结果，收到一行打印一行
`--protocol`	传输协议，默认 TCP
`--delimiter`	指定语句分隔符
`--execute / -e`	执行 SQL 后退出
`--version / -V`	显示版本并退出
`--help / -?`	显示帮助并退出

规律：长选项用 --，短选项用 -。

2.3 实用小技巧

命令行中第一个不带 - 的值会被解析为数据库名，可用来替代 --database：

mysql -uroot -p test_db        # 等价于 mysql -uroot -p -D test_db

三、配置文件加载顺序与优先级

3.1 Linux/Unix 读取顺序

顺序	文件	适用
1	`/etc/my.cnf`	全局
2	`/etc/mysql/my.cnf`	全局
3	`$MYSQL_HOME/my.cnf`	服务器（$MYSQL_HOME 是你系统的环境变量）
4	`--defaults-extra-file` 指定文件	额外（这个需要你自己在命令行指定）
5	`~/.my.cnf`	当前用户
6	`~/.mylogin.cnf`	客户端（mysql_config_editor 生成，加密）
7	`DATADIR/mysqld-auto.cnf`	服务器（8.0，JSON 格式，持久化变量）

3.2 优先级原理

越早读取优先级越低，越晚读取优先级越高——这就好比对同一个变量不断赋值，只有最后一次赋值生效。

整体优先级（从低到高）：

环境变量 → 选项文件（按读取顺序）→ mysqld-auto.cnf → 命令行选项

3.3 控制读取的三个命令行选项

选项	作用
`--no-defaults`	不读取任何选项文件
`--defaults-file=path`	只读取指定文件
`--defaults-extra-file=path`	读完标准文件后再读该文件

3.4 读取时机

服务器：启动 mysqld 时读取，用于设置 GLOBAL 变量；
客户端：建立连接时读取，用于设置会话选项。

文件名不一定固定，重点是 .cnf 后缀（Windows 下为 .ini）。

四、配置文件编写

4.1 选项组

选项组	作用范围
`[mysqld]`	只对 mysqld 服务器生效
`[client]`	对所有客户端程序生效
`[mysql]`	只对 mysql 客户端生效
`[mysqldump]` 等	只对对应工具生效

注释符号：# 和 ; 都可以。

4.2 服务端配置实例

编辑 /etc/mysql/mysql.conf.d/mysqld.cnf：

[mysqld]
server-id = 5201314

重启生效：

systemctl restart mysql
mysql -uroot -p -e "SELECT @@server_id;"

修改前记得备份原始文件。

4.3 客户端配置实例

编辑 /etc/mysql/conf.d/mysql.cnf：

[client]
host = 127.0.0.1
port = 3306
user = root
password          # 留空则连接时提示输入，但是我们在命令行中就可以不写 -p 了

[mysql]
port = 3307       # 更具体的组覆盖 [client]

最终 mysql 客户端使用 3307（在同文件中后出现）。

4.4 include 与 includedir

# 包含单个文件
!include /home/mydir/myop.cnf

# 包含目录下所有 .cnf 文件（不递归穿透子目录）
!includedir /home/mydir

Ubuntu 上 /etc/mysql/mysql.cnf 的内容正是：

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

这解释了为什么配置放在 mysql.conf.d 目录里也能被加载。

4.5 mysqld-auto.cnf 与 SET PERSIST

8.0 支持把变量持久化到 mysqld-auto.cnf：

SET PERSIST max_connections = 500;
SET PERSIST_ONLY xxx;       // 只写入文件，运行时不立即生效

该文件为 JSON 格式，由服务器自动维护，不要手动编辑。

五、mysqlcheck：表维护工具

5.1 作用

对表执行四种维护：

操作	作用	支持引擎
分析	查看关键字分布，辅助执行计划	InnoDB / MyISAM / NDB
检查	检查完整性与损坏	InnoDB / MyISAM / ARCHIVE / CSV
优化	回收空间、减少碎片	InnoDB / MyISAM / ARCHIVE
修复	修复损坏表	MyISAM / ARCHIVE / CSV

5.2 注意事项

服务器必须在运行；
执行期间表会被锁定；
不支持的引擎会报错；
修复前先备份。

5.3 经典实例

mysqlcheck -uroot -p test_db t_user                    # 检查单表
mysqlcheck -uroot -p test_db                           # 检查整库
mysqlcheck -uroot -p --all-databases                   # 检查所有库
mysqlcheck -uroot -p -a test_db t_user                 # 分析
mysqlcheck -uroot -p -o test_db t_user                 # 优化
mysqlcheck -uroot -p -r test_db t_user                 # 修复（MyISAM）
mysqlcheck -uroot -p --all-databases --auto-repair     # 检查并自动修复
mysqlcheck -uroot -p -f --all-databases                # 出错也继续

⚠️ InnoDB 不支持 -r 修复，会提示 The storage engine for the table doesn't support repair。

小技巧：把程序复制/软链接为 mysqlrepair、mysqlanalyze、mysqloptimize，默认行为随之变为修复、分析、优化。

六、mysqldump：逻辑备份工具

6.1 作用

执行逻辑备份，生成包含建库建表语句和数据的 SQL 文件，也可生成 CSV、XML。

6.2 权限要求

操作	所需权限
转储表	SELECT
转储视图	SHOW VIEW
转储触发器	TRIGGER
未用 `--single-transaction`	LOCK TABLES
未用 `--no-tablespaces`	PROCESS

参考MySQL进阶篇之备份（下半篇）-CSDN博客

七、mysqlimport：文本数据导入工具

7.1 作用

LOAD DATA INFILE 的命令行封装，文件名去掉扩展名后必须等于表名（t_user.csv → 导入 t_user）。

7.2 经典实例

# 导入 CSV
mysqlimport -uroot -p \
  --fields-terminated-by=',' \
  --fields-enclosed-by='"' \
  --lines-terminated-by='\n' \
  test_db /var/lib/mysql-files/t_user.csv

# 冲突时忽略 / 替换
mysqlimport -uroot -p --ignore test_db /var/lib/mysql-files/t_user.csv
mysqlimport -uroot -p --replace test_db /var/lib/mysql-files/t_user.csv

# 跳过表头
mysqlimport -uroot -p --ignore-lines=1 test_db /var/lib/mysql-files/t_user.csv

八、mysqladmin：服务器管理工具

8.1 常用命令

命令	作用
`ping`	检查服务器是否存活
`version`	版本、运行时长、线程数
`status`	简短状态
`processlist`	活动线程列表
`create/drop db`	建/删数据库
`password`	修改密码
`shutdown`	关闭服务器
`reload`	重新加载授权表
`flush-logs`	刷新日志
`flush-hosts`	刷新主机缓存
`kill`	终止线程
`extended-status`	状态变量
`start-replica/stop-replica`	8.0.26+ 启停复制

8.2 经典实例

mysqladmin -uroot -p ping
mysqladmin -uroot -p version
mysqladmin -uroot -p status
mysqladmin -uroot -p -i 2 status                     # 每 2 秒刷新
mysqladmin -uroot -p processlist
mysqladmin -uroot -p create test_db
mysqladmin -uroot -p drop test_db
mysqladmin -uroot -p password 'newpass'
mysqladmin -uroot -p shutdown
mysqladmin -uroot -p reload
mysqladmin -uroot -p flush-logs
mysqladmin -uroot -p flush-hosts
mysqladmin -uroot -p kill 12,15
mysqladmin -uroot -p extended-status
mysqladmin -uroot -p start-replica
mysqladmin -uroot -p stop-replica

密码含空格时用双引号包裹；

kill 的多个 ID 用逗号连接、不能有空格。

九、mysqlshow：库表列信息工具

9.1 作用

为 SHOW 语句提供命令行接口，直接写 SQL 也能获得相同结果。

9.2 经典实例

mysqlshow -uroot -p                                   # 列出所有库
mysqlshow -uroot -p test_db                           # 列出库中表
mysqlshow -uroot -p test_db t_user                    # 列出表的列
mysqlshow -uroot -p -k test_db t_user                 # 显示索引
mysqlshow -uroot -p --status test_db                 # 显示表状态
mysqlshow -uroot -p test_db "user%"                  # 通配符

支持通配符 *、?、%、_；只显示当前权限可访问的对象。

十、mysqldumpslow：慢查询日志汇总工具

10.1 作用

解析慢查询日志并按相似 SQL 分组汇总，默认把数字替换为 N、字符串替换为 'S'。

10.2 输出解读

Count: 3 Time=2.53s (7s) Lock=0.00s (0s) Rows=10.0 (30)
  ↑次数   ↑单次/总耗时       ↑锁等待            ↑单次/总行数

10.3 经典实例

# 按平均查询时间排序，前 10 条
mysqldumpslow -s at -t 10 /var/lib/mysql/host-slow.log

# 按出现次数 / 返回行数排序
mysqldumpslow -s c -t 10 /var/lib/mysql/host-slow.log
mysqldumpslow -s r -t 10 /var/lib/mysql/host-slow.log

# 只看包含 t_user 的
mysqldumpslow -g 't_user' /var/lib/mysql/host-slow.log

# 显示真实值
mysqldumpslow -a /var/lib/mysql/host-slow.log

排序参数：t/at（总/平均时间，默认）、l/al（锁时间）、r/ar（行数）、c（次数）。

十一、mysqlbinlog：二进制日志处理工具

11.1 作用

以文本格式显示 binlog 内容，也可用于重放（PITR）。

binlog 默认存于数据目录（Linux：/var/lib/mysql）；
文件名以 .00000n 递增。

11.2 经典实例

# 查看文本
mysqlbinlog /var/lib/mysql/binlog.000001

# ROW 格式加 -v 显示伪 SQL（-vv 更详细）
mysqlbinlog -v /var/lib/mysql/binlog.000001
mysqlbinlog -vv /var/lib/mysql/binlog.000001

# 解码 ROW 为可读 SQL
mysqlbinlog --base64-output=DECODE-ROWS -v /var/lib/mysql/binlog.000001

# 按时间段过滤
mysqlbinlog --start-datetime="2024-01-01 10:00:00" \
            --stop-datetime="2024-01-01 11:00:00" \
            /var/lib/mysql/binlog.000001

# 按位置过滤
mysqlbinlog --start-position=157 --stop-position=1000 \
            /var/lib/mysql/binlog.000001

# 只看某个库
mysqlbinlog --database=test_db /var/lib/mysql/binlog.000001

# 重放多个 binlog（PITR）
mysqlbinlog binlog.000001 binlog.000002 | mysql -uroot -p

# 远程读取
mysqlbinlog -R -h 192.168.1.100 -uroot -p binlog.000001

⚠️ PITR 时必须用 --stop-position/--stop-datetime 停在误操作之前，否则会把误操作也重放。

十二、mysqlslap：负载仿真工具

12.1 作用

模拟多个客户端并发访问服务器，报告各阶段耗时，用于性能诊断。

12.2 三个阶段

创建数据（单连接建库建表）；
运行负载（多并发查询）；
清理（单连接删库，--no-drop 可保留）。

12.3 经典实例

# 全自动，100 并发，1000 次查询
mysqlslap -uroot -p --auto-generate-sql \
  --concurrency=100 --number-of-queries=1000

# 简写，100 并发，跑 5 轮
mysqlslap -uroot -p -a -c 100 -i 5

# 对比引擎
mysqlslap -uroot -p -a --engine=innodb,myisam --concurrency=50

# 自定义查询
mysqlslap -uroot -p --concurrency=50 --iterations=10 \
  --query="SELECT * FROM t_user WHERE id=1" \
  --create-schema=test_db

# 使用 SQL 文件
mysqlslap -uroot -p --concurrency=50 --iterations=10 \
  --create=create.sql --query=query.sql --delimiter=";"

# 混合读写，每 10 条提交
mysqlslap -uroot -p -a -c 100 --number-of-queries=10000 --commit=10

# 自动生成表结构（2 INT 列、3 VARCHAR 列，1000 行）
mysqlslap -uroot -p -a -c 50 \
  --number-int-cols=2 --number-char-cols=3 \
  --auto-generate-sql-write-number=1000

# 输出 CSV
mysqlslap -uroot -p -a -c 50,100 -i 3 --csv=result.csv

注意：SQL 文件默认每行一条语句，多行语句需用 --delimiter 指定分隔符；文件中不能包含注释。

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/bksczm/article/details/169167798

MySQL高阶篇之配置文件详解与各类客户端工具