How to Dump Databases in MySQL Using mysqldump
In this tutorial, you'll learn how to dump a database in MySQL using mysqldump. There are many backup tools available on the market. However, if you need to back up a database using the built-in tool, then this tutorial is for you.
MySQL mysqldump is one of the most useful and powerful tools that allow you to dump databases quickly and easily. With MySQL mysqldump, you can dump data only, structure only, or both data and structure.
MySQL Dump Command
To dump a database in MySQL, we use the following command:
mysqldump -u [username] -p[password] database_name> file_name.sql
And, if you need to dump more than one databases, you use the following command:
mysqldump -u [username] -p[password] --databases database1 database2 ... > file_name.sql
The above commands dump both data and database structure. However, with the MySQL mysqldump, you also can dump databases with data only or structure only.
To dump a database with data only, you use the following command:
mysqldump -u [username] -p[password] --no-create-info database_name> file_name.sql
To dump a database with the structure only, you use the following command:
mysqldump -u [username] -p[password] --no-data database_name> file_name.sql
Remarks
- Username is a valid username.
- The password is the valid password for the specified username. Note that there is no space allowed between -p and password.
- Database_name is the name of the database that you want to dump.
- File_name is the name of the file that you want to store.
- If you want to dump databases with the structure only, simply specify --no-data .
- If you want to dump databases with data only, simply specify --no-create-info
- Neither --no-data nor --no-create-info is specified, MySQL dumps databases with both data and structure.
- If you want to dump databases with data only, simply specify --no-create-info
- Neither --no-data nor --no-create-info is specified, MySQL dumps databases with both data and structure.
Dump Database Examples
Example 1: Dump a Single Database
The following command dumps a single database named database_tutorial and saves the dump file as database_tutorial.sql in the c:\databases location:
mysqldump -u root -pscrete database_tutorial > c:\databases\database_tutorial.sql
Here, root is the username, secrete is the password, database_tutorial is the name of the database to dump, and database_tutorial.sql is the name of a file to save.
Example 2: Dump 2 Databases
The following command dumps 2 databases, db1, and db2 and saves the dump file as db1_db2_dump.sql in the c:\databases location:
mysqldump -u root -pscrete --databases db1 db2 > c:\databases\db1_db2_dump.sql
Example 3: Dump a Single Database with Data Only
The following command dumps a single database named db_tutorial with data only, and saves the dump file as db_tutorial.sql in the c:\databases location:
mysqldump -u root -pscrete --no-create-info db_tutorial > c:\databases\db_tutorial.sql
Example 4: Dump a Single Database with Structure Only
The following command dumps a single database named db_tutorial with the structure only and saves the dump file as db_tutorial.sql in the c:\databases location:
mysqldump -u root -pscrete --no-data db_tutorial > c:\databases\db_tutorial.sql
In this tutorial, you've learned how to dump databases in MySQL using mysqldump. With the MySQL mysqldump, you can dump databases with data only, structure only, or both data and structure.