.::: Script Drop All Database non-system in MariaDB MySQL in Centos 9 :::.

 

1. run script drop database non-system 

sh drop_database_nosystem_all.sh
 

2. run script drop database non-system with duration

sh drop_database_nosystem_all_duration.sh  

3. capture drop database non-system 
 

[root@teguhth drop]# cat drop_database_nosystem_all.sh
#!/bin/bash
# ===========================================================
# Script Name  : drop_all_db_confirm.sh
# Tujuan       : Menghapus semua database MariaDB non-sistem dengan konfirmasi
# OS Diuji     : CentOS 9
# Author       : Teguh Triharto
# ===========================================================

# === Konfigurasi koneksi ===
USER="admin"
PASS="admin"
HOST="localhost"
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 Database non-system on '$Server' ($dbversion) with IP $ip_address :::."
echo ""

# === Ambil semua database (kecuali sistem) ===
DBLIST=$(mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -Nse \
"SELECT SCHEMA_NAME
 FROM information_schema.SCHEMATA
 WHERE SCHEMA_NAME NOT IN ('information_schema','performance_schema','mysql','sys')
 ORDER BY SCHEMA_NAME;")

# === Jika tidak ada database selain sistem ===
if [ -z "$DBLIST" ]; then
  echo "✅ Tidak ada database non-sistem yang bisa dihapus."
  exit 0
fi

# === Tampilkan daftar database yang akan dihapus ===
echo "⚠️  Database yang akan dihapus:"
echo "$DBLIST" | sed 's/^/ - /'

# === Konfirmasi pengguna ===
read -p "Apakah Anda yakin ingin menghapus SEMUA database di atas? (ketik 'YES' untuk lanjut): " CONFIRM

if [ "$CONFIRM" == "YES" ]; then
  echo ""
  echo "🚨 Menghapus semua database..."
  echo ""

  # === Catat waktu mulai ===
  datex=$(date "+%Y-%m-%d %H:%M:%S.%N")
  epoch_start=$(date +%s)
  echo "$datex --->>> Start Process"
  echo ""

  # === Proses penghapusan database ===
  for DB in $DBLIST; do
    datedropbegin=$(date "+%Y-%m-%d %H:%M:%S.%N")
    echo "$datedropbegin --->>> Begin Drop database: $DB"
    mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -e "DROP DATABASE IF EXISTS \`$DB\`;"
    datedropend=$(date "+%Y-%m-%d %H:%M:%S.%N")
    echo "$datedropend --->>> Finish Drop database: $DB"
    echo ""
  done

  echo "✅ Semua database berhasil dihapus."
  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 database yang dihapus."
fi

[root@teguhth drop]#

4. capture drop database non-system with duration

 

[root@teguhth drop]# cat drop_database_nosystem_all_duration.sh
#!/bin/bash
# ===========================================================
# Script Name  : drop_all_db_confirm.sh
# Tujuan       : Menghapus semua database MariaDB non-sistem dengan konfirmasi
# OS Diuji     : CentOS 9
# Author       : Teguh Triharto
# ===========================================================

# === Konfigurasi koneksi ===
USER="admin"
PASS="admin"
HOST="localhost"
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 Database non-system on '$Server' ($dbversion) with IP $ip_address :::."
echo ""

# === Ambil semua database (kecuali sistem) ===
DBLIST=$(mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -Nse \
"SELECT SCHEMA_NAME
 FROM information_schema.SCHEMATA
 WHERE SCHEMA_NAME NOT IN ('information_schema','performance_schema','mysql','sys')
 ORDER BY SCHEMA_NAME;")

# === Jika tidak ada database selain sistem ===
if [ -z "$DBLIST" ]; then
  echo "✅ Tidak ada database non-sistem yang bisa dihapus."
  exit 0
fi

# === Tampilkan daftar database yang akan dihapus ===
echo "⚠️  Database yang akan dihapus:"
echo "$DBLIST" | sed 's/^/ - /'

# === Konfirmasi pengguna ===
read -p "Apakah Anda yakin ingin menghapus SEMUA database di atas? (ketik 'YES' untuk lanjut): " CONFIRM

if [ "$CONFIRM" == "YES" ]; then
  echo ""
  echo "🚨 Menghapus semua database..."
  echo ""

  # === Catat waktu mulai ===
  datex=$(date "+%Y-%m-%d %H:%M:%S.%N")
  epoch_start=$(date +%s)
  echo "$datex --->>> Start Process"
  echo ""

  # === Proses penghapusan database ===
  for DB in $DBLIST; 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 database: $DB"
    mysql -h"$HOST" -u"$USER" -p"$PASS" -P"$PORT" -e "DROP DATABASE IF EXISTS \`$DB\`;"
    datedropend=$(date "+%Y-%m-%d %H:%M:%S.%N")

        ######## Script Begin ########

# Fungsi untuk menampilkan tampilan waiting
function show_waiting_page {
    echo "Please wait while the process is running..."
    while true; do
        echo -n "*"
        sleep 1
    done
}

# Menjalankan proses dalam background (simulasi proses)
function run_process {
 #   echo "Simulating a long process..."
    sleep 72  # Simulasi proses yang lebih lama
}

# Menampilkan waiting page dalam background
#show_waiting_page &
#waiting_pid=$!

# Menjalankan proses utama
run_process

# Menghentikan tampilan waiting setelah proses selesai
#kill $waiting_pid

#echo -e "\nProcess completed successfully!"

######## Script Finish ########

        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 "$datedropend --->>> Finish Drop database: $DB"
        echo "Duration Drop database: $DB --->>> $formatted_timeevery"
    echo ""
  done

  echo "✅ Semua database berhasil dihapus."
  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 database yang dihapus."
fi

[root@teguhth drop]#