--add-drop--database adds a drop DATABASE statement before each CREATE DATABASE statement. --add-drop-tables adds a drop TABLE statement before each CREATE TABLE statement. --add-locking uses the lock tables and unlock tables statements to refer to each table dump. Reload the dump file when it is inserted faster. --all--database,-a dumps all tables in all databases. In the same way that you use the---database option, name all databases on the command line. --allow-keywords allows you to create a keyword column name. The table name prefix should precede each column name. ---comments[={0|1}] If set to 0, prevents other information in the dump file, such as program version, server version, and host. The--skip-comments is the same as the result of the---comments=0. The default value is 1, which includes additional information. --compact produces a small amount of output. This option disables annotations and enables--skip-add-drop-tables 、--no-set-names 、--skip-disable-keys and--skip-add-locking options. --compatible=name produces output that is more compatible with other database systems or older MySQL servers. Values can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_tables_options, or no_field_options. To use several values, separate them with commas. These values have the same meaning as the corresponding options for setting the server SQL mode. This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently able to make the dump output more compatible. For example,--compatible=oracle does not map Oracle types or data types that use Oracle annotation syntax. The  --COMPLETE-INSERT,-C uses a complete INSERT statement that includes the column name. --compress,-c compresses all information that is sent between the client and the server, if both support compression. --create-option includes all MySQL table options in the CREATE TABLE statement. ---database,-b dumps several databases. WildcardNormally, mysqldump the 1th name parameter in the command line as the database name, followed by the name as the table name. With this option, it considers all the name parameters as database names. The CREATE database IF not EXISTS db_name and use db_name statements are included in the output before each new database. ---debug[=debug_options],-# [debug_options] write debug log. The debug_options string is usually ' d:t:o,file_name '. --default-character-set=charset uses the Charsetas default character set. If not specified, Mysqldump uses UTF8. --delayed-insert inserts a row using the Insert delayed statement. --delete-master-logs on the primary replication server, delete the binary log after completing the dump operation. This option automatically enables--master-data. --disable-keys,-k for each table, use/*!40000 ALTER TABLE tbl_name disable keys */; and/*!40000 ALTER TABLE tbl_name ENABLE keys */; Statement to reference an INSERT statement. This will load the dump file faster because the index is created after all rows have been inserted. This option is only suitable for MyISAM tables.  --EXTENDED-INSERT,-E uses multiple-line insert syntax that includes several values lists. This makes the dump file smaller and can speed up insertion when the file is overloaded. --fields-terminated-by=...--fields-enclosed-by=...--fields-optionally-enclosed-by=...--fields-escaped-by =...--line-terminated-by=... These options are used in conjunction with the-t option, which has the same meaning as the corresponding clause of the load DATA infile. See section 13.2, 5, "LOAD DATA infile Syntax". --first-slave,-x deprecated, now renamed to--lock-all-tables. --flush-logs,-f refresh the MySQL server log file before starting the dump. This option requires Reload permissions. Please note that if combined with--all--databaThe SE (or-a) option uses this option to refresh the log based on the database for each dump. The exception is when using--lock-all-tables or--master-data: In this case, the log is refreshed only once and refreshed after all tables have been locked. If you want to dump and refresh the log at the same time, you should use--flush-logs with--lock-all-tables or--master-data. --force,-f continues even if a SQL error occurs during the table dump. --host=host_name,-h host_name dumps data from a given host's MySQL server. The default host is localhost. --hex-blob uses hexadecimal notation to dump binary string columns (for example, ' abc ' becomes 0x616263). The columns affected are binary, VARBINARY, BLOB. --lock-all-tables,-x All tables in all databases are locked. Implemented through global read locks during the overall dump. This option automatically turns off--single-transaction and--lock-tables. --lock-tables,-l locks All tables before starting the dump. Lock the table with read local to allow the MyISAM table to be inserted in parallel. For transactional tables such as InnoDB and BDB,--single-transaction is a better option because it does not need to lock the table at all. Note that when you dump more than one database,--lock-tables locks the table for each database, respectively. Therefore, this option does not guarantee logical consistency between the tables in the dump file and the database. The dump state of different database tables can be completely different. --master-data[=value] This option writes the location and file name of the binary log to the output. This option requires Reload permissions, and binary logging must be enabled. If the option value equals 1, the location and file name are written to the dump output as the Change master statement, and if you use the SQL dump master server to set up from the server, start from the server from the correct location of the primary server binary log. If the option value equals 2,change The Master statement is written as a SQL comment. If value is omitted, this is the default action. The --master-data option enables--lock-all-tables unless you also specify--single-transaction (in this case, a global read lock is obtained only for a short time when the dump is just started. See--single-transaction again. In either case,Log-related actions occur at dump time. This option automatically turns off--lock-tables. --no-create-db,-n This option disables the CREATE database/*!32312 if not exists*/db_name statement, if the---database or--all--database option is given, is included in the output. --no-create-info,-t does not write a CREATE TABLE statement that re-creates each dump table.  --NO-DATA,-D does not export any row data information for the table, only the structure of the table is exported. You can use this option if you want to export the entire database! --opt This option is shorthand, equivalent to specifying--add-drop-tables--add-locking--create-option--disable-keys--extended-insert-- Lock-tables--quick--set-charset. It can give a quick dump operation and generate a dump file that can be loaded into the MySQL server quickly. This option is turned on by default, but can be disabled with--skip-opt. To disable only the options that are sure to be enabled with-opt, use the--skip form, such as,--skip-add-drop-tables or--skip-quick. --password[=password],-p[password] the password to use when connecting to the server. If you use the Short option form (-P), you cannot have a space between the options and the password. If the password value after the--password or-p option is ignored on the command line, you will be prompted to enter one. --port=port_num,-p port_num The TCP/IP port number used for the connection. --protocol={tcp | sockets | PIPE | The connection protocol used by MEMORY}. --quick,-q This option is used to dump large tables. It forces mysqldump to retrieve rows from the server one row at a time instead of retrieving all the rows and caching it in memory before the output. --quote-names,-q references the database, table, and column names with the ' ' character. If the server SQL mode includes the Ansi_quotes option, use the ' "' character reference name. This option is enabled by default. can be disabled with--skip-quote-names, but this option should be followed by other options, such as the ability to enable--quote-names--COMPATIBLE&NThe bsp;--result-file=file,-r file shifts the output to the given files. This option is applied in Windows because it prohibits the conversion of the new line ' \ n ' characters to ' \ r \ n ' carriage returns, return/new line sequence. --routines,-R Storing stored procedures (functions and programs) in a dump database. The output produced using the---routines contains the CREATE procedure and create function statements to recreate the subroutine. However, these statements do not include attributes, such as subroutine definitions or the creation and modification of timestamps. This means that when you overload a subroutine, the person who defines it is set to overload the user, and the timestamp equals the reload time. If you need to create a subroutine that uses the original definition and timestamp properties, do not use--routines. Instead, use a MySQL account with the appropriate MySQL database permissions to dump and reload the contents of the Mysql.proc table directly. This option is added in MySQL 5.1.2. Before this, the stored program does not dump. --set-charset adds the set NAMES default_character_set to the output. This option is enabled by default. To disable the set NAMES statement, use--skip-set-charset. --single-transaction This option issues a BEGIN SQL statement before dumping data from the server. It applies only to transaction tables, such as InnoDB and BDB, because then it dumps a consistent database state when it issues a begin without blocking any applications. When you use this option, you should keep in mind that only the InnoDB table can be dumped in a consistent state. For example, the MyISAM or heap table of any dump can still change state when using this option. The --single-transaction option and the--lock-tables option are mutually exclusive because the lock tables causes any pending transactions to be implicitly committed. This option should be used in conjunction with--quick to dump large tables. --socket=path,-s path The socket file to use when connecting to localhost (the default host). --skip--comments See description of the---comments option.  --TAB=PATH,-T Path produces a tab-separated data file. For each dump table, Mysqldump creates a Tbl_name.sql file that contains the CREATE TABLE statement that created it, and a tbl_name.txt file that contains its data. Option value is writeDirectory into the file. By default, the format of a. txt data file is to use the tab character between the column values and the new rows following each row. Formats can be clearly specified using the--fields-xxx and-line--xxx options. Note: This option is only available when mysqldump is running on the same machine as the MYSQLD server. You must have file permissions, and the server must have a permission to write files in the directory you specify. --tables Overrides---Database or-b option. All parameters following the option are treated as table names. The --triggers is a table dump trigger for each dump. This option is enabled by default; disable it with--skip-triggers.  --TZ-UTC adds set time_zone= ' +00:00 ' to the dump file so that the timestamp column can be dumped and overloaded between servers with different time zones. (without this option, the timestamp column is dumped and reloaded between the source server and the destination server with the local time zone). --TZ-UTC can also protect changes due to daylight saving time. --TZ-UTC is enabled by default. To disable it, use--SKIP-TZ-UTC. This option is added in MySQL 5.1.2. --user=user_name,-u user_name the MySQL user name to use when connecting to the server. --verbose,-v verbose mode. Print out the details of the program action.  --VERSION,-V Displays the version information and exits. --where= ' Where-condition ',-w ' where-condition ' only dumps the record selected for the given where condition. Note that if the condition contains a special space or character for the command interpreter, you must refer to the condition. For example:--where= "sensorid=11 and Fieldid=0"--where= "name like '%li% '" Write dump output as XML. You can also use the--var_name=value option to set the following variables: Max_allowed_packet The maximum size of the buffer between client/server communication. The maximum is 1GB. Net_buffer_length the initial size of the buffer between client/server communication. When you create a multiline INSERT statement (as with options--extended-insert or--opt), Mysqldump creates a row of length up to net_buffer_length. If you add this variable, you should also ensure that the net_buffer_length variable in the MySQL server is at leastSo big. You can also use the--set-variable=var_name=value or-o var_name=value syntax to set variables. However, the use of this syntax is now deprecated.
Source: http://blog.itpub.net/22664653/viewspace-729939/
The use of mysqldump
