How to Back Up and Restore MySQL Databases in Linux?

Linux offers the “mysqldump” command-line tool to create the backup and restore the MYSQL databases. It is easy to use but takes too much time during the restoration process of large databases. This is because it executes all the SQL statements to create tables and inserts data when the user restores the databases.  

The aim of the post is to describe the possible ways to back up and restore the MYSQL databases in Linux. 

How to Backup MySQL Database in Linux?

The working of the “mysqldump” depends upon its syntax. The basic generalized syntax of the “mysqldump” utility to generate the backup of the MYSQL database is typed here:


$ mysqldump -u [user-name] -p [password] [database_name] > [dumpfilename.sql]

In the syntax of “mysqldump” command a list of following components are mentioned:

  • -u [username]:  Denotes the valid username
  • p [password]: Shows the valid user password
  • [database_name]: Identifies the database name whose backup is created.
  •  “>”: Helps to generate the backup.
  • [dumpfilename.sql]: Represent the created backup file.

Note: To backup and restore the databases with the “mysqldump” command line tool, make sure that you are login as the “root user” or use the “sudo” command.

Example 1: Create the Backup of a Single MySQL Database

 Suppose there is a database “db_office” in the existing database as shown in the screenshot:


To create the backup of the “db_office” into the single dump “db_office.sql” file use the “mysqldump” command in the following way:

$ sudo mysqldump -u root -p  db_office > db_office.sql

The above command has created the backup or dump file of “db_office” database. For more verification, execute the “ls” command followed by the “-l(list)” flag:

$ ls -l db_office.sql

The output confirms that the “db_office.sql” dump file of “db_office” database has been created into the “/home” directory.

Example 2: Create the Backup of  Multiple/All  MySQL Databases

The user can also take the backup of all the existing databases using the “mysqldump” command followed by the “–all database” argument in this way:

$ sudo mysqldump -u root -p --all-databases > all-databases.sql

The “all-databases.sql” has been created containing all the existing databases with their structures and data.

Example 3: Create the Backup of MySQL Database Structure

Sometimes, the user only wants to back up the database’s structure without its content. For this purpose, use the “–no-data” flag of the “mysqldump” utility. 

In this case, we created the backup of the database “db_employess” structure into the “db_emlpoyees_striucture.sql” dump file as shown below:

$ sudo mysqldump -u root -p --no-data db_employees> db_employees_structure.sql

Example 4: Create the Backup of MySQL Database Data

The “–no-create-info ” argument is beneficial to backup only the content of the desired database. For practical implementation, only backup the data available in the “db_test” database into the “db_test_data.sql” dump file:

$ sudo mysqldump -u root -p --no-create-info db_test > db_test_data.sql

The output displays that the “” has been created having the backup of “db_test” database content.

Example 5: Create the Backup of MySQL Database Particular Table

Except for the content or structure, the user can also create the backup of the particulate table available in any database. 

In this scenario, the “Sample” database contains two tables, “Record1” and “Record2”, as shown in the screenshot via the following command:


Execute the below-mentioned command to create the backup of the “Record1” table from the “Sample” database into the “Sample_Record1.sql”:

$ sudo mysqldump -u root -p Sample Record1 > Sample_Recoed1.sql

The “Sample_Record1.sql” dump/backup file has been created of the table “Record1”.

How to Restore MySQL Database in Linux?

The above section describes various practical examples to take the backup of the existing database’s content, structure, particular table, and many others. Once their backup is created, it can be restored using the “mysqldump” utility.


The basic syntax of the “mysqldump” command for restoring the database is written below:

$ mysqldump -u [user-name] -p [password] [database_name] > [dumpfilename.sql]

All the components are the same as the “backup” syntax except the “>” operator. The “>” operator is used to restore the created backup.

Example 1: Restore the Single and Multiple Databases

Before restoring, first, create an empty database in which the backup file is restored. In this case, the “db_office2” empty database is created to restore the above “db_office.sql” dump file in it:

$ sudo mysql -u root -p db_office2 < db_office.sql

Example 2: Restore the Particular Table of Database

To restore the particular table from the specified database dum file use the “mysql” command with “<” operator in this way:

$ sudo mysqldump -u root -p users employees> users_employees.sql

The “users” database table “employees” has been restored.


In Linux, take the backup of the MySQL database and restore it by utilizing the “mysqldump” command line tool. This command line tool allows Linux users to simultaneously back up and restore the database content, structure, specific table, and single or multiple databases. This post has briefly illustrated the complete procedure to back up and restore the MYSQL databases in Linux.