Backup, Recovery, Replication, and Partitioning

A backup you haven't restored is a hope#

Everything in this lesson comes back to one rule: test your restores. Plenty of teams discover their backups are empty or corrupt during the outage.

Logical backups with mysqldump#

mysqldump -u root -p --single-transaction --routines --triggers --events \
  shop > shop_backup.sql

--single-transaction takes a consistent snapshot of InnoDB tables without locking them. Without it, the dump of a busy database can be inconsistent across tables. --routines --triggers --events include the programmable objects, which are easy to forget.

Restore:

mysql -u root -p shop < shop_backup.sql

Logical dumps are portable and readable, but slow to restore on large databases, because every row is re-inserted and indexes rebuilt. Above a few hundred gigabytes, look at physical backup tools like Percona XtraBackup or MySQL Enterprise Backup.

CSV export and import#

SELECT * FROM products
INTO OUTFILE '/var/lib/mysql-files/products.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

LOAD DATA INFILE '/var/lib/mysql-files/products.csv'
INTO TABLE products_copy
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The server writes to a directory restricted by secure_file_priv. LOAD DATA is by far the fastest way to bulk-load rows.

Point-in-time recovery#

A nightly dump only restores you to last night. The binary log records every change, so you can replay it to a chosen moment:

full backup (02:00) ──▶ restore ──▶ replay binlog up to 14:31:59
                                    (just before the bad DELETE)

mysqlbinlog --start-datetime="2025-03-01 02:00:00" \
            --stop-datetime="2025-03-01 14:31:59" binlog.000042 | mysql -u root -p

This only works if binary logging was on and you kept the logs.

Replication#

 Primary ──binlog──▶ Replica 1 (reads)
    │                Replica 2 (reports)
  writes

A replica copies the primary's binary log and applies it. Use replicas to spread read traffic, run heavy reports away from production, and as a failover target. Replication is asynchronous by default, so a replica can lag. Reading right after a write from a replica may return stale data, so design for that. A replica is not a backup: a bad DROP TABLE replicates instantly.

Partitioning#

CREATE TABLE logs (
  id BIGINT NOT NULL AUTO_INCREMENT,
  created_at DATETIME NOT NULL,
  message TEXT,
  PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE (YEAR(created_at)) (
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION pmax  VALUES LESS THAN MAXVALUE
);

Partitioning splits one logical table into physical pieces. Queries that filter on the partition key skip irrelevant partitions (pruning), and you can drop an old partition instantly: ALTER TABLE logs DROP PARTITION p2024;. The partition key must be part of every unique key, which is a real design constraint. It isn't a general speed-up; if queries don't filter on the key, it can be slower.

Common mistakes#

• Never testing a restore.
• Treating a replica as a backup.
• Dumping without --single-transaction.
• Partitioning a table because it's "big" without a partition-friendly access pattern.

Practice#

Take a dump of shop, create a new database shop_restore, and load the dump into it. Compare row counts across all tables. Then enable binary logging if it isn't, make a change, and work out the commands to recover to a time just before it.