.::: Script Backup Full + Differential + Transaction Log in MariaDB MySQL & Scheduler just like SQL Server for SingleDB & MultipleDB include Restore in One File Script :::.

 
A. SingleDB

1. run script one time to full backup

sh /data/script/scriptbackup/backup_full_log_single_db.sh
 

2. run script two time to log backup

sh /data/script/scriptbackup/backup_full_log_single_db.sh
 

3. capture combine full & log
 

4. check file backup
 

5. sample cron

# crontab per menit for testing
* * * * * root sh /data/script/scriptbackup/backup_full_log_single_db.sh

# crontab per jam
0 * * * * root sh /data/script/scriptbackup/backup_full_log_single_db.sh


B. MultipleDB

1. run script one time to full backup

sh /data/script/scriptbackup/backup_full_log_multiple_db.sh
 

 

2. run script two time to log backup

sh /data/script/scriptbackup/backup_full_log_multiple_db.sh
 

 

3. check file backup 

 

4. sample cron

# crontab per menit for testing
* * * * * root sh /data/script/scriptbackup/backup_full_log_multiple_db.sh

# crontab per jam
0 * * * * root sh /data/script/scriptbackup/backup_full_log_multiple_db.sh

C. for restore backup & restore log
https://teguhth.blogspot.com/2024/02/script-to-restore-backup-full-all.html

D. script backup_full_log_single_db.sh

[root@teguhth ~]# cat /data/script/scriptbackup/backup_full_log_single_db.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 -pxxxxx -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='teguhth'


# 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"

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

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"

# 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 > "$OUTPUT_FILE"

#    mysqlbinlog --database=$listdb --start-datetime="$START_DATETIME" --stop-datetime="$STOP_DATETIME"  "$LATEST_BINLOG"  | sed '/^use / s/^/# /' | gzip -c > "$OUTPUT_FILE"
   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"
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"
#reset mysqlbinlog
mysqladmin -uadmin -pxxxxx flush-logs

# backup full
        mysqldump -uadmin -pxxxxx -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 -pxxxxx 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"

fi

# remove binary log
echo ""
echo "Cleansing binary log"
mysql -uadmin -pxxxxx -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 ""
echo "Cleansing file backup"
find $backuppath/* -type d -mtime +7 -exec rm -rf {} \;

# find $path/* -type d -mmin +600 -exec rm -rf {} \;'
datefnh=$(date +%Y"-"%m"-"%d" "%H":"%M":"%S"_%N");
echo ""
echo "Backup finish $datefnh"
echo ""

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

E. script backup_full_log_multiple_db.sh

[root@teguhth ~]# cat /data/script/scriptbackup/backup_full_log_multiple_db.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 -pxxxxx -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 -pxxxxx flush-logs
        echo "Reset mysqlbinlog"
else
    echo "Not reset mysqlbinlog"
fi

dbname=$(mysql -uadmin -pxxxxx -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"  | sed '/^use / s/^/# /' | 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"
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 -pxxxxx -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 -pxxxxx 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"

fi

done;
# remove binary log
echo ""
echo "Cleansing binary log"
mysql -uadmin -pxxxxx -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 ~]#