Backup and Restore

Introduction

Backups protect against mistakes, hardware failure, and bad migrations. mysqldump produces portable SQL files (logical backup); restore replays them into a server. This chapter dumps blog_dev, restores to a new database, and notes InnoDB-consistent options and large import tips.

Prerequisites

Database Management Basics
Users and Permissions — dump user needs SELECT + SHOW VIEW etc.
Shell access to run mysqldump and mysql

Logical Backup With mysqldump

Full database:

bash

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  blog_dev > blog_dev_backup.sql

Flag	Purpose
`--single-transaction`	Consistent InnoDB snapshot without locking all tables
`--routines`	Include procedures/functions
`--triggers`	Include triggers

Structure only:

bash

mysqldump -u root -p --no-data blog_dev > blog_dev_schema.sql

Data only:

bash

mysqldump -u root -p --no-create-info blog_dev > blog_dev_data.sql

Single table:

bash

mysqldump -u root -p blog_dev posts > posts_backup.sql

Conditional rows:

bash

mysqldump -u root -p blog_dev posts \
  --where="published_at IS NOT NULL" > posts_published.sql

Restore

Create empty database:

sql

CREATE DATABASE blog_dev_restore
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Import from shell:

bash

mysql -u root -p blog_dev_restore < blog_dev_backup.sql

Inside mysql client:

sql

USE blog_dev_restore;
SOURCE /full/path/to/blog_dev_backup.sql;

Verify:

sql

SHOW TABLES;
SELECT COUNT(*) FROM posts;

Before Production Restore

Restore to staging first
Stop app or put in maintenance mode
Confirm disk space for SQL file expansion
Note version compatibility—restore 8.0 dump to 8.0+ generally OK

Large Import Tips

Session tuning for bulk load (trusted data):

sql

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
SET autocommit = 0;
-- import batches
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;
SET autocommit = 1;

Split huge files:

bash

split -l 50000 blog_dev_data.sql blog_dev_part_

Physical backup (copy datadir)—requires server stop or Percona XtraBackup—enterprise ops topic.

Backup Schedule Habits

Environment	Frequency
Dev laptop	Before risky experiment
Staging	Daily automated dump
Production	Automated + off-site storage + test restore monthly

Pair with Git for schema migration files—not only dumps.

FAQ

mysqldump locks tables?

--single-transaction avoids global read lock on InnoDB; MyISAM different.

Restore overwrites?

DROP in dump file may recreate objects—read dump header options --add-drop-table.

Compressed backup?

bash

mysqldump -u root -p blog_dev | gzip > blog_dev.sql.gz
gunzip < blog_dev.sql.gz | mysql -u root -p blog_dev_restore

Point-in-time recovery?

Need binary logs enabled—beyond intro; ops feature.

Docker backup?

bash

docker exec mysql-learn mysqldump -u root -plearnpass blog_dev > backup.sql

Secrets in dump?

Dump contains data—encrypt at rest; restrict file permissions chmod 600.