- Change a Password for MySQL on Linux via Command Line
- Create a MySQL Database on Linux via Command Line
- Select a MySQL Database on Linux via Command Line
- How to Delete A Database In MySQL/MariaDB
- How to Back Up And Restore MySQL Databases From The Command Line
While automated backups are important, sometimes you just want to take a MySQL dump of the database 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 a backup of your database (or multiple databases) and also how to restore a backup from either command line or cPanel.
The mysqldump command is used to create a textfile “dump” of a database 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.
Backing Up a Single Database
If you want to back up a single database, you merely create the “dump” (aka make a backup) and send 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
Multiple databases can be backed up at the same time using the same “mysqldump” command:
mysqldump --databases database_one database_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.
Backing Up All Databases
It is also simple to back up all of the databases on a server:
mysqldump --all-databases > all_databases.sql
Again, this will add all databases into a single database .sql file.
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
- Log into cPanel by typing https://yourhostname/cpanel/ into your browser.
- In the Files section, click on the Backups icon.
- Under Partial Backups > Restore a MySQL Database 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.)
- In the popup that appears, navigate to the appropriate destination and select the backup file (.sql) you intend to use.
- Click Open.
- Click Upload.
Video by: Alex Gorzen!
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 pride ourselves on being The Most Helpful Humans In Hosting™!
Our Support Teams are filled with experienced Linux technicians and talented system administrators who have intimate knowledge of multiple web hosting technologies, especially those discussed in this article.
Should you have any questions regarding this information, we are always available to answer any inquiries with issues related to this article, 24 hours a day, 7 days a week 365 days a year.
If you are a Fully Managed VPS server, Cloud Dedicated, VMWare Private Cloud, Private Parent server, Managed Cloud Servers, or a Dedicated server owner, and you are uncomfortable with performing any of the steps outlined, we can be reached via phone at @800.580.4985, a chat or support ticket to assisting you with this process.