Build a Production-Ready Automated MySQL Backup with mydumper

Overview

This guide provides a production‑grade, fully automated MySQL backup solution based on mydumper for exporting data and myloader for restoring it. It covers installation on common Linux distributions, basic command usage, a Bash script that performs full and incremental backups, compression, optional OpenSSL AES‑256 encryption, retention cleanup, logging, and notification hooks.

Installation

Install mydumper from the package manager or compile from source.

sudo apt-get update
sudo apt-get install mydumper

sudo yum install epel-release
sudo yum install mydumper

Optional source compilation:

sudo apt-get install cmake build-essential libglib2.0-dev libmysqlclient-dev
git clone https://github.com/maxbube/mydumper.git
cd mydumper
cmake .
make
sudo make install

Basic mydumper usage

Full backup (multithreaded, compressed):

mydumper \
  --host=localhost \
  --user=root \
  --password=your_password \
  --outputdir=/path/to/backup \
  --compress \
  --threads=4 \
  --verbose=3

Backup a specific database or tables:

# Database
mydumper --database=your_database --outputdir=/path/to/backup --compress
# Tables
mydumper --database=your_database --tables=table1,table2 --outputdir=/path/to/backup

Production‑grade Bash automation script

The script defines configuration variables, logging and error‑handling functions, and separate routines for full and incremental backups. Key features:

Full backup on Sundays, incremental on other days.

Compression and optional OpenSSL AES‑256 encryption.

Retention cleanup (full > 30 days, incremental > 14 days).

Logging to /var/log/mysql_backup.log with timestamps.

Placeholder notification hook (extendable to email, Slack, DingTalk, etc.).

Binary‑log tracking for precise incremental recovery.

Core script (trimmed for readability):

#!/bin/bash

# Configuration
MYSQL_HOST="localhost"
MYSQL_PORT="3306"
MYSQL_USER="backup_user"
MYSQL_PASSWORD="backup_password"
BACKUP_DIR="/data/backups/mysql"
LOG_FILE="/var/log/mysql_backup.log"
RETENTION_FULL=30
RETENTION_INC=14
THREADS=4
COMPRESS="yes"
ENCRYPT="yes"

mkdir -p ${BACKUP_DIR}/{full,incremental}

log() { echo "$(date '+%F %T') - $1" >> ${LOG_FILE}; }
error_exit() { log "ERROR: $1"; exit 1; }
send_notification() { log "Notification: $2"; }

full_backup() {
  local BACKUP_NAME="full_$(date +%Y%m%d_%H%M%S)"
  local BACKUP_PATH="${BACKUP_DIR}/full/${BACKUP_NAME}"
  log "Starting full backup: ${BACKUP_NAME}"
  mydumper \
    --host=${MYSQL_HOST} \
    --port=${MYSQL_PORT} \
    --user=${MYSQL_USER} \
    --password=${MYSQL_PASSWORD} \
    --outputdir=${BACKUP_PATH} \
    --compress \
    --threads=${THREADS} \
    --chunk-filesize=256 \
    --verbose=3 \
    --success-on-1146 \
    --lock-all-tables \
    --regex '^(?!(mysql\.|test\.|sys\.))' >>${LOG_FILE} 2>&1
  if [ $? -eq 0 ]; then
    log "Full backup succeeded: ${BACKUP_NAME}"
    echo "${BACKUP_NAME}" > ${BACKUP_DIR}/last_full_backup.txt
    if [ "${ENCRYPT}" == "yes" ]; then
      tar czf - ${BACKUP_PATH} | openssl enc -aes-256-cbc -salt -out ${BACKUP_PATH}.tar.gz.enc
      rm -rf ${BACKUP_PATH}
    fi
  else
    error_exit "Full backup failed: ${BACKUP_NAME}"
  fi
}

incremental_backup() {
  local LAST_FULL_BACKUP=$(cat ${BACKUP_DIR}/last_full_backup.txt 2>/dev/null)
  if [ -z "${LAST_FULL_BACKUP}" ]; then
    log "No full backup found, performing full backup first"
    full_backup
    return
  fi
  local BACKUP_NAME="incremental_$(date +%Y%m%d_%H%M%S)"
  local BACKUP_PATH="${BACKUP_DIR}/incremental/${BACKUP_NAME}"
  log "Starting incremental backup: ${BACKUP_NAME} based on full ${LAST_FULL_BACKUP}"
  mydumper \
    --host=${MYSQL_HOST} \
    --port=${MYSQL_PORT} \
    --user=${MYSQL_USER} \
    --password=${MYSQL_PASSWORD} \
    --outputdir=${BACKUP_PATH} \
    --compress \
    --threads=${THREADS} \
    --chunk-filesize=256 \
    --verbose=3 \
    --success-on-1146 \
    --no-schemas \
    --no-data \
    --less-locking >>${LOG_FILE} 2>&1
  if [ $? -eq 0 ]; then
    log "Incremental backup succeeded: ${BACKUP_NAME}"
    local CURRENT_BINLOG_INFO=$(mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASSWORD} -e "SHOW MASTER STATUS\G")
    local CURRENT_BINLOG_FILE=$(echo "${CURRENT_BINLOG_INFO}" | grep "File:" | awk '{print $2}')
    local CURRENT_BINLOG_POS=$(echo "${CURRENT_BINLOG_INFO}" | grep "Position:" | awk '{print $2}')
    echo "${CURRENT_BINLOG_FILE} ${CURRENT_BINLOG_POS}" > ${BACKUP_DIR}/incremental/last_binlog.txt
    if [ "${ENCRYPT}" == "yes" ]; then
      tar czf - ${BACKUP_PATH} | openssl enc -aes-256-cbc -salt -out ${BACKUP_PATH}.tar.gz.enc
      rm -rf ${BACKUP_PATH}
    fi
  else
    error_exit "Incremental backup failed: ${BACKUP_NAME}"
  fi
}

cleanup_old_backups() {
  log "Cleaning old backups"
  find ${BACKUP_DIR}/full -type d -name "full_*" -mtime +${RETENTION_FULL} -exec rm -rf {} \; >>${LOG_FILE} 2>&1
  find ${BACKUP_DIR}/incremental -type d -name "incremental_*" -mtime +${RETENTION_INC} -exec rm -rf {} \; >>${LOG_FILE} 2>&1
  log "Old backup cleanup completed"
}

main() {
  log "========== MySQL Backup Started =========="
  command -v mydumper >/dev/null 2>&1 || error_exit "mydumper not installed"
  mysql -h${MYSQL_HOST} -P${MYSQL_PORT} -u${MYSQL_USER} -p${MYSQL_PASSWORD} -e "SELECT 1;" >/dev/null 2>&1 || error_exit "Cannot connect to MySQL"
  if [ $(date +%u) -eq 7 ]; then
    full_backup
  else
    incremental_backup
  fi
  cleanup_old_backups
  log "========== MySQL Backup Finished =========="
  send_notification "Success" "MySQL backup completed"
}

main

Cron scheduling

Run the script daily at 02:00 AM. The script itself decides whether to perform a full or incremental backup based on the day of the week.

0 2 * * * /bin/bash /path/to/mysql_backup.sh

Restoring backups with myloader

Full backup restore:

myloader --directory=/path/to/backup/full_backup \
  --user=root --password=your_password \
  --threads=4 --verbose=3

Incremental backup restore (apply in creation order or binlog order):

myloader --directory=/path/to/backup/incremental_backup \
  --user=root --password=your_password \
  --threads=4 --verbose=3

Production best practices

Backup user privileges : The MySQL user must have SELECT, RELOAD, LOCK TABLES, and REPLICATION CLIENT privileges.

Incremental consistency : Record the last binary‑log file and position; restore incremental sets in strict order.

Encryption & security : Set backup directory permissions to 700 and log file permissions to 600. Use OpenSSL AES‑256 encryption for archive files when required.

Retry & alerting : Implement retry logic for transient network or MySQL errors and integrate alerting (email, Slack, DingTalk, etc.).

Monitoring & auditing : Log backup size and duration; monitor disk usage to avoid overflow.

Retention policy : Keep full backups for 30 days and incremental backups for 7‑14 days. Consider replicating backups to off‑site storage or object storage for disaster recovery.

Test restores : Periodically restore backups to a test database to verify integrity.