How to Backup MySQL Databases via command line

By Raju

February 2, 2017

MySQL

In this tutorial you are going to learn how to backup MySQL databases via command line on Linux server. Also, we'll cover restoring a mysql database through command line.

Do you know that MySQL provides an amazing tool to backup and restore databases?

Let's dive in and learn about it.

mysqldump — A Database Backup Utility

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format..

MySQL.COM 
Definition

How to Backup a Database?

You can use mysqldump command utility to dump database or tables data into textfiles with SQL queries. Later, MySQL can restore database by executing all the SQL queries stored in dumped text files. 

There are in general three ways to use mysqldump—in order to dump a set of one or more tables, a set of one or more complete databases, or an entire MySQL server—as shown here:

mysqldump [options] db_name [tbl_name ...]
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

If you want to backup single database, execute below command to dump database contents to text file.

mysqldump sampledb  >  sampledb.sql

To backup multiple databases, execute below command.

mysqldump --databases database_db1 database_db2 > mutilple_databases.sql

For all databases on server, execute below command.

mysqldump --all-databases > all_databases.sql

All above commands generates .sql file which will be used for restoring databases.

How to Restore a Database?

You can restore MySQL database using dump files ( .sql ) created in earlier steps. The SQL files contain a series of SQL queries so it's easy to restore through mysql command.

To restore database "sampledb" from SQL dump file, use below command.

mysql sampledb  <  sampledb.sql

In the above command, sampledb is the name of the database you want to restore, and sampledb.sql is the name of the backup file to be restored.

To restore one particular database from SQL dump file, use below command.

mysql --one-database sampledb  <  all_databases.sql

Once command executed successfully, check your database.

So far, you have learned how to backup database using mysqldump tool and restore a database through mysql command.

Let us know what you think of this article and we'd love to hear from you.

Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Create a website in 3 simple steps

Choose a website template, add features, then customise! - Free Online Website builder.