no way to compare when less than two revisions

Differences

This shows you the differences between two versions of the page.


—mysqldump [2015/06/01 22:57] (current) – created - external edit 127.0.0.1
Line 1: Line 1:
 +====== 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>

Trace:

Navigation
QR Code
QR Code mysqldump (generated for current page)