【教程】MYSQL中my.ini配置文件内容解读

文章目录

前言

上一篇我们讲解了如何在win11下同时安装MYSQL5.7.44和MYSQL8.0.33，供不同的学习需求使用
【环境配置教程】MYSQL----win系统本地同时安装MySQL5.7.xx、MySQL8.0.xx 本篇讲解一下安装mysql过程中的my.ini配置文件中的详细内容

[mysqld]
# 基本设置
basedir=D:\\software\\environment\\mysql\\mysql-8.0.33 # MySQL安装目录
datadir=D:\\software\\environment\\mysql\\mysql-8.0.33\\data  # 数据库存储目录

# 端口号
port=3308

# 字符集设置
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# InnoDB 设置
innodb_buffer_pool_size=1G       # 根据内存大小适当调整
innodb_log_file_size=512M        # 日志文件大小
innodb_flush_log_at_trx_commit=2 # 提交事务时立即将日志写入磁盘

# 日志设置
log_error=D:\\software\\environment\\mysql\\mysql-8.0.33\\log\\error.log
general_log_file=D:\\software\\environment\\mysql\\mysql-8.0.33\\log\\mysql.log
general_log=ON                    # 开启通用查询日志

# 安全设置
# secure-file-priv=D:\MySQL\Uploads # 导入导出数据文件路径限制
# sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# 其他设置
max_allowed_packet=16M            # 最大允许的数据包大小
max_connections=150               # 最大连接数

# 性能优化
# query_cache_type=0  # 关闭查询缓存
thread_cache_size=16              # 线程缓存大小

# 监听地址
# bind-address=0.0.0.0              # 绑定所有网络接口

# Windows服务相关设置
[mysqld_safe]
log-error=D:\\software\\environment\\mysql\\mysql-8.0.33\\log\\safe_error.log
pid-file=D:\\software\\environment\\mysql\\mysql-8.0.33\\log\\mysqld.pid

# 客户端连接设置
[client]
default-character-set=utf8mb4

# Windows命令行工具设置
[mysql]
default-character-set=utf8mb4

InnoDB 设置

1.

innodb_buffer_pool_size

描述：

• innodb_buffer_pool_size 是 InnoDB 存储引擎用于缓存表数据和索引数据的内存区域大小。
• 这个缓冲池可以显著提高数据库的性能，因为它减少了磁盘 I/O 操作。

建议设置：

• 通常建议将 innodb_buffer_pool_size 设置为系统总物理内存的 50% 到 75%。
• 对于专用的数据库服务器，可以设置得更高，但不要超过物理内存的 80%，以避免操作系统和其他应用程序的内存不足问题。

示例：

nnodb_buffer_pool_size=1G  # 对于4GB内存的机器，可以设置为1G

2.

innodb_log_file_size

描述：

• innodb_log_file_size 是 InnoDB 日志文件的大小。
• InnoDB 日志文件（也称为重做日志文件）用于记录所有对数据库的更改，以便在系统崩溃后进行恢复。

建议设置：

• 较大的日志文件可以减少日志切换的频率，从而提高性能。
• 但是，较大的日志文件也会增加恢复时间，因为需要处理更多的日志数据。
• 常见的设置范围是 128MB 到 1GB，具体取决于你的工作负载和恢复时间要求。

示例：

innodb_log_file_size=512M  # 对于大多数应用场景，512MB是一个合理的设置**3. `innodb_flush_log_at_trx_commit`**

描述：

• innodb_flush_log_at_trx_commit 控制 InnoDB 如何处理事务日志的刷新策略。
• 这个参数决定了何时将事务日志从内存中的缓冲区写入磁盘。

取值及其含义：

• 0：事务提交时不立即将日志写入磁盘，而是每秒刷新一次日志缓冲区。这种方式性能最高，但安全性最低，因为系统崩溃可能会丢失最近的事务。
• 1（默认值）：每次事务提交时立即将日志写入磁盘。这种方式最安全，但性能较低。
• 2：事务提交时将日志写入操作系统缓存，但不立即写入磁盘。操作系统会在适当的时候将缓存写入磁盘。这种方式在性能和安全性之间做了平衡。

建议设置：

• 对于需要高可靠性的生产环境，推荐使用默认值 1。
• 对于对性能要求较高且可以容忍少量数据丢失的开发或测试环境，可以考虑使用 2。
• 如果对性能要求极高且可以接受较大的数据丢失风险，可以使用 0，但这通常不推荐。

示例：

innodb_flush_log_at_trx_commit=1  # 默认值，最安全

总结

• **innodb_buffer_pool_size**：设置为系统物理内存的 50% 到 75%。
• **innodb_log_file_size**：设置为 128MB 到 1GB，具体取决于工作负载和恢复时间要求。
• **innodb_flush_log_at_trx_commit**：对于生产环境推荐使用 1，对于开发或测试环境可以考虑使用 2。

安全设置

1.

secure-file-priv

描述：

• secure-file-priv 是一个安全设置，用于限制导入和导出数据文件的路径。
• 当设置了 secure-file-priv 后，只有在这个指定目录下的文件才能被 LOAD DATA INFILE 和 SELECT ... INTO OUTFILE 等命令访问。

作用：

• 增强安全性：防止恶意用户通过这些命令将文件写入或读取到任意目录，从而避免潜在的安全风险。
• 集中管理：将所有导入导出的数据文件集中在一个目录中，便于管理和维护。

示例：

secure-file-priv=D:\software\environment\mysql\mysql-8.0.39\Uploads

解释：

• 上述配置表示只有 D:\MySQL\Uploads 目录下的文件可以被 LOAD DATA INFILE 和 SELECT ... INTO OUTFILE 等命令访问。
• 如果未设置 secure-file-priv，则这些命令可以在任何目录下操作文件，可能存在安全风险。

2.

sql_mode

描述：

• sql_mode 是 MySQL 的 SQL 模式设置，用于控制 SQL 语句的行为和语法。
• 不同的 SQL 模式会影响 SQL 语句的解析和执行，从而影响数据的完整性和一致性。

常见模式：

• **NO_ENGINE_SUBSTITUTION**：当尝试创建一个表时，如果指定了一个不存在的存储引擎，MySQL 会抛出错误而不是使用默认的存储引擎。
• **STRICT_TRANS_TABLES**：对事务性表（如 InnoDB）启用严格模式，当插入或更新数据时，如果数据不符合表的定义（如超出列的最大长度），MySQL 会抛出错误而不是截断数据。

示例：

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

解释：

• **NO_ENGINE_SUBSTITUTION**： - 如果你在创建表时指定了一个不存在的存储引擎，MySQL 会抛出错误，而不是使用默认的存储引擎。- 例如，如果你试图创建一个使用 MyISAM 存储引擎的表，但 MyISAM 引擎不可用，MySQL 会抛出错误，而不是自动使用 InnoDB 引擎。
• **STRICT_TRANS_TABLES**： - 对事务性表（如 InnoDB）启用严格模式。- 如果插入或更新的数据不符合表的定义（如超出列的最大长度），MySQL 会抛出错误而不是截断数据。- 例如，如果你有一个 VARCHAR(10) 列，尝试插入长度为 15 的字符串，MySQL 会抛出错误而不是截断字符串。

总结

• **secure-file-priv**：限制导入导出数据文件的路径，增强安全性并便于管理。
• **sql_mode**：控制 SQL 语句的行为，确保数据的完整性和一致性。

其他设置

1.

max_allowed_packet

描述：

• max_allowed_packet 是 MySQL 中用于设置允许的最大数据包大小的配置项。
• 这个参数限制了客户端和服务器之间传输的最大数据量，包括查询结果、导入导出的数据等。

作用：

• 防止内存溢出：过大的数据包可能导致内存溢出，影响服务器的稳定性和性能。
• 支持大数据传输：对于需要传输大量数据的应用（如导入大文件、执行包含大量数据的查询），需要适当增大这个值。

建议设置：

• 对于普通的学习和开发环境，16MB 通常足够。
• 如果你需要处理更大的数据文件或执行复杂的查询，可以适当增加这个值，例如 32MB 或 64MB。

示例：

max_allowed_packet=16M  # 设置最大允许的数据包大小为 16MB

2.

max_connections

描述：

• max_connections 是 MySQL 中用于设置允许的最大并发连接数的配置项。
• 这个参数限制了同一时间内可以连接到 MySQL 服务器的最大客户端数量。
• MySQL内部限制，默认情况下，最大连接数的上限是65535（即2^16 - 1）

作用：

• 资源管理：防止过多的连接耗尽服务器资源，导致性能下降甚至崩溃。
• 并发控制：确保服务器能够处理合理的并发请求，满足应用的需求。

建议设置：

• 对于普通的学习和开发环境，150 个连接通常足够。
• 如果你需要支持更多的并发连接，可以适当增加这个值，但需要注意服务器的资源限制（如内存、CPU 等）。

示例：

max_connections=150  # 设置最大允许的连接数为 150

性能优化

1.

query_cache_type

• 注意：若在mysql8的配置文件中添加了会启动失败

描述：

• query_cache_type 是用于控制查询缓存行为的配置项。
• 查询缓存（Query Cache）用于缓存查询结果，以便下次执行相同的查询时可以直接返回缓存的结果，而不需要再次执行查询。

作用：

• 提高查询性能：对于重复的查询，查询缓存可以显著提高性能。
• 减少数据库负载：通过缓存查询结果，可以减少数据库的计算和I/O操作。

取值及其含义：

• 0：关闭查询缓存。MySQL 不会缓存任何查询结果，也不会检查缓存。
• 1（默认值）：开启查询缓存。MySQL 会缓存查询结果，并在执行查询时检查缓存。
• 2：智能缓存。MySQL 只缓存 SELECT SQL_CACHE 显式标记的查询结果。

注意事项：

• MySQL 8.0 已移除查询缓存：从 MySQL 8.0 开始，查询缓存功能已被移除，因此设置 query_cache_type 对 MySQL 8.0 及更高版本没有影响。
• 兼容性：如果你从 MySQL 5.7 升级到 MySQL 8.0，可能会看到这个配置项，但实际不起作用。

示例：

query_cache_type=0  # 关闭查询缓存（MySQL 8已移除查询缓存）

2.

thread_cache_size

描述：

• thread_cache_size 是用于设置线程缓存大小的配置项。
• 线程缓存用于缓存已关闭的线程，以便下次连接时可以快速重用这些线程，而不需要重新创建新的线程。

作用：

• 提高连接性能：通过重用已有的线程，可以减少线程创建和销毁的开销，从而提高连接性能。
• 减少系统资源消耗：频繁创建和销毁线程会消耗大量的系统资源，线程缓存可以有效减少这种情况。

建议设置：

• 对于普通的学习和开发环境，16 通常是一个合理的值。
• 对于高并发的生产环境，可以根据实际情况增加这个值，例如 50 或更高。

示例：

thread_cache_size=16  # 线程缓存大小

总结

• **query_cache_type**： - 控制查询缓存的行为。- 对于 MySQL 8.0 及更高版本，设置 query_cache_type 没有实际效果，因为查询缓存功能已被移除。- 示例：query_cache_type=0 关闭查询缓存。
• **thread_cache_size**： - 设置线程缓存的大小。- 通过缓存已关闭的线程，可以提高连接性能并减少系统资源消耗。- 示例：thread_cache_size=16 设置线程缓存大小为 16。

windows服务相关设置

[mysqld_safe]

是 MySQL 配置文件中的一个部分，专门用于配置

mysqld_safe

脚本的行为。

mysqld_safe

是一个启动脚本，用于安全地启动和停止 MySQL 服务器。它会尝试多次启动 MySQL 服务器，直到成功为止，并在启动失败时记录错误信息。

1.

log-error

描述：

• log-error 用于指定 MySQL 错误日志文件的路径。
• 错误日志记录了 MySQL 服务器在启动、运行和停止过程中发生的错误信息，以及其他重要的事件。

作用：

• 调试和故障排除：通过查看错误日志，可以诊断和解决 MySQL 服务器的问题。
• 监控：可以定期检查错误日志，确保 MySQL 服务器的正常运行。

示例：

log-error=D:\software\environment\mysql\mysql-8.0.39\Log\error.log

解释：

• 上述配置表示错误日志文件的路径为 D:\software\environment\mysql\mysql-8.0.39\Log\error.log。
• MySQL 服务器会将所有错误信息和其他重要事件记录到这个文件中。

2.

pid-file

描述：

• pid-file 用于指定 MySQL 服务器进程 ID 文件的路径。
• 进程 ID 文件记录了 MySQL 服务器进程的 PID（进程标识符）。

作用：

• 进程管理：通过读取进程 ID 文件，可以方便地管理和控制 MySQL 服务器进程，例如发送信号停止或重启服务器。
• 冲突检测：确保在同一台机器上只有一个 MySQL 服务器实例运行。

示例：

pid-file=D:\software\environment\mysql\mysql-8.0.39\mysqld.pid

解释：

• 上述配置表示进程 ID 文件的路径为 D:\software\environment\mysql\mysql-8.0.39\mysqld.pid。
• MySQL 服务器启动时会将自身的 PID 写入这个文件，停止时会删除或清空这个文件。