Log

日誌目錄的 MariaDB 隱藏磁碟使用情況

  • September 23, 2021

我們的環境中有一個簡單的主/從集群,它設置在 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 仍在使用它們,並且發生了這種情況。知道如何解決這個問題(顯然我可以重新啟動服務)?

好的,我找到了問題,這就是我在問題中添加的“更新 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,就是這樣。

引用自:https://dba.stackexchange.com/questions/300029