.::: Script restore database MariaDB using record to table include duration Restore :::.

 

A. simple record

1. create table restore_log

CREATE TABLE IF NOT EXISTS restore_log (
    db_name VARCHAR(100),
    restore_time DATETIME,
    backup_file VARCHAR(255),
    restored_by VARCHAR(100)
);

2. create script 

[root@teguhth script]# cat restore_with_record.sh

#!/bin/bash
DB="simpledb"
USER="admin"
PASS="admin"
BACKUPFILE="/data/teguhth.sql"
RESTORED_BY=$(whoami)

# restore database
mysql -u $USER -p$PASS $DB < $BACKUPFILE

# insert log ke MariaDB
mysql -u $USER -p$PASS dbatools -e "INSERT INTO restore_log (db_name, restore_time, backup_file, restored_by) VALUES ('$DB', NOW(), '$BACKUPFILE', '$USER');"
[root@teguhth script]#

3. run & check table restore log 

SELECT * FROM restore_log ORDER BY restore_time desc;
 

B. Simple with duration

1. ceate table 

CREATE TABLE IF NOT EXISTS restore_log_duration (
    restore_time DATETIME,
    start DATETIME NOT NULL,
    finish DATETIME NOT NULL,
    duration VARCHAR(255) NOT NULL,
    db_name VARCHAR(100),
    backup_file VARCHAR(255),
    restored_by VARCHAR(100)
);

2. create script 

[root@teguhth script]# more restore_with_record_duration.sh

#!/bin/bash
DB="simpledb"
USER="admin"
PASS="admin"
BACKUPFILE="/data/teguhth.sql"
RESTORED_BY=$(whoami)

# Menyimpan waktu mulai dalam format manusiawi
# For Test
# timedatectl set-time "2020-03-22 08:34:00"

datex=$(date "+%Y-%m-%d %H:%M:%S.%N")
#epoch_start='1580390912'
epoch_start=$(date +%s)  # Gunakan detik saja, jangan nanodetik
echo "$datex ->>> Start Process"

# For Test
# timedatectl set-time "2025-01-31 08:34:00"
# vmware-toolbox-cmd timesync enable

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

dater=$(date "+%Y-%m-%d %H:%M:%S")
# restore database
mysql -u $USER -p$PASS $DB < $BACKUPFILE

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

# Menyimpan waktu selesai dalam format manusiawi
datey=$(date "+%Y-%m-%d %H:%M:%S.%N")
epoch_end=$(date +%s)  # Gunakan detik saja
#epoch_end='1738253312'

echo "$datey ->>> Finish Process"

# Menghitung selisih waktu dalam detik
finish=$((epoch_end - epoch_start))

# Menghitung hari, jam, menit, dan detik
days=$((finish / 86400))
hours=$(((finish % 86400) / 3600))
minutes=$(((finish % 3600) / 60))
seconds=$((finish % 60))

# Konversi ke tahun, bulan, hari, jam, menit, dan detik
yearsx=$((finish / 31536000))             # 1 tahun = 365 * 24 * 3600 detik
remaining=$((finish % 31536000))

monthsx=$((remaining / 2592000))          # 1 bulan = 30 * 24 * 3600 detik
remaining=$((remaining % 2592000))

daysx=$((remaining / 86400))              # 1 hari = 86400 detik
remaining=$((remaining % 86400))

hoursx=$((remaining / 3600))              # 1 jam = 3600 detik
remaining=$((remaining % 3600))

minutesx=$((remaining / 60))              # 1 menit = 60 detik
secondsx=$((remaining % 60))

# Format output
formatted_time=$(printf "%d:%02d:%02d:%02d" "$days" "$hours" "$minutes" "$seconds")

# Format output
formatted_time2=$(printf "%d Tahun, %d Bulan, %d Hari, %02d:%02d:%02d" "$yearsx" "$monthsx" "$daysx" "$hoursx" "$minutesx" "$secondsx")

echo "Duration Process"
echo "Start     : $datex"
echo "Finish    : $datey"
echo "Duration 1 : $formatted_time"
echo "Duration 2 : $formatted_time2"
echo ""
echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""


# insert log ke MariaDB
mysql -u $USER -p$PASS dbatools -e "INSERT INTO restore_log_duration (restore_time,start,finish,duration,db_name,backup_file, restored_by) VALUES ('$dater','$date
x','$datey','$formatted_time','$DB','$BACKUPFILE','$USER');"

[root@teguhth script]#
 

3. run & check table restore_log_duration