MySQL backup process

How I usually prepare the backup regime of my mysql/mariadb databases of all my newly provisioned servers is to do the following tasks:

Create a DBA user

  > create user 'dba'@'localhost';

Give the DBA user only the relevant access just for backing

  > GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO 'dba'@'localhost';
  > GRANT LOCK TABLES ON *.* TO 'dba'@'localhost';
  > FLUSH PRIVILEGES;

Setup a backup script!

#!/bin/sh -e

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/root/

date_now=`date +%Y_%m_%d_%H_%M`
dir_name="db_backup"
backup_name="all_db_backup_${date_now}.sql"
tar_name="db_backup_${date_now}.tar.gz"
file_name="${dir_name}/${tar_name}"

if [ -z "$dir_name" ]; then
    mkdir $dir_name
fi

log() {
    echo $1
    echo $1 >> ${HOME}/tmp/mybackup.log
}

do_cleanup(){
    rm -rf db_backup*
    log 'cleaning up....'
}

do_backup(){
    log 'Starting the backup' && \
    mysqldump -udba -pdba1123 --all-database > ${HOME}/${backup_name} && \
    log 'Data dump created'
    cd ${HOME}
    tar -czf ${tar_name} ${backup_name} && \
    log 'Created archive'
    rm ${HOME}/${backup_name}

    # log 'saving the backup archive in amazon S3' && \
    # /usr/bin/s3cmd put --acl-private ${HOME}/${tar_name}
s3://bucket/db-backups/${tar_name} && \
    # log 'data backup saved in amazon s3'
}

do_backup
# do_backup && do_cleanup

Cron away!

  $ crontab -e
  0 2 * * * sh /path/to/backup.sh