Dumping table structure and data

Mysqldump, dumping table structure and data

Utility to dump a database or a collection of database for backup or for transferring the data to another SQL Server (not necessarily a MySQL Server ). the dump will contain SQL statements to create the table and/or populate the table.
Using mysqldump, you can copy the data in a database to another database (not necessarily MySQL database, the information generated by running the dump command contains the SQL command and create/drop table commands.

If you are doing a backup on the server, you shoshould consider using the mysqlhotcopy instead. See section
If you want to make a copy on the server, you can use the mysqlhotcopy command instead. See Chapter ‑
Mysqlhotcopy, copying MySQL Databases and tables.
Mysqlhotcopy copies MySQL database and tables.
The signature method is as follows ﹕
Shell> mysqldump [Options] database [Tables]
Or mysqldump [Options] -- databases [Options] db1 [DB2 DB3. ..]
Or mysqldump [Options] -- all-databases [Options]

If you don't give any tables or use the -- databases or -- all-databases, the whole database (s) will be dumped.
If you do not specify the table name or use -- databaeses and -- all-databases, all the information will be exported.

You can get a list of the options your version of mysqldump supports by executing mysqldump -- help.
You can use mysqldump -- help to list the selections for your mysqldump version.

Note that if you run mysqldump without -- quick or -- opt, mysqldump will load the whole result set into memory before dumping the result. this will probably be a problem if you are dumping a big database.
Note: If you use the mysqldump command without the -- quick or -- opt option, mysqldump puts all the resources in the memory before generating the data. If you export a large databaes, it may be a problem.

Note that if you are using a new copy of the mysqldump program and you are going to do a dump that will be read into a very old MySQL server, you shoshould not use the -- opt or-e options.

If you use a new mysqldump program and place the exported data in a very popular version of MySQL server, you cannot choose -- opt or-e.

Mysqldump supports the following options:
Mysqldump supports the following options:
-- Add-locks
Add lock tables before and unlock table after each table dump. (To get faster inserts into mysql .)
-- Add-drop-Table selection locks tables before loading data, unlock table after the data is fully loaded (as the data can be quickly added to the Mysql Data Source)

-- Add-drop-table
Add a drop table before each create statement.
-- Add-drop-table: the drop table command is added before each create command.

-A, -- all-Databases
Dump all the databases. This will be same as -- databases with all databases selected.
-A or-all-databases outputs all information. This is the same as the-databases option. It selects all resources in the database.

-A, -- all
Include all mysql-specific create options.
-A, -- all will contain all mysql-specific create selections.

-- Allow-keywords
Allow creation of column names that are keywords. This works by prefixing each column name with the table name.
-- Allow-keywords allows a keyword to be used for the specified bit name.
-C, -- complete-insert
Use complete insert statements (with column names ).
-C, -- complete-insert: Use the complete new command (including the complete bit name "values)

-C, -- Compress
Compress all information between the client and the server if both support compression.
-C, -- compress if both the client and the client support the response, all the generated information will be overwritten.
-B, -- databases
To dump several databases. note the difference in usage. in this case no tables are given. all name arguments are regarded as database names. use db_name; will be encoded in the output before each new database.
-B, -- databases dumped multiple data records for dump. Note the difference in this usage. No table name is given in this selection. All data is stored as the data source name. The metadata name will be included before each new item is generated.

-- Delayed
Insert rows with the insert delayed command.
Add the insert delayed command when adding a notebook.

-E, -- extended-insert
Use the new multiline insert syntax. (gives more compact and faster inserts statements .)
-E, -- extended-insert use this new multi-row addition method. (Generate more efficient new commands)

-#, -- Debug [= option_string]
Trace usage of the Program (for debugging ).
-#, -- Debug the usage of tracing mysqldump (for debugging)
-- Help
Display a help message and exit.
-- Help quit indicates the help information and exits.
-- Fields-terminated-by =...
-- Fields-enclosed-by =...
-- Fields-optionally-enclosed-by =...
-- Fields-escaped-by =...
-- Lines-terminated-by =...
These options are used with the-T option and have the same meaning as the corresponding clses for load data infile. See section 6.4.9 load data infile syntax.
-F, -- flush-logs
Flush log file in the MySQL server before starting the dump.
-F, -- force,
Continue even if we get a SQL error during a table dump.
-H, -- Host = ..
Dump data from the MySQL server on the named host. The default host is localhost.
-L, -- lock-tables.
Lock all tables before starting the dump. the tables are locked with read local to allow concurrent inserts in the case of MyISAM tables. please note that when dumping multiple databases, -- lock-tables will lock tables for each database separately. so using this option will not guarantee your tables will be logically consistent between databases. tables in different databases may be dumped in completely different states.