Backup & restore

Disks fail, people run DELETE without WHERE, deployments go wrong and ransomware happens. A backup you can restore is the only cure. This lesson covers logical backups with mysqldump, restoring them, and point-in-time recovery with binary logs, plus the habits that make backups trustworthy.

Types of backup#

A solid strategy combines a regular full backup (daily or weekly) with continuous binary logs in between.

mysqldump basics#

mysqldump is a command-line program, so run it from your shell, not inside the mysql> prompt:

# One database, consistent, including stored programs
mysqldump -u backup -p \
  --single-transaction --routines --triggers --events \
  shop > shop_2026-09-30.sql

# Several databases (adds CREATE DATABASE / USE statements)
mysqldump -u backup -p --single-transaction --databases shop blog > sites.sql

# Everything on the server
mysqldump -u backup -p --single-transaction --all-databases > full.sql

# Only some tables, or only the structure
mysqldump -u backup -p shop customers orders > some_tables.sql
mysqldump -u backup -p --no-data shop > schema_only.sql

Important options:

The file is plain SQL: CREATE TABLE statements followed by multi-row INSERTs. Open one and look. It's reassuring to see your data there.

Compress and timestamp

Dumps compress very well. Pipe straight into gzip or zstd:

mysqldump -u backup -p --single-transaction --routines --events shop \
  | gzip > /backups/shop_$(date +%F_%H%M).sql.gz

To run this unattended (from cron, for example), keep the credentials in a protected option file instead of on the command line:

[mysqldump]
user=backup
password=Backup-Pass-123!

The backup account needs only read-type privileges (RELOAD and REPLICATION CLIENT are needed for --source-data):

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'Backup-Pass-123!';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS, RELOAD, REPLICATION CLIENT
    ON *.* TO 'backup'@'localhost';

Restoring a dump#

A dump is just SQL, so restoring means running it with the mysql client:

# Database dump made without --databases: create the target first
mysql -u root -p -e "CREATE DATABASE shop_restored"
mysql -u root -p shop_restored < shop_2026-09-30.sql

# Compressed dump
gunzip < shop_2026-09-30.sql.gz | mysql -u root -p shop_restored

# Dump made with --databases or --all-databases contains its own CREATE DATABASE / USE
mysql -u root -p < sites.sql

From inside the mysql client, you can use source /path/to/shop_2026-09-30.sql; instead.

⚠️ Dumps contain DROP TABLE IF EXISTS before each CREATE TABLE. Restoring into a database replaces those tables. When in doubt, restore into a new database and copy over what you need.

Restoring a single table from a full dump: restore the whole dump into a scratch database, then copy the table back with INSERT INTO shop.orders SELECT * FROM scratch.orders .... (Or dump individual tables in the first place.)

Binary logs and point-in-time recovery#

The binary log records every change (inserts, updates, deletes, DDL) in order. It's on by default in MySQL 8:

SHOW VARIABLES LIKE 'log_bin';
SHOW BINARY LOGS;              -- list the log files
SHOW BINARY LOG STATUS;        -- current file and position (MySQL 8.2+; older: SHOW MASTER STATUS)

Point-in-time recovery (PITR) means restoring the last full backup, then replaying the binary logs from the moment of that backup to just before the disaster.

Scenario: a dump ran at 02:00 with --source-data=2. At 15:42 someone ran DROP TABLE orders.

1. Find where the dump started. The position is written near the top of the file:

   Terminal

   Copy

   grep -m1 "CHANGE REPLICATION SOURCE" shop_full.sql
   # -- CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='binlog.000002', SOURCE_LOG_POS=21199317;

2. Find the bad statement in the binary log (use --base64-output=DECODE-ROWS -v to read row events):

   Terminal

   Copy

   mysqlbinlog --start-position=21199317 /var/lib/mysql/binlog.000002 | grep -n -B4 "DROP TABLE"
   # # at 21199969
   # #261001 15:42:07 server id 1  end_log_pos 21200103 ... Query ...
   # DROP TABLE `orders` /* generated by server */

   The # at 21199969 line just before it is the position where the DROP begins.

3. Restore the dump, then replay everything from the dump's position up to (but not including) the DROP:

   Terminal

   Copy

   mysql -u root -p < shop_full.sql
   mysqlbinlog --start-position=21199317 --stop-position=21199969 \
     /var/lib/mysql/binlog.000002 | mysql -u root -p

If the logs span several files, list them all in order on the mysqlbinlog command line. You can also stop by time with --stop-datetime="2026-10-01 15:42:00". With GTIDs enabled, --exclude-gtids lets you skip exactly the bad transaction. The workflow above was tested on MySQL 8.4: after replay, the rows inserted and updated after the dump were all back.

Keep binary logs long enough. binlog_expire_logs_seconds (default 30 days) controls retention. Logs older than your oldest restorable full backup are useless for PITR, and copying binlogs off the server (for example with mysqlbinlog --read-from-remote-server --raw --stop-never) protects them if the disk dies.

Other tools worth knowing#

• MySQL Shell (mysqlsh) has util.dumpInstance(), util.dumpSchemas() and util.loadDump(). They're parallel, compressed and much faster than mysqldump on large databases. (The older mysqlpump tool was removed in MySQL 8.4.)
• Percona XtraBackup takes hot physical backups of InnoDB: the standard for databases of hundreds of GB and up.
• Cloud snapshots (EBS, managed MySQL services) are convenient, but make sure they're crash-consistent and test them too.
• SELECT ... INTO OUTFILE / LOAD DATA INFILE export and import CSV data quickly (restricted by secure_file_priv).

The backup rules that actually matter#

1. 3-2-1: three copies, on two kinds of storage, with one off-site (another region or cloud account).
2. Automate backups with cron or a systemd timer, and alert when one fails or is suspiciously small.
3. Test restores regularly. Restore to a scratch server, run row counts and a smoke test, and time it. Your restore time is what matters during an outage.
4. Protect backups: encrypt them and keep at least one copy where the production credentials can't delete it (ransomware deletes backups first).
5. Back up before risky changes: migrations, bulk updates, upgrades.
6. Document the procedure so a stressed colleague can follow it at 3 a.m.

What's next#

Next you'll store and query semi-structured data with MySQL's native JSON columns.