MySQL Dump Database Using mysqldump


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.

Leave a Reply

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