Backup MySQL /MariaDB Database using bash script

backupdb.sh

#! /bin/bash

# Buat user 'USERDB' dengan privileges :
# SELECT, RELOAD, SHOW DATABASES, LOCK TABLES, TRIGGER, SHOW VIEW
NOW=$(date +"%F_%H%M%S")
BACKUP_DIR="/data/backup/databases"
BACKUP_LOG="$BACKUP_DIR/backup.log"
MYSQL_USER="USERDB"
MYSQL_PSWD="YOUR_PASSWORD"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

mkdir -p $BACKUP_DIR
echo "------------------------------------------"
echo "Log backup database pada $NOW" >> $BACKUP_LOG

databases=`$MYSQL --user=$MYSQL_USER -p$MYSQL_PSWD -e "SHOW DATABASES;" | \
  grep -Ev "(Database|information_schema|performance_schema|sys)"`

for db in $databases; do
  ts=$(date +%s%N)
  # Proses backup utama disini
  mkdir -p "$BACKUP_DIR/$db"
  $MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PSWD --databases $db \
    --routines --skip-comments --single-transaction --quick --skip-compact  2>/dev/null | \
    grep -v "mysql: [Warning] Using a password on the command line interface can be insecure." | \
    gzip > "$BACKUP_DIR/$db/$db-$NOW.sql.gz"
  # Untuk menampilkan waktu backup
  tt=$((($(date +%s%N) - $ts)/1000000))
  echo "Backup database $db ($tt ms)" >> $BACKUP_LOG
  echo "Backup database $db ($tt ms)"
done

END=$(date +"%F_%H%M%S")
echo -e "Backup selesai pada $END\n" >> $BACKUP_LOG