.::: Create Quary Daily Backup All Database MariaDB MySQL & retention and Crontab :::.

1. Create script all backup using custom

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

infotgl=$(date)

echo ".::: Backup all database using custom in $Server 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`;
echo "Backup begin $infotgl"
 backuppath="/backup";
mkdir -p $backuppath/$year-$month-$day;
path="$backuppath/$year-$month-$day";

dbname=$(mysql -uroot -proot -sNe "select schema_name from information_schema.schemata where schema_name not in('information_schema','performance_schema','mysql');");

for listdb in $dbname
do
        mysqldump -uroot -proot -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false $listdb | gzip -c > "$path"/"$listdb"_"$year$month$day$hour$min$sec".sql.gz;
done;
echo ""
ls -lh $path
echo ""
echo "Backup finish $infotgl"
echo ""
echo ".::: Delete backup retention in $Server with $ip_address :::."
echo ""
echo "Delete begin $infotgl"
backuppath="/backup";

find $path/* -type d -mtime +10 -exec rm -rf {} \;
echo ""
echo "Folder Backup"
ls -lh $backuppath
echo ""
echo "Size Folder Backup"
du -h $backuppath
echo ""
echo "Delete finish $infotgl"
echo ""
echo "Copyright by           : Teguh Triharto"
echo "Website                : https://www.linkedin.com/in/teguhth"
echo ""
##[root@teguhth backup]#
 

2. add crontab for daily

0 0 * * * sh /backup/backuptest.sh

 

3. test run backup

4. check result