Home / Data Back Up / Export Data / Question No: 177

<p>I want to use MySQL mysqldump utility to export my table data. Can you help me to explain, how to use mysqldump to export data?</p>

The mysqldump client is used to export existing table data, table structures, or both from the MySQL server. If requested, the exported data can include all necessary SQL statements required to re-create the dumped information.  You can also specify whether to dump one, some, or all databases found on the server, or even just specific tables in a given database. You can invoke mysqldump using any of the following three syntax variations:

%>mysqldump [options] database [tables]

%>mysqldump [options] --databases [options] database1 [database2...]

%>mysqldump [options] --all-databases [options]

Let’s consider a few examples. The first example dumps just the table structures of all databases found on a local server to a file named output.sql:

%>mysqldump -u root -p --all-databases --no-data > mysql-data-structures.sql

Note that the output is being directed to a file; otherwise, the output would be sent to the screen. Also, keep in mind that the .sql extension is not required. This extension is used here merely for reasons of convenience; you can use any extension you wish.

The next example dumps just the data of a single database, cart:

%>mysqldump -u root -p --no-create-info cart > mysql-data-structures.sql

The final example dumps both the structure and the data of two tables located in the cart database, including DROP TABLE statements before each CREATE statement. This is particularly useful when you need to repeatedly re-create an existing database, because attempting to create already existing tables results in an error; thus the need for the DROP TABLE statements.

%>mysqldump -u root -p --add-drop-table cart products buyers > output.sql