Backup and Restore MySQL database with mysqldump
The mysqldump tool is a command-line program that backs up and restores MySQL databases by saving everything as a text file filled with SQL commands. Running this tool creates a complete safety net for your website data, protecting you from hard drive crashes, server errors, and accidental deletions across MySQL versions.
You can use mysqldump to grab an entire database or just pick out specific tables to save. Because it creates standard text files, you can easily move your saved data to a new server or restore it whenever something goes wrong.
Backup your MySQL database by running `mysqldump` with options like `–lock-all-tables` or `–single-transaction` followed by redirection to a `.sql` file. Restore your database by piping the `.sql` file into the `mysql` command.
Backup MySQL database
Protecting your information starts with a reliable backup routine. The utility creates a complete copy of all databases, acting as a safety net when unexpected problems occur.
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.
How can a MySQL database be archived or backed up?
To back up a MySQL database, administrators utilize third-party tools or execute the `mysqldump` command from the command line. `mysqldump` is a command-line utility used to generate a MySQL logical database backup. It creates a single .sql file that contains a set of SQL statements.
What is the physical backup tool for MySQL?
Percona XtraBackup. Percona XtraBackup is a popular open-source tool for physical backups of MySQL and MariaDB databases that use InnoDB. Key features and pros: Physical hot backup tool for InnoDB.
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 […]