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 指定分隔符；文件中不能包含注释。

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

1.1 基本对应关系

1.2 字符集与校对规则

1.3 数据目录与存储引擎

1.4 日志相关配置

1.5 连接与表缓存

1.6 InnoDB 相关

1.7 其他常用变量

1.8 动态变量与静态变量

二、客户端公共选项

2.1 连接选项

2.2 其他常用选项

2.3 实用小技巧

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

3.1 Linux/Unix 读取顺序

3.2 优先级原理

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

3.4 读取时机

四、配置文件编写

4.1 选项组

4.2 服务端配置实例

4.3 客户端配置实例

4.4 include 与 includedir

4.5 mysqld-auto.cnf 与 SET PERSIST

五、mysqlcheck：表维护工具

5.1 作用

5.2 注意事项

5.3 经典实例

六、mysqldump：逻辑备份工具

6.1 作用

6.2 权限要求

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

7.1 作用

7.2 经典实例

八、mysqladmin：服务器管理工具

8.1 常用命令

8.2 经典实例

九、mysqlshow：库表列信息工具

9.1 作用

9.2 经典实例

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

10.1 作用

10.2 输出解读

10.3 经典实例

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

11.1 作用

11.2 经典实例

十二、mysqlslap：负载仿真工具

12.1 作用

12.2 三个阶段

12.3 经典实例

相关推荐

评论 抢沙发

评论前必须登录！

热门标签

置顶推荐

热门文章

最新文章

评论抢沙发