How to Back Up and Restore a MySQL Database?

To back up a MySQL database, use mysqldump with the syntax mysqldump -u [username] -p [database_name] > backup_file.sql, which creates a SQL file containing all necessary commands to recreate the database, and include options like --databases, --all-databases, or --routines as needed; always prefer prompting for the password securely rather than including it in the command.To restore a MySQL database, ensure the target database exists or create it using CREATE DATABASE, then run mysql -u [username] -p [database_name]

Backing up and restoring a MySQL database is essential for data safety, disaster recovery, and migrations. The most common and reliable method uses mysqldump for backups and the mysql command-line tool for restoration. Here’s how to do it step by step.

1. Back Up a MySQL Database Using mysqldump

mysqldump is a utility that generates a SQL file containing commands to recreate your database (tables, data, stored procedures, etc.).

Basic Syntax:

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

Example:

mysqldump -u root -p my_database > my_database_backup.sql

• You’ll be prompted to enter the password after running the command.
• The backup file (my_database_backup.sql) will be created in your current directory.

Common Options:

• Backup multiple databases:

  

  mysqldump -u root -p --databases db1 db2 > backup_multiple.sql

• Backup all databases:

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

• Include stored procedures and events:

  

  mysqldump -u root -p --routines --events my_database > backup_with_routines.sql

• Add timestamp to filename (recommended):

  mysqldump -u root -p my_database > my_database_$(date  %F).sql

  This creates a file like my_database_2025-04-05.sql.

? Security Note: Avoid writing the password directly in the command (e.g., -pmypassword) as it may be visible in shell history. Use -p alone to prompt securely.

2. Restore a MySQL Database from Backup

To restore, you use the mysql command to execute the SQL dump file.

Prerequisites:

• Make sure the target database exists (or create it):

  CREATE DATABASE my_database;

Basic Restore Command:

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

Example:

mysql -u root -p my_database < my_database_backup.sql

• Enter your password when prompted.
• The command reads the SQL file and re-creates tables and inserts data.

Notes:

• If the database doesn’t exist, create it first.
• If restoring a full backup (--all-databases), don’t specify a database name:

  mysql -u root -p < full_backup.sql

3. Best Practices for MySQL Backups

To keep your data safe and recovery smooth:

• ✅ Schedule regular backups using cron (Linux) or Task Scheduler (Windows). Example cron job (daily at 2 AM):

  0 2 * * * mysqldump -u root -pYourPassword my_database > /backups/my_database_$(date  \%F).sql

• ✅ Compress backup files to save space:

  mysqldump my_database | gzip > my_database_$(date  %F).sql.gz

  To restore from .gz:

  gunzip < my_database_2025-04-05.sql.gz | mysql -u root -p my_database

• ✅ Test your backups periodically by restoring to a test server.

• ✅ Store backups offsite or in cloud storage (e.g., AWS S3, Google Drive) for disaster recovery.

• ✅ Avoid restoring over a live production database without a rollback plan.

---

4. Alternative: Using phpMyAdmin (for small databases)

If you're on shared hosting or prefer a GUI:

1. Log in to phpMyAdmin.
2. Select your database.
3. Click Export → Choose "Custom" → Download SQL file.
4. To restore: Select database → Import → Upload the .sql file.

⚠️ phpMyAdmin has file size limits (often 50–100MB). Use command line for larger databases.

---

Basically, mysqldump and mysql are the go-to tools for reliable, scriptable backups and restores. The process isn’t complicated, but consistency and testing are key.

The above is the detailed content of How to Back Up and Restore a MySQL Database?. For more information, please follow other related articles on the PHP Chinese website!