Linux系统中MariaDB的配置技巧

Linux系统中 MariaDB 配置技巧

一 基础安全与远程访问

• 运行安全初始化：执行 mysql_secure_installation，设置 root 密码、删除匿名用户、禁止 root 远程登录、移除测试库并刷新权限，提升基线安全性。完成后用 mysql -u root -p 验证登录。
• 远程访问的正确做法：优先创建专用用户并授权，例如：
  CREATE USER ‘app’@‘10.0.0.%’ IDENTIFIED BY ‘StrongPass!’;
  GRANT SELECT,INSERT,UPDATE,DELETE ON appdb.* TO ‘app’@‘10.0.0.%’;
  FLUSH PRIVILEGES;
  不建议直接放开 root@‘%’。
• 放开防火墙端口（以 firewalld 为例）：firewall-cmd --zone=public --add-port=3306/tcp --permanent && firewall-cmd --reload。
• 排查网络连通性：确认未禁用网络参数（如 skip-networking），必要时检查 bind-address 是否限制了来源地址；若仍不通，复核云安全组/本机防火墙策略。

二 配置文件与关键参数

• 配置文件路径（常见）：/etc/mysql/mariadb.conf.d/50-server.cnf（Debian/Ubuntu），或 /etc/my.cnf /etc/my.cnf.d/server.cnf（RHEL/CentOS）。修改前先备份，变更后重启服务生效。
• InnoDB 缓冲池：将 innodb_buffer_pool_size 设为物理内存的约 50%–75%（专用数据库可更高），如：innodb_buffer_pool_size=4G。大内存实例可配合 innodb_buffer_pool_instances 减少缓存争用（如 >1GB 时可考虑 8 或更高）。
• 日志与提交策略：适度增大 innodb_log_file_size（如 256M 起，视写入强度调至 1G–2G）；在允许一定数据丢失风险的场景可将 innodb_flush_log_at_trx_commit=2 提升吞吐，严格持久化保持默认 1。
• 查询与临时表：仅在确有大量重复查询时启用查询缓存（MariaDB 10.1+ 默认关闭），如：query_cache_type=1; query_cache_size=64M；同时调大 tmp_table_size 与 max_heap_table_size（如各 256M）以减少磁盘临时表。
• 连接与会话：根据业务峰值设置 max_connections（如 500 起），并通过 Threads_connected/Threads_created/Threads_cached 观察是否需要调大 thread_cache_size。
• 名称解析优化：在 [mysqld] 加入 skip_name_resolve，避免 DNS 反向解析带来的连接延迟与 “unauthenticated connections”。
• 可用性与观测：开启 slow_query_log=1、设置 long_query_time=2，将慢 SQL 记录到文件（如 /var/log/mysql/slow.log）以便用 EXPLAIN 持续分析与优化。

三 字符集与大小写敏感

• 统一使用 UTF-8：在 [client] 设置 default-character-set=utf8；在 [mysqld] 设置 character-set-server=utf8、collation-server=utf8_unicode_ci，确保连接与存储一致。
• 表名大小写（Linux 文件系统区分大小写）：在 [mysqld] 加入 lower_case_table_names=1 实现不区分大小写。注意：该参数在已有数据/表时变更可能导致兼容性问题，通常应在初始化阶段设定，变更前务必完整备份并按需重建库表。

四 运维与监控实践

• 资源与内核：适度提高文件描述符限制（如 ulimit -n 65535），并结合负载调优内核参数（如 vm.swappiness=10、vm.vfs_cache_pressure=50），减少换页与缓存压力。
• 表维护与空间回收：对高碎片表执行 OPTIMIZE TABLE；周期性清理或归档 二进制日志 与 慢查询日志，避免磁盘占满。
• 监控与告警：结合 Prometheus + Grafana 等监控 MariaDB 关键指标（连接数、缓冲池命中率、慢查询数、复制延迟等），建立基线并做容量规划与异常告警。

五 快速排障清单

• 无法远程连接：确认未设置 skip-networking，检查 bind-address 是否仅绑定 127.0.0.1；在防火墙/云安全组放行 3306/tcp；验证用户主机权限是否为 ‘%’ 或指定网段。
• 登录缓慢或间歇性卡顿：启用 skip_name_resolve，避免 DNS 故障引发全表扫描式授权校验；复核 max_connections 与 thread_cache_size 是否匹配并发。
• 导入/大事务变慢：适度增大 innodb_log_file_size，并在允许的场景将 innodb_flush_log_at_trx_commit 调为 2；确保使用 InnoDB 并合理设置事务边界。
• 磁盘空间告警：清理或轮转 slow_query_log、binary logs；对碎片较多的 InnoDB 表执行 OPTIMIZE TABLE。