How to Back up & Restore a MySQL Database

How to Back Up & Restore a MySQL Database

Managing data is a critical part of any application. In this tutorial, you’ll learn how to back up (export) and restore (import) a MySQL database using simple and effective commands.

Prerequisites

Before you begin:

• MySQL/MariaDB installed

• Access to terminal/command line

• Database user credentials

Step 1: Back Up a MySQL Database

You can use the A mysqldump command to export your database.

Syntax

mysqldump -u [username] -p [database_name] > [backup_file.sql]

Example

mysqldump -u root -p student_db > student_db_backup.sql

• -u root: Your MySQL username

• -p: Prompts for your password

• student_db: The database you want to back up

• student_db_backup.sql: The file where the backup will be saved

💡 This creates a .sql file containing all the commands needed to recreate the database.

Step 2: Restore a MySQL Database

Use the mysql command to import a database from a .sql backup file.

Syntax

mysql -u [username] -p [database_name] < [backup_file.sql]

Example

mysql -u root -p student_db < student_db_backup.sql

💡 Make sure the database (student_db) already exists before restoring. If it doesn’t, create it first:

mysql -u root -p -e "CREATE DATABASE student_db;"

Bonus: Back Up All Databases

To export all databases in one go:

mysqldump -u root -p --all-databases > all_databases_backup.sql

Bonus: Restore All Databases

mysql -u root -p < all_databases_backup.sql

Tips for Managing Backups

• Automate backups using cron jobs (Linux/macOS) or Task Scheduler (Windows)

• Compress backups to save space:

  mysqldump -u root -p student_db | gzip > student_db_backup.sql.gz
  

• To restore a compressed backup:

  gunzip < student_db_backup.sql.gz | mysql -u root -p student_db
  

Conclusion

Backing up and restoring MySQL databases is essential for any developer or administrator. Whether you’re migrating servers, recovering from issues, or preparing for deployment, having backups ensures peace of mind.