Lesson 99: mysqldump Command
In this lesson, you'll learn how to use the mysqldump command to back up and restore MySQL databases in Linux.
mysqldump Command: A reliable utility for backing up and exporting MySQL databases to a file.,mysqldump is a command-line client program. It is used to dump local or remote MySQL databases or collections of databases for backup into a single flat file.
If you don't have MySQL installed or don't have any exposure to MySQL, then refer to our installation blog.
mysqldump Command Syntax
To take a backup of MySQL databases, the database must exist on the database server, and you must have access to it. The format of the command would be:
# mysqldump -u [username] -p[password] [database_name] > [dump_file.sql]
The parameters of the said command are as follows:
[username]- A valid MySQL username.[password]- A valid MySQL password for the user.[database_name]- A valid database name you want to take a backup of.[dump_file.sql]- The name of the backup dump file you want to generate.
mysqldump Backup Options
| Option | Description |
|---|---|
--databases DB1 DB2 |
Backup multiple specific databases |
--all-databases |
Backup all databases on the server |
--no-data |
Backup database structure only (no data) |
--no-create-db |
Skip CREATE DATABASE statements |
--no-create-info |
Backup data only (no table structure) |
-h HOST |
Connect to a remote MySQL server |
1. Backup a Single MySQL Database
To take a backup of a single database, use the command as follows. The command will dump the database rsyslog structure with data into a single dump file called rsyslog.sql.
# mysqldump -u root -ptecmint rsyslog > rsyslog.sql
2. Backup Multiple MySQL Databases
If you want to take a backup of multiple databases, run the following command. The following example command takes a backup of the databases rsyslog and syslog structure and data into a single file called rsyslog_syslog.sql.
# mysqldump -u root -ptecmint --databases rsyslog syslog > rsyslog_syslog.sql