.::: Script insert database size & record to table base on size folder in OS Linux example MariaDB :::.

 

correlation https://teguhth.blogspot.com/2025/12/script-check-size-database-base-on-size.html

A. Using basic table info

1. create table

CREATE TABLE IF NOT EXISTS dbsizeos (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    capture_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    db_name VARCHAR(100) NOT NULL,
    size_bytes BIGINT NOT NULL,
    size_mb DECIMAL(12,2) NOT NULL  
);

2.script 

[root@teguhth size]# cat insert.sh
#!/bin/bash

MYSQL_USER="admin"
MYSQL_PASS="admin"
MYSQL_HOST="localhost"
MYSQL_DB="dbatools"   # database tempat tabel dbsizeos

BASE_DIR="/var/lib/mysql"

echo "Database Size Report (Folder Based Only)"
echo "======================================="
printf "%-25s %15s %15s\n" "DATABASE" "BYTES" "MB"
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

    # ambil size folder (KB)
    size_kb=$(du -s ${BASE_DIR}/${dbname} 2>/dev/null | awk '{print $1}')
    size_kb=${size_kb:-0}

    # konversi
    size_bytes=$((size_kb * 1024))
    size_mb=$(awk "BEGIN {printf \"%.2f\", $size_kb/1024}")

    # tampilkan
    printf "%-25s %15s %15s\n" "$dbname" "$size_bytes" "$size_mb"

    # insert ke MariaDB
    mariadb -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} ${MYSQL_DB} -sNe "
    INSERT INTO dbsizeos (db_name, size_bytes, size_mb)
    VALUES ('$dbname', $size_bytes, $size_mb);
    "
done

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

[root@teguhth size]#

3. run script 

[root@teguhth sizex]# sh insert.sh
Database Size Report (Folder Based Only)
=======================================
DATABASE                            BYTES              MB
------------------------------------------------
test                                 4096            0.00
teguhth                            421888            0.40
dbatools                           143360            0.14
hris                               143360            0.14
------------------------------------------------
TOTAL DATA DIRECTORY: 156M      /var/lib/mysql
[root@teguhth sizex]#
 

4. result
 

B. Using Insert with ip & hostname

1. create table 

CREATE TABLE IF NOT EXISTS dbsizeos (
    capture_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    ip VARCHAR(45) NOT NULL,
    hostname VARCHAR(100) NOT NULL,
    db_name VARCHAR(100) NOT NULL,
    size_bytes BIGINT NOT NULL,
    size_mb DECIMAL(12,2) NOT NULL) ;

2. create script 

[root@teguhth sizex]# pwd
/data/sizex
[root@teguhth sizex]# cat insert_sizedb.sh
#!/bin/bash

MYSQL_USER="admin"
MYSQL_PASS="admin"
MYSQL_HOST="localhost"
MYSQL_DB="dbatools"

BASE_DIR="/var/lib/mysql"

# ambil IP dari OS
IP_OS=$(hostname -I | awk '{print $1}' | sed 's/ //g')

echo "Database Size Report (Folder Based Only)"
echo "======================================="
printf "%-25s %15s %15s\n" "DATABASE" "BYTES" "MB"
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

    # ambil size folder (KB)
    size_kb=$(du -s ${BASE_DIR}/${dbname} 2>/dev/null | awk '{print $1}')
    size_kb=${size_kb:-0}

    # konversi
    size_bytes=$((size_kb * 1024))
    size_mb=$(awk "BEGIN {printf \"%.2f\", $size_kb/1024}")

    # tampilkan
    printf "%-25s %15s %15s\n" "$dbname" "$size_bytes" "$size_mb"

    # insert ke MariaDB
    mariadb -u${MYSQL_USER} -p${MYSQL_PASS} -h${MYSQL_HOST} ${MYSQL_DB} -sNe "
    INSERT INTO dbsizeos
        (ip, hostname, db_name, size_bytes, size_mb)
    VALUES
        ('$IP_OS', @@hostname, '$dbname', $size_bytes, $size_mb);
    "

done

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

[root@teguhth sizex]#

3. run script  

[root@teguhth sizex]# sh insert_sizedb.sh
Database Size Report (Folder Based Only)
=======================================
DATABASE                            BYTES              MB
------------------------------------------------
test                                 4096            0.00
teguhth                            421888            0.40
dbatools                           143360            0.14
hris                               143360            0.14
------------------------------------------------
TOTAL DATA DIRECTORY: 156M      /var/lib/mysql
[root@teguhth sizex]#
 

4. result