mysqldump
是 MySQL 数据库附带的一个工具命令,使用此命令可以把指定的数据库导出为文件,所以常用来做为 MySQL 数据库的备份操作。除了做备份,我个人经常还会把它用作本地开发环境和线上环境数据库的数据同步工具。
mysqldump 导出数据库
这是此命令最常用的操作,使用方式如下:
mysqldump -h192.168.1.100 -uzzxworld -p123456 -P3306 --all-databases > db.sql
上面示例中各选项的用途如下:
-h
指定要连接的 MySQL 服务器地址。-u
指定账号名称。-p
指定密码。-P
指定端口,如果用的默认端口 3306,此选项可以省略。--all-databases
意思是导出所有的数据库。
mysqldump
命令默认会把导出数据直接输出,所以这里用了 Linux 系统的 >
符号,把数据转向输出给了 db.sql 文件。
如果只想导出指定的几个数据库,可以把 --all-databases
选项换成 --databases
选项,然后指定要导出的数据库名称:
mysqldump -h192.168.1.100 -uzzxworld -p123456 --databases db1 db2 > db.sql
如果只想导出其中一个数据库,直接给数据库名称即可:
mysqldump -h192.168.1.100 -uzzxworld -p123456 db1 > db.sql
mysqldump 导出表
在本文开头有提到,我会用此命令来同步线上和本地开发环境的数据库,这主要是从线上同步数据到本地。当线上数据库很大时,使用上面导出整个数据库的方式会十分耗时。所以我使用的是导出表的功能。看看下面这个例子:
mysqldump -h192.168.1.100 -uzzxworld -p123456 wordpress wp_posts
这只会导出 wordpress 数据库中的 wp_posts 表的建表语句和数据。
如果碰到单个表数据也很大的时候,我还可以选择只导出其中几条数据:
mysqldump -h192.168.1.100 -uzzxworld -p123456 wordpress wp_posts --where='id=1'
通过 --where
选项,可以指定导出数据的筛选条件,让数据的导出操作更加的灵活和方便。
压缩导出和导入
在 Linux 系统上,mysqldump
命令可以搭配其他压缩命令来减小导出数据的体积。之前我分享了一篇配合 gzip 来压缩导出和导入 MySQL 数据库的文章,有兴趣可以前往了解一下,这里就不再重复了。文章链接如下:
MySQL 数据的压缩导出和导入使用 gzip 配合 mysqldump 命令,以压缩方式导出/导入 MySQL 数据库。
mysqldump 选项参数列表
mysqldump
命令提供的选项比较多,上面的例子只是使用了其中常用的几个。下面的表格是我收集的所有选项,大部分我都还没用过,所以暂时也没翻译成中文。如果你使用过其中一些还未翻译的参数,欢迎在评论中分享。
缩写 | 完整名称 | 说明 |
---|---|---|
-A |
--all-databases |
导出所有数据库。 |
-Y |
--all-tablespaces |
Dump all the tablespaces. |
-y |
--no-tablespaces |
Do not dump any tablespace information. |
--add-drop-database |
在导出数据的每个建库语句前添加删除数据库的 SQL。 | |
--add-drop-table |
在导出数据的每个建表语句前添加删除数据表的 SQL。 | |
--add-drop-trigger |
在导出数据的每个创建触发器的语句前添加删除触发器的 SQL。 | |
--add-locks |
在 INSERT 语句前后添加锁操作。 | |
--allow-keywords |
Allow creation of column names that are keywords. | |
--apply-slave-statements |
Adds 'STOP SLAVE' prior to 'CHANGE MASTER' and 'START SLAVE' to bottom of dump. | |
--character-sets-dir= name |
Directory for character set files. | |
-i |
--comments |
Write additional information. (Defaults to on; use --skip-comments to disable.) |
--compatible= name |
Change the dump to be compatible with a given mode. By default tables are dumped in a format optimized for MariaDB. Legal modes are: ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, no_field_options. One can use several modes separated by commas. Note: Requires MariaDB server version 4.1.0 or higher. This option is ignored with earlier server versions. | |
--compact |
Give less verbose output (useful for debugging). Disables structure comments and header/footer constructs. Enables options --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys --skip-set-charset. | |
-c |
--complete-insert |
Use complete insert statements. |
-C |
--compress |
Use compression in server/client protocol. |
--copy-s3-tables |
If 'no' S3 tables will be ignored, otherwise S3 tables will be copied as Aria tables and then altered to S3 | |
-a |
--create-options |
Include all MariaDB specific create options. (Defaults to on; use --skip-create-options to disable.) |
-B |
--databases |
指定要导出的数据库。 |
-# |
--debug[=#] |
This is a non-debug version. Catch this and exit. |
--debug-check |
Check memory and open file usage at exit. | |
--debug-info |
Print some debug info at exit. | |
--default-character-set= name |
Set the default character set. | |
--delayed-insert |
Insert rows with INSERT DELAYED. | |
--delete-master-logs |
Delete logs on master after backup. This automatically enables --master-data. | |
-K |
--disable-keys |
/*!40000 ALTER TABLE tb_name DISABLE KEYS */; and /*!40000 ALTER TABLE tb_name ENABLE KEYS */; will be put in the output. (Defaults to on; use --skip-disable-keys to disable.) |
--dump-slave[=#] |
This causes the binary log position and filename of the master to be appended to the dumped data output. Setting the value to 1, will printit as a CHANGE MASTER command in the dumped data output; if equal to 2, that command will be prefixed with a comment symbol. This option will turn --lock-all-tables on, unless --single-transaction is specified too (in which case a global read lock is only taken a short time at the beginning of the dump - don't forget to read about --single-transaction below). In all cases any action on logs will happen at the exact moment of the dump.Option automatically turns --lock-tables off. | |
-E |
--events |
导出事件。 |
-e |
--extended-insert |
Use multiple-row INSERT syntax that include several VALUES lists. (Defaults to on; use --skip-extended-insert to disable.) |
--fields-terminated-by= name |
Fields in the output file are terminated by the given string. | |
--fields-enclosed-by= name |
Fields in the output file are enclosed by the given character. | |
--fields-optionally-enclosed-by= name |
Fields in the output file are optionally enclosed by the given character. | |
--fields-escaped-by= name |
Fields in the output file are escaped by the given character. | |
-F |
--flush-logs |
Flush logs file in server before starting dump. Note that if you dump many databases at once (using the option --databases= or --all-databases), the logs will be flushed for each database dumped. The exception is when using --lock-all-tables or --master-data: in this case the logs will be flushed only once, corresponding to the moment all tables are locked. So if you want your dump and the log flush to happen at the same exact moment you should use --lock-all-tables or --master-data with --flush-logs. |
--flush-privileges |
Emit a FLUSH PRIVILEGES statement after dumping the mysql database. This option should be used any time the dump contains the mysql database and any other database that depends on the data in the mysql database for proper restore. | |
-f |
--force |
Continue even if we get an SQL error. |
--gtid |
Used together with --master-data=1 or --dump-slave=1.When enabled, the output from those options will set the GTID position instead of the binlog file and offset; the file/offset will appear only as a comment. When disabled, the GTID position will still appear in the output, but only commented. | |
-? |
--help |
Display this help message and exit. |
--hex-blob |
Dump binary strings (BINARY, VARBINARY, BLOB) in hexadecimal format. | |
-h |
--host= name |
指定 MySQL 服务器地址。 |
--ignore-database= name |
指定导出时要忽略的数据库。 | |
--ignore-table-data= name |
Do not dump the specified table data. To specify more than one table to ignore, use the directive multiple times, once for each table. Each table must be specified with both database and table names, e.g., --ignore-table-data=database.table. | |
--ignore-table= name |
指定导出时要忽略的数据表。 | |
--include-master-host-port |
Adds MASTER_HOST=<host>, MASTER_PORT=<port> to CHANGE MASTER TO.. in dump produced with --dump-slave . |
|
--insert-ignore |
Insert rows with INSERT IGNORE. | |
--lines-terminated-by= name |
Lines in the output file are terminated by the given string. | |
-x |
--lock-all-tables |
Locks all tables across all databases. This is achieved by taking a global read lock for the duration of the whole dump. Automatically turns --single-transaction and --lock-tables off. |
-l |
--lock-tables |
读取数据时锁定所有表。 |
--log-error= name |
Append warnings and errors to given file. | |
--log-queries |
When restoring the dump, the server will, if logging turned on, log the queries to the general and slow query log. (Defaults to on; use --skip-log-queries to disable.) | |
--master-data[=#] |
This causes the binary log position and filename to be appended to the output. If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will be prefixed with a comment symbol. This option will turn --lock-all-tables on, unless --single-transaction is specified too (on servers before MariaDB 5.3 this will still take a global read lock for a short time at the beginning of the dump; don't forget to read about --single-transaction below). In all cases, any action on logs will happen at the exact moment of the dump. Option automatically turns --lock-tables off. | |
--max-allowed-packet=# |
The maximum packet length to send to or receive from server. | |
--net-buffer-length=# |
The buffer size for TCP/IP and socket communication. | |
--no-autocommit |
Wrap tables with autocommit/commit statements. | |
-n |
--no-create-db |
不导出建库语句。 |
-t |
--no-create-info |
不导出建表语句。 |
-d |
--no-data |
不导出表数据。 |
--no-data-med |
No row information for engines that Manage External Data (MRG_MyISAM, MRG_ISAM, CONNECT, OQGRAPH, SPIDER, VP, FEDERATED). (Defaults to on; use --skip-no-data-med to disable.) | |
-N |
--no-set-names |
Same as --skip-set-charset. |
--opt |
Same as --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, and --disable-keys. Enabled by default, disable with --skip-opt. | |
--order-by-primary |
Sorts each table's rows by primary key, or first unique key, if such a key exists. Useful when dumping a MyISAM table to be loaded into an InnoDB table, but will make the dump itself take considerably longer. | |
-p |
--password[=name] |
指定数据库连接密码。 |
-P |
--port=# |
指定数据库连接端口。 |
--protocol=name |
指定数据库连接协议 (tcp, socket, pipe)。 | |
-q |
--quick |
Don't buffer query, dump directly to stdout. (Defaults to on; use --skip-quick to disable.) |
-Q |
--quote-names |
Quote table and column names with backticks (`). (Defaults to on; use --skip-quote-names to disable.) |
--replace |
Use REPLACE INTO instead of INSERT INTO. | |
-r |
--result-file=name |
Direct output to a given file. This option should be used in systems (e.g., DOS, Windows) that use carriage-return linefeed pairs (\r\n) to separate text lines. This option ensures that only a single newline is used. |
-R |
--routines |
Dump stored routines (functions and procedures). |
--set-charset |
导出时添加 SET NAMES default_character_set 语句。 |
|
--single-transaction |
Creates a consistent snapshot by dumping all tables in a single transaction. Works ONLY for tables stored in storage engines which support multiversioning (currently only InnoDB does); the dump is NOT guaranteed to be consistent for other storage engines. While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE, as consistent snapshot is not isolated from them. Option automatically turns off --lock-tables. | |
--dump-date |
在最后输出导出时间。 | |
--skip-opt |
Disable --opt. Disables --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, and --disable-keys. | |
-S |
--socket= name |
使用 socket 文件连接数据库。 |
--ssl |
使用证书加密方式连接数据库。 | |
--ssl-ca= name |
CA file in PEM format (check OpenSSL docs, implies --ssl). | |
--ssl-capath= name |
CA directory (check OpenSSL docs, implies --ssl). | |
--ssl-cert= name |
X509 cert in PEM format (implies --ssl). | |
--ssl-cipher= name |
SSL cipher to use (implies --ssl). | |
--ssl-key= name |
X509 key in PEM format (implies --ssl). | |
--ssl-crl= name |
Certificate revocation list (implies --ssl). | |
--ssl-crlpath= name |
Certificate revocation list path (implies --ssl). | |
--tls-version= name |
TLS protocol version for secure connection. | |
--ssl-verify-server-cert |
Verify server's "Common Name" in its cert against hostname used when connecting. This option is disabled by default. | |
--system= name |
Dump system tables as portable SQL. Any combination of: all, users, plugins, udfs, servers, stats, timezones | |
-T |
--tab= name |
Create tab-separated textfile for each table to given path. (Create .sql and .txt files.) NOTE: This only works if mysqldump is run on the same machine as the mysqld server. |
--tables |
Overrides option --databases (-B). | |
--triggers |
在每个数据表后导出触发器数据。 | |
--tz-utc |
在开头输出 SET TIME_ZONE='+00:00' 时区设置。 |
|
-u |
--user= name |
指定数据库连接用户。 |
-v |
--verbose |
Print info about the various stages. |
-V |
--version |
输出命令版本信息。 |
-w |
--where= name |
添加条件筛选语句。 |
-X |
--xml |
使用 XML 格式导出。 |
--plugin-dir= name |
Directory for client-side plugins. | |
--default-auth= name |
Default authentication client-side plugin to use. |