Backup, Restore & Security

Logical backups with mysqldump

# one database
mysqldump -u root -p --single-transaction --routines --triggers --events school > school.sql

# all databases
mysqldump -u root -p --all-databases --single-transaction > full.sql

# structure only / data only
mysqldump -u root -p --no-data school > schema.sql
mysqldump -u root -p --no-create-info school > data.sql

# specific tables, compressed
mysqldump -u root -p school students enrollments | gzip > tables.sql.gz

--single-transaction gives a consistent snapshot of InnoDB tables without locking writes. Add --source-data=2 (--master-data=2 on older versions) to record the binlog position, which you need for point-in-time recovery.

Restoring

mysql -u root -p school < school.sql
gunzip < tables.sql.gz | mysql -u root -p school

SOURCE /path/to/school.sql;   -- from inside the mysql client

Physical backups

For large databases, mysqldump becomes too slow to restore. Use Percona XtraBackup (hot, non-blocking, file-level) or your cloud provider's snapshots. The rule of thumb: logical backups under ~50 GB, physical above that.

Point-in-time recovery

SHOW BINARY LOGS;

# replay the binlog from the backup position up to just before the mistake
mysqlbinlog --start-position=1234 --stop-datetime="2026-09-17 14:29:00" \
  binlog.000042 | mysql -u root -p

This is how you recover from "someone ran DELETE without a WHERE at 14:30" — restore last night's dump, then replay the binlog up to 14:29.

Backup discipline

1. Follow 3-2-1: three copies, two media types, one off-site.
2. Test the restore. An untested backup is a guess, not a backup. Restore to a scratch server monthly.
3. Automate with cron and alert on failure — silent backup failures are the norm, not the exception.
4. Keep a retention ladder: daily for two weeks, weekly for three months, monthly for a year.
5. Encrypt backup files; they contain everything your database does.

Security checklist

Access

• Run mysql_secure_installation on every new server.
• No application connects as root; least privilege for every account.
• Bind to 127.0.0.1 or a private network; never expose port 3306 to the internet.
• Use a firewall and, where possible, connect over TLS: REQUIRE SSL on the account.

Application

• Use parameterised queries everywhere. String concatenation is how SQL injection happens:

-- vulnerable
"SELECT * FROM users WHERE email = '" + input + "'"

-- safe
"SELECT * FROM users WHERE email = ?"   with a bound parameter

• Validate and constrain input at the boundary, and let database constraints back it up.
• Hash passwords with bcrypt or Argon2 in the application — never with MySQL's MD5() or SHA1().

Data

• Encrypt at rest (InnoDB tablespace encryption or disk encryption) and in transit.
• Store only the personal data you actually need; consider AES_ENCRYPT or application-side encryption for sensitive fields.
• Keep an audit trail of privileged changes.

Operations

• Patch MySQL promptly; subscribe to security advisories.
• Review SHOW GRANTS for every account periodically and remove accounts for people who have left.
• Enable the slow query and error logs, and monitor failed login attempts.