Skip to content

Instantly share code, notes, and snippets.

@xluffy

xluffy/backup_mysql.sh

Created November 16, 2018 06:51
Show Gist options
  • Save xluffy/cf86a9f885eb809107b5d3483e8ce9a8 to your computer and use it in GitHub Desktop.
Save xluffy/cf86a9f885eb809107b5d3483e8ce9a8 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. Quang Kafka created this gist Nov 16, 2018.
    74 changes: 74 additions & 0 deletions backup_mysql.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,74 @@
    #!/bin/bash
    #
    # Author: @xluffy
    # Purpose: Backup MySQL to local (or remote)
    #

    # example ~/.my.cnf
    #
    #[client]
    #user = root
    #password = ahihidongoc

    set -e
    [ -n "${DEBUG}" ] && set -x

    ROOT="/backup/mysql"
    DATE=$(date +"%Y-%m-%d")
    MAX_DEPTH_DAY=7

    BKDIR="${ROOT}/${DATE}"
    OPTS="--single-transaction"

    _log() {
    printf "\x1B[2;32m"
    echo "[LOG]" "[$(date +'%Y-%m-%d %H:%M:%S')]:" "$*"
    printf "\x1B[0m"
    }

    _die() {
    printf "\x1B[2;31m"
    echo "[ERROR]" "[$(date +'%Y-%m-%d %H:%M:%S')]:" "$*" >&2
    exit 1
    printf "\x1B[0m"
    }

    _get_db() {
    DBS=( $(mysql -NBe "SHOW DATABASES" | grep -v "mysql\|test\|performance_schema\|information_schema") )
    }

    _backup() {
    if [ ! -d "${BKDIR}" ]; then
    mkdir -pv "${BKDIR}"
    fi

    for db in "${DBS[@]}"; do
    mysqldump "${OPTS}" "${db}" | gzip > "${BKDIR}/${db}.sql.gz"
    done
    }

    _cleanup() {
    find "${ROOT}/" -maxdepth 0 -mtime +"${MAX_DEPTH_DAY}" -exec rm -rf {} \;
    }

    _remote() {
    echo "upload to remote (gdrive, dropbox ...)"
    }

    main() {
    if [ ! "$USER" = "root" ]; then
    _die "You do not enough privileges. Please use root"
    fi

    if [ ! -f /root/.my.cnf ]; then
    _die "Let config user to access your MySQL"
    fi

    _get_db || _die "Can not get list databases name"
    _backup || _die "The backup is fail"
    _remote || _die "Can not upload backup to remote server"
    _cleanup || _die "Can not cleanup old backup"

    }

    main "$@"