Teguh Triharto Learning Center: .::: Script Check ETA (Estimated Time of Arrival) Backup Database MariaDB on % Percent in .sql or .sql.gz :::.

1. check size database (kb)

du -s /var/lib/mysql/teguhth

du -s --exclude='pembelian.ibd' /var/lib/mysql/teguhth

[root@teguhth10x testbackup]# du -sh /var/lib/mysql/teguhth
505M /var/lib/mysql/teguhth
[root@teguhth10x testbackup]# du -s /var/lib/mysql/teguhth
516132 /var/lib/mysql/teguhth

2. backup in sql or comprese

mariadb-dump -uroot -pxxx teguhth > teguhth_all.sql
mariadb-dump -uroot -pxxx teguhth | gzip -c > teguhth_all.sql.gz

mariadb-dump -uroot -pxxx marinadb > marinadb_all.sql
mariadb-dump -uroot -pxxx marinadb | gzip -c > marinadb_all.sql.gz

3. check size mariadb dump in .sql

[root@teguhth10x testbackup]# ls -lh
total 281M
-rw-r--r-- 1 root root 281M Sep 21 16:20 teguhth_all.sql
[root@teguhth10x testbackup]# ls -l
total 286744
-rw-r--r-- 1 root root 293625449 Sep 21 16:20 teguhth_all.sql
[root@teguhth10x testbackup]#

4. check size mariadb dump in compress .sql.gz

[root@teguhth10x testbackup]# ls -l | grep -i teguhth_all.sql.gz
-rw-r--r-- 1 root root 34334977 Sep 22 09:52 teguhth_all.sql.gz
[root@teguhth10x testbackup]# ls -lh | grep -i teguhth_all.sql.gz
-rw-r--r-- 1 root root 33M Sep 22 09:52 teguhth_all.sql.gz
[root@teguhth10x testbackup]#

5. ratio (change base on your database)

# ============================================================
# HISTORICAL RATIO
#
# Actual data:
#
# Physical DB :
# 516132 KB
#
# SQL :
# 293625449 bytes
#
# GZIP :
# 34334976 bytes
#
#SQL / Physical = 55.56%
#GZIP / SQL = 11.70%
#GZIP / Physical = 6.50%
# ============================================================
#with sql_ratio first
#sql_ratio=0.5556

#gzip_ratio=0.1170

#direct physical to gzip

sql_ratio=1

gzip_ratio=0.0650

6. run scrip eta backup

sh eta_backup_lab.sh teguhth 516132 /data/testbackup/teguhth_all.sql.gz
sh eta_backup_lab.sh marinadb 516132 /data/testbackup/marinadb_all.sql.gz

7. run backup monitor

mariadb-dump -uroot -pxxx teguhth | gzip -c > teguhth_all.sql
mariadb-dump -uroot -pxxx marinadb | gzip -c > marinadb_all.sql.gz

8. script

[root@teguhth10x testbackup]# pwd
/data/testbackup
[root@teguhth10x testbackup]# cat eta_backup_lab.sh
#!/bin/bash

# ============================================================
# MariaDB Compressed Backup ETA Monitor
# Teguh Triharto
#
# Usage:
# sh eta_backup_lab.sh <database> <source_size_KB> <backup_file.sql.gz>
#
# Example:
# sh eta_backup_lab.sh teguhth 516132 /data/testbackup/teguhth_all.sql.gz
# ============================================================

db="$1"
sizedbsourcex="$2"
backupfile="$3"

# ============================================================
# VALIDATION
# ============================================================

if [ -z "$db" ] || [ -z "$sizedbsourcex" ] || [ -z "$backupfile" ]; then

echo "Usage: $0 <database> <source_size_KB> <backup_file.sql.gz>"
echo
echo "Example:"
echo "sh $0 teguhth 516132 /data/testbackup/teguhth_all.sql.gz"

exit 1
fi

if ! [[ "$sizedbsourcex" =~ ^[0-9]+$ ]]; then

echo "ERROR: Source DB size harus berupa angka KB."

exit 1
fi

# ============================================================
# SERVER INFORMATION
# ============================================================

svrip=$(hostname -I | awk '{print $1}')
hostx=$(hostname -s)

osx=$(grep '^PRETTY_NAME=' /etc/os-release |
cut -d= -f2- |
tr -d '"')

dbv=$(systemctl show -p Description --value mariadb)

# ============================================================
# HISTORICAL RATIO
#
# Actual data:
#
# Physical DB :
# 516132 KB
#
# SQL :
# 293625449 bytes
#
# GZIP :
# 34334976 bytes
#
#SQL / Physical = 55.56%
#GZIP / SQL = 11.70%
#GZIP / Physical = 6.50%
# ============================================================

#with sql_ratio first
#sql_ratio=0.5556

#gzip_ratio=0.1170

#direct physical to gzip

sql_ratio=1

gzip_ratio=0.0650

# ============================================================
# ESTIMATE SQL SIZE
# ============================================================

estimated_sql_kb=$(awk "BEGIN {
printf \"%.0f\", $sizedbsourcex * $sql_ratio
}")

# ============================================================
# ESTIMATE FINAL GZIP SIZE
# ============================================================

estimated_gzip_kb=$(awk "BEGIN {
printf \"%.0f\", $estimated_sql_kb * $gzip_ratio
}")

# Target dalam bytes

target_bytes=$((estimated_gzip_kb * 1024))

# ============================================================
# PREVIOUS SIZE / TIME
# ============================================================

prev_size=0
prev_time=$(date +%s)

# ============================================================
# MONITOR LOOP
# ============================================================

# ps
#backup_proc=$(pgrep -af "mysqldump|mariadb-dump")
#restore_proc=$(pgrep -af "mysql|mariadb" | grep -F "$db")
#restore_procdb=$(pgrep -af "mysql|mariadb" | grep -E '(^| )-f( |$)')

while true
do

# --------------------------------------------------------
# Current backup file size
# --------------------------------------------------------

if [ -f "$backupfile" ]; then

current_size=$(stat -c %s "$backupfile")

else

current_size=0

fi

# --------------------------------------------------------
# Current time
# --------------------------------------------------------

now=$(date +%s)

elapsed=$((now - prev_time))

# --------------------------------------------------------
# Backup speed
# --------------------------------------------------------

if [ "$elapsed" -gt 0 ]; then

diff=$((current_size - prev_size))

# Prevent negative value
[ "$diff" -lt 0 ] && diff=0

speed_bps=$((diff / elapsed))

else

speed_bps=0

fi

# --------------------------------------------------------
# Current size KB
# --------------------------------------------------------

current_kb=$((current_size / 1024))

# --------------------------------------------------------
# Progress
# --------------------------------------------------------

if [ "$target_bytes" -gt 0 ]; then

raw_progress=$(awk "BEGIN {
printf \"%.2f\", ($current_size * 100) / $target_bytes
}")

percent=$(awk "BEGIN {
printf \"%d\", ($current_size * 100) / $target_bytes
}")

else

raw_progress=0
percent=0

fi

# --------------------------------------------------------
# Progress status
# --------------------------------------------------------

if [ "$current_size" -gt "$target_bytes" ]; then

progress_status="ABOVE ESTIMATE"

else

progress_status="WITHIN ESTIMATE"

fi

# --------------------------------------------------------
# Progress bar
# --------------------------------------------------------

[ "$percent" -gt 100 ] && percent=100
[ "$percent" -lt 0 ] && percent=0

width=50

filled=$((percent * width / 100))

empty=$((width - filled))

bar=$(printf "%${filled}s" | tr ' ' 'o')

# --------------------------------------------------------
# Remaining
# --------------------------------------------------------

remaining_bytes=$((target_bytes - current_size))

[ "$remaining_bytes" -lt 0 ] && remaining_bytes=0

# --------------------------------------------------------
# Size conversion
# --------------------------------------------------------

current_mb=$(awk "BEGIN {
printf \"%.2f\", $current_size/1024/1024
}")

target_mb=$(awk "BEGIN {
printf \"%.2f\", $target_bytes/1024/1024
}")

remaining_mb=$(awk "BEGIN {
printf \"%.2f\", $remaining_bytes/1024/1024
}")

source_gb=$(awk "BEGIN {
printf \"%.2f\", $sizedbsourcex/1024/1024
}")

estimated_sql_mb=$(awk "BEGIN {
printf \"%.2f\", $estimated_sql_kb/1024
}")

estimated_gzip_mb=$(awk "BEGIN {
printf \"%.2f\", $estimated_gzip_kb/1024
}")

# --------------------------------------------------------
# Actual compression ratio sementara
# --------------------------------------------------------

if [ "$estimated_sql_kb" -gt 0 ]; then

actual_gzip_ratio=$(awk "BEGIN {
printf \"%.2f\", ($current_size / ($estimated_sql_kb * 1024)) * 100
}")

else

actual_gzip_ratio="0.00"

fi

# --------------------------------------------------------
# Difference dari estimated target
# --------------------------------------------------------

if [ "$target_bytes" -gt 0 ]; then

difference_percent=$(awk "BEGIN {
printf \"%.2f\", (($current_size - $target_bytes) / $target_bytes) * 100
}")

else

difference_percent="0.00"

fi

# --------------------------------------------------------
# Speed
# --------------------------------------------------------

speed_mb=$(awk "BEGIN {
printf \"%.2f\", $speed_bps/1024/1024
}")

speed_kb=$(awk "BEGIN {
printf \"%.0f\", $speed_bps/1024
}")

# --------------------------------------------------------
# ETA
# --------------------------------------------------------

if [ "$speed_bps" -gt 0 ] && [ "$remaining_bytes" -gt 0 ]; then

eta_sec=$((remaining_bytes / speed_bps))

eta_h=$((eta_sec / 3600))

eta_m=$(((eta_sec % 3600) / 60))

eta_s=$((eta_sec % 60))

finish_time=$(date -d "+$eta_sec seconds" \
"+%Y-%m-%d %H:%M:%S")

else

eta_h=0
eta_m=0
eta_s=0

if [ "$remaining_bytes" -eq 0 ]; then

finish_time=$(date "+%Y-%m-%d %H:%M:%S")
else
finish_time="--:--:--"
fi

fi
backup_proc=$(pgrep -af "mysqldump|mariadb-dump")
#restore_proc=$(pgrep -af "mysql|mariadb" | grep -F "$db")
#restore_procdb=$(pgrep -af "mysql|mariadb" | grep -E '(^| )-f( |$)')

# ========================================================
# DISPLAY
# ========================================================

clear

echo ".::: Backup Compressed Progress '$db' :::."
echo
echo "Server Target : $svrip / $hostx"
echo "Product : $dbv"
echo "Database : $db"
echo

echo "Source DB : $sizedbsourcex KB (${source_gb} GB)"
echo "Estimated SQL : $estimated_sql_kb KB (${estimated_sql_mb} MB)"
echo "Estimated GZIP : $estimated_gzip_kb KB (${estimated_gzip_mb} MB)"
echo
echo "Backup File : $backupfile"
echo "GZIP Size : $current_kb KB (${current_mb} MB)"
echo "Target GZIP : $estimated_gzip_kb KB (${target_mb} MB)"
echo "Remaining : $remaining_bytes bytes (${remaining_mb} MB)"
echo

echo "SQL Ratio : ${sql_ratio} (${sql_ratio} = 55.56%)"
echo "GZIP Ratio : ${gzip_ratio} (${gzip_ratio} = 11.70% SQL)"
echo "Actual GZIP Ratio: ${actual_gzip_ratio}% of estimated SQL"
echo

echo "Backup Speed : $speed_kb KB/s ($speed_mb MB/s)"

printf "ETA : %02d:%02d:%02d\n" \
"$eta_h" "$eta_m" "$eta_s"

echo "Estimated Finish : $finish_time"
echo

printf "Progress Bar : %6.2f%% [\033[96m%-50s\033[0m]\n" \
"$raw_progress" "$bar"
echo
echo "Estimate Status : $progress_status"
echo "Estimate Delta : ${difference_percent}%"
echo

echo "Proccess : $backup_proc"
# ========================================================
# BACKUP FINISHED / TARGET REACHED
# ========================================================

if [ -f "$backupfile" ]; then

# Jika ukuran tidak berubah selama beberapa cycle,
# kemungkinan backup sudah selesai.

if [ "$current_size" -eq "$prev_size" ] && \
[ "$current_size" -gt 0 ]; then

echo "Backup File : SIZE STABLE"

fi

fi

echo
echo "Copyright : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"

echo "Operating System : $osx"
echo

# ========================================================
# UPDATE
# ========================================================

prev_size=$current_size
prev_time=$now

sleep 2

done
[root@teguhth10x testbackup]#

Teguh Triharto Learning Center

Blog List

Tuesday, September 22, 2026

.::: Script Check ETA (Estimated Time of Arrival) Backup Database MariaDB on % Percent in .sql or .sql.gz :::.

No comments:

Post a Comment

Popular Posts