Every reliable backup system is built around five fundamental components: the data source, the backup destination, the transport network, the backup engine, and the backup schedule. Users define what to protect, specify a storage target, transmit the data over a chosen network, run the backup engine (such as mysqldump or commercial tools), and automate the process with a predetermined schedule—usually outside peak business hours—to minimise manual intervention.

How MySQL Backups Work

MySQL backup essentially serialises the database into SQL statements that can later be replayed. Unlike Oracle’s block‑level RMAN backups, a MySQL dump is a logical backup: it exports CREATE DATABASE, USE, CREATE TABLE, and INSERT statements. Restoring a dump repeats those operations to rebuild the data from scratch.

Important MySQL Logs for Backup and Recovery

Binary Log

The binary log records every change to the database, similar to Oracle’s redo log. Because it contains all modifications, it is the foundation for point‑in‑time recovery. Enable it in my.cnf:

[mysqld]
log-bin = mysql-bin
log-bin-index = mysql-bin.index

Use mysqlbinlog to inspect, filter, and replay events. Events must be applied in the exact order they were generated.

Relay Log

Used in replication, the relay log is created on the replica (or intermediate master). It stores events received from the primary’s binary log, allowing the replica to apply the changes. Configure with:

[mysqld]
relay-log = relay-log
relay_log_index = relay-log.index

Slow Query Log

Captures queries that exceed a configurable execution time. This log is essential for performance tuning. Tools like mysqlsla can analyse it later. Example settings:

[mysqld]
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /var/log/mysql/slow.log

General Query Log

Records every client connection and statement. Because it grows quickly and impacts performance, its usually disabled in production. If needed:

[mysqld]
general_log = 1
log_output = FILE
general_log_file = /var/log/mysql/general.log

Error Log

Contains startup information and runtime errors. Indispensable for troubleshooting. Enable:

[mysqld]
log_warnings = 1
log_error = /var/log/mysql/error.log

Transaction Log (InnoDB Redo Log)

Buffers committed transactions and converts random I/O into sequential writes. Tune in my.cnf:

[mysqld]
innodb_log_buffer_size = 16M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3

Using mysqldump, mysql, and mysqlbinlog

mysqldump

This command‑line tool offers flexible options for backing up databases, tables, or an entire instance.

Single database:

mysqldump -u backupusr -p'Str0ngP@ss' -S /var/run/mysqld/mysqld.sock \
  mydb > /backups/dumps/mydb.sql

Multiple databases:

mysqldump -u backupusr -p'Str0ngP@ss' -S /var/run/mysqld/mysqld.sock \
  --databases sales inventory hr > /backups/dumps/multi_db.sql

Single table:

mysqldump -u backupusr -p'Str0ngP@ss' -S /var/run/mysqld/mysqld.sock \
  mydb employees > /backups/dumps/employees.sql

Multiple tables from the same database:

mysqldump -u backupusr -p'Str0ngP@ss' -S /var/run/mysqld/mysqld.sock \
  mydb employees departments > /backups/dumps/tables.sql

All databases:

mysqldump -u backupusr -p'Str0ngP@ss' -S /var/run/mysqld/mysqld.sock \
  --all-databases > /backups/dumps/all.sql

Frequently used options:

• --databases (or -B) adds CREATE DATABASE and USE statements to the output, essential for multi‑database restores.
• --flush-logs (-F) rotates the binary log before starting the dump, ensuring a clean recovery point.
• --master-data=2 writes the current binary log coordinates as a comment, useful for pinpointing the backup’s position.
• --lock-tables (-x) locks all tables during the backup (suitable for MyISAM).
• --no-data (-d) backs up schema only.
• --no-create-info (-t) backs up data without table definitions.
• --single-transaction starts a consistent snapshot for InnoDB tables without locking them, ideal for transactional workloads.

mysql client for recovery

Besides interactive logins, the mysql client executes SQL scripts for restoration.

Non‑interactive command:

mysql -u backupusr -p'Str0ngP@ss' -S /var/run/mysqld/mysqld.sock \
  -e "SHOW DATABASES;"

Restore a dump file:

mysql -u backupusr -p'Str0ngP@ss' -S /var/run/mysqld/mysqld.sock < /backups/dumps/mydb.sql

mysqlbinlog for point‑in‑time recovery

Apply binary log events selectively.

By position:

mysqlbinlog --database=sales --start-position=456 --stop-position=7890 \
  mysql-bin.000042 | mysql -u backupusr -p'Str0ngP@ss' -S /var/run/mysqld/mysqld.sock

By time range:

mysqlbinlog --database=sales \
  --start-datetime="2025-01-15 02:30:00" \
  --stop-datetime="2025-01-15 04:30:00" \
  mysql-bin.000042 | mysql -u backupusr -p'Str0ngP@ss' -S /var/run/mysqld/mysqld.sock

A safer approach is to extract the required events into a file, review it, and then feed it to the server.

Optimised Backup Commands by Storage Engine

MyISAM: lock all tables to ensure consistency.

mysqldump -u backupusr -p'Str0ngP@ss' --all-databases --flush-logs \
  --lock-tables --master-data=2 | gzip > /backups/dumps/full_myisam.sql.gz

InnoDB: use a consistent snapshot with --single-transaction instead of locking.

mysqldump -u backupusr -p'Str0ngP@ss' --all-databases --flush-logs \
  --single-transaction --master-data=2 | gzip > /backups/dumps/full_innodb.sql.gz

Automated Per‑Database Backup Script

When many databases exist, automate the process by fetching the list from the server and looping.

Script example (backup each database individually):

#!/bin/bash

MYSQL_BIN="/usr/bin/mysql"
DUMP_BIN="/usr/bin/mysqldump"
USER="backupusr"
PASS="Str0ngP@ss"
SOCK="/var/run/mysqld/mysqld.sock"
DEST="/backups/dumps"

EXCLUDE_DBS="Database|information_schema|mysql|performance_schema|sys"

DBS=$("$MYSQL_BIN" -u"$USER" -p"$PASS" -S "$SOCK" -N -B \
      -e "SHOW DATABASES" | egrep -v "$EXCLUDE_DBS")

for db in $DBS; do
  "$DUMP_BIN" -u"$USER" -p"$PASS" -S "$SOCK" \
    --databases --flush-logs --master-data=2 --single-transaction --events --routines \
    "$db" | gzip > "${DEST}/${db}_$(date +%Y%m%d).sql.gz"
done

Per‑Table Backup with Nested Loops

For even finer granularity, dump each table into its own file inside a database‑specific folder.

#!/bin/bash

MYSQL_BIN="/usr/bin/mysql"
DUMP_BIN="/usr/bin/mysqldump"
USER="backupusr"
PASS="Str0ngP@ss"
SOCK="/var/run/mysqld/mysqld.sock"
BASE_DEST="/backups/dumps"

EXCLUDE_DBS="Database|information_schema|mysql|performance_schema|sys"

DBS=$("$MYSQL_BIN" -u"$USER" -p"$PASS" -S "$SOCK" -N -B \
      -e "SHOW DATABASES" | egrep -v "$EXCLUDE_DBS")

for db in $DBS; do
  TABLES=$("$MYSQL_BIN" -u"$USER" -p"$PASS" -S "$SOCK" -N -B \
           -e "SHOW TABLES FROM \`$db\`")
  if [ -n "$TABLES" ]; then
    mkdir -p "${BASE_DEST}/${db}"
    for tbl in $TABLES; do
      "$DUMP_BIN" -u"$USER" -p"$PASS" -S "$SOCK" \
        --flush-logs --master-data=2 --single-transaction \
        "$db" "$tbl" | gzip > "${BASE_DEST}/${db}/${tbl}_$(date +%Y%m%d).sql.gz"
    done
  fi
done