Backup and Restore MySQL database with mysqldump
You can back up and restore MySQL databases on Ubuntu 24.04 using the mysqldump command-line utility.
Mysqldump generates a logical backup of your MySQL database, essentially creating a file containing SQL statements that can recreate your database structure and data. This is your primary defense against data loss from hardware failures, corruption, or accidental deletions.
The output from mysqldump is standard SQL, ensuring broad compatibility with various MySQL versions and even other SQL-compliant database systems. You can use this for a full database backup or even specific tables.
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.
Frequently Asked Questions
How do I backup my MySQL database using mysqldump?
What is the purpose of using --lock-all-tables in mysqldump?
How can I restore my MySQL database from a backup?
Is it necessary to lock tables when backing up MySQL databases?
How often should I backup my MySQL database?
Was this guide helpful?
About the Author
Richard
Tech Writer, IT Professional
Richard, a writer for Geek Rewind, is a tech enthusiast who loves breaking down complex IT topics into simple, easy-to-understand ideas. With years of hands-on experience in system administration and enterprise IT operations, he’s developed a knack for offering practical tips and solutions. Richard aims to make technology more accessible and actionable. He's deeply committed to the Geek Rewind community, always ready to answer questions and engage in discussions.
[…] How to backup MySQL databases […]