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.

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

• 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:

mysqldump --no-data --skip-comments --add-drop-table --skip-dump-date -r schema.sql

Export the data:

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

mysqldump --add-drop-database

mysqldump --add-drop-table

mysqldump --default-character-set=utf8

mysqldump --skip-comments

mysqldump --skip-set-charset

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.

mysqldump --order-by-primary

mysqldump -r database.sql

Dump one database

mysqldump [options] database

Dump database to a file

mysqldump [options] -r database.sql database

Dump multiple databases

mysqldump [options] -B database database

Dump all databases

mysqldump [options] -A

Schema only

mysqldump [options] --no-data

Data only

mysqldump [options] --no-create-info

Ignore comments

mysqldump [options] --skip-comments

Add DROP DATABASE commands

mysqldump [options] --add-drop-database

Add DROP TABLE commands

mysqldump [options] --add-drop-table

Include the column name in INSERT statements

mysqldump [options] --complete-insert

Each INSERT statement on its own row

mysqldump [options] --skip-extended-insert

Ignore the dump date

mysqldump [options] --skip-dump-date

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:

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

There are a few ways to avoid this error:

1. 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.
2. Create the user on the target database
3. 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:

mysqldump --no-create-info --compact mysql --tables user -w "User='username'"