How to Backup and Restore MySQL Databases from the Command Line

Posted on by Alex Gorzen | Updated:
Reading Time: 3 minutes

While automated backups are important, sometimes you just want to take a backup of the MySQL database in Linux (MySQL dump) before making a change to your site. When modifying files in Linux, you can simply copy a file to another name to make a new copy. In this tutorial, we will show you how to create MySQL database backups and also how to restore a backup from either command line or cPanel.

How to Back Up And Restore MySQL Databases From The Command Line

Creating A Backup

The mysqldump command is used to create a text file dump of one or multiple databases that can be managed by MySQL. These database dumps are simply text-based files containing all the SQL commands needed to recreate the database from scratch. The process truly is quick and easy.

How to Backup a MySQL Database

Single MySQL database backups are achieved by creating the dump file and sending the output of the mysqldump command into a .sql file.  Don't worry, this command doesn't affect the database in any way. It merely makes a copy of the database.

mysqldump database_name > database_name.sql

Backing Up Multiple Databases

Use mysqldump to backup multiple databases at the same time using the following syntax.

mysqldump --databases database_onedatabase_two > two_databases.sql

In the command above, database_one is the name of the first database to be backed up, and database_two is the name of the second. This command will incorporate both databases into a single database .SQL file.

Backing Up All Databases

It is also simple with MySQL to back up all of the databases on a server using the mysqldump command.

mysqldump --all-databases > all_databases.sql

Again, this will add all databases into a single database .sql file.

Here's 75% off a New VPS! Find out why 30,000 customers have chosen our Best-in-Class Performance & 24x7 Heroic Support

Restoring a Backup

Restoring a Database Via Command Line

Since the dump files are just data preceded by SQL commands, you can restore the database backup by telling MySQL to run the commands in it and put the data back into the proper database.

mysql database_name < database_name.sql

In the code above, database_name is the name of the database you want to restore to, and database_name.sql is the name of the backup file .sql file you are restoring from.

If you are trying to restore a single database from a dump of all the databases, you have to let MySQL know like this:

mysql --one-database database_name < all_databases.sql

This command pulls the original database_name .sql dump from the all_databases.sql mysql dump and restores it into its original database name.

Restoring Databases From cPanel Backups

cpanel backup
partial backup
  1. Log into cPanel by typing https://yourhostname/cpanel/ into your browser.

  2. In the Files section, click on the Backups icon.

  3. Under Partial Backups > Restorea MySQLDatabase Backup, click on the Browse button. (If you followed the steps in the above section, Creating A Backup you'll be able to click the Browse button to find a .sql file in your computer.)

  4. In the popup that appears, navigate to the appropriate destination and select the backup file (.sql) you intend to use.
  5. Click Open.

  6. Click Upload.

Video by: Alex Gorzen!

Conclusion

You don't have to worry about database uptime when you have Liquid Web’s High-Availability Databases. They provide bleeding-edge hardware at an affordable price! 

Want to know more?
We are The Most Helpful Humans In Hosting™! Our sales and support staff are available 24 hours a day, 7 days a week 365 days a year. We offer Fully Managed VPS servers, Cloud Dedicated servers, VMWare Private Cloud, Private Parent servers, Managed Cloud Servers, or Dedicated servers that would best fit your needs. Contact us today to get started.

Series Navigation
<< Previous Article
Avatar for Alex Gorzen

About the Author: Alex Gorzen

Alex Gorzen has been helping others with technology his whole life. He played with computers even before he could read and wants to make sure his children share that same love as they grow up. In his free time, he enjoys gardening, building things, and learning new skills.

Latest Articles

What is VMware HA and DRS?

Read Article

How to Make OpenStack Work for You

Read Article

Five Steps to Create a Robots.txt File for Your Website

Read Article

Premium Business Email Pricing FAQ

Read Article

Microsoft Exchange Server Security Update

Read Article