mysqldump参数
日前，因接手线上项目单表数据过大，对项目进行优化，添加日报表优化查询。需要对线上数据线下处理，并将处理结果同步至线上日报表，接触到了mysqldump命令，对其参数进行简单记录说明。来源-MySQL 5.6 Reference Manual 官方文档
mysqldump，顾名思义，对mysql库表的dump备份。主要用来对mysql数据库表结构级数据的转储，可以使用该命令将表结构、数据文件形式输出，从而进行数据库表的备份迁移。
mysqldump使用格式
mysqldump -u [用户名] -p [参数1，参数2] > [文件位置，如 F:/out.sql]
mysqldump应用
mysqldump -u root -p db_calm_easy tb_poet > f:/poet.sql
完整输出结果为
;;;;;;;;;;DROP TABLE IF EXISTS `tb_poet`;;;CREATE TABLE `tb_poet` ( `poet_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id', `poet_name` varchar(50) NOT NULL COMMENT '诗人名字', `dynasty` int(11) DEFAULT NULL COMMENT '诗人朝代', `gender` tinyint(4) DEFAULT '0' COMMENT '性别 0-男 1-女', `famous_for` varchar(500) DEFAULT NULL COMMENT '著作', `birth_year` int(11) DEFAULT NULL COMMENT '生年', `death_year` int(11) DEFAULT NULL COMMENT '卒年', `create_time` datetime DEFAULT NULL COMMENT '记录时间', `last_modified_time` datetime DEFAULT NULL COMMENT '最近修改时间', PRIMARY KEY (`poet_id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='诗人表';;LOCK TABLES `tb_poet` WRITE;;INSERT INTO `tb_poet` VALUES (1,'李白',2,0,'《将进酒》，《蜀道难》',701,762,'2017-05-01 19:34:43','2017-05-01 19:34:48');;UNLOCK TABLES;;;;;;;;;
对比执行结果为标准输出格式，但实际中我们可能只需要备份表结构或表数据
备份表结构和数据
mysqldump -u root -p db_calm_easy tb_poet > f:/poet.sql
仅备份表结构
mysqldump –no-data -u root -p db_calm_easy tb_poet > f:/poet.sql
仅备份表数据
mysqldump –no-create-info -u root -p db_calm_easy tb_poet > f:/poet.sql
此外，因为操作权限的原因，可能我们也不希望备份文件中有drop此类关键字，可以指定参数定制输出样式，可以使用source命令执行备份文件。
source f:/poet.sql
参数详情-MySQL 5.6 Reference Manual 官方
参数 | 参数使用 | –add-drop-database Add DROP DATABASE statement before each CREATE DATABASE statement –add-drop-table Add DROP TABLE statement before each CREATE TABLE statement –add-drop-trigger Add DROP TRIGGER statement before each CREATE TRIGGER statement –add-locks Surround each table dump with LOCK TABLES and UNLOCK TABLES statements –all-databases Dump all tables in all databases –allow-keywords Allow creation of column names that are keywords –apply-slave-statements Include STOP SLAVE prior to CHANGE MASTER statement and START SLAVE at end of output –bind-address Use specified network interface to connect to MySQL Server –character-sets-dir Directory where character sets are installed –comments Add comments to dump file –compact Produce more compact output –compatible Produce output that is more compatible with other database systems or with older MySQL servers –complete-insert Use complete INSERT statements that include column names –compress Compress all information sent between client and server –create-options Include all MySQL-specific table options in CREATE TABLE statements –databases Interpret all name arguments as database names –debug Write debugging log –debug-check Print debugging information when program exits –debug-info Print debugging information, memory, and CPU statistics when program exits –default-auth Authentication plugin to use –default-character-set Specify default character set –defaults-extra-file Read named option file in addition to usual option files –defaults-file Read only named option file –defaults-group-suffix Option group suffix value –delayed-insert Write INSERT DELAYED statements rather than INSERT statements –delete-master-logs On a master replication server, delete the binary logs after performing the dump operation –disable-keys For each table, surround INSERT statements with statements to disable and enable keys –dump-date Include dump date as “Dump completed on” comment if –comments is given –dump-slave Include CHANGE MASTER statement that lists binary log coordinates of slave’s master –enable-cleartext-plugin Enable cleartext authentication plugin –events Dump events from dumped databases –extended-insert Use multiple-row INSERT syntax –fields-enclosed-by This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE –fields-escaped-by This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE –fields-optionally-enclosed-by This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE –fields-terminated-by This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE –flush-logs Flush MySQL server log files before starting dump –flush-privileges Emit a FLUSH PRIVILEGES statement after dumping mysql database –force Continue even if an SQL error occurs during a table dump –help Display help message and exit –hex-blob Dump binary columns using hexadecimal notation –host Host to connect to (IP address or hostname) –ignore-table Do not dump given table –include-master-host-port Include MASTER_HOST/MASTER_PORT options in CHANGE MASTER statement produced with –dump-slave –insert-ignore Write INSERT IGNORE rather than INSERT statements –lines-terminated-by This option is used with the –tab option and has the same meaning as the corresponding clause for LOAD DATA INFILE –lock-all-tables Lock all tables across all databases –lock-tables Lock all tables before dumping them –log-error Append warnings and errors to named file –login-path Read login path options from .mylogin.cnf –master-data Write the binary log file name and position to the output –max_allowed_packet Maximum packet length to send to or receive from server –net_buffer_length Buffer size for TCP/IP and socket communication –no-autocommit Enclose the INSERT statements for each dumped table within SET autocommit = 0 and COMMIT statements –no-create-db Do not write CREATE DATABASE statements –no-create-info Do not write CREATE TABLE statements that re-create each dumped table –no-data Do not dump table contents –no-defaults Read no option files –no-set-names Same as –skip-set-charset –no-tablespaces Do not write any CREATE LOGFILE GROUP or CREATE TABLESPACE statements in output –opt Shorthand for –add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset. –order-by-primary Dump each table’s rows sorted by its primary key, or by its first unique index –password Password to use when connecting to server –pipe On Windows, connect to server using named pipe –plugin-dir Directory where plugins are installed –port TCP/IP port number to use for connection –print-defaults Print default options –protocol Connection protocol to use –quick Retrieve rows for a table from the server a row at a time –quote-names Quote identifiers within backtick characters –replace Write REPLACE statements rather than INSERT statements –result-file Direct output to a given file –routines Dump stored routines (procedures and functions) from dumped databases –secure-auth Do not send passwords to server in old (pre-4.1) format –set-charset Add SET NAMES default_character_set to output –set-gtid-purged Whether to add SET @@GLOBAL.GTID_PURGED to output –shared-memory-base-name The name of shared memory to use for shared-memory connections –single-transaction Issue a BEGIN SQL statement before dumping data from server –skip-add-drop-table Do not add a DROP TABLE statement before each CREATE TABLE statement –skip-add-locks Do not add locks –skip-comments Do not add comments to dump file –skip-compact Do not produce more compact output –skip-disable-keys Do not disable keys –skip-extended-insert Turn off extended-insert –skip-opt Turn off options set by –opt –skip-quick Do not retrieve rows for a table from the server a row at a time –skip-quote-names Do not quote identifiers –skip-set-charset Do not write SET NAMES statement –skip-triggers Do not dump triggers –skip-tz-utc Turn off tz-utc –socket For connections to localhost, the Unix socket file to use –ssl Enable secure connection –ssl-ca Path of file that contains list of trusted SSL CAs –ssl-capath Path of directory that contains trusted SSL CA certificates in PEM format –ssl-cert Path of file that contains X509 certificate in PEM format –ssl-cipher List of permitted ciphers to use for connection encryption –ssl-crl Path of file that contains certificate revocation lists –ssl-crlpath Path of directory that contains certificate revocation list files –ssl-key Path of file that contains X509 key in PEM format –ssl-mode Security state of connection to server –ssl-verify-server-cert Verify server certificate Common Name value against host name used when connecting to server –tab Produce tab-separated data files –tables Override –databases or -B option –triggers Dump triggers for each dumped table –tz-utc Add SET TIME_ZONE=’+00:00’ to dump file –user MySQL user name to use when connecting to server –verbose Verbose mode –version Display version information and exit –where Dump only rows selected by given WHERE condition –xml Produce XML output
