Teguh Triharto Learning Center: .::: 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, ETA All Table, Eta Table :::.

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

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

[root@teguhth10x ~]# ls -d /var/lib/mysql/*/ | wc -l
12
[root@teguhth10x ~]#

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

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

#or

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

[root@teguhth10x data]# du -s /var/lib/mysql/marinadb/
516132 /var/lib/mysql/marinadb/
[root@teguhth10x data]#

[root@teguhth10x ~]# du -s --exclude='pembelian.ibd' /var/lib/mysql/marinadb
389152 /var/lib/mysql/marinadb
[root@teguhth10x ~]#
[root@teguhth10x ~]# du -sh --exclude='pembelian.ibd' /var/lib/mysql/marinadb
381M /var/lib/mysql/marinadb
[root@teguhth10x ~]#

2 A. jumlah table via file <eta_all_table>

find /var/lib/mysql/teguhth -maxdepth 1 -type f -name "*.ibd" | wc -l

[root@teguhth10x ~]# find /var/lib/mysql/teguhth -maxdepth 1 -type f -name "*.ibd" | wc -l
5
[root@teguhth10x ~]# find /var/lib/mysql/marinadb -maxdepth 1 -type f -name "*.ibd" | wc -l
5
[root@teguhth10x ~]#

3. total all db mariadb (sample result 4649076 kib or 4,760,653,824 bytes) <eta_reinisiasi>

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

restore_procdbresume=$(if [ -n "$restore_procdb" ]; then
# echo "Proses RESTORE database '$db' sedang berjalan"
echo "$restore_procdb"
show_progress=1
else
echo "IDLE - Tidak ada proses RESTORE untuk database '$db'"
fi)

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=$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 "Process : $restore_procdbresume"
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

echo "📂 Latest Progress Files for MariaDB: '$dbprog'"
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 "📂 Latest MariaDB Database Directories: '$dbprog' in $datadirx"
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]#

10. eta all table

sh eta_all_table.sh marinadb 516132 5

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

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

#sh eta_all_table.sh marinadb 516132 5

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> <count table>"
echo "Usage: $0 <dbsf_nbc_xxxx> <21426936> <1000>"
echo "dbsize #du -s --exclude='pembelian.ibd' /var/lib/mysql/teguhth"
echo "count table #find /var/lib/mysql/teguhth -maxdepth 1 -type f -name "*.ibd" | wc -l"
echo "sh eta_all_table.sh marinadb 516132 5"
exit 1
fi

if [ -z "$sizedbsourcex" ]; then
echo "ERROR: Ukuran database source belum diisi."
echo "Usage: $0 <nama_database> <size_db_source>"
echo "dbsize #du -s --exclude='pembelian.ibd' /var/lib/mysql/teguhth"
echo "count table #find /var/lib/mysql/teguhth -maxdepth 1 -type f -name "*.ibd" | wc -l"
echo "sh eta_all_table.sh marinadb 516132 5"
exit 1
fi

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

while true
do
# dbsize contoh database teguhth
#du -s /var/lib/mysql/teguhth
#du -s --exclude='pembelian.ibd' /var/lib/mysql/teguhth

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="--:--:--"
finish_clock="--:--:--"
fi

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

#contoh jumlah table database
#find /var/lib/mysql/teguhth -maxdepth 1 -type f -name "*.ibd" | wc -l
tottblx=$(find $datadirx/$db -maxdepth 1 -type f -name "*.ibd" | wc -l)
remaintbl=$((tottbly - tottblx))
tblpersen=$((tottblx * 100 / tottbly))
tblremain=$((remaintbl * 100 / tottbly))

#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( |$)')
#restore_procdb=$(ps -ef | grep -E '[m]ysql|[m]ariadb' | grep -E '(^|[[:space:]])-f([[:space:]]|$)')

restore_procdbresume=$(if [ -n "$restore_procdb" ]; then
# echo "Proses RESTORE database '$db' sedang berjalan"
echo "$restore_procdb"
show_progress=1
else
echo "IDLE - Tidak ada proses RESTORE untuk database '$db'"
fi)

clear
datex=$(date)
echo ".::: Restore Progress '$db' at $datex ::::."
echo
#echo "Host Server Target : $hostx"
#echo "IP Server Target : $svrip"
echo "Server Target : $svrip / $hostx"
echo "Product : $dbv"
echo "Database : $db"
echo "Table Restore : $tottblx/$tottbly tables(${tblpersen}%),remain $remaintbl tables(${tblremain}%)"
#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 "DB Size : $sizedbdestx KB (${dest_gb} GB) restored / $sizedbsourcex KB (${source_gb} GB) total,remain $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 "Estimated Finish : $finish_time"
#echo "Finish At : $finish_clock"
#printf "Progress Bar : [%-50s] %6.2f%%\n" "$bar" "$progresx"
#printf "Progress Bar :%6.2f%% [%-50s]\n" "$progresx" "$bar"
#printf "Progress Bar :%6.2f%% [\033[93m%-50s\033[0m]\n" "$progresx" "$bar"
echo "Process : $restore_procdbresume"
echo
printf "Progress Bar :%6.2f%% [\033[96m%-50s\033[0m]\n" "$progresx" "$bar"
echo ""
echo "Copyright : Teguh Triharto"
echo "Website : https://www.linkedin.com/in/teguhth"
echo "Operating System : $osx"
echo ""
echo "MariaDB '$db' Latest File Progress ($tottblx/$tottbly tables)"
#echo "check Progres new file Maria '$db' in unit ($tottblx/$tottbly tables)"
echo
# find /var/lib/mysql/teguhth -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

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 "MariaDB '$db' Largest Files ($tottblx/$tottbly tables)"
#echo "check Sort file Maria '$db' in unit ($tottblx/$tottbly tables)"
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]#