.::: Script Backup Daily Multiple Database, Backup FULL, Backup LOG, Backup Incremental with Record Table backup logs in MariaDB MySQL MarinaDB :::.

 

correlation +update from https://teguhth.blogspot.com/2024/07/script-backup-full-differential.html

1. Create database & table for save backup log
create database dbatools;
CREATE TABLE backuplogs (
    host VARCHAR(255) NOT NULL,
    date DATETIME NOT NULL,
    dbname VARCHAR(255) NOT NULL,
    backup 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. put script sh backup_full_daily_with_record.sh

3. run sh backup_full_daily_with_record.sh
[root@teguhth01 script]# pwd
/data/script
[root@teguhth01 script]# sh backup_full_daily_with_record.sh

 

4. check result backup record
select * from dbatools.backuplogs;
mysql -uroot -pxxx -e "select * from dbatools.backuplogs;"
 

 

5. if want to delete record

delete from dbatools.backuplogs;

6. Script 

 

[root@teguhth01 script]# cat backup_full_daily_with_record.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='_';
datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S" ");
#echo "Backup begin $infotgl"
 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 not in('information_schema','performance_schema','mysql');");

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
   datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
   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
   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"
######
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\`.\`backuplogs\` (\`host\`, \`date\`, \`dbname\`, \`size\`) VALUES (@@hostname, NOW(), '$listdb', '$FILE_SIZEL');"
#mysql -uadmin -padmin -e "INSERT INTO \`dbatools\`.\`backuplogs\` (\`host\`, \`date\`, \`dbname\`,\`backup\`,\`size\`) VALUES (@@hostname, NOW(), '$listdb','LOG','$FILE_SIZEL');"
#mysql -uadmin -padmin -e "INSERT INTO \`dbatools\`.\`backuplogs\` (\`host\`, \`date\`, \`dbname\`,\`backup\`,\`size_mb\`,\`size\`) VALUES (@@hostname, NOW(), '$listdb','LOG','$FILE_SIZELM','$FILE_SIZELB');"
mysql -uadmin -padmin -e "INSERT INTO \`dbatools\`.\`backuplogs\` (\`host\`, \`date\`, \`dbname\`,\`backup\`,\`size_mb\`,\`size\`,\`filename\`) VALUES (@@hostname, NOW(), '$listdb','LOG','$FILE_SIZELM','$FILE_SIZELB','$FILEL');"
mysql -uadmin -padmin -e "select * from dbatools.backuplogs;"

######
else
echo ""
    # Jika tidak ditemukan xx"FULL"
    echo "Running Backup Full"
        echo ""
        datex=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
        echo "$datex --->>> 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;
    datey=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
        # mysqladmin -uadmin -padmin flush-logs
                echo "$datey --->>> Backup FULL for database '$listdb' Start from $START_DATETIME to $STOP_DATETIME"
    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\`.\`backuplogs\` (\`host\`, \`date\`, \`dbname\`, \`size\`) VALUES (@@hostname, NOW(), '$listdb', '$FILE_SIZEF');"
#mysql -uadmin -padmin -e "INSERT INTO \`dbatools\`.\`backuplogs\` (\`host\`, \`date\`, \`dbname\`,\`backup\`,\`size\`) VALUES (@@hostname, NOW(), '$listdb','FULL','$FILE_SIZEF');"
#mysql -uadmin -padmin -e "INSERT INTO \`dbatools\`.\`backuplogs\` (\`host\`, \`date\`, \`dbname\`,\`backup\`,\`size_mb\`,\`size\`) VALUES (@@hostname, NOW(), '$listdb','FULL','$FILE_SIZEFM','$FILE_SIZEFB');"
mysql -uadmin -padmin -e "INSERT INTO \`dbatools\`.\`backuplogs\` (\`host\`, \`date\`, \`dbname\`,\`backup\`,\`size_mb\`,\`size\`,\`filename\`) VALUES (@@hostname, NOW(), '$listdb','FULL','$FILE_SIZEFM','$FILE_SIZEFB','$FILEF');"
mysql -uadmin -padmin -e "select * from dbatools.backuplogs;"


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@teguhth01 script]#