本文介绍了mysqldump命令，主要用于MySQL数据库的表结构和数据备份。通过不同参数，可以实现备份全部内容、仅备份结构或仅备份数据。例如，`mysqldump -u root -p db_calm_easy tb_poet > f:/poet.sql`将备份表结构和数据，而`--no-data`和`--no-create-info`参数则分别用于只备份结构或数据。了解这些参数有助于更灵活地进行数据库管理。
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完整输出结果为
-- MySQL dump 10.13 Distrib 5.5.23, for Win64 (x86)
--
-- Host: localhost Database: db_calm_easy
-- ------------------------------------------------------
-- Server version 5.5.23
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `tb_poet`
--
DROP TABLE IF EXISTS `tb_poet`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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='诗人表';
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tb_poet`
--
LOCK TABLES `tb_poet` WRITE;
/*!40000 ALTER TABLE `tb_poet` DISABLE KEYS */;
INSERT INTO `tb_poet` VALUES (1,'李白',2,0,'《将进酒》，《蜀道难》',701,762,'2017-05-01 19:34:43','2017-05-01 19:34:48');
/*!40000 ALTER TABLE `tb_poet` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2017-05-31 19:37:12
对比执行结果为标准输出格式，但实际中我们可能只需要备份表结构或表数据
备份表结构和数据
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 |
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
