2. 首页 > MariaDB教程 > 正文

MariaDB教程FG008-MariaDB systemd服务管理与日志配置标准化实战

教程整理：风哥教程  |  更新时间：2026-01-01  |  教程分类：MariaDB教程  |  文档学习：5

本文档风哥主要介绍MariaDB的systemd服务管理与日志配置标准化，包括systemd服务文件配置、日志管理、服务管理命令等内容。风哥教程参考MariaDB官方文档Systemd Service、Logging内容，适合数据库管理员学习和实施。

Part01-基础概念与理论知识

1.1 systemd概述

systemd是现代Linux系统的初始化系统和服务管理器，负责：

  • 系统引导
  • 服务管理
  • 日志管理
  • 设备管理
  • 电源管理

1.2 服务管理基础

MariaDB服务管理包括：

  • 启动/停止/重启服务
  • 设置开机自启
  • 查看服务状态
  • 服务依赖管理

1.3 日志管理基础

MariaDB日志管理包括：

  • 错误日志
  • 查询日志
  • 慢查询日志
  • 二进制日志
  • 中继日志
更多视频教程www.fgedu.net.cn

Part02-生产环境规划与建议

2.1 服务规划

风哥提示：生产环境应使用systemd管理MariaDB服务，确保服务的稳定运行和自动恢复。

2.2 日志规划

日志规划建议：

  • 单独存储日志文件
  • 设置合理的日志轮转
  • 配置适当的日志级别
  • 定期清理日志文件

2.3 标准化建议

标准化建议：

  • 统一服务文件配置
  • 统一日志格式
  • 统一管理命令
  • 统一监控标准
学习交流加群风哥微信: itpux-com

Part03-生产环境项目实施方案

3.1 systemd服务配置

更多学习教程公众号风哥教程itpux_com

# 创建systemd服务文件
[root@fgedu.net.cn ~]# cat > /etc/systemd/system/mariadb.service << 'EOF'
[Unit]
Description=MariaDB 10.11.8 database server
After=network.target
[Service]
Type=simple
User=mysql
Group=mysql
ExecStart=/usr/local/mariadb/bin/mysqld –defaults-file=/etc/my.cnf
ExecStop=/usr/local/mariadb/bin/mysqladmin –defaults-file=/etc/my.cnf -u root -p shutdown
Restart=on-failure
RestartSec=5s
[Install]
WantedBy=multi-user.target
EOF
# 重新加载systemd配置
[root@fgedu.net.cn ~]# systemctl daemon-reload
# 启用服务
[root@fgedu.net.cn ~]# systemctl enable mariadb
# 启动服务
[root@fgedu.net.cn ~]# systemctl start mariadb

3.2 日志配置

# 配置日志参数
[root@fgedu.net.cn ~]# cat >> /etc/my.cnf << 'EOF'
# 日志配置
log_error = /mariadb/logs/mariadb.err
slow_query_log = 1
slow_query_log_file = /mariadb/logs/slow.log
long_query_time = 1
general_log = 0
general_log_file = /mariadb/logs/general.log
# 二进制日志
binlog_format = ROW
log_bin = /mariadb/logs/binlog
binlog_expire_logs_seconds = 86400
max_binlog_size = 100M
EOF
# 创建日志目录
[root@fgedu.net.cn ~]# mkdir -p /mariadb/logs
[root@fgedu.net.cn ~]# chown -R mysql:mysql /mariadb/logs
# 重启服务
[root@fgedu.net.cn ~]# systemctl restart mariadb
# 验证日志配置
[root@fgedu.net.cn ~]# mysql -u root -p
Enter password:
MariaDB [(none)]> SHOW VARIABLES LIKE ‘%log%’;
+—————————————–+——————————–+—————+
| Variable_name | Value | Type |
+—————————————–+——————————–+—————+
| log_bin | ON | global |
| log_bin_basename | /mariadb/logs/binlog | global |
| log_bin_index | /mariadb/logs/binlog.index | global |
| log_error | /mariadb/logs/mariadb.err | global |
| log_output | FILE | global |
| log_queries_not_using_indexes | OFF | global |
| log_slave_updates | OFF | global |
| log_slow_admin_statements | OFF | global |
| log_slow_slave_statements | OFF | global |
| log_warnings | 2 | global |
| long_query_time | 1.000000 | global |
| slow_query_log | ON | global |
| slow_query_log_file | /mariadb/logs/slow.log | global |
+—————————————–+——————————–+—————+

3.3 服务管理

# 服务管理命令
# 查看服务状态
[root@fgedu.net.cn ~]# systemctl status mariadb
# 启动服务
[root@fgedu.net.cn ~]# systemctl start mariadb
# 停止服务
[root@fgedu.net.cn ~]# systemctl stop mariadb
# 重启服务
[root@fgedu.net.cn ~]# systemctl restart mariadb
# 重新加载配置
[root@fgedu.net.cn ~]# systemctl reload mariadb
# 查看服务启动时间
[root@fgedu.net.cn ~]# systemctl show -p ActiveEnterTimestamp mariadb
# 查看服务依赖
[root@fgedu.net.cn ~]# systemctl list-dependencies mariadb
# 查看服务日志
[root@fgedu.net.cn ~]# journalctl -u mariadb
[root@fgedu.net.cn ~]# journalctl -u mariadb -n 50
[root@fgedu.net.cn ~]# journalctl -u mariadb –since “1 hour ago”
学习交流加群风哥QQ113257174

Part04-生产案例与实战讲解

4.1 systemd服务配置实战

# 检查服务状态
[root@fgedu.net.cn ~]# systemctl status mariadb
● mariadb.service – MariaDB 10.11.8 database server
Loaded: loaded (/etc/systemd/system/mariadb.service;
enabled;
vendor preset: disabled)
Active: active (running) since Mon 2026-04-07 16:00:00 CST;
10min ago
Main PID: 5678 (mysqld)
Status: “Taking your SQL requests now…”
Tasks: 35 (limit: 32768)
Memory: 1.2G
CPU: 2.5s
CGroup: /system.slice/mariadb.service
└─5678 /usr/local/mariadb/bin/mysqld –defaults-file=/etc/my.cnf
Apr 07 16:00:00 fgedu.net.cn systemd[1]: Started MariaDB 10.11.8 database server.
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] /usr/local/mariadb/bin/mysqld (server 10.11.8-MariaDB) starting as process 5678 …
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] InnoDB: Number of pools: 1
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] InnoDB: Initializing buffer pool, total size = 4G, instances = 4, chunk size = 1G
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] InnoDB: Completed initialization of buffer pool
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=16384
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] InnoDB: 128 rollback segments are active.
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] InnoDB: Setting file ‘./ibtmp1’ size to 12.0 MB. Physically writing the file full;
Please wait …
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] InnoDB: File ‘./ibtmp1’ size is now 12.0 MB.
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] InnoDB: 10.11.8 started;
log sequence number 16384;
transaction id 2
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] Plugin ‘FEEDBACK’ is disabled.
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] Server socket created on IP: ‘0.0.0.0’.
Apr 07 16:00:00 fgedu.net.cn mysqld[5678]: 2026-04-07 16:00:00 0 [Note] /usr/local/mariadb/bin/mysqld: ready for connections.
Version: ‘10.11.8-MariaDB’ socket: ‘/tmp/mysql.sock’ port: 3306 MariaDB Server

4.2 日志配置实战

# 查看错误日志
[root@fgedu.net.cn ~]# tail -f /mariadb/logs/mariadb.err
2026-04-07 16:00:00 0 [Note] /usr/local/mariadb/bin/mysqld (server 10.11.8-MariaDB) starting as process 5678 …
2026-04-07 16:00:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2026-04-07 16:00:00 0 [Note] InnoDB: Number of pools: 1
2026-04-07 16:00:00 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2026-04-07 16:00:00 0 [Note] InnoDB: Initializing buffer pool, total size = 4G, instances = 4, chunk size = 1G
2026-04-07 16:00:00 0 [Note] InnoDB: Completed initialization of buffer pool
2026-04-07 16:00:00 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=16384
2026-04-07 16:00:00 0 [Note] InnoDB: 128 rollback segments are active.
2026-04-07 16:00:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2026-04-07 16:00:00 0 [Note] InnoDB: Setting file ‘./ibtmp1’ size to 12.0 MB. Physically writing the file full;
Please wait …
2026-04-07 16:00:00 0 [Note] InnoDB: File ‘./ibtmp1’ size is now 12.0 MB.
2026-04-07 16:00:00 0 [Note] InnoDB: 10.11.8 started;
log sequence number 16384;
transaction id 2
2026-04-07 16:00:00 0 [Note] Plugin ‘FEEDBACK’ is disabled.
2026-04-07 16:00:00 0 [Note] Server socket created on IP: ‘0.0.0.0’.
2026-04-07 16:00:00 0 [Note] /usr/local/mariadb/bin/mysqld: ready for connections.
Version: ‘10.11.8-MariaDB’ socket: ‘/tmp/mysql.sock’ port: 3306 MariaDB Server
# 测试慢查询日志
[root@fgedu.net.cn ~]# mysql -u root -p
Enter password:
MariaDB [(none)]> USE fgedudb;
Database changed
# 创建测试表
MariaDB [fgedudb]> CREATE TABLE fgedu_test (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100));
Query OK, 0 rows affected (0.01 sec)
# 插入测试数据
MariaDB [fgedudb]> DELIMITER //
MariaDB [fgedudb]> CREATE PROCEDURE insert_test_data()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 100000 DO
INSERT INTO fgedu_test (name) VALUES (CONCAT(‘test_’, i));
SET i = i + 1;
END WHILE;
END //
MariaDB [fgedudb]> DELIMITER;
MariaDB [fgedudb]> CALL insert_test_data();
Query OK, 1 row affected (10.23 sec)
# 执行慢查询
MariaDB [fgedudb]> SELECT * FROM fgedu_test WHERE id > 50000;
+——-+———+
| id | name |
+——-+———+
| 50001 | test_50001 |
| 50002 | test_50002 |
| … | … |
| 100000 | test_100000 |
+——-+———+
50000 rows in set (0.23 sec)
# 查看慢查询日志
[root@fgedu.net.cn ~]# tail -f /mariadb/logs/slow.log
# Time: 2026-04-07 16:15:00
# User@Host: root[root] @ fgedu.localhost []
# Thread_id: 8 Schema: fgedudb QC_hit: No
# Query_time: 0.230000 Lock_time: 0.000000 Rows_sent: 50000 Rows_examined: 100000
SET timestamp=1712487300;
SELECT * FROM fgedu_test WHERE id > 50000;

4.3 服务管理实战

# 测试服务自动重启
[root@fgedu.net.cn ~]# systemctl status mariadb
● mariadb.service – MariaDB 10.11.8 database server
Loaded: loaded (/etc/systemd/system/mariadb.service;
enabled;
vendor preset: disabled)
Active: active (running) since Mon 2026-04-07 16:00:00 CST;
20min ago
# 模拟服务故障
[root@fgedu.net.cn ~]# pkill -9 mysqld
# 查看服务状态
[root@fgedu.net.cn ~]# systemctl status mariadb
● mariadb.service – MariaDB 10.11.8 database server
Loaded: loaded (/etc/systemd/system/mariadb.service;
enabled;
vendor preset: disabled)
Active: activating (auto-restart) (Result: signal) since Mon 2026-04-07 16:20:00 CST;
2s ago
Process: 5678 ExecStart=/usr/local/mariadb/bin/mysqld –defaults-file=/etc/my.cnf (code=killed, signal=KILL)
Main PID: 5678 (code=killed, signal=KILL)
# 等待自动重启
[root@fgedu.net.cn ~]# sleep 10
# 查看服务状态
[root@fgedu.net.cn ~]# systemctl status mariadb
● mariadb.service – MariaDB 10.11.8 database server
Loaded: loaded (/etc/systemd/system/mariadb.service;
enabled;
vendor preset: disabled)
Active: active (running) since Mon 2026-04-07 16:20:05 CST;
5s ago
Main PID: 5890 (mysqld)
Status: “Taking your SQL requests now…”
# 验证服务恢复
[root@fgedu.net.cn ~]# mysql -u root -p -e “SELECT VERSION();
”
Enter password:
+—————–+
| VERSION() |
+—————–+
| 10.11.8-MariaDB |
+—————–+
风哥提示：安全开发是防止SQL注入的第一道防线

Part05-风哥经验总结与分享

5.1 常见问题与解决

  • 服务启动失败：查看错误日志，检查权限和配置
  • 日志文件过大：配置日志轮转，定期清理
  • 服务自动重启失败：检查系统资源，调整重启策略
  • 日志权限问题：确保mysql用户对日志目录有写权限

5.2 最佳实践

风哥提示：生产环境应配置合理的服务管理策略和日志管理方案，确保服务的稳定运行和问题的及时排查。

5.3 故障排查

  • 查看systemd服务日志：journalctl -u mariadb
  • 查看MariaDB错误日志：tail -f /mariadb/logs/mariadb.err
  • 检查服务状态：systemctl status mariadb
  • 检查端口监听：netstat -tlnp | grep 3306
  • 检查进程状态：ps aux | grep mysqld
from MariaDB视频:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息