MySQL Tutorials - Herong's Tutorial Examples
Dr. Herong Yang, Version 4.11

mysqldump - Dumping Data to Files

This section provides some tutorial examples on how to use mysqldump tool to dump the structure and data of tables from a MySQL server to files.

What Is mysqldump? mysqldump is a tool to dump table structure and data to files. This tool can be used in different ways.

1. Dump tables of a database as SQL statements into a single file. Table structures will be dumped as create table statements. Data rows will be dumped as insert statements. If table is not specified, all tables will be dumped.

\mysql\bin\mysqldump --result-file=file.sql db_name [tbl_name]

2. Dump tables of a database into two files per table. One file contains a create table statement. The other file contains table data as tab delimited values. Output files will be created in a sub directory specified in the command line.

\mysql\bin\mysqldump --tab=dir_name db_name [tbl_name]

3. Dump tables of a database as XML format into a single file.

\mysql\bin\mysqldump --xml --result-file=file.xml db_name [tbl_name]

In order to test mysqldump, I created a database with two tables with the following SQL file:

-- CreateDatabase.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
DROP DATABASE IF EXISTS Library;
CREATE DATABASE Library;
USE Library;
--
CREATE TABLE Book (ID INT, Title VARCHAR(64), Author_ID INT);
INSERT INTO Book VALUES (1, 'Java', 1);
INSERT INTO Book VALUES (2, 'C++', 1);
INSERT INTO Book VALUES (3, 'FORTRAN', 2);
--
CREATE TABLE Author (ID INT, Name VARCHAR(16));
INSERT INTO Author VALUES (1, 'Herong');
INSERT INTO Author VALUES (2, 'Mike');
--
SELECT Title, Name FROM Book INNER JOIN Author ON Book.Author_ID=Author.ID;

Output from this SQL file:

Title   Name
Java    Herong
C++     Herong
FORTRAN Mike

I used the following commands to test mysqldump. The output files look perfect to me.

\mysql\bin\mysqldump --result-file=Library.sql Library
mkdir Library
\mysql\bin\mysqldump --tab=Library Library
\mysql\bin\mysqldump --xml --result-file=Library.xml Library

Table of Contents

 About This Book

 Introduction of SQL

 MySQL 4.0 Introduction and Installation

 Installing MySQL 5.5.15

 Installing MySQL 5.0.2 (Alpha)

Introduction of MySQL 5.0 Programs

 List of MySQL Programs

 mysqld - The MySQL Server Program

 mysqladmin - The Client Tool for Administrators

 mysql - The Client Tool for End Users

 Using mysql to Run SQL Statements

mysqldump - Dumping Data to Files

 mysqlimport - Loading Data from Files

 Perl Programs and MySQL Servers

 PHP Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

 Using DDL to Create Tables and Indexes

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Transaction Management and Isolation Levels

 Locks-Used-in-MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 References

 Printable Copy - PDF Version

Dr. Herong Yang, updated in 2012
mysqldump - Dumping Data to Files