How to do a MySQL Backup/Restore

Try it in our public cloud & Get $50 Credit
CLAIM NOW

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.

Getting Started

Before we get start, you will need a few things:
• 1 Node (Cloud Server or Dedicated Server) running Ubuntu 14.
• MySQL
• Access to the ‘root’ user of the MySQL installation

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.

Single-Database Backups
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.

Multi-Database Backups
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.

System-Wide Backups
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

Table-Level Backups
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.

Conclusion

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.