Backup and Restore MySQL database with mysqldump

The mysqldump tool is a command-line utility that backs up and restores MySQL databases by saving your data as a text file filled with SQL commands. Running this utility protects your website and app data from hard drive crashes, server errors, and accidental deletions across multiple database versions.

You can use this built-in program to export an entire database or grab specific tables when you need to move your saved data to a new server. Because it creates standard text files, you can restore your database easily whenever something goes wrong.

Backup MySQL database

To perform a MySQL database backup, run the mysqldump command in your terminal to export all tables and events into a single SQL file. This creates an exact copy of your data so you can restore it later if something goes wrong. Make sure you have administrator access before you run the command.

Run the command below to back up all databases and lock tables. This prevents any writes or reads during the process.

sudo mysqldump --lock-all-tables --all-databases --events > mysql_backup.sql

Passing the transaction option backs up all databases while maintaining data integrity, avoiding the need to lock tables entirely.

sudo mysqldump --single-transaction --all-databases --events > mysql_backup.sql

Targeting a specific database requires the following command instead.

Restoring your MySQL database from a backup file uses the mysql command. This process brings back all your MySQL databases or just a single one you need. Following these steps helps you recover your MySQL data and get it back online quickly.

Executing the command below restores every database from a complete backup.

sudo mysql < mysql_backup.sql

Targeting a specific database involves creating an empty database first, then performing the restore.

sudo mysql database_name < mysql_backup.sql

That completes the process.

Conclusion:

Backing up and restoring MySQL databases with mysqldump prevents potential data loss. Using the commands outlined here helps keep your database safe. Here are some key takeaways:

• Regular Backups: Schedule regular backups to avoid data loss due to unexpected issues.
• Use Locking Wisely: Choose between locking tables and single-transaction methods based on your needs.
• Specific Database Backups: If required, you can focus on specific databases rather than backing up all databases.
• Test Restores: Regularly test your backup and restore process to ensure reliability.
• Monitor Backup Files: Monitor backup file sizes and retention policies to manage storage efficiently.

Following these practices maintains the health of your MySQL databases and ensures preparedness for unforeseen circumstances.