[BASH] Backup script for mysql - fourslickz/notes GitHub Wiki

#!/bin/bash
host=ip-server
port=mysql-port
user=mysql-username
pass=mysql-password
dir=/usr/local/src/backup_sql
sql=/usr/bin/mysql
dump=/usr/bin/mysqldump


###########################################
# BACKUP FUNCTIONS
###########################################
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 $dbs -u$user -p$pass -h$host -P$port --set-gtid-purged=OFF -R -K --triggers > $dir/$tahun/$bulan/$tanggal/$dbs-$date.sql
  gzip -kf $dir/$tahun/$bulan/$tanggal/$dbs-$date.sql
}

###########################################
# MAIN
###########################################
for dbs in $($sql -h$host -u$user -p$pass -P$port -e 'show databases' | sed 1d);
do
  if [ $dbs != "mysql" && $dbs != "performance_schema" && $dbs != "information_schema" && $dbs != "sys" && $dbs != "defaultdb" ](/fourslickz/notes/wiki/-$dbs-!=-"mysql"-&&-$dbs-!=-"performance_schema"-&&-$dbs-!=-"information_schema"-&&-$dbs-!=-"sys"-&&-$dbs-!=-"defaultdb"-);
    then
      backup;
  fi;
done

###########################################
# REMOVE OLD BACKUP DATA
###########################################
rm `find /usr/local/src/backup_sql/ -mtime +365`