Otomatisasi Backup Database MySQL

Backup database menggunakan curl dengan menggunakan bash script dan memanfaatkan crontab
buat file db-backup.sh dan berikan izin eksekusi:

vi /root/db-backup.sh

chmod +x /root/db-backup.sh

copy paste script berikut, kemudian edit di beberapa bagian sesuaikan dengan kebutuhan:

#!/bin/sh
# Set up all the variables

# Database name
databases="db1 db2"

# Current date
date=$(date +"%Y-%m-%d")

# Mysql user, password
user=username
pass=password

# FTP user, password, and host (you can specify the port also eg. ftp.example.com:2002)
ftpUser=username
ftpPass=password
ftpHost=ftp.example.com
ftpFolder="backup/"

# Local backup folder
bPath="/var/backups/databases"

# Create the backup dir if doesn't exist
if [ ! -d $bPath ]; then
    mkdir -p $bPath
fi

# Delete backup file older than 3 days (local backup)
find $bPath/*.sql.gz -mtime +3 -exec rm {} ;

# Backup the database
for db in $databases; do
    # Database backup name
    file=$db-$date.sql.gz

    # Do the mysql database backup (mysqldump)
    echo "Starting to dump the $db database as $file"
    mysqldump --user=$user --password=$pass $db | gzip -9 > $bPath/$file

    # Upload it via curl
    echo "Starting to upload the $file to FTP server"
    curl --ftp-create-dirs -T $bPath/$file -u $ftpUser:$ftpPass ftp://$ftpHost/$ftpFolder
done

# Clear the cache (not work on OpenVZ)
free && sync && echo 3 > /proc/sys/vm/drop_caches && echo "" && free

buat cron job untuk mengeksekusi script di atas, cron berjalan pada pukul 2 dini hari setiap harinya

crontab -e

tulis crontab dan simpan

00 02 * * * /root/db-backup.sh

di ujicoba terlebih dahulu ketika ingin di implementasikan

source: https://www.danpros.com/2017/06/auto-backup-mysql-database-to-ftp-server-using-curl