Lesson 19 intermediate

Backup, Restore, Import and Export

Protect Orbit's data: logical versus physical backups, consistent dumps with mysqldump --single-transaction, faster parallel dumps with MySQL Shell's dumpInstance and loadDump, physical backups with XtraBackup and the clone plugin, point-in-time recovery from binary logs with mysqlbinlog, testing restores, a least-privilege backup account and automation, plus CSV import and export with LOAD DATA and SELECT ... INTO OUTFILE.

Replication, RAID and cloud disks do not replace backups: they faithfully copy a DROP TABLE or a bad migration to every copy. A backup you have restored successfully is the only real protection. This lesson builds Orbit’s backup routine and covers moving data in and out as CSV.

Kinds of backup

KindWhat it copiesToolsGood for
LogicalSQL statements or data files that recreate the datamysqldump, MySQL Shell dump utilitiesSmall to medium databases, moving between versions, single tables
PhysicalThe InnoDB data files themselvesPercona XtraBackup, MySQL Enterprise Backup, clone plugin, disk snapshotsLarge databases, fast restores
Binary logsEvery change since the last backupBuilt-in binlog + mysqlbinlogPoint-in-time recovery

A sound strategy combines a full backup (daily, for example) with continuous binary logs, so you can restore to any moment, not just to last night.

mysqldump

mysqldump writes a SQL file that recreates tables and data:

$ mysqldump --single-transaction --routines --events \
    --set-gtid-purged=OFF orbit | gzip > orbit-$(date +%F).sql.gz
OptionWhy
--single-transactionDumps all InnoDB tables from one consistent snapshot, without locking them (lesson 14’s MVCC)
--routines, --eventsInclude stored procedures, functions and events (triggers are included by default)
--set-gtid-purged=OFFOmit GTID statements when restoring into a server that is not a replica of this one
--databases a b / --all-databasesSeveral databases, with CREATE DATABASE statements
--no-dataSchema only
--where="created_at >= '2026-10-01'"Only some rows
--source-data=2Record the binary log position as a comment, for point-in-time recovery

Without --single-transaction, mysqldump locks each table while dumping it, and tables dumped at different times may not match each other. Avoid running DDL during a dump: an ALTER TABLE breaks the consistent snapshot.

The connection details come from ~/.my.cnf or --login-path (lesson 3), so passwords never appear in cron entries.

Note: mysqlpump, a parallel variant of mysqldump, was deprecated in 8.0.34 and removed in MySQL 8.4. Use MySQL Shell’s dump utilities instead.

Restoring a dump

$ mysql -e "CREATE DATABASE orbit_restore"
$ gunzip < orbit-2026-11-11.sql.gz | mysql orbit_restore

A dump made without --databases contains no CREATE DATABASE or USE, so you can restore it under any name, which is perfect for testing. Restoring large SQL dumps is single-threaded and slow: count on roughly 10 to 50 GB per hour depending on hardware and indexes.

MySQL Shell dump utilities

MySQL Shell (mysqlsh) dumps and loads in parallel, compresses with zstd by default and can resume interrupted loads:

$ mysqlsh orbit_backup@localhost
util.dumpSchemas(["orbit"], "/backups/orbit-2026-11-11", {threads: 4})

And on the server you restore to:

util.loadDump("/backups/orbit-2026-11-11", {threads: 8})

The family also includes util.dumpInstance() for every database and util.dumpTables() for single tables, and the same functions are available in the shell’s Python mode. They also check compatibility when you move to a managed service (ocimds, compatibility options). For databases beyond a few gigabytes they are much faster than mysqldump on both ends.

Physical backups

For large databases, copying data files is faster than rebuilding them from SQL:

  • Percona XtraBackup (free, open source) copies InnoDB files while the server runs, plus the redo log written meanwhile, and supports incremental backups. Use a version matching your server line, such as XtraBackup 8.4 for MySQL 8.4.
  • MySQL Enterprise Backup does the same as part of the Enterprise edition.
  • The clone plugin copies a whole running instance to another server over the network, which is the quickest way to build a new replica: CLONE INSTANCE FROM 'donor'@'10.0.0.5':3306 IDENTIFIED BY '...';.
  • Filesystem or cloud snapshots (LVM, ZFS, EBS) are consistent if taken atomically while InnoDB runs; crash recovery fixes them up on start. Test this carefully with your storage.

Physical backups restore only to the same major version and platform, so keep a periodic logical dump too.

Point-in-time recovery with binary logs

The binary log records every change. It is enabled by default since MySQL 8.0:

SHOW BINARY LOGS;
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

Logs are kept for 30 days by default (binlog_expire_logs_seconds = 2592000); they must cover at least the time between full backups. Copy them off the server continuously, for example with mysqlbinlog --read-from-remote-server --raw --stop-never running on the backup host.

Now the disaster: at 14:37 someone runs DELETE FROM notes; without a WHERE. Recover like this:

  1. Restore last night’s full backup, made with --source-data=2, into a server. The dump header says where the binary logs continue, for example binlog.000042 position 1337.
  2. Find the bad statement in the binary logs:
$ mysqlbinlog --base64-output=DECODE-ROWS -vv --start-datetime="2026-11-11 14:30:00" binlog.000045 | less
  1. Replay everything from the backup position up to just before the bad statement:
$ mysqlbinlog --start-position=1337 binlog.000042 binlog.000043 binlog.000044 \
    | mysql
$ mysqlbinlog --stop-position=8812301 binlog.000045 | mysql

--start-datetime and --stop-datetime work too, but positions (or GTIDs with --exclude-gtids) are exact. Practise this on a test server before you need it.

A backup account

Backups should not run as root. For mysqldump --single-transaction with routines, events and triggers:

CREATE USER 'orbit_backup'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES, PROCESS, RELOAD,
      REPLICATION CLIENT ON *.* TO 'orbit_backup'@'localhost';

RELOAD and REPLICATION CLIENT are needed to record binary log coordinates; MySQL Shell’s dump additionally uses BACKUP_ADMIN for its consistent locking. Physical backup tools document their own privilege lists.

Automate and verify

A backup job worth having:

  • Runs automatically (cron, systemd timer or your orchestrator) and alerts when it fails or when it did not run.
  • Writes to a different machine or object storage, encrypted, with copies in another region.
  • Keeps a retention schedule, for example 7 daily, 4 weekly and 12 monthly.
  • Is restored regularly, ideally automatically into a scratch server, with a sanity query such as SELECT COUNT(*) FROM notes compared against production.
  • Has a written runbook with the exact restore commands and how long a restore takes.

Importing CSV with LOAD DATA

LOAD DATA is the fastest way to bring a file into a table, often 10 to 20 times faster than individual inserts. Given tags.csv:

name
travel
recipes
LOAD DATA LOCAL INFILE 'tags.csv'
INTO TABLE tags
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
  • LOCAL reads the file from the client machine. It must be allowed on both sides: local_infile = ON on the server and --local-infile=1 for the client.
  • Without LOCAL, the server reads the file, and only from the directory in secure_file_priv (SHOW VARIABLES LIKE 'secure_file_priv';).
  • The column list maps file columns to table columns; SET col = expression can transform values on the way in, for example SET created_at = STR_TO_DATE(@raw_date, '%d/%m/%Y').
  • mysqlimport is a command-line wrapper around the same statement.

Remove the imported tags if you are following along: DELETE FROM tags WHERE name IN ('travel', 'recipes');.

Exporting CSV

The server can write a query result to a file in secure_file_priv:

SELECT id, title, created_at
INTO OUTFILE '/var/lib/mysql-files/notes.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM notes;

The file is created on the database server, owned by the mysql user, and must not exist yet. To get a file on your own machine instead, use the client’s batch mode:

$ mysql -B -e "SELECT id, title, created_at FROM notes" orbit > notes.tsv

That produces tab-separated values with a header line, which spreadsheets open directly.

Quick reference

TaskCommand
Consistent logical dumpmysqldump --single-transaction --routines --events db > db.sql
Restore a dumpmysql db < db.sql
Parallel dump / loadmysqlsh -- util dump-schemas db --outputUrl=dir, util load-dump dir
Copy an instanceCLONE INSTANCE FROM 'u'@'host':3306 IDENTIFIED BY '...';
List binary logsSHOW BINARY LOGS;
Replay binary logsmysqlbinlog --start-position=N --stop-position=M binlog.0000xx, piped into mysql
Import CSVLOAD DATA LOCAL INFILE 'f.csv' INTO TABLE t FIELDS TERMINATED BY ',' IGNORE 1 LINES;
Export on the serverSELECT ... INTO OUTFILE '/var/lib/mysql-files/f.csv' ...
Export on the clientmysql -B -e "SELECT ..." db > f.tsv

Exercise

What does mysqldump –single-transaction give you for InnoDB tables?

  1. A consistent snapshot of all tables without blocking writes
  2. A dump that is restored inside one transaction
  3. Faster parallel dumping of each table
  4. Automatic compression of the output
Show answer

Answer: A. The dump runs inside one REPEATABLE READ transaction, so every table is read as of the same moment while the application keeps writing.

Exercise

An accidental DELETE ran at 14:37. You restored last night’s backup. What brings the data back to 14:36?

  1. Running the backup again with –where
  2. Restoring the same backup twice
  3. Replaying the binary logs from the backup’s position up to just before the DELETE with mysqlbinlog
  4. FLUSH LOGS on the restored server
Show answer

Answer: C. Point-in-time recovery means restoring a full backup and applying binary log events from its recorded position up to the moment before the mistake.

Exercise

LOAD DATA INFILE ‘/home/ana/tags.csv’ fails with error 1290 about –secure-file-priv. Why?

  1. CSV files must use semicolons
  2. Without LOCAL, the server reads the file and only from the secure_file_priv directory
  3. The tags table has a unique key
  4. LOAD DATA is disabled in MySQL 8.4
Show answer

Answer: B. The server-side form is limited to secure_file_priv. Move the file there, or use LOAD DATA LOCAL with local_infile enabled so the client sends the file.

Key takeaways

  • Backups only count once you have restored them; automate both the backup and a test restore.
  • Use mysqldump --single-transaction --routines --events or MySQL Shell’s parallel utilities for logical backups; mysqlpump is gone in 8.4.
  • Physical backups (XtraBackup, clone, snapshots) restore large databases much faster.
  • Binary logs plus a full backup allow point-in-time recovery; copy them off the server.
  • LOAD DATA and SELECT ... INTO OUTFILE move CSV data fast, within secure_file_priv and local_infile rules.

FAQ

How often should I back up?

Decide from two questions: how much data can you afford to lose (recovery point objective) and how long may a restore take (recovery time objective). Daily full backups plus continuously copied binary logs give a recovery point of seconds; restore time depends on size and method.

Can I back up a replica instead of the primary?

Yes, and it is common: it keeps backup load off the primary. Make sure the replica is not lagging, record its GTID position, and still keep the primary’s binary logs for point-in-time recovery.

Can I restore a dump from MySQL 8.0 into 9.7?

Yes, logical dumps move forward across versions; that is one of their main uses. Check the target with MySQL Shell’s upgrade checker (lesson 21) and review warnings, for example about removed authentication plugins in CREATE USER statements.

Why is my restore so slow?

A SQL dump is replayed statement by statement with index maintenance. Speed it up with MySQL Shell’s parallel loadDump, by restoring onto fast disks, and for very large databases by switching to physical backups.

↑ ↓ navigate Enter open Esc close