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
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:
DEFINER=`username`@`hostname` SQL SECURITY DEFINER
from the dump file. This will create the views as the same user that is executing the restore.
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'"