====== mysqldump ======

  * [[MySQL]]
  * [[mysqladmin]]
  * [[mysqlhotcopy]]
  * [[mysqlimport]]

** Options are processed in the order given.**

Dump a MySQL database that will, on restore, drop the database and it's tables and then recreate them.  It will also ignore comments, and set the character set to UTF8.

<code>
mysqldump --add-drop-database --add-drop-table --default-character-set=utf8 --skip-comments --order-by-primary
</code>

==== Clean Database Dumps ====

  * Two separate exports: schema, then data
  * Skip comments
  * Skip dump date
  * Add DROP TABLE
  * Order INSERTs by primary key
  * Include column name in INSERT statements
  * One INSERT statement per row

Export the schema:

<code>
mysqldump --no-data --skip-comments --add-drop-table --skip-dump-date -r schema.sql
</code>

Export the data:

<code>
mysqldump --skip-comments --add-drop-table --skip-dump-date --no-create-info --complete-insert --order-by-primary --skip-extended-insert -r data.sql [database]
</code>

==== Restore Options ====

== Add ''DROP DATABASE'' ==

<code>
mysqldump --add-drop-database
</code>

== Add ''DROP TABLE'' ==

<code>
mysqldump --add-drop-table
</code>

==== Schema Options ====

== Character set ==

<code>
mysqldump --default-character-set=utf8
</code>

== No comments ==

<code>
mysqldump --skip-comments
</code>

== Don't set character encoding ==

<code>
mysqldump --skip-set-charset
</code>

== Order table dumps by primary key ==

Dump each table's rows sorted by its primary key, or by its first unique index, if such an index exists. **This is useful when dumping a MyISAM table to be loaded into an InnoDB table**, but will make the dump operation take considerably longer.

<code>
mysqldump --order-by-primary
</code>
==== File and Output Options ====

== Output File ==

<code>
mysqldump -r database.sql
</code>

==== Database Options ====

**Dump one database**

<code>
mysqldump [options] database
</code>

**Dump database to a file**

<code>
mysqldump [options] -r database.sql database
</code>

**Dump multiple databases**

<code>
mysqldump [options] -B database database
</code>

**Dump all databases**

<code>
mysqldump [options] -A
</code>

** Schema only **

<code>
mysqldump [options] --no-data
</code>

** Data only **

<code>
mysqldump [options] --no-create-info
</code>

** Ignore comments **

<code>
mysqldump [options] --skip-comments
</code>

** Add DROP DATABASE commands **

<code>
mysqldump [options] --add-drop-database
</code>

** Add DROP TABLE commands **

<code>
mysqldump [options] --add-drop-table
</code>

** Include the column name in INSERT statements **

<code>
mysqldump [options] --complete-insert
</code>

** Each INSERT statement on its own row **

<code>
mysqldump [options] --skip-extended-insert
</code>

** Ignore the dump date **

<code>
mysqldump [options] --skip-dump-date
</code>
==== Views ====

When ''mysqldump'' dumps a database containing views, it will also export the user that created the view.

When restoring the database dump, the views will be created successfully, but they also can only be executed by the same user.

Trying to access the view using an SQL statement, or trying to dump the database will throw an error:

<code>
ERROR 1449 (HY000): The user specified as a definer ('username'@'hostname') does not exist
</code>

There are a few ways to avoid this error:

  - Delete the lines containing ''DEFINER=`username`@`hostname` SQL SECURITY DEFINER'' from the dump file.  This will create the views as the same user that is executing the restore.
  - Create the user on the target database
  - Modify the user to be something else

If you want to recreate the user, here's how to get the statement to insert into the ''mysql'' database:

<code>
mysqldump --no-create-info --compact mysql --tables user -w "User='username'"
</code>