How to do a MySQL Backup/Restore
MySQL is one of the giants of the database world. Much of the web relies on it – from small blogs to large companies like Facebook. Even Uber has come to rely on this relational database management system due to its solid performance and lack of a price tag. Sometimes, however, mistakes happen; failure is imminent. That’s why regular backups are important – as is the ability to restore them.
If you don’t have MySQL installed, check out our guide on How to install MySQL on Ubuntu 14.04 LTS.
Part 1: Back Up The Database
To perform backups, you will need a tool called mysqldump. It is included with MySQL, so there’s no need to install anything else. This utility simplifies the process and allows four important types of backups that this guide will cover: single-database, multi-database, system-wide, and table-level.
Assume that a single database – globotech – needs to be backed up. This can be done as follows:
mysqldump -u root -p globotech > globotech_backup.sql
In this example, the single database globotech is backed up to a file named globotech.sql. What comes after the filename is important; a user (and password) is required for mysqldump backups. You will be prompted for the password of the user after executing the command.
A multi-database backup will allow you to back up several databases to one .sql file. Here, we want to back up the production as well as the test globotech databases.
mysqldump -u root -p --databases globotech username customer > 3databases_backup.sql
As you can see, each database name must be separated by a space. Everything else operates like one would expect from a single-database backup.
Periodic backups of your entire system’s database are paramount. They ensure that everything can be recovered in the event of drive failures or other catastrophes. While they may take longer to perform, the command is actually simpler than others.
mysqldump -u root -p --all-databases > all_globotech_databases.sql
In this example, the database globotech contains a table named customer_records. You must specify both the database the table is located in and the name of the table.
mysqldump -u root -p globotech customer_table > globotech_customer_table.sql
The command will prompt you for root’s password, and then customer_records will be backed up to globotech.sql.
Part 2: Restore The Backups
What good are backups without the ability to restore them?
Unlike Part 1 of this guide, here you will no longer need mysqldump. All restoration tasks can be done directly from mysql commands. This section will assume that globotech.sql contains a system-wide backup – holding each database (production and test) as well as their tables.
Restore an Entire MySQL Backup
If you are performing a server migration – or if your drive has failed – restoring a system-wide backup will be the easiest thing you do. Simply specify the name of the user and the name of the file holding the backup.
mysql -u root -p globotech < globotech_backup.sql
After entering the password of root, all databases and tables from globotech.sql will be backed up to the database.
Restore a Single Database
You can restore a single database from a .sql file by specifying three things: the user, target database, and the name of the backup file. In code, it is done like so:
mysql -u root -p --one-database globotech < all_globotech_databases.sql
This command will ask for the password of root and then restore the contents of globotech. It will not restore any data from the other database in globotech.sql: globotech_test.
No one ever wants bad things to happen; smart administrators make sure they are prepared when they do. With the completion of this guide, you’re now able to almost guarantee the existence of your data when the server goes down – and when it comes back up.
If you liked this guide, check out our other tutorials where we cover topics such as networking, servers, security, and much more.