将web网站服务器的Mysql5.5数据库更换为了Mariadb10.1.23,Mariadb配置文件说明

2018-03-20 · Ryan · 发表评论

MariaDB 配置文件参数说明

以下是对 MariaDB/MySQL 配置文件中常见核心参数的说明与建议。请注意，参数的具体数值需根据服务器硬件配置、数据量及业务负载进行调整。

通用与连接参数

key_buffer_size：用于缓存 MyISAM 存储引擎的索引。若主要使用 InnoDB 引擎，可设置为较小值，如 64MB。
max_allowed_packet：服务器与客户端通信时允许的最大数据包大小。过小可能导致操作失败，过大则存在风险。通常建议设置为 16M 或更高。
table_open_cache：缓存已打开表的文件描述符数量。设置过小会导致频繁打开/关闭表文件，影响性能；过大则可能耗尽系统文件描述符。建议根据并发连接数调整。
max_connections：允许的最大并发连接数。设置过高可能导致内存耗尽和性能下降。一般中小型网站设置在 200-500，大型或高并发应用可能需要更高。
max_connect_errors：允许的最大连接错误数。为防止因网络波动导致客户端被误封禁，可适当调高此值。
open_files_limit：MySQL/MariaDB 进程可打开的最大文件数。需确保此值大于 table_open_cache 等参数的需求，并受系统限制。
thread_cache_size：缓存空闲线程的数量。可减少频繁创建和销毁线程的开销，提升连接响应速度。

查询与排序缓存

sort_buffer_size：用于对无索引字段进行 ORDER BY 或 GROUP BY 排序操作的缓冲区大小。每个连接线程独享。设置过大（如数MB）会消耗过多内存，通常 256K-1M 即可。根本解决方法是增加索引。
read_buffer_size：用于顺序扫描（如全表扫描）的缓冲区大小。每个连接线程独享。通常 128K-256K 足够。
read_rnd_buffer_size：用于随机读取（如按非索引字段排序后读取行）的缓冲区大小。每个连接线程独享。通常 256K-512K。
query_cache_size：注意：自 MariaDB 10.1.7 / MySQL 5.7.20 起，查询缓存已被弃用并在后续版本中移除。 对于旧版本，若读多写少可尝试开启（query_cache_type=1），但写频繁的场景下建议关闭（query_cache_type=0 且 query_cache_size=0）以避免缓存失效带来的性能开销。
tmp_table_size：内存临时表的最大大小。超过此限制的临时表会转换为磁盘上的 MyISAM 表。需与 max_heap_table_size 配合设置。

InnoDB 存储引擎核心参数

innodb_buffer_pool_size：最重要的参数。用于缓存 InnoDB 的表数据和索引。在专用数据库服务器上，通常设置为可用物理内存的 60%-80%。对于 4G 内存的服务器，建议至少设置为 1G-2G。
innodb_file_per_table：设置为 1（默认）时，每个 InnoDB 表使用独立的 .ibd 文件。便于管理、备份和回收空间，推荐启用。
innodb_flush_log_at_trx_commit：控制事务日志的持久化级别。
- 1（默认）：每次事务提交都写入并刷新到磁盘，最安全，性能较低。
- 2：每次事务提交写入日志文件，但每秒刷新一次到磁盘。在操作系统崩溃时可能丢失约1秒的数据。
- 0：每秒写入并刷新一次日志。性能最高，但崩溃时可能丢失最多1秒的数据。
根据对数据安全性和性能的要求进行权衡。
innodb_log_file_size：单个重做日志文件的大小。更大的日志文件可以减少检查点操作，提升写性能，但会增加崩溃恢复的时间。通常建议设置为 innodb_buffer_pool_size 的 25% 左右。
innodb_log_buffer_size：重做日志缓冲区大小。对于有大量更新事务的场景，适当增大（如 16M-64M）可减少磁盘 I/O。
innodb_lock_wait_timeout：InnoDB 行锁等待超时时间（秒）。超过此时间的事务将被回滚。默认 50 秒，可根据应用场景调整。

二进制日志与复制

log-bin：启用二进制日志，用于数据复制和基于时间点的恢复。
binlog_format：二进制日志格式。STATEMENT（基于SQL语句）、ROW（基于行变化）、MIXED（混合模式）。MIXED 是较通用的选择。
server-id：在复制拓扑中，每个服务器必须具有唯一的 ID。
expire_logs_days：自动清理多少天前的二进制日志文件，防止磁盘被占满。建议设置为 7-14 天。

4G 内存服务器配置示例

以下是一个适用于 4G 内存、以 InnoDB 为主的 MariaDB 服务器的参考配置片段。请根据实际情况调整路径和参数值。

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
user        = mariadb
basedir     = /usr/local/mariadb
datadir     = /data/mysql
log_error   = /data/mysql/mariadb.err
pid-file    = /data/mysql/mariadb.pid
skip-external-locking

# 通用参数
key_buffer_size         = 64M
max_allowed_packet      = 16M
table_open_cache        = 512
sort_buffer_size        = 1M
read_buffer_size        = 256K
read_rnd_buffer_size    = 512K
myisam_sort_buffer_size = 32M
thread_cache_size       = 32
tmp_table_size          = 64M
max_heap_table_size     = 64M

# 连接与限制
explicit_defaults_for_timestamp = true
max_connections         = 300
max_connect_errors      = 10000
open_files_limit        = 65535

# 二进制日志
log-bin                 = mysql-bin
binlog_format           = mixed
server-id               = 1
exire_logs_days         = 10

# InnoDB 引擎
default_storage_engine          = InnoDB
innodb_file_per_table           = 1
innodb_buffer_pool_size         = 2G
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_flush_log_at_trx_commit  = 2
innodb_lock_wait_timeout        = 30

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 1M
read_buffer = 2M
write_buffer = 2M

重要提示：修改配置文件（通常是 my.cnf 或 my.ini）后，需要重启 MariaDB/MySQL 服务才能使更改生效。建议在调整重要参数（尤其是 innodb_buffer_pool_size）前，先在测试环境验证。