使用mysqldump导出数据库

程序员文章站 2022-06-07 21:04:18

...

mysqldump是mysql用于转存储数据库的客户端程序。它主要产生一系列的SQL语句，可以封装到文件，该文件包含有所有重建您的数据库所

mysqldump是mysql用于转存储数据库的客户端程序。它主要产生一系列的SQL语句，可以封装到文件，该文件包含有所有重建您的数据库所需要的SQL命令如CREATE DATABASE，CREATE TABLE，，INSERT等等。可以用来实现轻量级的快速迁移或恢复数据库。是mysql数据库实现逻辑备份的一种方式。本文描述了mysqldump的一些重要参数以及给出了相关示例供大家参考。

Linux下通过mysqldump备份MySQL数据库成sql文件

Linux中使用mysqldump对MySQL数据库进行定时备份

mysqldump缺失-q参数导致MySQL被oom干掉

mysqldump和LVM逻辑卷快照

MySQL备份方案-->(利用mysqldump以及binlog二进制日志)

[MySQL] 用mysqldump制作文本备份

1、获取mysqldump的帮助信息
[root@SZDB ~]# mysqldump --help|more
mysqldump Ver 10.13 Distrib 5.6.12, for Linux (x86_64)

#以下为MySQL dump调用的几种常用方式
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]

#如果没有指定任何表或使用了---database或--all--database选项，则转储整个数据库

--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.

-q, --quick Don't buffer query, dump directly to stdout.
(Defaults to on; use --skip-quick to disable.)
#以上2个参数未使用的情况下，在转储结果之前会把全部内容载入到内存中，对于较大的数据库转储将严重影响性能。
#缺省情况下这2个参数为开启状态。有些类似于Oracle的绕过PGA而直接写direct write。

--skip-opt Disable --opt. Disables --add-drop-table, --add-locks,
--create-options, --quick, --extended-insert,
--lock-tables, --set-charset, and --disable-keys.
#skip-opt与前2个参数相反，在转储之前先load到内存中。

--compatible=name Change the dump to be compatible with a given mode. By
default tables are dumped in a format optimized for
MySQL. 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 MySQL server
version 4.1.0 or higher. This option is ignored with
earlier server versions.

#产生与其它数据库系统或旧版本MySQL服务器相兼容的输出。用于跨数据库，跨版本之间的迁移。
#其值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options或者no_field_options。
#如果要使用多个值，用逗号将它们隔开。该选项不能保证同其它数据库服务器之间的完全兼容。如Oracle的数据类型等。

--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.
#该选项使得输出的文件更小，启用后等用于使用一些skip项等。

-B, --databases Dump several databases. Note the difference in usage; in
this case no tables are given. All name arguments are
regarded as database names. 'USE db_name;' will be
included in the output.
#该选项一次导出多个数据库所有名字参量看作数据库名，更重要的是会生成CREATE DATABASE IF NOT EXISTS dbname

--default-character-set=name
Set the default character set.
#设置导出脚本的字符集，未指定的情况下为UTF8。

--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.
#在dump mysql数据库以及依赖于mysql数据库恢复时建议使用该选项生成FLUSH PRIVILEGES语句

-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.
#在启动dump前会flush日志，此方式可以用于实现增量备份

-d, --no-data No row information.
#不输出数据行，仅导出结构

-f, --force Continue even if we get an SQL error.
#在碰到错误时，依旧强制dump

--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 (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.
#添加二进制日志位置到输出。1表示输出change master命令，2则注释输出change master命令。

-R, --routines Dump stored routines (functions and procedures).
#导出函数和过程以及触发器，缺省情况下，这些不会被导出

-t, --no-create-info
Don't write table creation info.
#不生成建表语句

--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.
#创建一致性快照，仅仅针对innodb引擎
#不能存在其他操作：ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE，关闭--lock-tables。

-w, --where=name Dump only selected records. Quotes are mandatory.
#使用where子句只导出符合条件的记录
# Author : Leshami
# Blog :