Is it normal to have 26 G in /var/lib/mysql

du -xh --max-depth=3 /var/lib 16K /var/lib/initramfs-tools 333M /var/lib/clamav 364K /var/lib/ucf/cache 476K /var/lib/ucf 4.0K /var/lib/systemd/coredump 4.0K /var/lib/systemd/deb-systemd-user-helper-masked 4.0K /var/lib/systemd/linger 4.0K /var/lib/systemd/deb-systemd-helper-enabled/open-vm-tools.service.requires 4.0K /var/lib/systemd/deb-systemd-helper-enabled/timers.target.wants 4.0K /var/lib/systemd/deb-systemd-helper-enabled/sockets.target.wants 4.0K /var/lib/systemd/deb-systemd-helper-enabled/.wants 4.0K /var/lib/systemd/deb-systemd-helper-enabled/default.target.wants 4.0K /var/lib/systemd/deb-systemd-helper-enabled/sleep.target.wants 4.0K /var/lib/systemd/deb-systemd-helper-enabled/cloud-init.target.wants 4.0K /var/lib/systemd/deb-systemd-helper-enabled/emergency.target.wants 4.0K /var/lib/systemd/deb-systemd-helper-enabled/mdmonitor.service.wants 4.0K /var/lib/systemd/deb-systemd-helper-enabled/sysinit.target.wants 4.0K /var/lib/systemd/deb-systemd-helper-enabled/multi-user.target.wants 4.0K /var/lib/systemd/deb-systemd-helper-enabled/rescue.target.wants 4.0K /var/lib/systemd/deb-systemd-helper-enabled/paths.target.wants 4.0K /var/lib/systemd/deb-systemd-helper-enabled/graphical.target.wants 376K /var/lib/systemd/deb-systemd-helper-enabled 4.0K /var/lib/systemd/timesync 4.0K /var/lib/systemd/timers 4.0K /var/lib/systemd/deb-systemd-user-helper-enabled/sockets.target.wants 32K /var/lib/systemd/deb-systemd-user-helper-enabled 144K /var/lib/systemd/catalog 580K /var/lib/systemd 4.0K /var/lib/git 4.0K /var/lib/letsencrypt 4.0K /var/lib/milter-greylist 4.0K /var/lib/os-prober 4.0K /var/lib/quota 4.0K /var/lib/update-notifier/package-data-downloads/partial 8.0K /var/lib/update-notifier/package-data-downloads 4.0K /var/lib/update-notifier/user.d 20K /var/lib/update-notifier 8.0K /var/lib/dovecot 4.0K /var/lib/sudo/lectured 8.0K /var/lib/sudo 4.0K /var/lib/dhcp 8.0K /var/lib/grub/ucf 4.0K /var/lib/grub/esp 16K /var/lib/grub 4.0K /var/lib/unattended-upgrades 4.0K /var/lib/awstats 20K /var/lib/postfix 4.0K /var/lib/spamassassin/sa-update-keys/private-keys-v1.d 20K /var/lib/spamassassin/sa-update-keys 1.8M /var/lib/spamassassin/compiled/5.030 1.8M /var/lib/spamassassin/compiled 1.1M /var/lib/spamassassin/.spamassassin/sa-compile.cache 1.1M /var/lib/spamassassin/.spamassassin 2.9M /var/lib/spamassassin 51M /var/lib/mecab/dic/ipadic-utf8 40M /var/lib/mecab/dic/ipadic 91M /var/lib/mecab/dic 91M /var/lib/mecab 4.0K /var/lib/udisks2 20K /var/lib/ghostscript/CMap 12K /var/lib/ghostscript/fonts 36K /var/lib/ghostscript 40M /var/lib/dpkg/info 208K /var/lib/dpkg/alternatives 4.0K /var/lib/dpkg/updates 56K /var/lib/dpkg/triggers 4.0K /var/lib/dpkg/parts 43M /var/lib/dpkg 4.0K /var/lib/misc 36K /var/lib/PackageKit 608K /var/lib/usbutils 4.0K /var/lib/mysql-upgrade 4.0K /var/lib/polkit-1/localauthority/50-local.d 16K /var/lib/polkit-1/localauthority/10-vendor.d 4.0K /var/lib/polkit-1/localauthority/20-org.d 4.0K /var/lib/polkit-1/localauthority/90-mandatory.d 4.0K /var/lib/polkit-1/localauthority/30-site.d 36K /var/lib/polkit-1/localauthority 40K /var/lib/polkit-1 4.0K /var/lib/fwupd/gnupg/private-keys-v1.d 24K /var/lib/fwupd/gnupg 12K /var/lib/fwupd/pki 8.0K /var/lib/fwupd/builder 792K /var/lib/fwupd/remotes.d/lvfs 796K /var/lib/fwupd/remotes.d 864K /var/lib/fwupd 4.0K /var/lib/tpm 4.0K /var/lib/ubuntu-advantage/messages 8.0K /var/lib/ubuntu-advantage 8.0K /var/lib/bind 4.0K /var/lib/apt/periodic 4.0K /var/lib/apt/mirrors/partial 8.0K /var/lib/apt/mirrors 32K /var/lib/apt/lists/partial 4.0K /var/lib/apt/lists/auxfiles 332M /var/lib/apt/lists 332M /var/lib/apt 116K /var/lib/postgrey 36K /var/lib/cloud/data 248K /var/lib/cloud/instances/14937817 208K /var/lib/cloud/instances/iid-datasource-none 460K /var/lib/cloud/instances 4.0K /var/lib/cloud/scripts/per-instance 4.0K /var/lib/cloud/scripts/per-once 4.0K /var/lib/cloud/scripts/per-boot 4.0K /var/lib/cloud/scripts/vendor 20K /var/lib/cloud/scripts 4.0K /var/lib/cloud/handlers 12K /var/lib/cloud/sem 4.0K /var/lib/cloud/seed 540K /var/lib/cloud 28K /var/lib/pam 4.0K /var/lib/boltd 4.0K /var/lib/ubuntu-release-upgrader 804K /var/lib/mysql/#innodb_temp 4.0K /var/lib/mysql/herboristerie_principale 1.6M /var/lib/mysql/performance_schema 116K /var/lib/mysql/sys 36K /var/lib/mysql/mysql 243M /var/lib/mysql/herboristerie_principale_wordpress 26G /var/lib/mysql 4.0K /var/lib/nginx/uwsgi 4.0K /var/lib/nginx/proxy 4.0K /var/lib/nginx/body 404K /var/lib/nginx/fastcgi/2 404K /var/lib/nginx/fastcgi/7 404K /var/lib/nginx/fastcgi/6 404K /var/lib/nginx/fastcgi/1 404K /var/lib/nginx/fastcgi/9 404K /var/lib/nginx/fastcgi/8 404K /var/lib/nginx/fastcgi/3 404K /var/lib/nginx/fastcgi/0 404K /var/lib/nginx/fastcgi/4 400K /var/lib/nginx/fastcgi/5 4.0M /var/lib/nginx/fastcgi 4.0K /var/lib/nginx/scgi 4.0M /var/lib/nginx 4.0K /var/lib/shim-signed/mok 8.0K /var/lib/shim-signed 4.0K /var/lib/python 448K /var/lib/fail2ban 3.2M /var/lib/command-not-found 4.0K /var/lib/dbus 4.0K /var/lib/apport/coredump 8.0K /var/lib/apport 4.0K /var/lib/mysql-files 4.0K /var/lib/vim/addons 8.0K /var/lib/vim 4.0K /var/lib/plymouth 12K /var/lib/update-manager 4.0K /var/lib/php/sessions 32K /var/lib/php/modules/7.4 36K /var/lib/php/modules 44K /var/lib/php 4.0K /var/lib/landscape 4.0K /var/lib/man-db 4.0K /var/lib/gems/2.7.0 8.0K /var/lib/gems 4.0K /var/lib/private 8.0K /var/lib/logrotate 4.0K /var/lib/mysql-keyring 27G /var/lib

To me, no. That is not normal at all. By comparison, one of my servers has a total of 78.5 GB on the system. The var/lib/mysql folder makes up 194.67 MB of it.

And that server runs Virtualmin and my home Plex Media server.

It can also depend a lot on how you have the system set up and what you’re actually running on it.

yeah thats what I noticed too it is not normal at alll :dizzy_face: :dizzy_face: :open_mouth: :open_mouth: :open_mouth:

Any idea how to fix it , if you have any information

You just go deeper until you find the huge file(s) that is causing this.
du -xh --max-depth=3 /var/lib/mysql would be the next step.

And please do some formatting on the output as this is near impossible to read.

image_2021-11-23_150037
thats what I found

at 15.24.06
I found the binlog files are eating up more space

Should really keep 1 month worth of log. Did you made some changes to MariaDB (MySQL) configuration? Did you read the logs to see what is causing so many records?

Can try the following but backup 1 month or so worth of logs.

Edit the file /etc/mysql/my.cnf

Find a line that reads “log_bin” and remove or comment it as follows:

#log_bin = /var/log/mysql/mysql-bin.log

You also need to remove or comment following lines:

#expire_logs_days = 10

#max_binlog_size = 100M

Close and save the file. Restart mysql server:

# service mysql restart

1 Like

Thank you @Whoops , I’ll try it

Dont delete all logs. It’s recommend only remove MySQL Binary Log older than 1 month. You can purge existing old logs. Just be aware to keep say 1 month.

You can “trim” them within MySQL like this:

mysql> PURGE BINARY LOGS BEFORE '2021-01-01 00:00:00';

Be sure to specify the actual file you are trimming to. Do not simply delete the files from the file system. MySQL will complain at some time in the future in a terrible fashion.

Your cnf file could have something like this:

[mysqld]
log_bin = # turn off
binlog_expire_logs_seconds = 86400 # 1 day
max_binlog_size = 104857600 # 100M

I have the binlog file from this 2021/10/25


till now
so what I delete now ?

All the binlog files equals the 26GB or is there more elsewhere? The script that is using mysql, anything in settings to purge or edit to logs? I would start to edit .cnf file so it will start to manage the future logging. I would then leave old files as is whilst reading more about trunking binlogs via mysql shell :smiley: :wink: I dont use Ubuntu but perhaps this can help Ubuntu Manpage: mysqlbinlogpurge - Binary log purge utility

yes the binlog files are many so they eat up space from 26G the other files are just Kib,


image

You can purge from mysql shell.
As an example:

  • Delete binary log file mysql-bin.000735 or older files.

mysql> PURGE BINARY LOGS TO ‘mysql-bin.000735’;

  • Delete all binary log files created till date “2015-07-21 23:59:59”.

mysql> PURGE BINARY LOGS BEFORE ‘2015-07-21 23:59:59’;

Now list binary log files again, You will find that all older logs been deleted. Also change your cnf file otherwise logs will build up again.

Okay Thank you @Whoops

Okay @Whoops , Thank you so much

Thank you It works