主页 / dba / 问题 / 300029
Accepted
RoohAllah Godazgar
Asked: 2021-09-24 01:03:33 +0800 CST

日志目录的 MariaDB 隐藏磁盘使用情况

  • 772

我们的环境中有一个简单的主/从集群，它设置在 Ubuntu 20.04 和 MariaDB 10.5 上：

MariaDB [(none)]> SELECT VERSION();
+--------------------------------------------+
| VERSION()                                  |
+--------------------------------------------+
| 10.5.11-MariaDB-1:10.5.11+maria~bionic-log |
+--------------------------------------------+

我们将所有日志目录设置为/var/lib/mysql包括 bin log & relay log: /var/lib/mysql/binfiles。

对于这个目录 ( /var/lib/mysql)，我创建了一个 100GB 空间的 LVM，但是 2 天后，我收到了一条警报，说这个目录已满，我必须向它添加一个额外的 100GB 空间。

奇怪的是这个目录中的文件只使用了 18GB 磁盘空间，我不知道其余的发生了什么：

输出df -h：

Filesystem                           Size  Used Avail Use% Mounted on
/dev/mapper/vg--data-lv--mysql--log   99G   91G  3.4G  97% /var/log/mysql

输出du -h --max-depth=1 /var/log/mysql（包括隐藏文件；没有）：

13G /var/log/mysql/binfiles
19G /var/log/mysql

包含/var/log/mysql（包括隐藏文件；没有）：

total 5426300
drwxr-s---  3 mysql adm          4096 Sep 23 00:18 .
drwxrwxr-x 10 root  syslog       4096 Sep 23 00:18 ..
drwxr-sr-x  2 mysql adm          4096 Sep 23 12:21 binfiles
-rw-r-----  1 mysql adm             0 Sep 23 00:00 mariadb-slow.log
-rw-rw----  1 mysql adm      14629234 Sep 23 00:00 mariadb-slow.log.1.gz
-rw-rw----  1 mysql adm    1074177544 Sep 22 21:29 mariadb-slow.log.old
-rw-rw----  1 mysql adm         33699 Sep 22 05:12 mariadb.err
-rw-r-----  1 mysql adm             0 Sep 23 00:00 mysql.log
-rw-rw----  1 mysql adm    4467658081 Sep 23 00:00 mysql.log.1.gz

包含/etc/mysql/my.cnf：

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
# port = 3306
socket                  = /run/mysqld/mysqld.sock

[client]
port                    = 3306
socket                  = /var/run/mysqld/mysqld.sock

[isamchk]
key_buffer              = 16M

[mariabackup]
open_files_limit            = 65535

[mysql]
#no-auto-rehash                                                 # faster start of mysql but no tab completion

[mysqld_safe]
socket                  = /var/run/mysqld/mysqld.sock
nice                    = 0

[mysqldump]
quick
quote-names
max_allowed_packet          = 16M

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

PS：示例中有一些配置文件/etc/mysql/mariadb.conf.d/，我已经注释了其中的所有配置。我的主要配置位于 2 个文件中/etc/mysql/conf.d/：mysqld.cnf&master.cnf

包含mysqld.cnf：

[mysqld]
# General
plugin_load_add             = query_response_time
user                    = mysql
pid-file                = /var/run/mysqld/mysqld.pid
socket                  = /var/run/mysqld/mysqld.sock
port                    = 3306
basedir                 = /usr
datadir                 = /var/lib/mysql
tmpdir                  = /tmp
lc_messages_dir             = /usr/share/mysql
lc_messages             = en_US
skip-external-locking
skip_name_resolve           = 0
myisam_recover_options          = BACKUP
concurrent_insert           = 2
sql_mode                = NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION
default_storage_engine          = InnoDB
performance_schema          = ON
query_response_time_stats       = ON
userstat                = ON
innodb_buffer_pool_dump_at_shutdown     = OFF
innodb_rollback_on_timeout              = ON


# Performance
max_connections             = 5000
connect_timeout             = 5
wait_timeout                = 600
max_allowed_packet          = 100M
thread_cache_size           = 128
sort_buffer_size            = 4M
bulk_insert_buffer_size         = 16M
tmp_table_size              = 128M
max_heap_table_size         = 128M
key_buffer_size             = 128M
open-files-limit            = 65535
table_open_cache            = 2048
myisam_sort_buffer_size         = 512M
read_buffer_size            = 2M
read_rnd_buffer_size            = 1M
query_cache_limit           = 0
query_cache_size            = 0
long_query_time             = 0
join_buffer_size            = 3M
table_definition_cache                  = 1424

# Innodb
innodb_buffer_pool_size         = 186G
###innodb_buffer_pool_instances     = 64
innodb_monitor_enable           = all
innodb_file_per_table           = 1
innodb_open_files           = 65535
innodb_io_capacity          = 30000
innodb_io_capacity_max          = 40000
innodb_flush_neighbors          = 0
innodb_flush_method         = O_DIRECT
innodb_log_buffer_size          = 8M
innodb_log_file_size            = 6G
###innodb_log_files_in_group        = 2
innodb_log_group_home_dir       = /var/lib/mysql/
innodb_autoinc_lock_mode        = 2 

# Log
general_log             = 1
general_log_file            = /var/log/mysql/mysql.log
slow_query_log              = 1
slow_query_log_file         = /var/log/mysql/mariadb-slow.log
expire_logs_days            = 5
log_error               = /var/log/mysql/mariadb.err
log_warnings                = 0
long_query_time             = 0.5
#log_slow_rate_limit            = 1000
log_queries_not_using_indexes       = ON
log_slow_verbosity          = query_plan,explain
log_slow_admin_statements       = ON
log_slow_slave_statements       = ON
log_bin                 = /var/log/mysql/binfiles/mariadb-bin
log_bin_index               = /var/log/mysql/binfiles/mariadb-bin.index
max_binlog_size             = 100M
binlog_format               = row
relay_log                               = /var/log/mysql/binfiles/mariadb-relay-bin
relay_log_index                         = /var/log/mysql/binfiles/mariadb-relay-bin.index
max_relay_log_size                      = 100M

包含master.cnf：

[mysqld]
# Master/Slave settings for master node
bind-address                = a.b.c.d
server-id               = 10
gtid_domain_id              = 0
log_slave_updates           = 1
innodb_flush_log_at_trx_commit      = 1

我认为这无关紧要，但我也在我的/etc/sysctl.conf文件中设置了这些：

net.core.somaxconn=65535
fs.file-max=2097152
fs.nr_open=2097152
vm.max_map_count=262144
net.core.rmem_max=16777216
net.core.wmem_max=16777216
net.core.rmem_default=262144
net.core.wmem_default=262144
net.ipv4.tcp_max_syn_backlog=65535
net.ipv4.tcp_fin_timeout=30
net.ipv4.tcp_syn_retries=2
net.core.netdev_max_backlog=300000
net.ipv4.ip_local_port_range=1024 65535
net.ipv4.tcp_wmem=8192 65536 16777216
net.ipv4.tcp_rmem=8192 87380 16777216
net.ipv4.tcp_keepalive_intvl=30
net.ipv4.tcp_keepalive_time=120

您能否指导我在/var/log/mysql没有其他文件时使用的空间以及如何修复它？

编辑 1：数据目录 ( /var/lib/mysql) 和日志目录 ( /var/log/mysql) 各有一个单独的分区；所以它必须是关于这个目录（/var/log/mysql）的东西，而不是其他东西。

更新 1：似乎当日志、慢日志和错误日志文件轮换时，mariadb 仍在使用它们，并且发生了这种情况。知道如何解决这个问题（显然我可以重新启动服务）？

log mariadb-10.5

1 个回答

  1. Best Answer

    好的，我找到了问题，这就是我在问题中添加的“更新 1 ”。主要问题是关于 logrotate；让我们看看原始内容：

    # - I put everything in one block and added sharedscripts, so that mysql gets
#   flush-logs'd only once.
#   Else the binary logs would automatically increase by n times every day.
# - The error log is obsolete, messages go to syslog now.
/var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log /var/log/mysql/mariadb-slow.log /var/log/mysql/error.log /var/log/mysql/mariadb.err {
    daily
    rotate 7
    missingok
    create 640 mysql adm
    compress
    sharedscripts
    postrotate
          test -x /usr/bin/mysqladmin || exit 0
          if [ -f `my_print_defaults --mysqld | grep -oP "pid-file=\K[^$]+"` ]; then
            # If this fails, check debian.conf!
            mysqladmin --defaults-file=/etc/mysql/debian.cnf --local flush-error-log \
              flush-engine-log flush-general-log flush-slow-log
          fi
    endscript
}

    首先，我测试了是否可以访问mysqladmin，我运行了以下命令：

    mysqladmin --defaults-file=/etc/mysql/debian.cnf ping

    它说：

    mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'

    那是因为我已经恢复了自己的数据并删除了所有内容（包括mysql数据库）；所以我已经改变了/etc/mysql/debian.cnf它应该是并运行相同的命令来查看是否一切都很好，它是：

    mysqld is alive

    然后为了释放磁盘，我手动执行了 logrotate 过程：

    logrotate --force /etc/logrotate.d/mysql-server

    但它说：

    logrotate_script: 3: [: /var/run/mysqld/mysqld.pid: unexpected operator

    我通过运行以下命令检查了我的配置并搜索pid：

    grep -inR "pid" /etc/mysql

    输出是：

    /etc/mysql/conf.d/mysqld.cnf:5:pid-file             = /var/run/mysqld/mysqld.pid
/etc/mysql/mariadb.conf.d/50-server.cnf:16:pid-file                = /run/mysqld/mysqld.pid

    所以我对我的配置不够小心，留下了两个不同的地方pid-file；我已经评论了第二个，但我不能只是重新启动它；所以我更改了 logrotate 文件并将-m1选项添加到grep：

    /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log /var/log/mysql/mariadb-slow.log /var/log/mysql/error.log /var/log/mysql/mariadb.err {
    daily
    rotate 7
    missingok
    create 640 mysql adm
    compress
    sharedscripts
    postrotate
          test -x /usr/bin/mysqladmin || exit 0
          if [ -f `my_print_defaults --mysqld | grep -m1 -oP "pid-file=\K[^$]+"` ]; then
            # If this fails, check debian.conf!
            mysqladmin --defaults-file=/etc/mysql/debian.cnf --local flush-error-log \
              flush-engine-log flush-general-log flush-slow-log
          fi
    endscript
}

    然后我再次运行 logrotate，就是这样。

    • 2

相关问题