How to Back Up MySQL Databases From The Command Line

While automated backups are important, sometimes you just want to take a quick and dirty snapshot before making a change to your data. When changing files in Linux, you can simply copy a file to another name. In this tutorial, we will show you how to create a backup of your database (or multiple databases).  Furthermore, we will also show you how to restore backups from either command line or cPanel.

Creating A Backup

The mysqldump command is used to create textfile “dumps” of databases managed by MySQL. These dumps are just files with all the SQL commands needed to recreate the database from scratch. The process is quick and easy.

Backing Up a Single Database

If you want to back up a single database, you merely create the “dump” (aka a backup) and send the output into a file.  Don’t worry this command doesn’t affect the database, 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 following command:

mysqldump --databases database_one database_two > two_databases.sql

In the code above, database_one is the name of the first database to be backed up, and database_two is the name of the second.

 

Backing Up All Databases

It is also simple to back up all of the databases on a server:

mysqldump --all-databases > all_databases.sql

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 SQL commands, you can restore the database backup by telling mysql to run the commands in it and put the data 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, and database_name.sql is the name of the backup file to be restored..

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

mysql --one-database database_name < all_databases.sql

 

Restoring Databases From cPanel Backups

  1. Log into cPanel by typing https://yourhostname/cpanel/ into your browser.
  2. In the Files section, click on the Backups icon.In cPanel you can restore a database by clicking on the Backups section.
  3. 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 cPanel you'll find the Partial Backup section where you will be able to upload a .sql file to restore a database.
  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.
Series Navigation<< Delete a MySQL Database on Linux via Command Line
Be Sociable, Share!
    Here's 75 % off, Launch a New VPS Today. Find out why 30,000 customers have chosen our Best-in-Class Performance & 24x7 Heroic Support