MySQL Backup and Restore Using mysqldump

Backing up your MySQL databases is essential for data protection, disaster recovery, and migration. MySQL provides the mysqldump utility to export databases into .sql files, which can later be restored using the mysql command-line tool.

Using mysqldump for Backups

mysqldump is a command-line utility that exports your database (or selected tables) into a .sql file containing SQL statements to recreate the structure and data.

Syntax:

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

Example:

mysqldump -u root -p school_db > school_backup.sql

Backing Up Specific Tables

You can back up selected tables from a database instead of the entire database.

Example:

mysqldump -u root -p school_db students teachers > partial_backup.sql

Backing Up All Databases

To back up all databases on your MySQL server:

Example:

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

Backing Up Only Schema (Structure)

To back up just the table structure without any data:

Example:

mysqldump -u root -p --no-data school_db > schema_only.sql

Restoring a Backup

To restore a .sql file created with mysqldump into an existing database:

Syntax:

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

Example:

mysql -u root -p school_db < school_backup.sql

Restoring All Databases

If your backup file contains all databases (created using --all-databases):

Example:

mysql -u root -p < full_backup.sql