Log
日誌目錄的 MariaDB 隱藏磁碟使用情況
我們的環境中有一個簡單的主/從集群，它設置在 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，就是這樣。