Categories
Tutorial, Manual, Tips dan Trik

Auto-Backup Skema MySQL (Ubuntu)

Keamanan dalam basis data tidak hanya dalam hal SQL Injection, brute force dan kejahatan lain dalam konteks basis data. Backup merupakan keamanan dalam sisi jaminan ketersediaan data, entah karena ada salah query, kegagalan perangkat dan karena alasan lainnya.

Daftar Isi

Auto Backup Skema MySQL

Backup dengan mysqldump merupakan salah satu metode melakukan backup basis data MySQL dari beberapa model backup yang tersedia. Admin dapat menggunakan campuran metode-metode backup tersebut. Salah satu alasan melakukan backup dengan mysqldump adalah menyimpan data secara historis.

— Auto-Backup Skema MySQL (Ubuntu)
https://bptsi.unisayogya.ac.id/auto-backup-skema-mysql-ubuntu/ 2018-01-06 09:39:28

Persiapan

  1. Masuk ke server sebagai user
    ssh penggunaserver@123.123.123.123
  2. Masuk sebagai root
    su
  3. Pasang zip
    apt-get install zip
  4. Ketik (disesuaikan)
    _USERBACKUPSERVER="penggunaserver"
    _GROUPBACKUPSERVER="gruppenggunaserver"
  5. Buat folder backup dan ssh
    mkdir /home/backup
    chown -R $_USERBACKUPSERVER:$_GROUPBACKUPSERVER /home/backup
    mkdir /home/$_USERBACKUPSERVER/.ssh
    chown -R $_USERBACKUPSERVER:$_GROUPBACKUPSERVER /home/$_USERBACKUPSERVER/.ssh

Buat berkas konfigurasi

#!/bin/bash
#path: /root/conf.sh

_USERBACKUPSERVER="penggunaserver"
_GROUPBACKUPSERVER="gruppenggunaserver"
_USER="userdatabase"
_PASSWORD="passworddatabase"
_HOST="localhost"
_FOLDERBACKUP=$(mysql -u $_USER -p$_PASSWORD  -sN -e "select @@secure_file_priv")
if [[ "$_FOLDERBACKUP" == "" ]]; then $_FOLDERBACKUP="/home/backup/"; fi;
_FOLDERBACKUP=$_FOLDERBACKUP"autobackup"
_FOLDERBACKUPFILE="/home/backup/"

Buat skrip untuk melakukan backup berdasarkan daftar nama basisdata yang ingin di-backup

#!/bin/bash
#path: /root/mysql_backup.sh

#include
source /root/conf.sh

### Script auto-backup

_DO_BACKUP() {
_BACKUPLOG=$_FOLDERBACKUP"/err_"$_DATABASE".log"
_FILEBACKUP=$_DATABASE"_"`date +\%Y-\%m-\%d_\%H-\%M`
#prepare folder
mkdir $_FOLDERBACKUP
mkdir $_FOLDERBACKUP$_FILEBACKUP
mkdir $_FOLDERBACKUPFILE$_DATABASE
chown -R mysql:mysql $_FOLDERBACKUP$_FILEBACKUP
chmod 722 $_FOLDERBACKUP$_FILEBACKUP
#dump database per table
mysqldump --host=$_HOST --user=$_USER --password=$_PASSWORD --lock-tables=false --lock-all-tables=false --log-error=$_BACKUPLOG \
 --force=TRUE --tab=$_FOLDERBACKUP$_FILEBACKUP $_DATABASE
#zip to a file
zip -q -r $_FOLDERBACKUPFILE$_DATABASE"/"$_FILEBACKUP $_FOLDERBACKUP$_FILEBACKUP
#copy error log
cp -u $_BACKUPLOG $_FOLDERBACKUPFILE
#remove backup folder
rm -r $_FOLDERBACKUP$_FILEBACKUP
#change the owner of backup (not root)
chown -R $_USERBACKUPSERVER:$_GROUPBACKUPSERVER $_FOLDERBACKUPFILE$_DATABASE
}

#schema to backup
_SCHEMALIST=( $_SCHEMALISTGLOBAL )
#backup
for _CURSCHEMA in "${_SCHEMALIST[@]}"
do
  _DATABASE=$_CURSCHEMA
  _DO_BACKUP
done

Buat skrip untuk melakukan backup sesuai dengan pola nama basisdata yang ingin di-backup

#!/bin/bash
#path: /root/mysql_backup_pattern.sh

#include
source /root/conf.sh

### Script auto-backup

_DO_BACKUP() {
_DATABASELIST=$(mysql -u$_USER -p$_PASSWORD -sN information_schema -e "SELECT DISTINCT(TABLE_SCHEMA) FROM tables WHERE TABLE_SCHEMA LIKE '$_PATTERNGLOBAL%'")

for _DATABASE in $_DATABASELIST
do
  _BACKUPLOG=$_FOLDERBACKUP"/err_"$_DATABASE".log"
  _FILEBACKUP=$_DATABASE"_"`date +\%Y-\%m-\%d_\%H-\%M`
  #prepare folder
  mkdir $_FOLDERBACKUP
  mkdir $_FOLDERBACKUP$_FILEBACKUP
  mkdir $_FOLDERBACKUPFILE$_DATABASE
  chown -R mysql:mysql $_FOLDERBACKUP$_FILEBACKUP
  chmod 722 $_FOLDERBACKUP$_FILEBACKUP
  #dump database per table
  mysqldump --host=$_HOST --user=$_USER --password=$_PASSWORD --lock-tables=false --lock-all-tables=false --log-error=$_BACKUPLOG \
   --force=TRUE --tab=$_FOLDERBACKUP$_FILEBACKUP $_DATABASE
  #zip to a file
  zip -q -r $_FOLDERBACKUPFILE$_DATABASE"/"$_FILEBACKUP $_FOLDERBACKUP$_FILEBACKUP
  #copy error log
  cp -u $_BACKUPLOG $_FOLDERBACKUPFILE
  #remove backup folder
  rm -r $_FOLDERBACKUP$_FILEBACKUP
  #change the owner of backup (not root)
  chown -R $_USERBACKUPSERVER:$_GROUPBACKUPSERVER $_FOLDERBACKUPFILE$_DATABASE
done
}

#backup
_DO_BACKUP

Buat skrip untuk membersihkan folder backup karena sudah disalin di tempat lain

#!/bin/bash
#path: /root/cleanup.sh

#include
source /root/conf.sh

find $_FOLDERBACKUPFILE -mmin +$((1*60*24)) -exec rm {} \; &> /dev/null
find $_FOLDERBACKUP -mmin +$((1*60*24)) -exec rm {} \; &> /dev/null

Jalankan perintah agar berkas dapat dieksekusi
chmod +x /root/*.sh

Jadwalkan dengan crontab -e

#yang disalin ke cloud dan external adalah backup tanggal 1, 10, 21 jam 2*:**
#backup data transaksi penting
00 08,12,21 * * *  _SCHEMALISTGLOBAL="db1 db2" && export _SCHEMALISTGLOBAL && /bin/bash /root/mysql_backup.sh
#backup data
30 20 * * * _SCHEMALISTGLOBAL="db3" && export _SCHEMALISTGLOBAL && /bin/bash /root/mysql_backup.sh
00 20 01 * * _PATTERNGLOBAL="wordpress" && export _PATTERNGLOBAL && /bin/bash /root/mysql_backup_pattern.sh
#hapus yang lebih dari 1 hari
50 05 * * * /bin/bash /root/cleanup.sh

Persiapan:

  1. Pada komputer backup, buat kunci RSA (jika belum ada)
    ssh-keygen -t rsa
  2. Ketik (disesuaikan)
    _USERBACKUPCLIENT="user"
    _GROUPBACKUPCLIENT="grupuser"
    _SERVER_USER="penggunaserver"
    _SERVER_IP="123.123.123.123"
    _SERVER_PORTSSH=22
  3. Salin kunci publik RSA ke server
    scp -P $_SERVER_PORTSSH $HOME/.ssh/id_rsa.pub $_SERVER_USER@$_SERVER_IP:/$_SERVER_USER/.ssh/authorized_keys
    atau
    ssh-copy-id "-p $_SERVER_PORTSSH $_SERVER_USER@$_SERVER_IP"
  4. Finalizing
    ssh-add
  5. Buat folder backup
    mkdir /home/backup
    mkdir /home/backup/internal
    mkdir /home/backup/fresh
    sudo -R chown $_USERBACKUPCLIENT:$_GROUPBACKUPCLIENT /home/backup

Buat berkas konfigurasi

#!/bin/bash
#path: /home/backup/conf.sh

#keep last backup (in days) on the cloud, 0=NEVER CLEAN
_KEEPCLOUDBACKUP=0

#komputer server
#folder backup di server
_FOLDERBACKUPSERVER="/home/backup/"
#remote
_SERVER_USER="penggunaserver"
_SERVER_IP="123.123.123.123"
_SERVER_PORTSSH=22

#komputer backup
#user
_USERBACKUPCLIENT="user"
_GROUPBACKUPCLIENT="grupuser"
#dev eksternal
_DEV_EXT="/dev/sdb1"
#fresh backup di internal disk
_FOLDERBACKUP_FRESH="/home/backup/fresh/"
#folder backup di external disk
_FOLDERBACKUP_EXT="/media/$_USERBACKUPCLIENT/287660E67660B5EA/backupDB/"
#folder backup di internal disk
_FOLDERBACKUP_INT="/home/backup/internal/"
#folder backup di internal disk yang terhubung ke Cloud Storage (misal: Dropbox, Mega, GoogleDrive, dsb) atau ke NAS
_FOLDERCLOUD="/home/$_USERBACKUPCLIENT/nas/"
_FOLDERBACKUP_CLOUD=$_FOLDERCLOUD"backup/"

Buat skrip untuk menyalin backup ke 3 lapisan backup dari server basis data

#!/bin/bash
#path: /home/backup/salin_backup.sh

#include
source /home/backup/conf.sh

#salin backup ke 3 lapisan, ambil dari server diletakkan ke folder fresh, kemudian disalin ke 3 lapisan (internal disk, eksternal disk [tanggal 1, 11, 21 jam 2*:**] dan Cloud Storage [tanggal 1, 11, 21 jam 2*:**])
scp -P $_SERVER_PORTSSH -r $_SERVER_USER@$_SERVER_IP:$_FOLDERBACKUPSERVER* $_FOLDERBACKUP_FRESH &> /dev/null && cp -r -u $_FOLDERBACKUP_FRESH* $_FOLDERBACKUP_INT &> /dev/null && cp -r -u $_FOLDERBACKUP_FRESH*1_2?* $_FOLDERBACKUP_EXT &> /dev/null && cp -r -u $_FOLDERBACKUP_FRESH*1_2?* $_FOLDERBACKUP_CLOUD &> /dev/null

Buat skrip untuk membersihkan folder backup

#!/bin/bash
#path: nano /home/backup/perawatan_backup.sh

#include
source /home/backup/conf.sh

#hapus yang tidak diperlukan (find rm{}\;), buat list 5 backup terbaru (ls -goAt), diletakkan diluar folder backup karena folder tersebut di-exclude dari klien dan buat laporan ukuran disk backup (df)
 find $_FOLDERBACKUP_INT -mmin +$((30*60*24)) -exec rm {} \; &> /dev/null && find $_FOLDERBACKUP_FRESH -mmin +$((1*60*24)) -exec rm {} \; &> /dev/null && if [[ $_KEEPCLOUDBACKUP -ne 0 ]]; then find $_FOLDERBACKUP_CLOUD -mmin +$(($_KEEPCLOUDBACKUP*60*24)) -exec rm {} \; &> /dev/null; fi; && ls -goAt $_FOLDERBACKUP_CLOUD | grep -m 5 'gz' > $_FOLDERCLOUD"terbaruserverdb.list" && df --output='target,pcent,avail' -h $_DEV_EXT /home > $_FOLDERCLOUD"backupdisk.list"

Jalankan perintah agar berkas dapat dieksekusi
chmod +x /home/backup/*.sh

Jadwalkan dengan crontab -e

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/bin
00 03 * * * /home/backup/salin_backup.sh
00 07 * * * /home/backup/perawatan_backup.sh

Pada waktu install aplikasi cloud storage, harap mengecualikan folder backup, karena nanti akan sangat sering ganti. Jika ingin ambil back up-nya bisa menggunakan versi web.

Demikian, semoga bermanfaat. [admin]

Sumber Bacaan:

https://pdsi.unisayogya.ac.id/feed/rss2/

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.