Teguh Triharto Learning Center: .::: Script Backup Daily Multiple Database, Backup FULL, Backup LOG, Backup Incremental with Record Table with start, finish, duration backup logs and using insert table client in MariaDB MySQL MarinaDB :::.

correlation https://teguhth.blogspot.com/2025/02/script-backup-daily-multiple-database.html

1. Create database & table for save backup log

create database dbatools;
CREATE TABLE backuplogduration (
    host VARCHAR(255) NOT NULL,
    backuptime DATETIME NOT NULL,
    start DATETIME NOT NULL,
    finish DATETIME NOT NULL,
    duration VARCHAR(255) NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    size_mb BIGINT NOT NULL,
    size BIGINT NOT NULL,
    filename VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

2. create table tbl_clients

CREATE TABLE tbl_clients (
    client_id VARCHAR(20) PRIMARY KEY, -- ID unik dalam format id-client-XX
    client_name VARCHAR(255) NOT NULL, -- Nama client
    dbname VARCHAR(255) NOT NULL UNIQUE, -- Nama database client
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP -- Timestamp pembuatan data
);

3. insert db will be backup

INSERT INTO dbatools.tbl_clients (client_id, client_name, dbname)
VALUES
    ('id-client-01', 'Client A', 'adventureworks'),
    ('id-client-02', 'Client B', 'dbatools'),
    ('id-client-03', 'Client C', 'sakila'),
    ('id-client-04', 'Client D', 'teguhthtools');

INSERT INTO dbatools.tbl_clients (client_id, client_name, dbname)
VALUES ('id-client-22', 'Client Teguh', 'teguhth');

## delete from dbatools.tbl_clients where client_id='id-client-22';
select * from dbatools.tbl_clients;

4. put script and run

[root@teguhth inserttable]# pwd
/data/script/scriptbackup/inserttable
[root@teguhth inserttable]# sh backup_full_daily_with_record_duration_tbl_client.sh

5. check result backup record
select * from dbatools.backuplogduration ORDER BY backuptime desc;
mysql -uroot -pxxx -e "select * from dbatools.backuplogduration ORDER BY backuptime desc;"

## delete from dbatools.tbl_clients where client_id='id-client-22';
select * from dbatools.tbl_clients;

6. if want to delete record

delete from dbatools.backuplogduration;
## delete from dbatools.tbl_clients where client_id='id-client-22';
select * from dbatools.tbl_clients;

7. Script backup_full_daily_with_record_duration_tbl_client.sh

[root@teguhth inserttable]# cat backup_full_daily_with_record_duration_tbl_client.sh
echo ""
Server=$(hostname)
ip_address=$(ip a | awk '/inet / && $2 !~ /^127\./ {gsub("addr:","",$2); print $2}')
# Print the IP address

infotgl=$(date)
dbversion=$(mysql -uadmin -padmin -sNe "select @@version as VersionDB");

echo ".::: Backup FULL & Transaction Log database MariaDB / MySQL using custom in '$Server $dbversion' with $ip_address :::."
echo ""
#!/bin/bash
#
#clear;
year=`date +%Y`; month=`date +%m`; day=`date +%d`; hour=`date +%H`; min=`date +%M`; sec=`date +%S`; dayname=`date +%a`;underscore='_';

#echo "Backup begin $infotgl"
backuptime=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
backuppath="/backup";
#mkdir -p $backuppath/$year-$month-$day;
#path="$backuppath/$year-$month-$day";
#listdb='dbsupportsite'
#listdb='sakila'

# Current date and time
CURRENT_DATETIME=$(date +"%Y%m%d_%H%M%S")

# Calculate start datetime (1 hour ago)
#START_DATETIME=$(date -d "1 hour ago" +"%Y-%m-%d %H:%M:%S")
#pembulatan 00
START_DATETIME=$(date -d "1 hour ago" +"%Y-%m-%d %H:%M:00")

#START_DATETIME=$(date -d "15 minutes ago" +"%Y-%m-%d %H:%M:%S")
#START_DATETIME=$(date -d "30 minutes ago" +"%Y-%m-%d %H:%M:%S")
#START_DATETIME=$(date -d "45 minutes ago" +"%Y-%m-%d %H:%M:%S")
#START_DATETIME=$(date -d "60 minutes ago" +"%Y-%m-%d %H:%M:%S")

# Stop datetime (current time)
#STOP_DATETIME=$(date +"%Y-%m-%d %H:%M:%S")
#pembulatan 00
STOP_DATETIME=$(date +"%Y-%m-%d %H:%M:00")

#echo "Backup begin $infotgl for periode $START_DATETIME to $STOP_DATETIME"
#echo ""
# Find the latest mysql-bin log file
LATEST_BINLOG=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)

echo "mysqlbin using $LATEST_BINLOG"
echo ""

#mkdir -p $backuppath/$year$month$day/$listdb;
#path="$backuppath/$year$month$day/$listdb";

# Output file path
#OUTPUT_FILE="$path/LOG_${listdb}_${CURRENT_DATETIME}.sql.gz"

#echo "$datex --->>> Backup Transaction Log for database '$listdb' in $path"

#!/bin/bash

# Mendapatkan jam dan menit saat ini
current_hour=$(date +"%H")
current_minute=$(date +"%M")

# Mengecek apakah saat ini berada dalam rentang 00:00-00:10
if [ "$current_hour" -eq 0 ] && [ "$current_minute" -le 10 ]; then
    mysqladmin -uadmin -padmin flush-logs
        echo "Reset mysqlbinlog"
else
    echo "Not reset mysqlbinlog"
fi

dbname=$(mysql -uadmin -padmin -sNe "select schema_name from information_schema.schemata where schema_name in(select dbname from dbatools.tbl_clients);");

for listdb in $dbname
do

mkdir -p $backuppath/$year$month$day/$listdb;
path="$backuppath/$year$month$day/$listdb";

# Check for files with "full" and "log" filters
full_files=$(find "$path" -type f -name "*FULL*")

if [ -n "$full_files" ] ; then
echo ""
    echo "Running Backup Log"
        echo ""
   # Run mysqlbinlog command
startbackupl=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_startl=$(date +%s)

   echo "$datex --->>> Backup Transaction Log for database '$listdb' in $path"
# mysqlbinlog --database=$listdb --start-datetime="$START_DATETIME" --stop-datetime="$STOP_DATETIME" "$LATEST_BINLOG"   | gzip -c > "$path"/LOG_"$listdb"_$year$month$day$underscore$hour$min$sec.sql.gz
   mysqlbinlog --database=$listdb --start-datetime="$START_DATETIME" --stop-datetime="$STOP_DATETIME" "$LATEST_BINLOG" | gzip -c > "$path"/LOG_"$listdb"_$year$month$day$underscore$hour$min$sec.sql.gz

######## Script Begin ########
######## Script Finish ########

   datey=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
      echo "$datey --->>> Backup Transaction Log for database '$listdb' Start from $START_DATETIME to $STOP_DATETIME"
   echo "$datey --->>> Finished Backup Transaction Log for database '$listdb' in $path"

   finishbackupl=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_endl=$(date +%s)

# Menghitung selisih waktu dalam detik
finishl=$((epoch_endl - epoch_startl))

# Menghitung hari, jam, menit, dan detik
daysl=$((finishl / 86400))
hoursl=$(((finishl % 86400) / 3600))
minutesl=$(((finishl % 3600) / 60))
secondsl=$((finishl % 60))

# Format output
formatted_timel=$(printf "%d:%02d:%02d:%02d" "$daysl" "$hoursl" "$minutesl" "$secondsl")

echo "Duration Process LOG"
echo "Duration Full : $formatted_timel"
echo ""
echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""

######
FILEL=""$path"/LOG_"$listdb"_$year$month$day$underscore$hour$min$sec.sql.gz"
FILELX="LOG_"$listdb"_$year$month$day$underscore$hour$min$sec.sql.gz"
#FILEY="$path/FULL_$listdb_${year}${month}${day}${underscore}${hour}${min}${sec}.sql.gz"
FILE_SIZELB=$(ls -l "$FILEL" | awk '{print $5}')
FILE_SIZELM=$(echo "scale=2; $(ls -l "$FILEL" | awk '{print $5}') / 1024 / 1024" | bc)

#FILEZ=$(echo "$FILEY")

echo "sizenya $FILE_SIZELM MB in $FILEL"
echo "filenya $FILELX"
mysql -uadmin -padmin -e "INSERT INTO \`dbatools\`.\`backuplogduration\` (\`host\`, \`backuptime\`,\`start\`,\`finish\`,\`duration\`, \`dbname\`,\`type\`,\`size_mb\`,\`size\`,\`filename\`)
VALUES (@@hostname, '$backuptime','$startbackupl','$finishbackupl' ,'$formatted_timel','$listdb','LOG','$FILE_SIZELM','$FILE_SIZELB','$FILEL');"
mysql -uadmin -padmin -e "select * from dbatools.backuplogduration ORDER BY backuptime DESC FETCH FIRST 1 ROWS ONLY;"

######
else
echo ""
    # Jika tidak ditemukan xx"FULL"
    echo "Running Backup Full"
        echo ""
        datexf=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
      startbackupf=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
      epoch_startf=$(date +%s)
        echo "$datexf --->>> Backup FULL for database '$listdb' in $path"
                 mysqldump -uadmin -padmin -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/FULL_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql.gz;

######## Script Begin ########
######## Script Finish ########

        dateyf=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");

        # mysqladmin -uadmin -padmin flush-logs
                echo "$dateyf --->>> Backup FULL for database '$listdb' Start from $START_DATETIME to $STOP_DATETIME"

finishbackupf=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"");
epoch_endf=$(date +%s)

# Menghitung selisih waktu dalam detik
finishf=$((epoch_endf - epoch_startf))

# Menghitung hari, jam, menit, dan detik
daysf=$((finishf / 86400))
hoursf=$(((finishf % 86400) / 3600))
minutesf=$(((finishf % 3600) / 60))
secondsf=$((finishf % 60))

# Format output
formatted_timef=$(printf "%d:%02d:%02d:%02d" "$daysf" "$hoursf" "$minutesf" "$secondsf")

echo "Duration Process Full"
echo "Duration Full : $formatted_timef"
echo ""
echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""

    echo "$datey --->>> Finished Full for database '$listdb' in $path"
FILEF=""$path"/FULL_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql.gz"
FILEFX="FULL_"$listdb"_"$year$month$day$underscore$hour$min$sec".sql.gz"
#FILEY="$path/FULL_$listdb_${year}${month}${day}${underscore}${hour}${min}${sec}.sql.gz"
FILE_SIZEFB=$(ls -l "$FILEF" | awk '{print $5}')
FILE_SIZEFM=$(echo "scale=2; $(ls -l "$FILEF" | awk '{print $5}') / 1024 / 1024" | bc)

#FILEZ=$(echo "$FILEY")

echo "sizenya $FILE_SIZEFM MB in $FILEF"
echo "filenya $FILEFX"

mysql -uadmin -padmin -e "INSERT INTO \`dbatools\`.\`backuplogduration\` (\`host\`, \`backuptime\`,\`start\`,\`finish\`,\`duration\`, \`dbname\`,\`type\`,\`size_mb\`,\`size\`,\`filename\`)
VALUES (@@hostname, '$backuptime','$startbackupf','$finishbackupf' ,'$formatted_timef','$listdb','FULL','$FILE_SIZEFM','$FILE_SIZEFB','$FILEF');"

mysql -uadmin -padmin -e "select * from dbatools.backuplogduration ORDER BY backuptime DESC FETCH FIRST 1 ROWS ONLY;"

fi

done;
# remove binary log
echo ""
echo "Cleansing binary log"
mysql -uadmin -padmin -sNe "purge binary logs before date(now() - interval 2 day);"

echo ""
LATEST_BINLOG_AFTER=$(ls -1t /var/lib/mysql/mysql-bin.* | grep -v 'mysql-bin.index' | head -n 1)
echo "mysqlbin after flush using $LATEST_BINLOG_AFTER"
echo ""
#ls -lh $path

echo "Cleansing file backup"
find $backuppath/* -type d -mtime +7 -exec rm -rf {} \;
echo ""
datefnh=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
echo "Backup finish $datefnh"
echo ""

echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""
[root@teguhth inserttable]#

Teguh Triharto Learning Center

Blog List

Wednesday, March 12, 2025

.::: Script Backup Daily Multiple Database, Backup FULL, Backup LOG, Backup Incremental with Record Table with start, finish, duration backup logs and using insert table client in MariaDB MySQL MarinaDB :::.

No comments:

Post a Comment

Popular Posts