Backup and Restore MySQL database with mysqldump

This article explains how to backup and restore MySQL databases using the mysqldump tool on Ubuntu 24.04

mysqldump is a command-line utility used to create a backup of MySQL databases. It lets you create backups of your databases to prevent data loss in case of hardware failure, data corruption, or accidental deletion.

The SQL output generated by mysqldump is standard SQL, making it compatible with other database management systems that can interpret SQL.

Backup MySQL database

Before backing up your MySQL database, you should lock all the database tables.

Run the command below to back up all databases while locking all tables to prevent writing and reading during backup.

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

You can back up all the databases with the transaction option to maintain data integrity without locking the tables.

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

If you want to backup a specific database, run the command below.

sudo mysqldump database_name --single-transaction --events > mysql_backup.sql

Restore MySQL database

After backing up your database, you can restore it using the command below.

If you backed up all the databases, run the command below to restore.

sudo mysql < mysql_backup.sql

To restore a specific database, create an empty database, then restore.

sudo mysql database_name < mysql_backup.sql

That should do it!

Conclusion:

Backing up and restoring MySQL databases using mysqldump is a straightforward process that can save you from potential data loss. By following the commands outlined, you can ensure the safety and integrity of your database. 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 will help maintain the health of your MySQL databases and ensure you are prepared for any unforeseen circumstances.