.::: Script check size database base on size folder in OS Linux example database size MariaDB :::.

  

ini shell script sederhana, rapi, dan standar DBA untuk list folder di /var/lib/mysql + hitung size-nya.

1. Versi Paling Simpel (langsung pakai du) semual folder n non folder 
[root@teguhth sizex]# cat cek_foldernisi.sh
#!/bin/bash

BASE_DIR="/var/lib/mysql"

echo "Database Directory Size Report"
echo "=============================="
printf "%-25s %10s\n" "FOLDER" "SIZE"
echo "------------------------------"

du -sh ${BASE_DIR}/* 2>/dev/null | sort -h | while read size folder; do
    printf "%-25s %10s\n" "$(basename "$folder")" "$size"
done

echo "------------------------------"
echo "TOTAL:"
du -sh "$BASE_DIR" | awk '{print $1}'

[root@teguhth sizex]#
 
Output contoh:

[root@teguhth sizex]# sh cek_foldernisi.sh
Database Directory Size Report
==============================
FOLDER                          SIZE
------------------------------
multi-master.info                  0
mysql.sock                         0
aria_log_control                4.0K
ddl_recovery-backup.log         4.0K
ddl_recovery.log                4.0K
ib_buffer_pool                  4.0K
mariadb_upgrade_info            4.0K
performance_schema              4.0K
teguhth.pid                     4.0K
test                            4.0K
tc.log                           24K
dbatools                         72K
hris                            140K
aria_log.00000001               408K
teguhth                         412K
sys                             600K
mysql                           3.8M
undo001                          10M
undo002                          10M
undo003                          10M
ibdata1                          12M
ibtmp1                           12M
ib_logfile0                      96M
------------------------------
TOTAL:
156M
[root@teguhth sizex]#
 

2. check folder only
[root@teguhth sizex]# cat cek_folderdb.sh
#!/bin/bash

BASE_DIR="/var/lib/mysql"

echo "Database Directory Size Report (Folders Only)"
echo "============================================"
printf "%-25s %10s\n" "FOLDER" "SIZE"
echo "--------------------------------------------"

du -sh ${BASE_DIR}/*/ 2>/dev/null | sort -h | while read size folder; do
    printf "%-25s %10s\n" "$(basename "$folder")" "$size"
done

echo "--------------------------------------------"
echo -n "TOTAL FOLDER SIZE: "
du -sh "$BASE_DIR"

[root@teguhth sizex]#
[root@teguhth sizex]# sh cek_folderdb.sh
Database Directory Size Report (Folders Only)
============================================
FOLDER                          SIZE
--------------------------------------------
performance_schema              4.0K
test                            4.0K
dbatools                         72K
hris                            140K
teguhth                         412K
sys                             600K
mysql                           3.8M
--------------------------------------------
TOTAL FOLDER SIZE: 156M /var/lib/mysql
[root@teguhth sizex]#
 

3. Versi DBA Friendly (KB & mudah diproses) format excel
[root@teguhth sizex]# cat cek_folder_isi_excel.sh
#!/bin/bash

BASE_DIR="/var/lib/mysql"

echo "folder_name,size_kb"
du -sk ${BASE_DIR}/* 2>/dev/null | sort -n | while read size folder; do
    echo "$(basename "$folder"),$size"
done

[root@teguhth sizex]#

?? Cocok untuk:

Import ke Excel
Monitoring growth
Script alert disk
?? Catatan Penting DBA
Ini size REAL disk (paling akurat)
Lebih valid daripada query SQL
Cocok untuk capacity planning
 

3.1 Bonus (hanya folder DB user, exclude system)

du -sh /var/lib/mysql/*/ | egrep -v '/(mysql|sys|performance_schema|test)/$'

[root@teguhth sizex]# du -sh /var/lib/mysql/*/ | egrep -v '/(mysql|sys|performance_schema|test)/$'
72K     /var/lib/mysql/dbatools/
140K    /var/lib/mysql/hris/
412K    /var/lib/mysql/teguhth/
[root@teguhth sizex]#

Kalau mau:

Tambah warna (merah jika > 1GB)
Tambah persentase total disk
Jadikan cron harian + log
Export ke CSV / JSON

bilang saja ??

4. Versi DBA Friendly (KB & mudah diproses) format excel only folder

[root@teguhth sizex]# cat cek_folder_excel.sh
#!/bin/bash

BASE_DIR="/var/lib/mysql"

echo "folder_name,size_kb"
du -s ${BASE_DIR}/*/ 2>/dev/null | sort -n | while read size folder; do
    echo "$(basename "$folder"),$size"
done

[root@teguhth sizex]#
[root@teguhth sizex]# sh cek_folder_excel.sh
folder_name,size_kb
performance_schema,4
test,4
dbatools,72
hris,140
teguhth,412
sys,600
mysql,3824
[root@teguhth sizex]#
 

5. check only folder 

[root@teguhth sizex]# cat cek_folderdb_only.sh
#!/bin/bash

MYSQL_USER="admin"
MYSQL_PASS="admin"
MYSQL_HOST="localhost"

BASE_DIR="/var/lib/mysql"

echo "Database Size Report (Folder Based Only)"
echo "======================================="
printf "%-25s %12s\n" "DATABASE" "FOLDER(Byte)"
echo "---------------------------------------"

mariadb -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -sNe "
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema','performance_schema','mysql','sys');
" | while read dbname; do

    folder_mb=$(du -s ${BASE_DIR}/${dbname} 2>/dev/null | awk '{print $1}')
    folder_mb=${folder_mb:-0}

    printf "%-25s %12s\n" "$dbname" "$folder_mb"
done

echo "---------------------------------------"
echo -n "TOTAL DATA DIRECTORY: "
du -sh "$BASE_DIR"

[root@teguhth sizex]#
[root@teguhth sizex]# sh cek_folderdb_only.sh
Database Size Report (Folder Based Only)
=======================================
DATABASE                  FOLDER(Byte)
---------------------------------------
test                                 4
teguhth                            412
dbatools                            72
hris                               140
---------------------------------------
TOTAL DATA DIRECTORY: 156M      /var/lib/mysql
[root@teguhth sizex]#
 

6. check folder compare size 

[root@teguhth sizex]#
[root@teguhth sizex]# cat compare_size_maria_disk.sh
#!/bin/bash

MYSQL_USER="admin"
MYSQL_PASS="admin"
MYSQL_HOST="localhost"

BASE_DIR="/var/lib/mysql"

echo "Database Size Report (Information_Schema vs Folder)"
echo "==================================================="
printf "%-25s %12s %12s\n" "DATABASE" "DB_SIZE(MB)" "FOLDER(MB)"
echo "---------------------------------------------------"

mariadb -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} -sNe "
SELECT
    table_schema,
    ROUND(SUM(data_length + index_length)/1024)
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','performance_schema','mysql','sys')
GROUP BY table_schema;
" | while read dbname dbsize; do

    folder_size=$(du -s ${BASE_DIR}/${dbname} 2>/dev/null | awk '{print $1}')
    folder_size=${folder_size:-0}

    printf "%-25s %12s %12s\n" "$dbname" "$dbsize" "$folder_size"
done

echo "---------------------------------------------------"
echo -n "TOTAL DATA DIRECTORY: "
du -sh "$BASE_DIR"

[root@teguhth sizex]#
[root@teguhth sizex]# sh compare_size_maria_disk.sh
Database Size Report (Information_Schema vs Folder)
===================================================
DATABASE                   DB_SIZE(MB)   FOLDER(MB)
---------------------------------------------------
dbatools                            16           72
hris                                32          140
teguhth                            144          412
---------------------------------------------------
TOTAL DATA DIRECTORY: 156M      /var/lib/mysql
[root@teguhth sizex]#