Simple Script Auto Backup Database MySQL dengan Bash

[lwptoc]

Mengapa Auto Backup Diperlukan?

Sebagai layanan data, MySQL tidak lepas dari berbagai macam risiko. Risiko adalah potensi terjadinya insiden. Potensi terjadinya hal-hal yang tidak diinginkan, dan tidak diketahui kapan persis terjadinya. Kadang, sebagian risiko tersebut akan memiliki dampak (impact) yang besar terhadap database. Entah corrupt, rusak, atau bahkan terhapus.  Belakangan ini, ransomware mulai menjamah database MySQL. Dilaporkan puluhan ribu kasus database di MySQL Server mengalami perusakan oleh ransomware. Ransomware tersebut menyatakan bahwa mereka telah mengenkripsi database tersebut, dan minta tebusan sejumlah uang untuk mendapatkan kunci dekripnya.

Pada bagian ini akan dibahas mengenai pembuatan skrip yang sederhana untuk dapat dioperasikan auto backup database dan diberikan schedule eksekusi pada jam-jam tertentu. Misal dieksekusi pada jam 01 dini hari, maka jika terjadi kerusakan database pada pukul 05, kita masih memiliki gap data 4 jam. Database bisa saja pulih jika kita juga mengaktifkan binary logging. Auto backup yang akan dibuat dan dieksekusi ini, dibuat dengan menggunakan bahasa Bash Script, shell standard untuk Linux.

Mengenal MySQL Dump

Database yang ada di dalam MySQL dapat disimpan dalam file text berformat SQL dengan menggunakan perintah mysqldump. Sintaks mysqldump dasarnya adalah sebagai berikut :

Backup database tertentu :

mysqldump namadatabase -u user -ppassword 〉 namadatabase.sql

Backup database dan tabel tertentu :

mysqldump namadatabase namatable -u user -ppassword 〉 namatable.sql

Backup All Database

mysqldump --all-database -u user -ppassword 〉 namafile.sql

Terkadang, ada kebutuhan khusus, membackup database lengkap dengan Stored Procedure, Trigger dan Functions

mysqldump namadatabase -u user -ppassword -R -K --triggers 〉 namadatabase.sql

Terkadang, diharapkan dapat membackup database yang ada pada remote server, dan tidak pada port standar MySQL. Maka sintaksnya adalah :

mysqldump namadatabase -u user -ppassword -h 192.168.10.1 -P 4406 -R -K --triggers 〉 namadatabase.sql

Mengenal Bash Script

Bash merupakan sebuah Unix Shell. Shell adalah antar muka antara mesin dengan manusia. Wujudnya adalah text mode. Manusia dapat memberikan perintah dengan mengetikkan command-command bash. Nah, sejarah tentang Bash dapat dilihat di https://en.wikipedia.org/wiki/Bash_(Unix_shell). Kumpulan perintah Bash Shell ini dapat digabungkan dan diurutkan dalam sebuah program script, yang selanjutnya dinamakan dengan Bash Script. Bash Script inilah yang banyak digunakan untuk membuat otomasi program, termasuk auto backup MySQL.

Sebagai sebuah bahasa pemrograman script, bash shell memiliki persyaratan sebagai sebuah bahasa pemrograman. Antara lain :

• pengambilan variable
• looping
• pengambilan keputusan
• dan lain-lainnya.

Untuk mempelajarinya, sobat kampus dapat lebih detail di sini.

Membuat Script Auto Backup MySQL

Tim eCampuz memiliki sebuah simple script auto backup yang biasa digunakan untuk melakukan backup MySQL secara otomatis. Script tersebut berisi perintah-perintah :

1. Membaca variable user, password, port dan host
2. Melisting nama database
3. Membuat direktori sesuai dengan tahun, bulan dan tanggal
4. Membackup database dengan melibatkan nama waktu backup

Pada script ini, diberikan pilihan. Apakah kita akan membackup seluruh database satu persatu, atau menentukan database tertentu saja yang akan dibackup.

#!/bin/bash
#script auto backup database by ecampuz
#bagian-1
user=userbackup
pass=pass123
host=localhost
dir=/home/ecampuz/backup_sql

#bagian-2
backup(){
  date=$(date +%Y%m%d-%H%M)
  tanggal=$(date +%d)
  bulan=$(date +%m)
  tahun=$(date +%Y)
  if [ ! -d "$dir/$tahun/$bulan/$tanggal/" ];
    then mkdir --parents $dir/$tahun/$bulan/$tanggal;
  fi
  dump=/usr/bin/mysqldump
  $dump $dbs -u$user -p$pass -h$host -R -K --triggers 〉 $dir/$tahun/$bulan/$tanggal/$dbs-$date.sql
  gzip -f $dir/$tahun/$bulan/$tanggal/$dbs-$date.sql
}

#bagian-3
#######jika seluruh db dalam grant tersebut ingin dibackup
sql=/usr/bin/mysql

for dbs in $($sql -u$user -p$pass -e 'show databases' | sed 1d); 
do if [ "$dbs" != "mysql" ] || [ "$dbs" != "information_schema" ] || [ "$dbs" != "performance_schema" ]; 
then backup; 
fi; 
done;

#bagian-4
#######jika database ditentukan yang akan dibackup

#database="db1 db2 db3"
#for dbs in $database
#do
#backup
#done
exit 0

#bagian-5
rm `find /home/ecampuz/backup_sql/ -mtime +365`

Pada bahasa bash ataupun shell yang lain, tanda # berarti tidak dijalankan, alias dianggap sebagai komentar saja. Pada script bagian-1, merupakan baris definisi variable. Bagian user dan password , adalah user password database yang memiliki kemampuan untuk melakukan dump/backup. Jika usernya adalah root, maka pastikan script ini terlindungi dari terbaca oleh pihak selain root.

Pada bagian-2, dibuatlah fungsi backup(), fungsi ini dibagi lagi menjadi dua :

• membuat direktori, jika direktori tersebut belum terbuat.
• melakukan perintah backup, berdasar dari perintah yang memanggil fungsi backup tersebut.

Struktur direktori auto backup yang direncanakan adalah sebagai berikut :

└── 2020
│   ├── 01
│   │   ├── 01
│   │   ├── 02
│   │   │
│   ...........
└── 2021
    ├── 01
    │   ├── 01
    │   ├── 02
    │   ├── 03
    │   ├── 04
    │   ├── 05
    │   ├── 06
    │   ├── 07
            ├── db1-20210107-0101.sql.gz
            ├── db2-20210107-0101.sql.gz
            ├── db3-20210107-0101.sql.gz
            ├── db4-20210107-0101.sql.gz

Pada pangkal direktori akan terisi nama direktori tahun. Selanjutnya dalam direktori tahun akan ada subdirektori bulan. Di dalam subdirektori bulan, akan ada subdirektori tanggal. Di dalam tanggal terdapat nama database yang dibackup. Bagian 2 ini merupakan fungsi utama dari backup. Semua dieksekusi pada fungsi backup().

Bagian-3 adalah bagian melisting seluruh database dengan show databases, dan sekaligus memanggil fungsi backup. Sedangkan bagian-4 adalah melisting database hanya yang ditentukan oleh user saja.

Script tersebut sebaiknya diberikan permission executable. Misal script itu diletakkan di /root dan bernama sqlbackup.sh. Maka berikan atribut executable untuk script tersebut.

chmod +x /root/sqlbackup.sh

Silakan diuji dengan cara dijalankan.. Perintahnya bisa menggunakan :

/root/sqlbackup.sh

Dengan dijalankannya script tersebut, harusnya direktori tahun, bulan dan tanggal akan segera terbentuk. Pastikan parameter config pada bagian-1, bagian dir, sudah sesuai dengan struktur direktori yang sobat kampus miliki.

Lho kok di bagian-5 ada perintah rm? Yes. Bagian-5 memiliki fungsi penting agar menjaga server tidak lekas penuh. File yang ada di find /home/ecampuz/backup_sql/, akan dihapus yang berusia lebih dari 365 hari.

Membuat Cronjob Auto Backup Database

Setelah dipastikan bahwa script tersebut berjalan dengan baik, saatnya sobat kampus membuat penjadwalan otomatis agar auto backup berjalan pada jam-jam yang dikehendaki. Kita bisa menuliskan penjadwalan tersebut pada /etc/crontab, bisa juga pada perintah crontab -e. Eh, sudah kenal crontab? Crontab adalah model penjadwalan untuk sistem operasi Unix dan Linux. Mari kita bahas dulu crontab…

# |---------------- menit ke  (0 - 59)
# |  .------------- jam ke    (0 - 23)
# |  |  .---------- tanggal   (1 - 31)
# |  |  |  .------- bulan     (1 - 12)
# |  |  |  |  .---- hari ke, dalam pekan (0 - 6) (Minggu=0 or 7)
# |  |  |  |  |
# *  *  *  *  *  command.sh

Terdapat 5 buah bintang:

• Bintang pertama menunjukkan menit ke
• Bintang kedua menunjukkan jam ke
• Bintang ketiga menunjukkan tanggal
• Bintang keempat menunjukkan bulan ke, boleh diisi dengan jan, feb, mar, apr
• Bintang kelima menunjukkan hari ke (dalam pekan), boleh juga diisi sun, mon, tue,wed,thu,fri,sat

Contoh, saya ingin menjalankan auto backup tiap jam 01.01 malam, maka saya akan mengisikan sebagai berikut

1 1 * * * /home/ecampuz/sqlbackup.sh

Apa bedanya crontab -e dengan /etc/crontab? Diharapkan semua proses crontab itu atas nama user, jadi menggunakan crontab -e. Penulisan di /etc/crontab harus juga disertakan dengan user yang mengeksekusinya.  Pada crontab -e, semua user dapat menuliskan penjadwalan sendiri-sendiri.

Kita dapat melakukan uji crontab dengan auto backup, dengan membuat jam backup secara dummy yang mendekati pada jam sekarang. Untuk sistem operasi Linux CentOS, dapat juga dimonitor lognya pada file text /var/log/cron. Sedangkan pada Ubuntu / Debian, dapat dilihat pada /var/log/message atau /var/log/syslog.

Ngomong-ngomong crontab, bisa nggak si kita membuat crontab yang jalan 5 menitan sekali? Bisa saja.. Dengan cara seperti berikut :

*/5 * * * * /home/ecampuz/testscript.sh

Nah, setelah kita pahami tentang auto backup database, di catatan selanjutnya sobat kampus akan belajar cara mengkopi database ke lain server secara berkala juga. Untuk script auto backup ini, silakan dicoba lebih dulu, selamat scripting, semoga sukses dan bermanfaat.