Ubuntu上的MariaDB日志管理涉及多个方面,包括日志类型、配置、查看、轮转与管理、分析与监控等。以下是详细的要点:
SHOW VARIABLES LIKE 'log_error';
在/etc/mysql/mariadb.conf.d/50-server.cnf
中配置:[mysqld]
log_error = /var/log/mysql/mariadb-error.log
log_error_verbosity = 3
SET GLOBAL general_log = 'ON';
SET GLOBAL general_log_file = '/var/log/mysql/mariadb-query.log';
永久配置(/etc/mysql/mariadb.conf.d/50-server.cnf
):[mysqld]
general_log = 1
general_log_file = /var/log/mysql/mariadb-query.log
general_log_output = FILE
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mariadb-slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';
(/etc/mysql/mariadb.conf.d/50-server.cnf
)[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
SET GLOBAL log_bin = 'ON';
SET GLOBAL binlog_format = 'ROW';
SET GLOBAL expire_logs_days = 7;
(/etc/mysql/mariadb.conf.d/50-server.cnf
)[mysqld]
log_bin = /var/log/mysql/mariadb-bin
binlog_format = ROW
expire_logs_days = 7
sync_binlog = 1
使用logrotate
进行日志轮转:
/etc/logrotate.d/mariadb {
daily
rotate 7
missingok
compress
delaycompress
notifempty
create 640 mysql mysql
postrotate
if test -x /usr/bin/mysqladmin && \
/usr/bin/mysqladmin ping &/dev/null; then
/usr/bin/mysqladmin flush-logs
fi
endscript
}
手动轮转日志:
mysqladmin flush-logs
或通过SQL:
FLUSH LOGS;
mysqldumpslow
分析慢查询日志:mysqldumpslow /var/log/mysql/mariadb-slow.log
使用pt-query-digest
(Percona工具包):pt-query-digest /var/log/mysql/mariadb-slow.log
mysqlbinlog /var/log/mysql/mariadb-bin.000001
只查看特定时间范围内的日志:mysqlbinlog --start-datetime="2023-01-01 00:00:00" --stop-datetime="2023-01-31 23:59:59" /var/log/mysql/mariadb-bin.000001
通过以上配置和管理要点,可以有效地进行Ubuntu上MariaDB的日志管理,确保数据库的高效运行和安全性。