MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Data Back Up / Export Data / Question No: 177

How to use mysqldump to export data?

<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>

Answer No: 177

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

Related MySQL FAQs to the Above FAQ

How-to-use-SELECT-INTO-OUTFILE-statement-to-export-data How to use SELECT INTO OUTFILE statement to export data?

How-to-export-table-data-to-text-file-with-SELECT-INTO-OUTFILE-statement How to export table data to text file with SELECT INTO OUTFILE statement?

How-to-export-table-data-to-Microsoft-Excel-with-SELECT-INTO-OUTFILE-statement How to export table data to Microsoft Excel with SELECT INTO OUTFILE statement?

How-to-take-dump-of-database-without-its-data How to take dump of database without its data?

How-to-take-dump-of-the-database How to take dump of the database?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.