backup script to backup all sql database to remote ftp server

backup all database using mysql root pass to remote ftp server here is the code of ssh script save it and modify mysql root user and password with ftp details

#!/bin/bash

# MySQL root credentials change here
MYSQL_USER="root"
MYSQL_PASSWORD="passwordhere"

# Backup directory change here this is the backup directory in local server where all database copied and compressed

BACKUP_DIR="/home/db"

# FTP credentials change here
FTP_SERVER="ftpserver"
FTP_USER="user"
FTP_PASSWORD="password"
REMOTE_DIR="/remoteserver/ftp/directory"

# Get the current date and time
DATE=$(date +"%Y-%m-%d_%H-%M-%S")

# Create a new directory for this backup
mkdir -p $BACKUP_DIR/$DATE

# Log file
LOG_FILE="/log/file/path/backup_log_$DATE.txt"

# Get the list of databases
databases=$(mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database)

# Loop through each database and back it up
for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Backing up database: $db" | tee -a $LOG_FILE
        mysqldump -u$MYSQL_USER -p$MYSQL_PASSWORD --databases $db > $BACKUP_DIR/$DATE/$db.sql
        tar -czf $BACKUP_DIR/$DATE/$db.tar.gz -C $BACKUP_DIR/$DATE $db.sql
        rm $BACKUP_DIR/$DATE/$db.sql
    fi
done

echo "Backup completed! Starting FTP upload..." | tee -a $LOG_FILE

# Connect to FTP server
ftp -inv $FTP_SERVER <<EOF
user $FTP_USER $FTP_PASSWORD

# Change to remote directory and create a new directory for this backup
cd $REMOTE_DIR
mkdir $DATE
cd $DATE

# Upload backup files
lcd $BACKUP_DIR/$DATE
mput *.tar.gz

# Exit FTP
bye
EOF

echo "FTP upload completed! Cleaning up local backups..." | tee -a $LOG_FILE

# Remove local backup files
rm -rf $BACKUP_DIR/$DATE

echo "Local backups removed! Cleaning up old backups on FTP..." | tee -a $LOG_FILE

# Cleanup old backups on FTP (older than 15 days)
find_date=$(date --date="15 days ago" +"%Y-%m-%d")
ftp -inv $FTP_SERVER <<EOF
user $FTP_USER $FTP_PASSWORD

cd $REMOTE_DIR
ls -d */ | while read dir; do
    if [[ "$dir" < "$find_date" ]]; then
        rm -rf "$dir"
    fi
done

# Exit FTP
bye
EOF

echo "Old backups on FTP cleaned up!" | tee -a $LOG_FILE

now how to add cron for this backup script

(crontab -l 2>/dev/null; echo "30 10 * * * /bin/bash /folder/pathof/script.sh > /root/back/script.log 2>&1") | crontab -

above mentioned example run cron at 10:30 am daily and /folder/pathof/script.sh is the folder where script is located run above command to set cronjob