mysqldump - Dumping Data to Files

MySQL Tutorials - Herong's Tutorial Examples

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

herong> %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.

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

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

herong> %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 HerongYang.com. All Rights Reserved.
--
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

Test 1 - Dump a database into a single *.sql file. It worked correctly.

herong> %mysql%\bin\mysqldump --user=root --password=TopSecret \
   --result-file=Library.sql Library

herong> type Library.sql
-- MySQL dump 10.13  Distrib 8.0.16, for Win64 (x86_64)
--
-- Host: localhost    Database: Library
-- ------------------------------------------------------
-- Server version       8.0.16

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
 SET NAMES utf8mb4 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, ... */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, ... */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
...

Test 2 - Dump a database into a folder with multiple files. It did not work. I got an error with MySQL Server 8.0 and 5.7. Older versions of MySQL server gave me no errors. This error can resolved by changing the MySQL Server "--secure-file-priv" option as shown in the next tutorial.

herong> mkdir Library

herong> %mysql%\bin\mysqldump --user=root --password=TopSecret \
   --tab=Library Library

mysqldump: Got error: 1290: The MySQL server is running with the
--secure-file-priv option so it cannot execute this statement when
executing 'SELECT INTO OUTFILE'

Test 3 - Dump a database into a single *.xml file. It worked correctly.

herong> %mysql%\bin\mysqldump --user=root --password=TopSecret \
   --xml --result-file=Library.xml Library

herong> type Library.xml
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="Library">
  <table_structure name="author">
    <field Field="ID" Type="int(11)" Null="YES" Key="" ... />
    <field Field="Name" Type="varchar(16)" Null="YES" Key="" ... />
    <options Name="author" Engine="InnoDB" Version="10" ... />
  </table_structure>
  <table_data name="author">
...