Backup MySQL MyISAM with minimum downtime

backup_mysql.sh

#!/bin/bash
set -euo pipefail
IFS=$'\n\t'

# Backup MySQL MyISAM with minimum downtime
# IMPORTANT! No for InnoDB
# Based on https://stackoverflow.com/a/39327926

# TODO: check available space

PREFIX="backup_"

DIR_WORK="/tmp/mysqlbackup"
DIR_DATA="/var/lib/mysql"

if [[ "$EUID" -ne 0 ]]; then
	echo "Error! You need to run this as root"
	exit 1
fi


if ! type "pigz" > /dev/null 2>&1; then
	echo "Error! You need install pigz"
	exit 1
fi


USER_MYSQL=$(mysql -u root -Ne "SELECT CURRENT_USER()")
if [ "$USER_MYSQL" != "root@localhost" ]; then
	echo "Error! You need to run this as MySQL root (Current user: $USER_MYSQL)"
	exit 1
fi


DIR_TMP="$DIR_WORK/tmp"
mkdir -p "$DIR_TMP"

FILENAME="$DIR_WORK/$PREFIX$(date '+%Y%m%d_%H%M').tar.gz"

echo "[$(date '+%Y-%m-%d %H:%M:%S')] Flush tables"
mysql -u root -Ne 'FLUSH TABLES'

echo
echo "[$(date '+%Y-%m-%d %H:%M:%S')] First copy"
rsync --archive --partial --info=progress2 $DIR_DATA/* "$DIR_TMP"

echo
echo "[$(date '+%Y-%m-%d %H:%M:%S')] Lock tables"
mysql -u root -Ne 'FLUSH TABLES WITH READ LOCK'

echo
echo "[$(date '+%Y-%m-%d %H:%M:%S')] Second copy"
rsync --archive --partial --info=progress2 $DIR_DATA/* "$DIR_TMP"

echo
echo "[$(date '+%Y-%m-%d %H:%M:%S')] Unlock tables"
mysql -u root -Ne 'UNLOCK TABLES'

echo
echo "[$(date '+%Y-%m-%d %H:%M:%S')] Compress"
tar --use-compress-program pigz -gzip --create --file "$FILENAME" "$DIR_TMP"