澳门在线威尼斯官方 > 电脑数据库 > mysqldump工具用法详述,深入研究

原标题:mysqldump工具用法详述,深入研究

浏览次数:128 时间:2019-11-16

MariaDB/MySQL备份恢复系列:
备份和恢复(一):mysqldump工具用法详述
备份和恢复(二):导入、导出表数据
备份和恢复(三):xtrabackup用法和原理详述

mysqldump 是MySQL的一个命令行工具,用于逻辑备份。可以将数据库和表的结构,以及表中的数据分别导出成:create database, create table, insert into的sql语句。当然也可以导出 存储过程,触发器,函数,调度事件(events)。不管是程序员,还是DBA都会经常使用的一个工具。


1. mysqldump --help

1.备份分类

按照是否能够继续提供服务,将数据库备份类型划分为:

  • 热备份:在线备份,能读能写
  • 温备份:能读不能写
  • 冷备份:离线备份

按照备份数据库对象分类:

  • 物理备份:直接复制数据文件
  • 逻辑备份:将数据导出至文件中,必要时将其还原(也包括备份成sql语句的方式)

按照是否备份整个数据集分为:

  • 完全备份:备份从开始到某段时间的全部数据
  • 差异备份:备份自完全备份以来变化的数据
  • 增量备份:备份自上次增量备份以来变化的数据

分类方式不同,不同分类的备份没有冲突的关系,它们可以任意组合。

mysqldump 的选项很多,我们看一下他的帮助文档:

2.备份内容和备份工具

需要备份的内容:文件、二进制日志、事务日志、配置文件、操作系统上和MySQL相关的配置(如sudo,定时任务)。

物理备份和逻辑备份的优缺点:

  • 物理备份:直接复制数据文件,速度较快。
  • 逻辑备份:将数据导出到文本文件中或其他格式的文件中。有MySQL服务进程参与,相比物理备份而言速度较慢;可能丢失浮点数精度;但可以使用文本工具二次处理;可以跨版本和跨数据库系统进行移植。

备份策略:要考虑安全,也要考虑还原时长

  • 完全备份+增量
  • 完全备份+差异

备份工具:

  • mysqldump:逻辑备份工具。要求mysql服务在线。MyISAM(温备),InnoDB(热备)
  • mysqlhotcopy:物理备份工具,温备份,实际上是冷备。加锁、flush table并进行cp或scp。即将废弃的工具
  • cp:冷备
  • lvm快照:几乎热备。注意点是:先flush table、lock table、创建快照、释放锁、复制数据。因为要先flush table和lock table,这对于MyISAM来说很简单很容易实现。但对于InnoDB来说,因为事务的原因,锁表后可能还有缓存中的数据在写入文件中,所以应该监控缓存中的数据是真的已经完全写入数据文件中,之后才能进行复制数据。
  • xtrabackup:开源。MyISAM(温备),InnoDB(热备),速度较快。

图片 1图片 2

3.mysqldump用法详述

官方手册:https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump默认会从配置文件中的mysqldump段读取选项,配置文件读取的顺序为:

/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf
mysqldump  Ver 10.13 Distrib 5.6.26, for linux-glibc2.5 (i686)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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]

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file,
                        except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=#
                        Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.
  -A, --all-databases Dump all the databases. This will be same as --databases
                      with all databases selected.
  -Y, --all-tablespaces 
                      Dump all the tablespaces.
  -y, --no-tablespaces 
                      Do not dump any tablespace information.
  --add-drop-database Add a DROP DATABASE before each create.
  --add-drop-table    Add a DROP TABLE before each create.
                      (Defaults to on; use --skip-add-drop-table to disable.)
  --add-drop-trigger  Add a DROP TRIGGER before each create.
  --add-locks         Add locks around INSERT statements.
                      (Defaults to on; use --skip-add-locks to disable.)
  --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.
  --bind-address=name IP address to bind to.
  --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
                      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.
  --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.
  -a, --create-options 
                      Include all MySQL specific create options.
                      (Defaults to on; use --skip-create-options to disable.)
  -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.
  -#, --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        Dump 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.
  -?, --help          Display this help message and exit.
  --hex-blob          Dump binary strings (BINARY, VARBINARY, BLOB) in
                      hexadecimal format.
  -h, --host=name     Connect to host.
  --ignore-table=name Do not dump the specified table. 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=database.table.
  --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   Lock all tables for read.
                      (Defaults to on; use --skip-lock-tables to disable.)
  --log-error=name    Append warnings and errors to given file.
  --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.
  --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  Suppress the CREATE DATABASE ... IF EXISTS statement that
                      normally is output for each dumped database if
                      --all-databases or --databases is given.
  -t, --no-create-info 
                      Don't write table creation info.
  -d, --no-data       No row information.
  -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] 
                      Password to use when connecting to server. If password is
                      not given it's solicited on the tty.
  -P, --port=#        Port number to use for connection.
  --protocol=name     The protocol to use for connection (tcp, socket, pipe,
                      memory).
  -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 (rn) to separate text lines. This option
                      ensures that only a single newline is used.
  -R, --routines      Dump stored routines (functions and procedures).
  --set-charset       Add 'SET NAMES default_character_set' to the output.
                      (Defaults to on; use --skip-set-charset to disable.)
  --set-gtid-purged[=name] 
                      Add 'SET @@GLOBAL.GTID_PURGED' to the output. Possible
                      values for this option are ON, OFF and AUTO. If ON is
                      used and GTIDs are not enabled on the server, an error is
                      generated. If OFF is used, this option does nothing. If
                      AUTO is used and GTIDs are enabled on the server, 'SET
                      @@GLOBAL.GTID_PURGED' is added to the output. If GTIDs
                      are disabled, AUTO does nothing. If no value is supplied
                      then the default (AUTO) value will be considered.
  --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         Put a dump date to the end of the output.
                      (Defaults to on; use --skip-dump-date to disable.)
  --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   The socket file to use for connection.
  --secure-auth       Refuse client connecting to server if it uses old
                      (pre-4.1.1) protocol.
                      (Defaults to on; use --skip-secure-auth to disable.)
  --ssl               Enable SSL for connection (automatically enabled with
                      other flags).
  --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).
  --ssl-verify-server-cert 
                      Verify server's "Common Name" in its cert against
                      hostname used when connecting. This option is disabled by
                      default.
  -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          Dump triggers for each dumped table.
                      (Defaults to on; use --skip-triggers to disable.)
  --tz-utc            SET TIME_ZONE='+00:00' at top of dump to allow dumping of
                      TIMESTAMP data when a server has data in different time
                      zones or data is being moved between servers with
                      different time zones.
                      (Defaults to on; use --skip-tz-utc to disable.)
  -u, --user=name     User for login if not current user.
  -v, --verbose       Print info about the various stages.
  -V, --version       Output version information and exit.
  -w, --where=name    Dump only selected records. Quotes are mandatory.
  -X, --xml           Dump a database as well formed XML.
  --plugin-dir=name   Directory for client-side plugins.
  --default-auth=name Default authentication client-side plugin to use.

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
all-databases                     FALSE
all-tablespaces                   FALSE
no-tablespaces                    FALSE
add-drop-database                 FALSE
add-drop-table                    TRUE
add-drop-trigger                  FALSE
add-locks                         TRUE
allow-keywords                    FALSE
apply-slave-statements            FALSE
bind-address                      (No default value)
character-sets-dir                (No default value)
comments                          TRUE
compatible                        (No default value)
compact                           FALSE
complete-insert                   FALSE
compress                          FALSE
create-options                    TRUE
databases                         FALSE
debug-check                       FALSE
debug-info                        FALSE
default-character-set             utf8
delayed-insert                    FALSE
delete-master-logs                FALSE
disable-keys                      TRUE
dump-slave                        0
events                            FALSE
extended-insert                   TRUE
fields-terminated-by              (No default value)
fields-enclosed-by                (No default value)
fields-optionally-enclosed-by     (No default value)
fields-escaped-by                 (No default value)
flush-logs                        FALSE
flush-privileges                  FALSE
force                             FALSE
hex-blob                          FALSE
host                              (No default value)
include-master-host-port          FALSE
insert-ignore                     FALSE
lines-terminated-by               (No default value)
lock-all-tables                   FALSE
lock-tables                       TRUE
log-error                         (No default value)
master-data                       0
max-allowed-packet                25165824
net-buffer-length                 1046528
no-autocommit                     FALSE
no-create-db                      FALSE
no-create-info                    FALSE
no-data                           FALSE
order-by-primary                  FALSE
port                              0
quick                             TRUE
quote-names                       TRUE
replace                           FALSE
routines                          FALSE
set-charset                       TRUE
single-transaction                FALSE
dump-date                         TRUE
socket                            /var/lib/mysql/mysql.sock
secure-auth                       TRUE
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-crl                           (No default value)
ssl-crlpath                       (No default value)
ssl-verify-server-cert            FALSE
tab                               (No default value)
triggers                          TRUE
tz-utc                            TRUE
user                              (No default value)
verbose                           FALSE
where                             (No default value)
plugin-dir                        (No default value)
default-auth                      (No default value)

3.1 语法选项

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

mysqldump --help

3.1.1 连接选项

-u, --user=name        指定用户名
-S, --socket=name      指定套接字路径 
-p, --password[=name]  指定密码
-P, --port=#           指定端口
-h, --host=name        指定主机名 

-r, --result-file=name
将导出结果保存到指定的文件中,在Linux中等同于覆盖重定向。在windows中因为回车符rn的原因,使用该选项比重定向更好

总体看来,帮助文档分成了两个部分。前一个部分是对各种选项的说明,后一个部分是mysqldump的各种选项的默认值。

3.1.2 筛选选项

--all-databases, -A  
指定dump所有数据库。等价于使用--databases选定所有库
--databases, -B  
指定需要dump的库。该选项后的所有内容都被当成数据库名;在输出文件中的每个数据库前会加上建库语句和use语句
--ignore-table=db_name.tbl_name  
导出时忽略指定数据库中的指定表,同样可用于忽略视图,要忽略多个则多次写该选项
-d, --no-data       
不导出表数据,可以用在仅导出表结构的情况。
--events, -E  
导出事件调度器
--routines, -R   
导出存储过程和函数。但不会导出它们的属性值,若要导出它们的属性,可以导出mysql.proc表然后reload
--triggers  
导出触发器,默认已开启
--tables 
覆盖--databases选项,导出指定的表。但这样只能导出一个库中的表。格式为--tables database_name tab_list
--where='where_condition', -w 'where_condition'  
指定筛选条件并导出表中符合筛选的数据,如--where="user='jim'"

mysqldump拥有超多的选项,这样说明它很灵活,功能强大。为了好理解,可以将他的众多的选项和参数,根据他们的功能分为几个类别来介绍。

3.1.3 DDL选项

--add-drop-database 
在输出中的create database语句前加上drop database语句先删除数据库
--add-drop-table    
在输出的create table语句前加上drop table语句先删除表,默认是已开启的
--add-drop-trigger  
在输出中的create trigger语句前加上drop trigger语句先删除触发器
-n, --no-create-db   
指定了--databases或者--all-databases选项时默认会加上数据库创建语句,该选项抑制建库语句的输出
-t, --no-create-info 
不在输出中包含建表语句
--replace            
使用replace代替insert语句

在此之前,我们还要搞清楚mysqldump的所有的默认选项的值,它们的含义。

3.1.4 字符集选项

--default-character-set=charset_name 
在导出数据的过程中,指定导出的字符集。很重要,客户端服务端字符集不同导出时可能乱码,默认使用utf8
--set-charset 
在导出结果中加上set names charset_name语句。默认启用。

2. mysqldump 默认选项:

3.1.5 复制选项

--apply-slave-statements
--delete-master-logs
--dump-slave[=value]
--include-master-host-port

--master-data[=value]  
该选项主要用来建立一个replication,当值为1时,导出文件中记录change master语句;
当值为2时,change master语句被写成注释语句,默认值为空。
该选项自动忽略--lock-tables,当没有使用--single-transaction时自动启用--lock-all-tables。

图片 3图片 4

3.1.6 格式化选项

--compact                   
简化输出导出的内容,几乎所有注释都不会输出

--complete-insert, -c       
在insert语句中加上插入的列信息

--create-options            
在导出的建表语句中,加上所有的建表选项

--tab=dir_name, -T dir_name 
将每个表的结构定义和数据分别导出到指定目录下文件名同表名的.sql和txt文件中,其中.txt
文件中的字段分隔符是制表符。要求mysqldump必须和MySQL Server在同一主机,且mysql用
户对指定的目录有写权限,并且连接数据库的用户必须有file权限。且指定要dump的表,不能和
--databases或--all-databases一起使用。它的实质是执行select into outfile。

--fields-terminated-by=name            
指定输出文件中的字段分隔符

--fields-enclosed-by=name              
指定输出文件中的字段值的包围符,如使用引号将字符串包围起来引用

--fields-optionally-enclosed-by=name   
指定输出文件中可选字段引用符

--fields-escaped-by=name               
指定输出文件中的转义符

--lines-terminated-by=name            
指定输出文件中的换行符   

-Q, --quote-names                       
引用表名和列名时使用的标识符,默认使用反引号"`" 
Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
all-databases                     FALSE
all-tablespaces                   FALSE
no-tablespaces                    FALSE
add-drop-database                 FALSE
add-drop-table                    TRUE
add-drop-trigger                  FALSE
add-locks                         TRUE
allow-keywords                    FALSE
apply-slave-statements            FALSE
bind-address                      (No default value)
character-sets-dir                (No default value)
comments                          TRUE
compatible                        (No default value)
compact                           FALSE
complete-insert                   FALSE
compress                          FALSE
create-options                    TRUE
databases                         FALSE
debug-check                       FALSE
debug-info                        FALSE
default-character-set             utf8
delayed-insert                    FALSE
delete-master-logs                FALSE
disable-keys                      TRUE
dump-slave                        0
events                            FALSE
extended-insert                   TRUE
fields-terminated-by              (No default value)
fields-enclosed-by                (No default value)
fields-optionally-enclosed-by     (No default value)
fields-escaped-by                 (No default value)
flush-logs                        FALSE
flush-privileges                  FALSE
force                             FALSE
hex-blob                          FALSE
host                              (No default value)
include-master-host-port          FALSE
insert-ignore                     FALSE
lines-terminated-by               (No default value)
lock-all-tables                   FALSE
lock-tables                       TRUE
log-error                         (No default value)
master-data                       0
max-allowed-packet                25165824
net-buffer-length                 1046528
no-autocommit                     FALSE
no-create-db                      FALSE
no-create-info                    FALSE
no-data                           FALSE
order-by-primary                  FALSE
port                              0
quick                             TRUE
quote-names                       TRUE
replace                           FALSE
routines                          FALSE
set-charset                       TRUE
single-transaction                FALSE
dump-date                         TRUE
socket                            /var/lib/mysql/mysql.sock
secure-auth                       TRUE
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-crl                           (No default value)
ssl-crlpath                       (No default value)
ssl-verify-server-cert            FALSE
tab                               (No default value)
triggers                          TRUE
tz-utc                            TRUE
user                              (No default value)
verbose                           FALSE
where                             (No default value)
plugin-dir                        (No default value)
default-auth                      (No default value)

3.1.7 性能选项

--delayed-insert  
对于非事务表,在insert时支持delayed功能,但在MySQL5.6.6开始该选项已经废弃
--disable-keys, -K  
在insert语句前后加上禁用和启用索引语句,大量数据插入时该选项很适合。默认开启
--insert-ignore  
使用insert ignore语句替代insert语句
--quick, -q 
快速导出数据,该选项对于导出大表非常好用。默认导出数据时会一次性检索表中所有数据并加入
到内存中,而该选项是每次检索一行并导出一行

mysqldump

3.1.8 加锁和事务相关选项

--add-locks         
在insert语句前后加上lock tables和unlock tables语句,默认已开启。

--flush-logs, -F 
在开始dump前先flush logs,如果同时使用了--all-databases则依次在每个数据库dump前flush,
如果同时使用了--lock-all-tables,--master-data或者--single-transaction,则仅flush
一次,等价于使用flush tables with read lock锁定所有表,这样可以让dump和flush在完全精
确的同一时刻执行。

--flush-privileges   
在dump完所有数据库后在数据文件的结尾加上flush privileges语句,在导出的数据涉及mysql库或
者依赖于mysql库时都应该使用该选项

--lock-all-tables, -x  
为所有表加上一个持续到dump结束的全局读锁。该选项在dump阶段仅加一次锁,一锁锁永久且锁所有。
该选项自动禁用--lock-tables和--single-transaction选项

--lock-tables, -l  
在dump每个数据库前依次对该数据库中所有表加read local锁(多次加锁,lock tables...read local),
这样就允许对myisam表进行并发插入。对于innodb存储引擎,使用--single-transaction比
--lock-tables更好,因为它不完全锁定表。因为该选项是分别对数据库加锁的,所以只能保证每个数
据库的一致性而不能保证所有数据库之间的一致性。该选项主要用于myisam表,如果既有myisam又有
innodb,则只能使用--lock-tables,或者分开dump更好

--single-transaction 
该选项在dump前将设置事务隔离级别为repeatable read并发送一个start transaction语句给
服务端。该选项对于导出事务表如innodb表很有用,因为它在发出start transaction后能保证导
出的数据库的一致性时而不阻塞任何的程序。该选项只能保证innodb表的一致性,无法保证myisam表
的一致性。在使用该选项的时候,一定要保证没有任何其他连接在使用ALTER TABLE,CREATE TABLE,
DROP TABLE,RENAME TABLE,TRUNCATE TABLE语句,因为一致性读无法隔离这些语句。
--single-transaction选项和--lock-tables选项互斥,因为lock tables会隐式提交事务。
要导出大的innodb表,该选项结合--quick选项更好

--no-autocommit  
在insert语句前后加上SET autocommit = 0,并在需要提交的地方加上COMMIT语句

--order-by-primary  
如果表中存在主键或者唯一索引,则排序后按序导出。对于myisam表迁移到innobd表时比较有用,但是
这样会让事务变得很长很慢

这些默认值,有的是 false, 有的是 true,有的没有默认值。

3.2 mysqldump导出示例

我们要关注的是 默认为 true,也就是 mysqldump 默认给我打开的选择,它们的具体含义如下

3.2.1 简单备份示例

创建示例数据库和表。

# 创建第一个数据库
CREATE DATABASE backuptest;
USE backuptest;
# 创建innodb表
CREATE TABLE `student` (
    `studentid` INT (11) NOT NULL,
    `sname` CHAR (30) NOT NULL,
    `gender` enum ('male', 'female') DEFAULT NULL,
    `birth` date DEFAULT NULL,
    PRIMARY KEY (`studentid`)
) ENGINE = INNODB DEFAULT CHARSET = latin1

INSERT INTO student
VALUES
    (1,'malongshuai','male',curdate()),
    (2,'gaoxiaofang','female',date_add(curdate(), INTERVAL - 2 YEAR)),
    (3,'longshuai','male',date_add(curdate(), INTERVAL - 5 YEAR)),
    (4,'meishaonv','female',date_add(curdate(), INTERVAL - 3 YEAR)),
    (5,'tun'er','female',date_add(curdate(), INTERVAL - 4 YEAR));

# 创建myisam表,并且字符集设置为UTF8
CREATE TABLE teacher (
    tid INT NOT NULL PRIMARY KEY,
    tname VARCHAR (30),
    gender enum ('male', 'female'),
    classname CHAR (10)
) ENGINE = myisam DEFAULT charset = utf8;

INSERT INTO teacher
VALUES
    (1,'wugui','male','计算机网络'),
    (2,'woniu','female','C语言'),
    (3,'xiaowowo','female','oracle');

# 创建第二个数据库
CREATE DATABASE backuptest1;
USE backuptest1;
create table student1 as select * from backuptest.student;
create table teacher1 charset=utf8 engine=myisam as select * from backuptest.teacher;

mysqldump三种备份方式如下:

mysqldump [OPTIONS] database [tables]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
mysqldump [OPTIONS] --all-databases [OPTIONS]

备份单个库,此处备份mysql库。重定向符号可以在Linux中等价于mysqldump的-r选项,所以下面的语句是等价的。

[root@xuexi ~]# mysqldump -uroot -p123456 -S /mydata/data/mysql.sock mysql >/tmp/mysql.bak
[root@xuexi ~]# mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -r /tmp/mysql1.bak mysql

查看备份文件,会发现dump单个库的时候不会在输出文件中记录建库语句和use语句

[root@xuexi ~]# less /tmp/mysql.bak 
-- MySQL dump 10.13  Distrib 5.6.35, for linux-glibc2.5 (x86_64)
--
-- Host: localhost    Database: mysql
-- ------------------------------------------------------
-- Server version       5.6.35-log

/*!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 `columns_priv`
--

DROP TABLE IF EXISTS `columns_priv`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
......

备份多个库。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest backuptest1 >/tmp/mutil_db.bak

备份所有库。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --all-databases >/tmp/all_db.bak

备份多个库或所有库时,会在dump文件中加入建库语句和use语句。实际上,只要使用--databases选项,即使只备份一个库也会加上建库语句和use语句。

[root@xuexi ~]# grep -C 2 -i 'use' /tmp/mutil_db.bak  
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `backuptest` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `backuptest`;

--
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `backuptest1` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `backuptest1`;

--

所以使用mysqldump备份的时候,无论何时都建议--databases或者--all-databases选项二选一,这样就免去了连进数据库的过程。

add-drop-table                    TRUE 表示在生成表结构语句之前,生成对应的 DROP TABLE IF EXISTS `table_name`; 语句
add-locks                         TRUE 表示在生成表中数据的 insert into `table_name` values(...) 之前生成 LOCK TABLES `tab` WRITE;语句
comments                          TRUE 表示生成备注,就是所有 -- 开头的说明,比如:-- Dumping data for  for table `tab`. 最好还是启用;
create-options                    TRUE 表示在生成表结构时会生成:ENGINE=InnoDB AUTO_INCREMENT=827 DEFAULT CHARSET=utf8; 附加建表选项
default-character-set             utf8 指定语句:/*!40101 SET NAMES utf8 */;中的字符集;可能你需要改成 --default-character-set=utf8mb4
disable-keys                      TRUE 表示生产 insert 语句之前,生成:/*!40000 ALTER TABLE `tbl` DISABLE KEYS */; 可以加快insert速度;
extended-insert                   TRUE 表示生产的insert是insert into `tbl` values(...),(...),数据行按照net-buffer-length分割合并成多个batch insert
lock-tables                       TRUE 表示在导出的过程中会锁定所有表;
max-allowed-packet                25165824 最大支持 24M 的数据包;
net-buffer-length                 1046528  1M大小的socket buffer
quick                             TRUE 表示在导出语句时,不缓存,直接输出到控制台或者文件中;
quote-names                       TRUE 表示对表名和列名使用 `` 符号包裹;防止它们是关键字时会出错;
set-charset                       TRUE default-character-set=utf8指定字符集,而--set-charset=1/0 表示是否生成/*!40101 SET NAMES utf8 */; 
dump-date                         TRUE 表示是否在导出文件的末尾生成导出时间:-- Dump completed on 2015-09-15 11:15:10 
secure-auth                       TRUE 表示登录判断密码时使用新的加密算法,拒绝就的加密算法
triggers                          TRUE 表示生成触发器脚本;
tz-utc                            TRUE 表示是否生成:/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */;

3.2.2 使用DDL选项备份示例

DDL选项如下:

--add-drop-database
--add-drop-table   
--add-drop-trigger 
-n, --no-create-db  
-t, --no-create-info
--replace     

--no-create-db将抑制建库语句,所以不建议使用。

--no-create-info将抑制建表语句。使用和不使用的对比如下:

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest.bak
mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --no-create-info --databases backuptest >/tmp/backuptest1.bak
vimdiff /tmp/backuptest.bak /tmp/backuptest1.bak

图片 5

--replace将会把insert语句替换为replace语句。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --replace --databases backuptest >/tmp/backuptest2.bak
shell> grep -i 'replace' /tmp/backuptest2.bak                                                                     
REPLACE INTO `student` VALUES (1,'malongshuai','male','2017-03-31'),(2,'gaoxiaofang','female','2015-03-31'),(3,'longshuai','male','2012-03-31'),(4,'meishaonv','female','2014-03-31'),(5,'tun'er','female','2013-03-31');
REPLACE INTO `teacher` VALUES (1,'wugui','male','计算机网络'),(2,'woniu','female','C语言'),(3,'xiaowowo','female','oracle');

如果要关闭这些打开的默认选项时,需要明确指定:mysqldump -uxxx -p aazj --extended-insert=0 Users > Users.sql; =0 表示关闭选项。

3.2.3 使用字符集选项示例

dump数据的时候,客户端和数据库的字符集不一致的话会进行字符集转换,转换的过程是不可逆的,所以有可能会导致乱码。

例如,插入一个带有中文字符的记录到字符集为latin1的表student中。

insert INTO backuptest.`student` VALUES (6,'马','male','2017-03-31');

如果提示无法插入,则设置客户端字符集和连接字符集为latin1,character_set_client、character_set_connection、character_set_results,使用set names latin1即可,它会设置它们3个。

插入成功之后,其他会话连接数据库查询将会是乱码的。dump的时候也是乱码的,因为dump默认会使用utf8字符集,在latin1转码为utf8的过程中出现了乱码。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest.bak
shell> grep -i 'insert' /tmp/backuptest.bak
INSERT INTO `student` VALUES (1,'malongshuai','male','2017-03-31'),(2,'gaoxiaofang','female','2015-03-31'),(3,'longshuai','male','2012-03-31'),(4,'meishaonv','female','2014-03-31'),(5,'tun'er','female','2013-03-31'),(6,'马','male','2017-03-31');
INSERT INTO `teacher` VALUES (1,'wugui','male','计算机网络'),(2,'woniu','female','C语言'),(3,'xiaowowo','female','oracle');

再使用乱码的文件来恢复的话,肯定是乱码的结果。

这时可以指定dump时的字符集为latin1来使得dump数据时无需转换字符集。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --default-character-set=latin1 --databases backuptest >/tmp/backuptest.bak
shell> grep -i 'insert' /tmp/backuptest.bak
INSERT INTO `student` VALUES (1,'malongshuai','male','2017-03-31'),(2,'gaoxiaofang','female','2015-03-31'),(3,'longshuai','male','2012-03-31'),(4,'meishaonv','female','2014-03-31'),(5,'tun'er','female','2013-03-31'),(6,'马','male','2017-03-31');

测试完成之后,将新插入的含有中文字符的记录删除。

delete from backuptest.student where studentid=6;

上面这些默认选项,最好结合一个例子来理解:

3.2.4 使用格式化选项示例

格式化选项如下:

--compact                   
--complete-insert, -c       
--create-options            
--tab=dir_name, -T dir_name 
--fields-terminated-by=name            
--fields-enclosed-by=name              
--fields-optionally-enclosed-by=name   
--fields-escaped-by=name               
--lines-terminated-by=name            
-Q, --quote-names                       

选项--compact可以极大的简化输出,让输出变得更加简单明晰。但是却不安全,因为它不止会简化注释语句,还会简化部分非注释语句,如insert前后的lock语句。所以该选项仅用来调试使用。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --compact --databases backuptest >/tmp/backuptest.bak
shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest1.bak
shell> vimdiff /tmp/backuptest.bak /tmp/backuptest1.bak

图片 6

"--complete-insert, -c"选项会在每个insert语句中列出插入列列表,而默认情况下mysqldump备份时是不加的。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --databases backuptest >/tmp/backuptest.bak
shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -c --databases backuptest >/tmp/backuptest1.bak
shell> vimdiff /tmp/backuptest.bak /tmp/backuptest1.bak  

图片 7

--tab选项是将表的结构定义和数据分开dump,结构定义语句dump到表的同名.sql文件中,数据dump到表的同名.txt文件中。--tab指定这些文件的输出目录。但要求mysqldump和MySQL服务器必须在同一台服务器上,且mysql系统用户对指定的输出目录有写权限,连接数据库的用户还需要有file权限。而且该选项不能和--databases或--all-databases一起使用。因为它只能定义表的结构。

一般和该选项一起使用的有:该部分内容见select ... into outfile。

--fields-terminated-by=name            
--fields-enclosed-by=name              
--fields-optionally-enclosed-by=name   
--fields-escaped-by=name               
--lines-terminated-by=name  

图片 8图片 9

3.2.5 使用筛选选项示例

--all-databases, -A  
--databases, -B  
--ignore-table=db_name.tbl_name  
-d, --no-data       
--events, -E  
--routines, -R   
--triggers  
--tables 
--where='where_condition', -w 'where_condition'  

筛选数据库此处不再赘述。

--ignore-table表示忽略数据库中的某张表不被dump。要忽略多张表的时候多次写该选项即可。例如忽略backuptest.student和backuptest1.student1。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --ignore-table backuptest.student --ignore-table backuptest1.student1 --databases backuptest backuptest1 >/tmp/backuptest.bak
shell> grep -i 'student' /tmp/backuptest.bak |wc -l
0

--no-data表示只dump表的结构,不dump数据。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -d backuptest>/tmp/backuptest.bak

grep -i 'insert' /tmp/backuptest.bak | wc -l
0

--tables指定数据库来导出其中的某些表。只支持指定一个数据库。该选项会覆盖--databases选项,所以不会建库语句和use语句。该选项可以用在分开备份innodb表和myisam表。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --tables backuptest student teacher>/tmp/backuptest.bak

--where指定某个表的筛选条件,格式为--where="condition"。如果指定了筛选列,则要导出的表中必须要含有该列。另外,显式指定表时,格式为"db_name tab_list"。以下格式都正确。

# 导出某数据库下的一个表中的筛选数据
mysqldump -uroot -p123456  -S /mydata/data/mysql.sock --where="gender='male'" backuptest student>/tmp/backuptest.bak
# 导出某数据库下多个表中的筛选数据
mysqldump -uroot -p123456  -S /mydata/data/mysql.sock --where="gender='male'" backuptest student teacher>/tmp/backuptest.bak
# 导出多个数据库中的筛选数据
mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --where="gender='male'" --databases backuptest backuptest1>/tmp/backuptest.bak
-- MySQL dump 10.13  Distrib 5.6.26, for linux-glibc2.5 (i686)
--
-- Host: localhost    Database: gs
-- ------------------------------------------------------
-- Server version    5.6.26-log

/*!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 */;

--
-- Current Database: `gs`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `gs` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */;

USE `gs`;

--
-- Table structure for table `user`
--

DROP TABLE IF EXISTS `user`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  `sex` tinyint(4) DEFAULT NULL,
  `phone` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
  `addr` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES (1,'yuanfang',1,'14465463786','hangzhou'),(2,'Andy',0,'14465463786','beijing'),(3,'peter',0,'14465463786','Hongkong');
/*!40000 ALTER TABLE `user` 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 2015-10-22 10:16:37

3.2.6 使用事务选项示例

--add-locks选项是在insert语句前后加上lock tables和unlock tables语句。

图片 10

--no-autocommit选项是在insert语句前后加上set autocommit=0,并在事务结束的地方加上commit语句。这样插入数据时只需一次提交,可以大幅提升大量插入时的性能。

--flush-logs, -F

在开始dump前先flush logs,如果同时使用了--all-databases,则依次在每个数据库dump前flush;如果同时使用了--lock-all-tables,--master-data或者--single-transaction,则仅flush一次,因为这几个选项是dump前开启一个长事务或者全局锁定。等价于使用flush tables with read lock锁定所有表,这样可以让dump和flush在完全精确的同一时刻执行。

--flush-privileges  

在dump完所有数据库后在数据文件的结尾加上flush privileges语句,在导出的数据涉及mysql数据库或者依赖于mysql数据库时都应该使用该选项。

--lock-all-tables, -x 

为所有表加上一个持续到dump结束的全局读锁。该选项在dump阶段仅加一次锁,一锁锁永久且锁所有。该选项自动禁用--lock-tables和--single-transaction选项。

--lock-tables, -l 

在dump每个数据库前依次对该数据库中所有表加上read local锁(多次加锁,lock tables ... read local),这样就允许对myisam表进行并发插入。对于innodb存储引擎,使用--single-transaction比--lock-tables更好,因为它不完全需要锁定表。因为该选项是分别对数据库加锁的,所以只能保证每个数据库之间的一致性而不能保证在所有数据库之间的一致性。 该选项主要用于myisam表,如果既有myisam又有innodb,则只能使用--lock-tables了,或者分开dump更好。

--single-transaction

该选项在dump前将设置事务隔离级别为repeatable read并发送一个start transaction**语句给服务端。**该选项对于导出事务表如innodb表很有用,因为它在发出start transaction后能保证导出的数据库的一致性时而不阻塞任何的程序。当使用该选项只能保证innodb表的一致性,对于myisam表是无法保证的。但是在使用该选项的时候,一定要保证没有任何其他数据库的连接在使用ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE语句,因为一致性读无法隔离这些语句。--single-transaction选项和--lock-tables选项互斥,因为lock tables会隐式提交事务,要导出大的innodb表,该选项结合--quick选项更好。

mysqldump

3.3 mysqldump使用建议

1.从性能考虑:在需要导出大量数据的时候,使用--quick选项可以加速导出,但导入速度不变。如果是innodb表,则可以同时加上--no-autocommit选项,这样大量数据量导入时将极大提升性能。

2.一致性考虑:对于innodb表,几乎没有理由不用--single-transaction选项。对于myisam表,使用--lock-all-tables选项要好于--lock-tables。既有innodb又有myisam表时,可以分开导出,又能保证一致性,还能保证效率。

3.方便管理和维护性考虑:在导出时flush log很有必要。加上--flush-logs选项即可。而且一般要配合--lock-all-tables选项或者--single-transaction选项一起使用,因为同时使用时,只需刷新一次日志即可,并且也能保证一致性。同时,还可以配合--master-data=2,这样就可以方便地知道二进制日志中备份结束点的位置。

4.字符集考虑:如果有表涉及到了中文数据,在dump时,一定要将dump的字符集设置的和该表的字符集一样。

5.杂项考虑:备份过程中会产生二进制日志,但是这是没有必要的。所以在备份前可以关掉,备份完后开启。set sql_log_bin=0关闭,set sql_log_bin=1开启。

以下是备份不同存储引擎类型表的示例:

备份myisam表:需要时加上--default-character-set

set sql_log_bin=0

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -q --lock-all-tables --flush-logs --master-data=2 --tables backuptest teacher >/tmp/myisam.sql ;

set sql_log_bin=1

备份innodb表:需要时加上--default-character-set

set sql_log_bin=0

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -q --no-autocommit --flush-logs --single-transaction --master-data=2 --tables backuptest student >/tmp/innodb.sql;

set sql_log_bin=1

2. mysqldump 登录服务器的相关选项

3.4 mysqldump + 二进制日志备份

mysqldump可以实现全备份,在mysqldump之后再二进制日志备份就相当于增量备份,这样就可以实现全备份之后的定时点还原。

假设要备份的是一张innodb表。使用下面的语句:

mysqldump  -uroot -p123456 -S /mydata/data/mysql.sock -q --no-autocommit --flush-logs --single-transaction --master-data=2 --tables backuptest student >/tmp/innodb.sql;

因为dump前会flush二进制日志,所以之后对该表的操作会记录到新的滚动日志中。然后只需备份新的二进制日志即可。

然后在该表中插入一行记录。

insert into student select 10,'xiaolonglong','male','2015-01-02';

备份新的二进制日志。

mysqlbinlog mysql-bin.000002 >/tmp/new_binlog.sql

设计刚才备份的表误操作,如删除该表。

drop table student;

使用该表的完全备份和二进制日志恢复。因为备份时使用的是--tables选项,所以要恢复需要进入数据库指定数据库,然后使用source来加载sql文件。

use backuptest;
source /tmp/innodb.sql;
source /tmp/new_binlog.sql;

Default options are read from the following files in the given order:

3.5 mysqldump工具的评价

mysqldump备份的文件是逻辑SQL语句,总体来说,简单,便捷,在有些时候迁移数据的时候比较有用。另外,它的功能也很多,例如导出数据,导出表结构等。

但是缺点是恢复速度太慢,因为恢复数据时是通过insert不断插入记录的,它的恢复速度远不及load data infile导入数据。

在备份方式上,mysqldump备份myisam表时因为要加--lock-all-tables,这时要备份的数据库全部被上锁,可读不可写,所以实现的是温备。mysqldump备份innodb表时因为要加--single-transaction,会自动将隔离级别设置为repeatable read并开启一个事务,这时mysqldump将获取dump执行前一刻的行版本,并处于一个长事务中直到dump结束。所以不影响目标数据库的使用,可读也可写,即实现的是热备。

/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf

The following groups are read: mysqldump client
The following options may be given as the first argument:
--no-defaults           Don't read default options from any option file, except for login file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-group-suffix=# Also read groups with concat(group, suffix)
--login-path=#          Read this path from the login file.

1)mysqldump 作为一个客户端工具,它会去上诉目录中寻找 my.cnf 文件,然后读取该文件中 [mysqldump] 和 [client] 下面的选项;

2)那些 defaults 相关的选项都是为了另外指定 配置文件和登录文件,极少使用;

  -u, --user=name     User for login if not current user.

  -p, --password[=name] Password to use when connecting to server. If password is not given it's solicited on the tty.

  -h, --host=name     Connect to host.

  -P, --port=#        Port number to use for connection.
  --protocol=name     The protocol to use for connection (tcp, socket, pipe, memory).

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

3)这几个选项指定 登录的用户名,密码,mysqld IP地址,端口,连接使用的协议等等。

    一般常用的是 -h192.168.2.xx -uxxx -p ,如果mysqld默认端口不是3306,则需要使用 -Pxxx 指定端口.

    --max-allowed-packet 我们一般配置在my.cnf中。--net-buffer-length 是为了优化网络连接的socket buffer.

使用示例: mysqldump -h192.168.1.20 -uxxx -p -P3057

3. mysqldump 选择备份内容的相关选项

我们可以选择备份所有数据库,某几个数据库,某一个数据库,某一个数据库中的某几个表,某一个数据库中的一个表;

可以选择是否备份 存储过程和函数,触发器,调度事件.

1)选择导出的数据库 和 表:

-A, --all-databases Dump all the databases. This will be same as --databases with all databases selected.

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

database [tables]   导出数据库 database 中的表结构 和 表中数据;

2)选择是否导出 建库,建表语句,是否导出 表中的数据:

-n, --no-create-db  Suppress the CREATE DATABASE ... IF NOT EXISTS statement that normally is output for each dumped database if 

                             --all-databases or --databases is given. (不导出建库语句: CREATE DATABASE,也就是不导库结构)

-t, --no-create-info Don't write table creation info. (不导出建表语句)

-d, --no-data         No row information. (不导出数据,有时我们仅仅需要导出表结构,也就是建表语句就行了)

3)选择是否导出 存储过程和函数,触发器,调度事件:

-R, --routines      Dump stored routines (functions and procedures). (导出存储过程和函数)

--triggers            Dump triggers for each dumped table. (Defaults to on; use --skip-triggers to disable.) (导出触发器)

--skip-triggers     不导出触发器

-E, --events        Dump events. 导出调度事件(根据备份的目的进行选择,如果是搭建slave,那么就不要导出events.)

4)指定不导出 某个库的某个表:

--ignore-table=name   Do not dump the specified table. 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=database.table. (在导出数据库时,排除某个或者某几个表不导出)

5) 按照 where 条件导出:

   -w, --where='where_condition' Dump only selected records. Quotes are mandatory.

6)使用示例:

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]

1> 导出单表的结构和数据:mysqldump -uxxx -p db1 tb1 > tb1.sql; 导出数据库 db1 中的 表 tb1 的表结构 和 表中数据;

2> 导出多表的结构和数据:mysqldump -uxxx -p db1 tb1 tb2 > tb1_tb2.sql; 导出数据库 db1 中的 表 tb1、tb2 的表结构 和 表中数据;

3> 导出单表的结构:mysqldump -uxxx -p --no-data db1 tb1 > tb1.sql; 导出数据库 db1 中的 表 tb1 的表结构; 其实也可以使用: show create table tb1

4> 我们无法使用 mysqldump 到达 只导出某个或某几个表的数据,而不导出建表语句的目的。

     但是我们可以使用 select * from table into outfile 'file.sql', 比如:select * from Users into outfile '/tmp/Users.sql'; 注意需要对目录的写权限。

5> 导出单个库中库结构、表结构、表数据:mysqldump -uxxx -p --databases db1 > db1.sql

6> 导出多个库中库结构、表结构、表数据:mysqldump -uxxx -p --databases db1 db2 > db1_db2.sql

7> 导出单个库中库结构、表结构、不要表数据:mysqldump -uxxx -p --no-data --databases db1 > db1.sql

8> 导出单个库中数据,不要库结构和表结构:mysqldump -uxxx -p --no-create-db --no-create-info --databases db1 > db1.sql

9> 导出多个库中库结构、表结构、不要表数据:mysqldump -uxxx -p --no-data --databases db1 db2 > db1_db2.sql

10> 导出数据库中所有 库 的库结构,表结构,数据:mysqldump -uxxx -p --all-databases > all.sql

11> 导出数据库中所有 库 的库结构,表结构,不要数据:mysqldump -uxxx -p --all-databases --no-data > all.sql

12> 导出单个库中库结构、表结构、表数据,排除某个表:mysqldump -uxxx -p --databases db1 --ignore-table=db1.test > db1.sql

4. mysqldump 事务 和 数据一致性(锁) 的相关选项

在使用mysqldump逻辑备份时,事务和数据一致性的选项时至关重要的。

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

     --single-transaction 可以得到一致性的导出结果。他是通过将导出行为放入一个事务中达到目的的。

     它有一些要求:只能是 innodb 引擎;导出的过程中,不能有任何人执行 alter table, drop table, rename table, truncate table等DDL语句

     实际上DDL会被事务所阻塞,因为事务持有表的metadata lock 的共享锁,而DDL会申请metadata lock的互斥锁,所以阻塞了。

     --single-transaction 会自动关闭 --lock-tables 选项;上面我们说到mysqldump默认会打开了--lock-tables,它会在导出过程中锁定所有表。

    因为 --single-transaction 会自动关闭--lock-tables,所以单独使用--single-transaction是不会使用锁的。与 --master-data 合用才有锁。

2)--lock-tables

     该选项默认打开的,上面已经说到了。它的作用是在导出过程中锁定所有表。--single-transaction 和 --lock-all-tables 都会将该选项关闭。

3)--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. 启用该选项,会自动关闭 --single-transaction 和 --lock-tables.

上面三个选项中,只有 --lock-tables 默认是打开的;打开 --single-transaction 或者 打开 --lock-all-tables 都将关闭 --lock-tables. 而--lock-all-tables会自动关闭 --single-transaction 和 --lock-tables。所以三者是互斥的。我们应该一次只启用其中一个选项。

4)--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-logs 选项和 --lock-all-tables 或者 --master-data 一起使用:

       mysqldump --flush-logs --lock-all-tables;  mysqldump --flush-logs --master-data=2 ;

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

      如何导出包含了mysql数据,就应该启用该选项。该选项会在导出的 mysql 数据库的后面加上 flush privileges 语句,因为在向mysql数据库inert了语句

      之后,必须使用 flush privileges,不然权限不生效。下面是例子:

图片 11

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

所以为了获得一致性的备份数据和在备份是同时刷新binary日志,我们应该如下结合使用这些选项(完美组合):

mysqldump -uxxx -pxxx --single-transaction --master-data=2 --flush-logs --routines --databases db1 > db1.sql;

(其中 --flush-logs 不是必须的; 搭建slave时,不要导出events,但是需要导出rountines.)

其中被 --master-data 打开的 --lock-all-tables 选项,又被 --single-transaction 关闭掉了。--flush-logs 借助于 --master-data 可以达到即使一次导出多个数据库时,其 flush 的二进制日志也是在同一个时间点的,不是每一个数据库flush一次的。并且这个时间点 和 --master-data 记录的 binary log position 和 binary log file是同一个时间点,这些都是利用了 --single-transaction 和 --master-data 合用时短暂的使用一个全局的读锁来达到目的的。

5. mysqldump 复制 的相关选项

1)  --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;

本文由澳门在线威尼斯官方发布于电脑数据库,转载请注明出处:mysqldump工具用法详述,深入研究

关键词:

上一篇:Server数据库状态和文书状态

下一篇:【威尼斯澳门在线】Innodb三大特性之insert