mysqldump is a utility program used for making a logical backup by generating a set of SQL statements that can be executed to reproduce the original database table data and object definition. It dumps one or more database for backup or may transfer to another SQL server. It can also generate data output in different formats, such as CSV, XML, or other delimited text files.
By default, output for each dumped table consists of a CREATE TABLE statement that creates the table, followed by a set of INSERT statements that load the contents of the table.
mysqldump can be run in any of three modes:
mysqldump [options] db_name [tbl_name] ... mysqldump [options] --databases db_name ... mysqldump [options] --all-databases
MySql Dump
The “–opt” option optimizes table dumping speed and writes a dump file that is optimal for reloading speed. This option turns on –add-drop-table, –add-locks, –create-options, –disable-keys, –extended-insert, –lock-tables, –quick, and –set-charset. This option is enabled by default. To disable it, use –skip-opt.
# mysqldump -u dbuser -p[pwd] --opt databasename > database_backup.sql
MySql Dump without data (only structure)
The “–no-data” option causes table data not to be written. This is useful to dump only the CREATE TABLE statements.
# mysqldump -u dbuser -p[pwd] --no-data databasename > database_backup.sql
or
# mysqldump -u dbuser -p[pwd] -d databasename --no-data > database_backup.sql
MySql Dump without structure (no drop/create table)
The option “–no-create-db” causes ‘CREATE DATABASE’ statements not to be written. (Normally, these are added to the output automatically when –databases or –all-databases are used.)
# mysqldump -u dbuser -p[pwd] --no-create-db --no-create-info databasename > database_backup.sql
Import SQL file
mysql -u user_name -p database_name < /path/to/file.sql