Senin, 17 Desember 2018

Schedule Backup MySQL Database di Ubuntu

Saya biasa melakukan prosedur backup MySQL secara otomatis dengan membuat beberapa langkah seperti ini.

Langkah pertama

Membuat folder /script

sudo mkdir /scripts

Kemudian membuat file mysql-backup.sh

sudo nano /scripts/mysql-backup.sh
Kemudian isi file mysql-backup.sh dengan script seperti ini :

#!/bin/bash
#----------------------------------------
# OPTIONS
#----------------------------------------
USER='root' # MySQL User
PASSWORD='webdev' # MySQL Password
DAYS_TO_KEEP=0 # 0 to keep forever
GZIP=1 # 1 = Compress
BACKUP_PATH='/backups/mysql'
#----------------------------------------
# Create the backup folder
if [ ! -d $BACKUP_PATH ]; then
mkdir -p $BACKUP_PATH
fi
# Get list of database names
databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "|" | grep -v Database`
for db in $databases; do
if [ $db == 'information_schema' ] || [ $db == 'performance_schema' ] || [ $db == 'mysql' ] || [ $db == 'sys' ]; then
echo "Skipping database: $db"
continue
fi

date=$(date -I)
if [ "$GZIP" -eq 0 ] ; then
echo "Backing up database: $db without compression"
mysqldump -u $USER -p$PASSWORD --databases $db > $BACKUP_PATH/$date-$db.sql
else
echo "Backing up database: $db with compression"
mysqldump -u $USER -p$PASSWORD --databases $db | gzip -c > $BACKUP_PATH/$date-$db.gz
fi
done
# Delete old backups
if [ "$DAYS_TO_KEEP" -gt 0 ] ; then
echo "Deleting backups older than $DAYS_TO_KEEP days"
find $BACKUP_PATH/* -mtime +$DAYS_TO_KEEP -exec rm {} \;
fi

Buat mysql-backup.sh menjadi executable

sudo chmod +x mysql-backup.sh 

Kemudian lakukan test script tersebut

sudo ./mysql-backup.sh

Langkah kedua

Buka setting crontab

sudo crontab -e

Kemudian tambahkan baris di crontab seperti ini

@daily sh /scripts/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

Sekian dan terima kasih.
Schedule Backup MySQL Database di Ubuntu

Posting Komentar