.::: Check ETA (Estimated Time of Arrival) restore status include All Database / Reinisiasi MariaDB MarinaDB on Persent (%) for GTID Mirroring & Galera MariaDB MarinaDB - ETA All Final :::.

 

correlation 
Check ETA (Estimated Time of Arrival) restore status MariaDB MarinaDB on Persent (%) 

Check ETA (Estimated Time of Arrival) restore status All Database / Reinisiasi MariaDB MarinaDB on Persent (%) for GTID Mirroring & Galera 
 

https://teguhth.blogspot.com/2025/10/script-drop-all-database-non-system-in.html

 

1. Total database/folder (result 11 folder or db)
 

ls -d /var/lib/mysql/*/ | wc -l

2. sample size database mariadb (sample result 516132 kib or 528,519,168 bytes)
 

du -s /var/lib/mysql/marinadb/
du -sh /var/lib/mysql/marinadb/

[root@teguhth10x data]# du -s /var/lib/mysql/marinadb/
516132  /var/lib/mysql/marinadb/
[root@teguhth10x data]#
3. total all db mariadb (sample result 4649076 kib or 4,760,653,824 bytes)
 

du -sc -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}' 
du -sch -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}' 

[root@teguhth10x data]# du -sc -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'
4649076
[root@teguhth10x data]#

4. list all database
 

ls -dt /var/lib/mysql/*/ | sed 's#^/var/lib/mysql/##; s#/$##' | paste
ls -dt /var/lib/mysql/*/ | sed 's#^/var/lib/mysql/##; s#/$##' | tr '\n' ' '; echo

[root@teguhth10x data]# ls -d /var/lib/mysql/*/ | wc -l
11
[root@teguhth10x data]#
[root@teguhth10x data]# du -s /var/lib/mysql/marinadb/
516132  /var/lib/mysql/marinadb/
[root@teguhth10x data]#
[root@teguhth10x data]# du -sh /var/lib/mysql/marinadb/
505M    /var/lib/mysql/marinadb/
[root@teguhth10x data]#
[root@teguhth10x data]# du -sc -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'
4649076
[root@teguhth10x data]#
[root@teguhth10x data]# du -sch -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'
4.5G
[root@teguhth10x data]#
[root@teguhth10x data]# ls -dt /var/lib/mysql/*/ | sed 's#^/var/lib/mysql/##; s#/$##' | paste
zahra
teguhth
simpledb
secretdb
raju
marinadb
amran
aisyah
abcde
performance_schema
mysql
[root@teguhth10x data]#
[root@teguhth10x data]# ls -dt /var/lib/mysql/*/ | sed 's#^/var/lib/mysql/##; s#/$##' | tr '\n' ' '; echo
zahra teguhth simpledb secretdb raju marinadb amran aisyah abcde performance_schema mysql
[root@teguhth10x data]#
 

5. sample capture eta a database 

sh eta_all.sh marinadb 516132
 

6. sample eta reiniiasi

sh eta_reinisiasi.sh 4649076 11
 

7. eta complete
 

 

8. script eta all 
 
sh eta_all.sh zahra 516132

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

## automatic
db="$1"
sizedbsourcex="$2"

svrip=$(hostname -I | awk '{print $1}' | sed "s/ //g");
hostx=$(hostname -s);
datadirx='/var/lib/mysql'
osx=$(grep '^PRETTY_NAME=' /etc/os-release | cut -d= -f2- | tr -d '"')
dbv=$(systemctl show -p Description --value mariadb)

## manual only test
#db="teguhth"
#sizedbsourcex=1987456

if [ -z "$db" ]; then
    echo "ERROR: Nama database belum diisi."
    echo "Usage: $0 <nama_database> <size_db_source>"
     echo "Usage: $0 <dbsf_nbc_xxxx> <21426936>"
    exit 1
fi

if [ -z "$sizedbsourcex" ]; then
    echo "ERROR: Ukuran database source belum diisi."
    echo "Usage: $0 <nama_database> <size_db_source>"
    exit 1
fi

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

while true
do
    sizedbdestx=$(du -s $datadirx/$db | awk '{print $1}')

    progresx=$(awk "BEGIN {printf \"%.2f\", $sizedbdestx * 100 / $sizedbsourcex}")
    percent=$(awk "BEGIN {printf \"%d\", $sizedbdestx * 100 / $sizedbsourcex}")

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

    width=50
    filled=$((percent * width / 100))
    empty=$((width - filled))

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

    now=$(date +%s)
    elapsed=$((now - prev_time))

    if [ "$elapsed" -gt 0 ]; then
        diff=$((sizedbdestx - prev_size))
        speed_kb=$((diff / elapsed))
    else
        speed_kb=0
    fi

        # Hitung ETA
remaining_kb=$((sizedbsourcex - sizedbdestx))

# Sisa size
remaining_mb=$(awk "BEGIN {printf \"%.2f\", $remaining_kb/1024}")
remaining_gb=$(awk "BEGIN {printf \"%.2f\", $remaining_kb/1024/1024}")

# Source & Destination dalam GB
source_gb=$(awk "BEGIN {printf \"%.2f\", $sizedbsourcex/1024/1024}")
dest_gb=$(awk "BEGIN {printf \"%.2f\", $sizedbdestx/1024/1024}")


if [ "$speed_kb" -gt 0 ]; then
    eta_sec=$((remaining_kb / speed_kb))

    eta_h=$((eta_sec / 3600))
    eta_m=$(((eta_sec % 3600) / 60))
    eta_s=$((eta_sec % 60))
else
    eta_h=0
    eta_m=0
    eta_s=0
fi

# Perkiraan waktu selesai
if [ "$speed_kb" -gt 0 ]; then
    finish_time=$(date -d "+$eta_sec seconds" "+%Y-%m-%d %H:%M:%S")
    finish_clock=$(date -d "+$eta_sec seconds" "+%H:%M:%S")
else
    finish_time="-+idle+-"
    finish_clock="-+idle+-"
fi

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

clear
datex=$(date)
echo ".::: Progres Restore '$db' at $datex ::::."
echo
echo "Host Server Target  : $hostx"
echo "IP Server Target    : $svrip"
echo "Database Product    : $dbv"
echo "Database Restore to : $db"
echo "Source DBSize       : $sizedbsourcex KB (${source_gb} GB)"
echo "Destination DBSize  : $sizedbdestx KB (${dest_gb} GB)"
echo "Remaining DBSize    : $remaining_kb KB (${remaining_gb} GB)"
echo "Progress            : $progresx %"
echo "Restore Speed       : $speed_kb KB/s ($speed_mb MB/s)"
printf "ETA                 : %02d:%02d:%02d\n" $eta_h $eta_m $eta_s
echo "Finish At           : $finish_time"
#echo "Finish At          : $finish_clock"
printf "Progress Bar        : [%-50s] %6.2f%%\n" "$bar" "$progresx"

echo ""
echo "Copyright by        : Teguh Triharto"
echo "Website             : https://www.linkedin.com/in/teguhth"
echo "Operating System    : $osx"
echo ""

echo "check Progres file Maria '$db' in unit"
echo
find "$datadirx/$db" -type f \
    -printf "%TY-%Tm-%Td %TH:%TM:%TS %s %p\n" |
    sort -r |
    head -5 |
    while read d t s f
    do
        printf "%s %s %10s (%15s bytes) %s\n" \
            "$d" "$t" \
            "$(numfmt --to=iec-i --suffix=B "$s")" \
            "$s" \
            "$f"
    done
echo

echo "check Sort file Maria '$db' in unit"
echo

find "$datadirx/$db" -type f -printf '%s\t%p\n' |
sort -nr |
head -5 |
while IFS=$'\t' read -r size file
do
    printf "%8s (%s bytes) %s\n" \
        "$(numfmt --to=iec "$size")" \
        "$size" \
        "$file"
done

    prev_size=$sizedbdestx
    prev_time=$now

    sleep 2
done
[root@teguhth10x data]#

9. script eta reiniaasi

sh eta_reinisiasi.sh 4649076 11

[root@teguhth10x data]# pwd
/data
[root@teguhth10x data]# more eta_reinisiasi.sh
# cat etamanual.sh

#!/bin/bash

## automatic
#db="$1"
sizedbsourcex="$1"

svrip=$(hostname -I | awk '{print $1}' | sed "s/ //g");
hostx=$(hostname -s);
datadirx='/var/lib/mysql'
osx=$(grep '^PRETTY_NAME=' /etc/os-release | cut -d= -f2- | tr -d '"')
dbv=$(systemctl show -p Description --value mariadb)

#sizedbsourcex from
#du -sch -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'
#du -sc -- "/var/lib/mysql"/*/ | tail -1 | awk '{print $1}'
# 3879372

## manual only test
#db="teguhth"
#sizedbsourcex=1987456

if [ -z "$sizedbsourcex" ]; then
    echo "ERROR: Ukuran database source belum diisi."
    echo "Usage: $0 <nama_database> <size_db_source>"
    exit 1
fi

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

while true
do
    #sizedbdestx=$(du -s $datadirx | awk '{print $1}')
sizedbdestx=$(du -s -- "$datadirx"/*/ | awk '{sum += $1} END {print sum}')

    progresx=$(awk "BEGIN {printf \"%.2f\", $sizedbdestx * 100 / $sizedbsourcex}")
    percent=$(awk "BEGIN {printf \"%d\", $sizedbdestx * 100 / $sizedbsourcex}")

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

    width=50
    filled=$((percent * width / 100))
    empty=$((width - filled))

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

    now=$(date +%s)
    elapsed=$((now - prev_time))

    if [ "$elapsed" -gt 0 ]; then
        diff=$((sizedbdestx - prev_size))
        speed_kb=$((diff / elapsed))
    else
        speed_kb=0
    fi

        # Hitung ETA
remaining_kb=$((sizedbsourcex - sizedbdestx))

# Sisa size
remaining_mb=$(awk "BEGIN {printf \"%.2f\", $remaining_kb/1024}")
remaining_gb=$(awk "BEGIN {printf \"%.2f\", $remaining_kb/1024/1024}")

# Source & Destination dalam GB
source_gb=$(awk "BEGIN {printf \"%.2f\", $sizedbsourcex/1024/1024}")
dest_gb=$(awk "BEGIN {printf \"%.2f\", $sizedbdestx/1024/1024}")

if [ "$speed_kb" -gt 0 ]; then
    eta_sec=$((remaining_kb / speed_kb))

    eta_h=$((eta_sec / 3600))
    eta_m=$(((eta_sec % 3600) / 60))
    eta_s=$((eta_sec % 60))
else
    eta_h=0
    eta_m=0
    eta_s=0
fi

# Perkiraan waktu selesai
if [ "$speed_kb" -gt 0 ]; then
    finish_time=$(date -d "+$eta_sec seconds" "+%Y-%m-%d %H:%M:%S")
    finish_clock=$(date -d "+$eta_sec seconds" "+%H:%M:%S")
else
    finish_time="-"
    finish_clock="-"
fi

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

        #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( |$)')

dbprog=$(find "$datadirx" -mindepth 1 -maxdepth 1 -type d \
    -printf "%T@ %f\n" |
    sort -nr |
    head -1 |
    awk '{print $2}')

dbokx=$(ls -dt /var/lib/mysql/*/ | sed 's#^/var/lib/mysql/##; s#/$##' | paste -sd,)
dbtotx=$(ls -d /var/lib/mysql/*/ | wc -l)
#dbtott=$(ls -d /var/lib/mysql/*/ | wc -l)
dbtott=$2

clear
datex=$(date)
echo ".::: Progres Restore '$datadirx' Reinisasi Slave at $datex ::::."
echo
echo "Host Server Target  : $hostx"
echo "IP Server Target    : $svrip"
echo "Database Product    : $dbv"
echo "Progres Restore to  : $datadirx with '$dbprog'"
echo "Source DBSize       : $sizedbsourcex KB (${source_gb} GB)"
echo "Destination DBSize  : $sizedbdestx KB (${dest_gb} GB)"
echo "Remaining DBSize    : $remaining_kb KB (${remaining_gb} GB)"

echo "Progress            : $progresx %"
echo "Restore Speed       : $speed_kb KB/s ($speed_mb MB/s)"
printf "ETA                 : %02d:%02d:%02d\n" $eta_h $eta_m $eta_s
echo "Finish At           : $finish_time"
#echo "Finish At          : $finish_clock"
printf "Progress Bar        : [%-50s] %6.2f%%\n" "$bar" "$progresx"
echo
echo "Database Complete   : ($dbtotx/$dbtott DB)-=> $dbokx"
echo ""
echo "Copyright by        : Teguh Triharto"
echo "Website             : https://www.linkedin.com/in/teguhth"
echo "Operating System    : $osx"
echo

if [ -n "$restore_procdb" ]; then
    echo "Running Reiniasi '$dbprog' '$restore_procdb' "
        echo
    show_progress=1

else
    echo "IDLE - Tidak ada proses BACKUP maupun RESTORE untuk database '$restore_procdb'"
        echo
fi

#echo "check Progres database Maria '$dbprog' in unit sort by size"
#echo
#du -sh -- "$datadirx"/*/ | sort -hr | head -5

echo "check Progres file Maria '$dbprog' in unit"
echo
find "$datadirx/$dbprog" -type f \
    -printf "%TY-%Tm-%Td %TH:%TM:%TS %s %p\n" |
    sort -r |
    head -5 |
    while read d t s f
    do
        printf "%s %s %10s (%15s bytes) %s\n" \
            "$d" "$t" \
            "$(numfmt --to=iec-i --suffix=B "$s")" \
            "$s" \
            "$f"
    done
echo

echo "check Progres database Maria '$dbprog' in unit sort new create"
echo
find "$datadirx" -mindepth 1 -maxdepth 1 -type d \
    -printf "%T@ %TY-%Tm-%Td %TH:%TM:%TS %p\n" |
    sort -nr |
    head -5 |
    while read epoch date time dir
    do
        size=$(du -sb "$dir" | awk '{print $1}')
        printf "%s %s %10s (%15s bytes) %s\n" \
            "$date" "$time" \
            "$(numfmt --to=iec-i --suffix=B "$size")" \
            "$size" \
            "$dir"
    done

#echo "check Progres database Maria '$dbprog' in unit sort by size"

#echo
#find "$datadirx" -mindepth 1 -maxdepth 1 -type d \
#    -printf "%T@ %TY-%Tm-%Td %TH:%TM:%TS %p\n" |
#    sort -nr |
 #   head -5 |
 #   while read epoch date time dir
 #   do
#        size=$(du -sb "$dir" | awk '{print $1}')
#        printf "%s %s %10s (%15s bytes) %s\n" \
#            "$date" "$time" \
#            "$(numfmt --to=iec-i --suffix=B "$size")" \
 #           "$size" \
  #          "$dir"
#    done
    prev_size=$sizedbdestx
    prev_time=$now

    sleep 2
done
[root@teguhth10x data]#