mysqldump - Dumping Data to Files

<< Introduction of MySQL 5.0 Programs

<< MySQL Tutorials - Herong's Tutorial Notes

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

Sections in This Chapter

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