mysqldump Command Use detailed

Mysqldump is a database backup program

Usage: mysqldump [options] [db_name [Tbl_name ...]

Description: Mysqldump is the work of a client-side logical backup that can be restored on other MySQL servers by a backup of the SQL file.

For backup, you need at least SELECT permission on the table, the backup view needs to change the account has Show view permission, the trigger needs trigger. If you want to lock the table, you cannot use the--single-transaction option. Other permissions are not currently listed.

If you want to restore, you need the corresponding execution permissions, such as the CREATE table, you need to create permissions on the library.

Mysqldump export can include ALTER DATABASE and related changes, including exporting metadata, changing character encoding, and when importing the corresponding file, the corresponding permission changes will also affect.

If the tables backed up on the server are MyISAM, you can also use Mysqlhotcopy to complete the task.

Note: If you use the default shell in a Windows environment, the redirected output file is UTF-16 encoded. Shell> mysqldump [Options] > Dump.sql

However, UTF-16 is a character set that is not allowed to be connected (see 10.1.5 unit, ' Connection Character sets and collations '), so the export file cannot be used for normal loading. To solve this problem, use the--result-file option to create an ASCII format file: shell> mysqldump [Options]--result-file=dump.sql

From performance and scalability considerations, the benefits of mysqldump include the ability to view or edit files easily before output, and you can clone the database with the DBA or perform a small test of the data in the production environment. This is not a very fast backup or a highly scalable scenario. That is, for different sizes of data need to arrange a reasonable time, when you need to restore the speed is not fast, because I need to re-I/O, create indexes and so on.

For large amounts of data, physical backups may be more appropriate because they can be restored quickly.

• If your table is dominated by the InnoDB engine, consider using the MySQL mysqlbackup command. Because he offers the best InnoDB backup: It can also back up other storage engines such as MyISAM and others, providing more backup parameters to choose from. See the manual 25.2 "MySQL Enterprise Backup Overview" for details

　　　　• If your table is dominated by MyISAM engines, consider using mysqlhotcopy instead, which may be better than mysqldump performance, see Mysqlhotcopy Manual for details.

Mysqldump can export data at the row level, or you can load the entire table into memory one-time export, and if you import all of the data into memory, you may face insufficient memory buffers. If you want to export data at the row level, use the--quick option (or--opt, which supports--quick). The--OPT option is the default option, so in order to guarantee the memory buffer, use--skip-quick.

If you use a higher version of mysqldump to back up data from a low-version MySQL server, use--skip-opt instead of the--opt or-extended-insert option.

For more information about mysqldump, refer to the manual 7.4 unit "useing mysqldump for Backups" Here are a few common options: back up one or more tables, back up a full database, back up all databases on a MySQL server

>shell mysqldump [Options] db_name [tbl_tables ...]

>shell mysqldump [options]--databases db_name ...

>shell mysqldump [Options]--all-databases

When backing up the entire database, do not use name and tables, specify db_name directly or use--databases or--all-databases

Mysqldump the INFORMATION_SCHEMA and Performance_schema databases are not backed up by default. If you want to back up the content, you need to specify the use--skip-lock-tables option. You can also specify a database name when using--databases. Before MySQL5.5, you specify, mysqldump will not back up the above content.

Mysqldump does not back up the Performance_schema database.

Before the MySQL5.5.25 version, Mysqldump does not back up the General_log or Slow_query_log tables of the MySQL library. In 5.5.25, the backup file includes the rebuild instructions. The log content in the table is not backed up.

Mysqldump also does not back up MySQL cluster nbinfo database information.

You can use the mysqldump--help command to view a list of options.

There are some abbreviations for the option groups:

• Use--opt directives equivalent to--add-drop-table,--add-locks,--create-options,--disable-keys,--extended-insert,--lock-tables,-- Quick, A and--set-charset, these options are the default, because--opt is the default option to use.

• Use--compact equivalent to colleagues using--skip-add-drop-table,--skip-add-locks,--skip-comments,--skip-disable-keys, and-- Skip-set-charset.

To not affect option group other options, use the group's--skip-xxx from (--skip-opt or--skip-compack) He can also select some options within the option group, for example:

• If you need to select an option that--opt option group does not take effect, use the--skip plus option. To avoid writing memory, use--opt--skip-extend-insert--skip-quick (General--skip-extend-insert--skip-quick is disabled by default because--OPT is enabled by default)

· Reversal--Choose to disable all features and table locks except the index, using--skip-opt--disable-keys--lock-tables

Specifying is important when you need to enable or disable certain options in an option group, because these options will always take effect when the process runs. For example,--disable-keys--lock-tables--skip-opt will not work. It's like--skip-opt acting on himself.

Now let's talk about the options for mysqldump:

· --help: Displays help information and exits the program

· --add-drop-databases: Write drop databases before create database. This option is typically used in conjunction with--all-databases or--database, because the CREATE DATABASE statement executes at least one option selected.

· --add-drop-table: Write the drop table command before each CREATE table

· --add-drop-tigger: Writes the drop Tigger before each create Tigger. Note: This option is available only for mysqldump as a MySQL cluster.

· --add-locks: The lock table is performed during backup, and the release table lock is completed after backup. This avoids the ability to write data quickly to cause dirty reads.

· --all-databases,-A: Backs up all tables in all libraries, with the same effect as--database followed by all library names.

· --all-tablespaces,-y: Use the Ndbcluster table to add table spaces for all table backup statements. This information is not included in the output of the mysqldump. This option is only available for MySQL cluster currently related tables.

· --allow-keywords: A keyword is allowed to be used as a column name, but it is prefixed with a table name before the column name.

· --apply-slave-statements: The option added after MySQL5.5.3, to use--dump-slave from the server, to stop backing up data from the primary server at backup time.

· --Bind-address=ip_address: When you have multiple network card devices on a single host, select one of the specified NICs to connect to the MySQL server. This option only supports MySQL clusters supported by the mysqldump version, which is not supported in the 5.5 stable version.

· --character-sets-dir=dir_name: The folder will use the character set of the installation database.

· --comments,-i: The MySQL version, server version, host information, etc. are added to the backup file by default. If you do not need this information, you can use--skip-comments.

· --compact: Output More relevant information, this option supports the use of--skip-drop-tables--skip-add-locks--skip-comments--skip-disable-keys-- Skip-set-charset option.

· --compatible=name: Generates backup files that are compatible with other versions of the database. Name can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_table_options, or No_field_opti ONS. If you need to fill in multiple, use ', ' delimited, the backup file will be developed for different versions of the SQL schema.

This option does not guarantee compatibility with other servers. It allows only those currently available SQL mode values to be more compatible with the dump output. For example,-compatible=oracle incompatible data types are mapped to Oracle types or are commented using Oracle syntax.

This option requires MySQL version 4.1.0 or more

· --COMPLETE-INSERT,-C: Contains the column name when the INSERT statement occurs.

· --COMPRESS,-C: In compression algorithms supported by both the client and the server, select the compressed data for communication.

· --create-options: The CREATE TABLE statement contains all table options.

· --database,-b: Backing up the development database. In general, when mysqld treats the name parameter, the first parameter acts as the database name, followed by the table name. However, when you use this option, all the name parameters are backed up as database names. The CREATE database and use directives are added before every backup of the databases.

· --debug=[debug_options],-# [debug_options]: Write debug log, common debug_options use string d:t:o,filen_ame, default value is d:t:o,/tmp/ Mysqldump.trace

· --debug-check: Prints debug information when the program exits.

· --debug_info: Print memory on program exit, CPU usage status to debug information.

· --default-auth=plugin: Tips for using the client authentication plug-in. This feature 5.5.9 start joining.

· --default-character-set=charset_name: Use Charset_name as the character set.

· --Defaults-extra-file=file_name: reads the options file as a global option (Unix environment) and takes effect before the user option. If the file does not exist or is inaccessible due to permissions, an error is generated. Absolute paths must be used before MySQL5.5.8. Relative paths can be used after 5.5.8.

· --defaults-file=file_name: Use only the default options file. If the file does not exist or is inaccessible due to permissions, an error is generated. Absolute paths must be used before MySQL5.5.8. Relative paths can be used after 5.5.8.

· --defaults-group-suffix=str:read not only uses the usual group names, but also supports defining groups. For example: Mysqldump typically reads [client] and [mysqldump] groups. The [Client_ohter] and [mysqldump_other] groups are also read if the--defaults-group-suffix=str,mysqldump is given.

· --delayed-insert: Insert delayed is used when insert

· --delete-mastelogs: In the master-slave architecture, the binary log from server backup is removed from the primary server using the purge binary log instruction. This option will automatically enable--master-data

· --disable-keys,-k: Insert in each table will use/*!40000 ALTER TABLE tbl_name disable KEYS *//*!40000 ALTER TABLE Tbl_name ENABLE KE YS * * Package up. This makes the restore time faster because the index information is not established until after all insert operations, and this option only affects the nonunique index of the MyISAM table and does not take effect for other engines.

· --dump-date: If the--comments option is given, mysqldump will add-dump completed on date at the end of the backup file. However, due to the time it takes to back up, a different date is generated. --dump-date and--skip-dump-date control how dates are recorded, defaults to--dump-date, dates are included in comment, and--skip-dump-date supports blocking time printing.

· --dump-slavel=[value]: This option is similar to--master-data (except that it contains a master-slave relationship), which causes the backup output to the binary log from the server (including the change Master to instruction) to be replaced by the master server.

--dump-slave: Coordinating a master-slave server is like using the--master-data option. But it can cause--master-data to fail. The value of this option is like the behavior of suspending--master-data (not passing in a value or 1,change master to, set to 2 causes SQL to be wrapped) and has the same effect. This option causes Mysqldump to stop slave SQL threads before the backup, and then restart. It can also be combined with--dump-slave--applay-slave-statements and--include-master-host-port. This attribute is introduced in MySQL5.5.3.

· --EVENTS,-E: Use the event Scheduler to complete the backup, but requires database-related permissions.

· --Extended-insert,-e: Writes a list to insert, which is a single statement that inserts multiple values, which makes the backup file smaller.

· --fields-terminated-by= ...,--fields-enclosed-by= ...,--fields-optionally-enclosed-by= ...,--fields-escaped-by= ... : These options work with the--tab tab and have a corresponding meaning for the fields column as load TATA INFILE

· --first-slave: Deprecated! Use--lock-all-tables instead. This directive is enabled in MySQL5.5.3.

· --FLUSH-LOGS,-F: Refreshes the log of the MySQL server before the backup, this option requires reload permissions. If you use this option in a--all-databases scenario, the logs is refreshed at each database. --lock-all-tables,--master-data, or (as of MySQL 5.5.21)--single-transaction: The log will only be refreshed once and the table lock will be added when executed. If you want to refresh the log at backup time,--lock-all-tables,--master-data, or--single-transaction. Use with this option.

· --flush-privileges: Add the Execute Flush privileges directive in the backup file. You should use this option if you need to back up the MySQL library to reply to certain permissions.

· --FORCE,-F: The backup table will continue to execute when errors occur. One of the usage scenarios for this option is that there was an error while backing up because the table was deleted. If you do not use this option, Mysqldump has an error message. If used, the error message will be printed and the SQL instructions will continue to execute.

· --enable-cleartext-plugin: Turn on the cleartext plugin. This attribute is introduced when MySQL5.5.7.

· --host=name,-h HOST_NAME: From the given hostname backup, if not given, the default is localhost

· --hex-blob: The package containing binary information will be output by hexadecimal (for example, ' ABC ', will be output as 0x616263, the affected data type: binary, VARBINARY, the Blob types, and BIT

· --include-master-host-port: The change master to instruction on the slave server will be backed up by the--dump-slave option, Master_host,master_port refers to the TCP/IP port. This feature is introduced in MySQL5.5.3

· --ignore-table=db_name.tbl_name: Ignore the table to be backed up, and if you omit multiple, you need to use this option more than once, this option can also ignore view.

· --insert-ignore: Use Insert ignore instead of insert

· --lines-terminated-by= ... : This option uses the corresponding--tab as the lines column in load DATA infile

· --lock-all-tables,-x: Adds a global lock when backing up. This option automatically turns off--single-transaction and--lock-tables.

· --lock-tables,-L: For each database, all tables will be locked before the backup. The MyISAM table will be applied to read LOCAL. --single-transaction may be a better choice for an engine with things like InnoDB.

· --log-error=file_name: The error is logged to the specified file_name and is not logged by default.

· --master-data[=value]: Use this option to dump the primary server as a backup file, which can still be used as the primary server when restoring with another server. However, these servers should be restored from the server before restoring the primary server. If the option value is 2,change MASTER to is written as a SQL comment, it is informational only; it does not affect when the dump file is restored. If the option value is 1, the declaration is not written and the comment takes effect when the dump file is reloaded. If no option value is specified, the default value is 1. This option requires reload and you want to turn on the binary log.

The--master-data option turns off--lock-tables by default, and--lock-all-tables is turned on by default, except--lock-all-tables. In one case: it will take some time to add a global read lock before starting the backup. As for how, logs will record the dump moment in detail.

A slave server may also be established if the backup primary server contains a slave server. You can use--dump-slave when MySQL5.5.3 or later, but overwrite the--master-data option if it is present. Before MySQL5.5.3, use the following rules to back up:

1. Stop getting the current state from the server

mysql> STOP SLAVE Sql_thread;
mysql> SHOW SLAVE STATUS;

2. The binary log of the new slave server corresponding to the primary server from the content output from the show SLAVE status directive should begin replacing relay_master_log_file and Exec_master_log_pos to represent file_name and File _pos

3. Backing up from the server

shell> mysqldump--master-data=2--all-databases > DumpFile

4. Restart the slave server

Mysql> START SLAVE;

5. Importing data from a new server

shell> MySQL < dumpfile

6. Setting the primary server information on the new slave server

Mysql> Change MASTER to

-Master_log_file = ' file_name ', master_log_pos = File_pos;

　　　　　　　　The change master to instruction may also require additional parameters, such as master_host pointing to the primary server that is currently from the server. Add other necessary parameters

· --no-autocommit: Turns off autocommit when insert is executed, i.e. autocommit = 0, using commit

· --no-create-db,-N: The Create DATABASE directive is not executed when the--databases or--all-databases option is given

· --no-create-info,-T: In creating a backup representation, do not execute create TABLE. Note: However, this option does not exclude mysqldump when the file group and tablespace are recorded in the log file, but you can use--no-tablespaces to do so.

· --no-data,-D: Information is not logged in the table. This is useful when you just want to create a table (for example, you just want to back up the table structure)

· --no-defaults: The option file is not read and can be used to avoid reading if the program fails because of an unknown file read.

· --no-set-names,-N: This option works the same as--skip-set-charset.

· --no-tablespaces,-y:create LOGFILE Group and the CREATE tablespace directive will not be written into the backup file.

· --opt: Equivalent to--add-drop-table--add-locks--create-options--disable-keys--extended-insert--lock-tables--quick-- Set-charset, which provides a quick backup and restore capability.

· --order-by-primary: When you back up a table, it is sorted based on the primary key or the first non-repeating index. If the index exists, this makes it convenient to import from a MyISAM table to the InnoDB table, but this also makes the backup time to be calculated longer.

· --password[=password],-p[password]: the password to connect to the database. If you use a short label, do not have a space between the password and p, if you omit the value of password, mysqldump will prompt for input. For the sake of safety, of course you can also provide in the options file, avoid input when using.

· --pipe,-W: In Windows, the MySQL server is connected through a pipe. This option provides only a pipe connection provided by the MySQL server.

· --plugin-dir=dir_name: The folder of your plugin. If--default-auth, mysqldump will not seek.

--port=port_num, the port number used by the-P PORT_NUM:TCP/IP.

· --print-defaults: Prints the program name and options in the output file.

· --protocol={tcp| Socket| pipe| MEMORY}: With this option you can specify the transfer mode.

· --quick,-Q: Useful when backing up a table with a larger amount of data. The data is read into memory and a memory buffer exists before the output is complete.

· --quote-names,-Q: Escapes characters will be escaped, "'" ' "will be swapped", if it is ansi_quotes SQL mode support, will be "" ", this option is started by default, if you need to close, you can use--skip-quote-names.

· --replace: Replace insert with replace.

· --result-file=file_name,-R file_name: Direct output to the specified file. Creates a result file and its previous content overrides, generating a dump even if an error occurs. This option should be used on Windows to prevent line break "\ n" characters from being converted to "\ r \ n" carriage return/line feed sequence

'--routines,-r: Includes dump database output of stored procedures (procedures and functions). This option requires the SELECT permission of the Mysql.proc table. Using, the output generated by the routine contains the creation process and the CREATE FUNCTION statement creation routines. However, these statements do not include attributes, such as routines to create and modify timestamps, so when routines. Reload, create timestamp equals reload time. If you need to create a routine and the original timestamp property, do not use the routine. Instead, dump and reload the contents of MySQL. Proc Direct table, use MySQL account to have the appropriate MySQL database privileges.

· --set-charset: Add set NAMES specified in the output file, this option is default, if you want to turn off the use of--skip-set-charset.

· --shared-memory-base-name=name: On Windows, use shared memory for the name of the shared memory used to connect to the local server. The default value is MySQL. Shared memory names are case-sensitive. The server must start-the shared memory option to enable the shared memory connection.

· --single-transaction: This option sets the transaction isolation mode to repeatedly read and send an SQL statement that starts the transaction server and puts the data. It is useful to have only innodb, such as transactional tables, because it dumps a consistent state when a database transaction starts when the publication does not block any applications. When using this option, you should remember that only the InnoDB table is in a consistent state. For example, any MyISAM table or memory dump used in this option may still change state.

--Separate transaction dumps are in the process to ensure a valid dump file and binary log coordinates (the correct table content, no other connections should use the following statement: ALTER table, create TABLE, delete table, rename table, truncate. The aim is to maintain consistency. This will also add a table lock.

This option supports the MySQL cluster, and the results are not guaranteed because the Ndbcluster consistent storage engine supports only the read_committed transaction isolation level. You should always use NDB for backup and recovery.

When backing up a large table, combine--quick with this option.

· --skip-comments: Reference--comments

· --skip-opt: Refer to--opt.

· --socket=path,-S path: When communicating with the native computer, the UNIX system communicates based on Unix sock file, and Windows is based on pipe.

· --ssl*: Encrypting communication Data using SSL

· --tab=dir_name,-t dir_name: A data file that produces a tab-delimited text format. For each dump table, Mysqldump creates a tbl_name. SQL file, which contains CREATE TABLE statements, and server Tbl_name writes. TXT file, which contains the data for the option value is the directory where the files are written.

This option should only be used when the MySQL server is backed up.

· After--tables:--databases, this option causes Mysqldump to treat the following parameters as table names.

· --triggers: The trigger is exported when each table is exported, but requires Tigger permissions, which is enabled by default.

· --TZ-UTC: Enabled by default, disable can use--SKIP-TZ-UTC. This is the time zone information.

· --user=user_name,-u user_name: Username used to connect to MySQL server

· --verbose,-v:verbose mode, this mode will output more information about the program.

· --version,-V: Displays the version information and exits the program.

· --where= ' Where_condition ',-W ' where_condition ': Use the Where condition when backing up

· --xml,-X: Output is in XML format

　　　　

Mysqldump command Use detailed