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:
Syntax:
$ 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:
> SHOW DATABASES;
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 “db_test.data.sql” 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:
> SHOW TABLES;
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.
Syntax:
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.
Conclusion
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.
