CentOS Disk Space Finished Due to MYSQL Database table growth

Some time and it can be due to an error in the database or the application using it cause the database to expand and take all the disk space until total stop of the database service. as a result the application stops. here is the steps to find the files/database that cause the issue and free up some space.

First lets free some space in order to give the system some air, clean old installation package :

yum clean all

You can try and trim/delete some old logs or very large one at /var/log/

find /var -name "*.log" \( \( -size +50M -mtime +7 \) -o -mtime +30 \) -exec truncate {} --size 0 \;

Get the current disk space :

df -h

Now that you have some space, install NCDU which is nice and easy file information tool

yum install epel-release
yum install ncdu

Now you can get specific idea about you disk space, with the arrow keys you can browse the file system

 cd /      (to get to the root file system)
ncdu       (it will start enumerating the file size)

Another way to get the largest 10 files in a directory (in this example /var folder):

du -a /var | sort -n -r | head -n 10

Lets assume you have found /var/lib/mysql as the biggest directory and the file ibdata1 as the largest file around, you can not just delete this file as all your information will be gone !!!

Lets start getting information on the databases :

du -h --max-depth=1 /var/lib/mysql/        (will show you the database folder size)

To get the database true size :

mysql -u root -p
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 / 1024 AS "Size (GB)" FROM information_schema.TABLES GROUP BY table_schema ;

In this session it is zabbix_db that takes a lot of space, To get the all the table size in GB run :

SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024/1024/1024 AS TABLE_SIZE_in_MB FROM information_schema.TABLES;

To empty specific table

use zabbix_db;
TRUNCATE TABLE table_name;

Some time the space reclamation do not happen so the best way is to replace the table with new and empty one, Lets assume we want to empty table call history_unit , here are the steps :

CREATE TABLE new_history_uint LIKE history_uint;
   INSERT INTO new_history_uint SELECT * FROM history_uint;  -- This could take a long time
   RENAME TABLE history_uint TO old, new_history_uint TO history_uint;   -- Atomic swap
   DROP TABLE old;

Now if that didn’t help reclaim the disk space you will need to drop the databse, delete the ibdata1 file and restore the database all together. (if you are running zabbix you might want to stop it )

systemctl stop zabbix-server

create Backup folder
cd /var/lib/mysql
mkdir /backup

install pv progressbar viewer:
yum install pv

backup all DB:
mysqldump -u root -p --all-databases | pv -W > /var/lib/mysql/backup/all-database.sql

backup only zabbix:
mysqldump –q -l -u root -p zabbix_db | pv -W > /var/lib/mysql/backup/zabbix_db.sql

More comprehensive back way

mysqldump -u USERNAME -p --all-databases --events --ignore-table=mysql.event --extended-insert --add-drop-database --disable-keys --flush-privileges --quick --routines --triggers | gzip > "all_databases.gz"

To restore from this sql dump :
gunzip < all_databases.gz | mysql -u USERNAME -p

Now you are ready to start . Make sure your backup has succeed before continue (check SQL file size for example)!

drop all databases:
mysql -u root -p
mysql> show databases;

mysql> drop database zabbix_db;

stop mysql:
systemctl stop mariadb

delete the file:
cd /var/lib/mysql/
rm ibdata1
rm ib_logfile0
rm ib_logfile1

start the mysql:
systemctl start mariadb      (it will re-create ibdate1)

At this point make sure the free disk space is accordingly. Now restore the database from the dump you made before :

mysql -u root -p --all-databases | pv -W < /var/lib/mysql/backup/all-database.sql
in case of one DB you will need to recreate the database and the structure:
CREATE DATABASE zabbix_db;
mysql -u root -p zabbix_db | pv -W  < /var/lib/mysql/backup/zabbix_db.sql

In case of very large Database this can take some time .

Good Luck