correlation with https://teguhth.blogspot.com/2025/10/script-drop-all-database-non-system-in.html
1. Drop table using script
2. Drop table using script and duration
3. script drop_table_indb.sh
[root@teguhth tbldrop]# pwd
/data/drop/tbldrop
[root@teguhth tbldrop]# cat drop_table_indb.sh
#!/bin/bash
# ===========================================================
# Script Name : drop_all_tbl_confirm.sh
# Tujuan : Menghapus semua tables dari 1 Database MariaDB dengan konfirmasi
# OS Diuji : CentOS 9
# Author : Teguh Triharto
# ===========================================================
# === Konfigurasi koneksi ===
USER="admin"
PASS="admin"
HOST="localhost"
DBX="secretdb"
PORT="3306"
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {sub("/.*","",$2); print $2}')
infotgl=$(date)
dbversion=$(mysql -u"$USER" -p"$PASS" -sNe "SELECT @@version;")
echo ".::: Drop All tables in database '$DBX' on '$Server' ($dbversion) with IP $ip_address :::."
echo ""
# === Ambil semua tables (kecuali sistem) ===
tablelist=$(mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -Nse \
" SELECT table_name FROM information_schema.tables WHERE table_schema = '$DBX';")
# === Jika tidak ada tables selain sistem ===
if [ -z "$tablelist" ]; then
echo "? Tidak ada tables didatabase '$DBX' yang bisa dihapus."
exit 0
fi
# === Tampilkan daftar tables yang akan dihapus ===
echo "?? tables yang akan dihapus didatabase '$DBX': diserver $ip_address"
echo "$tablelist" | sed 's/^/ - /'
# === Konfirmasi pengguna ===
read -p "Apakah Anda yakin ingin drop SEMUA tables di atas di '$DBX' di server $ip_address ? (ketik 'YES' untuk lanjut): " CONFIRM
if [ "$CONFIRM" == "YES" ]; then
echo ""
echo "?? Menghapus semua tables..."
echo ""
# === Catat waktu mulai ===
datex=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_start=$(date +%s)
echo "$datex --->>> Start Process"
echo ""
mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -e "SET GLOBAL FOREIGN_KEY_CHECKS = 0;"
# === Proses penghapusan tables ===
for tbl in $tablelist; do
datedropbegin=$(date "+%Y-%m-%d %H:%M:%S.%N")
echo "$datedropbegin --->>> Begin Drop tables: $tbl"
mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -D "$DBX" -e "TRUNCATE TABLE $tbl;"
mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -D "$DBX" -e "drop TABLE $tbl;"
datedropend=$(date "+%Y-%m-%d %H:%M:%S.%N")
echo "$datedropend --->>> Finish Drop tables: $tbl"
echo ""
done
mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -e "SET GLOBAL FOREIGN_KEY_CHECKS = 1;"
echo "? Semua tables berhasil dihapus di database '$DBX' di server $ip_address."
echo ""
# === Catat waktu selesai ===
datey=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_end=$(date +%s)
echo "$datey --->>> Finish Process"
echo ""
# === Hitung durasi ===
finish=$((epoch_end - epoch_start))
days=$((finish / 86400))
hours=$(((finish % 86400) / 3600))
minutes=$(((finish % 3600) / 60))
seconds=$((finish % 60))
yearsx=$((finish / 31536000))
remaining=$((finish % 31536000))
monthsx=$((remaining / 2592000))
remaining=$((remaining % 2592000))
daysx=$((remaining / 86400))
remaining=$((remaining % 86400))
hoursx=$((remaining / 3600))
remaining=$((remaining % 3600))
minutesx=$((remaining / 60))
secondsx=$((remaining % 60))
formatted_time=$(printf "%d:%02d:%02d:%02d" "$days" "$hours" "$minutes" "$seconds")
formatted_time2=$(printf "%d Tahun, %d Bulan, %d Hari, %02d:%02d:%02d" \
"$yearsx" "$monthsx" "$daysx" "$hoursx" "$minutesx" "$secondsx")
echo "?? Durasi Proses"
echo "Start : $datex"
echo "Finish : $datey"
echo "Duration 1: $formatted_time"
echo "Duration 2: $formatted_time2"
echo ""
echo "Copyright by : Teguh Triharto"
echo "LinkedIn : https://www.linkedin.com/in/teguhth"
echo ""
else
echo "?? Pembatalan: tidak ada tables yang dihapus."
fi
[root@teguhth tbldrop]#
4. script drop_table_indb_duration.sh
[root@teguhth tbldrop]# pwd
/data/drop/tbldrop
[root@teguhth tbldrop]# cat drop_table_indb_duration.sh
#!/bin/bash
# ===========================================================
# Script Name : drop_all_tbl_confirm.sh
# Tujuan : Menghapus semua tables dari 1 Database MariaDB dengan konfirmasi
# OS Diuji : CentOS 9
# Author : Teguh Triharto
# ===========================================================
# === Konfigurasi koneksi ===
USER="admin"
PASS="admin"
HOST="localhost"
DBX="secretdb"
PORT="3306"
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {sub("/.*","",$2); print $2}')
infotgl=$(date)
dbversion=$(mysql -u"$USER" -p"$PASS" -sNe "SELECT @@version;")
echo ".::: Drop All tables in database '$DBX' on '$Server' ($dbversion) with IP $ip_address :::."
echo ""
# === Ambil semua tables (kecuali sistem) ===
tablelist=$(mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -Nse \
" SELECT table_name FROM information_schema.tables WHERE table_schema = '$DBX';")
# === Jika tidak ada tables selain sistem ===
if [ -z "$tablelist" ]; then
echo "? Tidak ada tables didatabase '$DBX' yang bisa dihapus."
exit 0
fi
# === Tampilkan daftar tables yang akan dihapus ===
echo "?? tables yang akan dihapus didatabase '$DBX': diserver $ip_address"
echo "$tablelist" | sed 's/^/ - /'
# === Konfirmasi pengguna ===
read -p "Apakah Anda yakin ingin drop SEMUA tables di atas di '$DBX' di server $ip_address ? (ketik 'YES' untuk lanjut): " CONFIRM
if [ "$CONFIRM" == "YES" ]; then
echo ""
echo "?? Menghapus semua tables..."
echo ""
# === Catat waktu mulai ===
datex=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_start=$(date +%s)
echo "$datex --->>> Start Process"
echo ""
mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -e "SET GLOBAL FOREIGN_KEY_CHECKS = 0;"
# === Proses penghapusan tables ===
for tbl in $tablelist; do
datedropbegin=$(date "+%Y-%m-%d %H:%M:%S.%N")
datexevery=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_startevery=$(date +%s)
echo "$datedropbegin --->>> Begin Drop tables: $tbl"
mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -D "$DBX" -e "TRUNCATE TABLE $tbl;"
mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -D "$DBX" -e "drop TABLE $tbl;"
datedropend=$(date "+%Y-%m-%d %H:%M:%S.%N")
echo "$datedropend --->>> Finish Drop tables: $tbl"
dateyevery=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_endevery=$(date +%s)
# === Hitung durasi every===
finishevery=$((epoch_endevery - epoch_startevery))
daysevery=$((finishevery / 86400))
hoursevery=$(((finishevery % 86400) / 3600))
minutesevery=$(((finishevery % 3600) / 60))
secondsxsevery=$((finishevery % 60))
formatted_timeevery=$(printf "%d:%02d:%02d:%02d" "$daysevery" "$hoursevery" "$minutesevery" "$secondsxsevery")
echo "Duration Drop table in '$DBX' --->>> $formatted_timeevery"
echo ""
done
mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -e "SET GLOBAL FOREIGN_KEY_CHECKS = 1;"
echo "? Semua tables berhasil dihapus di database '$DBX' di server $ip_address."
echo ""
# === Catat waktu selesai ===
datey=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_end=$(date +%s)
echo "$datey --->>> Finish Process"
echo ""
# === Hitung durasi ===
finish=$((epoch_end - epoch_start))
days=$((finish / 86400))
hours=$(((finish % 86400) / 3600))
minutes=$(((finish % 3600) / 60))
seconds=$((finish % 60))
yearsx=$((finish / 31536000))
remaining=$((finish % 31536000))
monthsx=$((remaining / 2592000))
remaining=$((remaining % 2592000))
daysx=$((remaining / 86400))
remaining=$((remaining % 86400))
hoursx=$((remaining / 3600))
remaining=$((remaining % 3600))
minutesx=$((remaining / 60))
secondsx=$((remaining % 60))
formatted_time=$(printf "%d:%02d:%02d:%02d" "$days" "$hours" "$minutes" "$seconds")
formatted_time2=$(printf "%d Tahun, %d Bulan, %d Hari, %02d:%02d:%02d" \
"$yearsx" "$monthsx" "$daysx" "$hoursx" "$minutesx" "$secondsx")
echo "?? Durasi Proses"
echo "Start : $datex"
echo "Finish : $datey"
echo "Duration 1: $formatted_time"
echo "Duration 2: $formatted_time2"
echo ""
echo "Copyright by : Teguh Triharto"
echo "LinkedIn : https://www.linkedin.com/in/teguhth"
echo ""
else
echo "?? Pembatalan: tidak ada tables yang dihapus."
fi
[root@teguhth tbldrop]#
No comments:
Post a Comment