How to restore MySQL or MariaDB from backup

Topic: Databases core

Restore a MySQL or MariaDB database from a mysqldump file using the mysql client. Create the database first if needed. Use this when recovering from a failure or when cloning a database from a logical backup.

Intent: How-to

Quick answer

  • Create the database if needed: CREATE DATABASE mydb; Then restore: mysql -u root -p mydb < mydb.sql. For a full dump use mysql -u root -p < all.sql and ensure no conflicts with existing data.
  • Large dumps may need more time or max_allowed_packet. Redirect stderr to a file to capture errors. Verify with a few SELECTs or an application smoke test after restore.
  • For point-in-time recovery you need binary logs and a procedure to apply them after the full restore. Document restore time and source backup for RTO and audit.

Prerequisites

Steps

  1. Prepare target

    Create database: mysql -u root -p -e 'CREATE DATABASE mydb;' For full replace: DROP DATABASE mydb; CREATE DATABASE mydb;

  2. Restore

    mysql -u root -p mydb < /backup/mydb.sql. For large dumps run in screen or tmux. Increase max_allowed_packet in session or config if needed.

  3. Verify

    Connect and run key queries; run application smoke test. Document restore duration and source backup.

  4. PITR if needed

    If binary logging is enabled, restore full backup then apply binlogs from backup time to desired time with mysqlbinlog. Test procedure on a copy first.

Summary

Restore with mysql mydb < dump.sql after creating the database. Verify and document. Use binary logs for point-in-time recovery when available.

Prerequisites

Steps

Step 1: Prepare target

Create the database; drop and recreate for full replace if intended.

Step 2: Restore

Run mysql mydb < dump.sql; handle large dumps and errors.

Step 3: Verify

Run queries and smoke tests; document the restore.

Step 4: PITR if needed

Apply binary logs for point-in-time recovery; test on a copy first.

Verification

  • Database is consistent and usable; restore is documented.

Troubleshooting

Restore fails — Check dump integrity and MySQL version; ensure disk space and max_allowed_packet. Slow restore — Normal for large dumps; consider parallel restore tools if available.

Next steps

Continue to