Script auto backup mysql linux

Skenario terdapat 2 server.

Server database. IP address 192.168.1.100
Server backup. IP address 192.168.1.111

Alur backup

server database menjalankan script mysqldump secara otomatis dengan memanfaatkan crontab
mengkompres backup sql ke zip file
copy file backup ke server backup

Config di Server Database

Buat direktori /backup-db1

Buat user MySQL khusus untuk backup dengan privileges setara root. Contoh root01

CREATE USER 'root01'@'%' IDENTIFIED BY 'P@ssw0rd';
GRANT ALL PRIVILEGES ON *.* TO 'root01'@'%';
FLUSH PRIVILEGES;

CREATE USER 'root01'@'%' IDENTIFIED BY 'P@ssw0rd';

GRANT ALL PRIVILEGES ON *.* TO 'root01'@'%';

FLUSH PRIVILEGES;

Pastikan server database dan backup memiliki autentikasi passwordless.

Saran : Sebaiknya buat user linux khusus untuk keperluan backup. Jangan menggunakan root

ssh-keygen
ssh-copy-id root@192.168.1.111

1 2	ssh-keygen ssh-copy-id root@192.168.1.111

Buat file backup_db.sh dan isikan script berikut ini (Download dari Git ) :

backupfolder=/backup-db1
user=root01
password=P@ssw0rd
keep_day=14
tanggal=$(date +%Y-%m-%d_%H-%M-%S)
hari=$(date +%Y-%m-%d)
sqlfile=all-database-$tanggal.sql
zipfile=all-database-$tanggal.zip
cd $backupfolder
sudo mysqldump -u $user -p$password --all-databases > $sqlfile
if &#91; $? == 0 ]; then
echo 'Backup SQL berhasil pada '$hari >> log-db.txt
else
echo 'Backup SQL gagal pada '$hari >> log-db.txt
exit
fi
zip $zipfile $sqlfile
if &#91; $? == 0 ]; then
echo 'File backup terkompres pada '$hari >> log-db.txt
else
echo 'File gagal kompres pada '$hari >> log-db.txt
exit
fi
rm $sqlfile
echo $zipfile ' berhasil backup' >> log-db.txt
find $backupfolder -mtime +$keep_day -delete
scp log-db.txt root@192.168.1.111:/backup-db1
scp $zipfile root@192.168.1.111:/backup-db1

backupfolder=/backup-db1

user=root01

password=P@ssw0rd

keep_day=14

tanggal=$(date +%Y-%m-%d_%H-%M-%S)

hari=$(date +%Y-%m-%d)

sqlfile=all-database-$tanggal.sql

zipfile=all-database-$tanggal.zip

cd $backupfolder

sudo mysqldump -u $user -p$password --all-databases > $sqlfile

if [ $? == 0 ]; then

echo 'Backup SQL berhasil pada '$hari >> log-db.txt

else

echo 'Backup SQL gagal pada '$hari >> log-db.txt

exit

zip $zipfile $sqlfile

if [ $? == 0 ]; then

echo 'File backup terkompres pada '$hari >> log-db.txt

else

echo 'File gagal kompres pada '$hari >> log-db.txt

exit

rm $sqlfile

echo $zipfile ' berhasil backup' >> log-db.txt

find $backupfolder -mtime +$keep_day -delete

scp log-db.txt root@192.168.1.111:/backup-db1

scp $zipfile root@192.168.1.111:/backup-db1

Jalankan crontab -e yang mengeksekusi file shell setiap hari pada jam 02.03

3 2 * * * /root/backup_db.sh

1	3 2 * * * /root/backup_db.sh

Config di Server Backup

Buat direktori /backup-db1

Buat script sh yang melakukan penghapusan file backup jika lebih dari 31 hari, isinya sebagai berikut :

find /backup-db1 -mtime +31 -delete

1	find /backup-db1 -mtime +31 -delete

Jalankan crontab -e yang mengeksekusi file shell pada jam 03.03.

3 3 * * * /root/autodelete-backup-db1.sh

1	3 3 * * * /root/autodelete-backup-db1.sh

Sumber :

https://www.jotform.com/blog/how-to-backup-mysql-database/
https://www.backuphowto.info/how-backup-mysql-database-automatically-linux-users
https://web.archive.org/web/20120408181841/http://www.blogthority.com/87/how-to-backup-mysql-database-without-phpmyadmin//
https://www.cyberciti.biz/faq/ubuntu-linux-mysql-nas-ftp-backup-script/
https://sqlbak.com/blog/how-to-automate-mysql-database-backups-in-linux

Was this helpful?

0 / 0

#mysql