This script is a combination of tasks, including cleaning up old log and backup files, performing a MySQL database dump, compressing the dump, and uploading files to an SFTP server. Make sure to replace MYSQL_USER=mysql MYSQL_PASSWORD=mysql SFTP_USER=user SFTP_PASSWORD=******** with your actual user names and passwords for security.

mysql_backup.sh

#!/bin/bash

# ----------------------------------------------------------------------- #
# Automate MySQL Database Backups to Secure SFTP Server.             #
# ----------------------------------------------------------------------- #
# This Bash script simplifies the process of backing up                   #
# your MySQL database by automating the entire workflow.                  #
# It cleans up old log and backup files, performs a secure database dump, #
# compresses the backup, and uploads it to a remote SFTP server.          #
# Ensure data integrity and easy retrieval with this efficient script.    #
# ----------------------------------------------------------------------- #

# Set the current date in the format YYYY-MM-DD
FILE_NAME=`date +%Y-%m-%d`

# MySQL database credentials
MYSQL_USER=mysql      # Replace with the actual user
MYSQL_PASSWORD=mysql  # Replace with the actual password

# Directory paths for database backups
DIR_BACKUP=/home/db_dumps
DIR_BACKUP_DATE=$DIR_BACKUP/$FILE_NAME

# SFTP (Secure File Transfer Protocol) credentials
SFTP_USER=user      # Replace with the actual user
SFTP_PASSWORD=***** # Replace with the actual password
SFTP_HOST=sftp://nagiyev.pro
SFTP_PORT=22
SFTP_DIR=/db_dumps

# Number of backup dates to keep
KEEP_DATES=10

# Remove old backup directories that are older than $KEEP_DATES days
find $DIR_BACKUP -type d -mtime +$KEEP_DATES -exec rm -rf {} \;

# Create a new backup directory with the current date
mkdir $DIR_BACKUP_DATE

# Dump each MySQL database (excluding system databases) to individual SQL files
mysql -s -r -N -u $MYSQL_USER -p$MYSQL_PASSWORD -e 'show databases where `Database` not in("information_schema","performance_schema","phpmyadmin")' | while read db; do 
    mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD $db -r $DIR_BACKUP_DATE/${db}.sql; 
    [[ $? -eq 0 ]] && gzip $DIR_BACKUP_DATE/${db}.sql; 
done

# Dump all databases into a single SQL file
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --all-databases > $DIR_BACKUP_DATE/all_databases.sql

# Compress the all databases SQL file
gzip $DIR_BACKUP_DATE/all_databases.sql

# Use lftp to securely upload the backup files to the remote SFTP server
lftp -c "set net:timeout 5;set net:max-retries 3;set net:reconnect-interval-multiplier 1;set net:reconnect-interval-base 5;set sftp:auto-confirm yes;open -u $SFTP_USER,$SFTP_PASSWORD $SFTP_HOST -p $SFTP_PORT;mirror -X .* -X .*/ --parallel=10 --reverse --verbose --delete $DIR_BACKUP $SFTP_DIR;bye"

The command to make script runnable chmod +x mysql_backup.sh

To run backup with crontab add the foloowing line to crontab -e
0 20 * * * /directory/to/mysql_backup.sh 2> /directory/to/cron.log