Backup and Restore MySQL database with mysqldump
You back up and restore MySQL databases using the mysqldump command-line utility.
Mysqldump performs a logical backup of your MySQL database by generating a file of SQL statements. These statements reconstruct your database’s structure and data, acting as your primary defense against data loss.
This process is essential for protecting your information from hardware failures, corruption, or accidental deletions. The output from mysqldump is standard SQL, ensuring compatibility across many MySQL versions and even other SQL-compliant systems.
You can use mysqldump for a complete database backup or to target specific tables within your database.
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
Before you back up your MySQL database, it’s a good idea to lock all the database tables.
Run the command below to back up all databases and lock tables, preventing any writes or reads during the backup process.
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 back up 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 with mysqldump is a process that can prevent data loss. By using the commands we’ve covered, you can keep your database safe and sound. 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.
How can a MySQL database be archived or backed up?
To back up a MySQL database, you can use either 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 […]