MySQL数据导出mysqldump

   MySQL逻辑备份mysqldump（mysqldump至少需要具有对转储对象的SELECT、SHOW VIEW、TRIGGER、LOCK TABLES等权限），输出包含DROP、CREATE TABLE、INSERT等语句，与物理备份相比，逻辑备份还原所需的时间要长得多，对于大规模备份和还原，物理备份更适合于快速还原的原始格式复制数据文件。
   通过mysqldump --help命令来了解一下参数：
  [root@chengyu ~]# mysqldump --help

mysqldump  Ver 8.0.20 for Linux on x86_64 (Source distribution)
Copyright (c) 2000, 2020, 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/mysql8/etc/my.cnf /usr/local/mysql8/my.cnf ~/.my.cnf 
The following groups are read: mysqldump client
The following options may be given as the first argument:
--print-defaults        打印默认选项
--no-defaults           除了登录文件，不读取其他选项文件中的任意默认参数
--defaults-file=        仅读取指定文件中的默认参数
--defaults-extra-file=  global选项文件之后读取的指定选项文件
--defaults-group-suffix= 
                        Also read groups with concat(group, suffix)
--login-path=           从.mylogin.cnf中读取登录路径选项
  -A, --all-databases   导出所有的数据库，这与--databases选定所有的数据库时同效
  -Y, --all-tablespaces 导出所有的表空间
  -y, --no-tablespaces  不要在输出中写入任何CREATE LOGFILE GROUP或CREATE TABLESPACE语句
  --add-drop-database 在每个CREATE DATABASE语句之前添加DROP DATABASE语句	
  --add-drop-table    在每个CREATE TABLE语句之前添加DROP TABLE语句（默认是on，关闭使用--skip-add-drop-table）
  --add-drop-trigger  在每个CREATE TRIGGER语句之前添加DROP TRIGGER语句
  --add-locks         导出表用LOCK TABLES和UNLOCK TABLES语句(默认是on，关闭使用--skip-add-locks)	
  --allow-keywords    允许关键字作为列名
  --apply-slave-statements 	在CHANGE MASTER语句之前包含STOP SLAVE，在输出结束时包含START SLAVE
  --bind-address  使用指定的网络接口连接到MySQL Server
  --character-sets-dir=name 字符集的安装目录
  --column-statistics 编写ANALYZE TABLE语句以生成统计数据直方图（默认on，关闭使用--skip-column-statistics）
  -i, --comments      添加注释到转储文件（默认on，关闭使用--skip-comments）	
  --compatible=name   产生与其他数据库系统或更旧的MySQL服务器更兼容的输出
  --compact           产生更紧凑的输出，开启该功能的参数--skip-add-drop-table --skip-add-locks
                      --skip-comments --skip-disable-keys --skip-set-charset.
  -c, --complete-insert 使用包含列名称的完整INSERT语句	
  -C, --compress      压缩客户端和服务器之间发送的所有信息
  -a, --create-options 	在CREATE TABLE语句中包括所有特定于MySQL的表选项(默认on;关闭使用--skip-create-options)
  -B, --databases    将所有名称参数解释为数据库名称
  -#, --debug[=#]    编写调试日志
  --debug-check      程序退出时打印调试信息
  --debug-info       程序退出时打印调试信息，内存和CPU统计信息
  --default-character-set=name 指定默认字符集   
  --delete-master-logs 在主库服务器上，执行备份操作后删除二进制日志，该参数默认开启--master-data.
  -K, --disable-keys  对于每个表，在INSERT语句上下加上用于禁用和启用KEYS的语句（默认on，关闭使用--skip-disable-keys）
  --dump-slave[=]    包含CHANGE MASTER语句，该语句列出了从库主机的二进制日志坐标
  -E, --events        导出events.
  -e, --extended-insert 使用多行INSERT语法 (默认on; 禁止使用--skip-extended-insert)
  --fields-terminated-by=name 列分隔符
  --fields-enclosed-by=name 字段包括符
  --fields-optionally-enclosed-by=name 列可选包括符
  --fields-escaped-by=name 列终止符
  -F, --flush-logs   开始备份之前刷新MySQL服务器日志文件，注意：使用--databases或--all-databases 同时导出多个数据库，将刷新每个数据库的日志文件，除--lock-all-tables或者--master-data参数被使用时，只会刷新一次日志，同时指定的数据库的所有表将被锁住。
  --flush-privileges  备份mysql数据库后发出FLUSH PRIVILEGES语句
  -f, --force         忽略导出错误，继续执行导出
  -?, --help          显示帮助信息并退出
  --hex-blob          使用十六进制表示法转储二进制列
  -h, --host=name     MySQL服务器所在的主机
  --ignore-error=name 忽略指定的错误
  --ignore-table=name 不要备份指定的表
  --include-master-host-port 在使用--dump-slave生成的CHANGE MASTER语句中包括MASTER_HOST/MASTER_PORT选项
  --insert-ignore     编写INSERT IGNORE而不是INSERT语句	
  --lines-terminated-by=name 行终止符
  -x, --lock-all-tables 锁定所有数据库中的所有表
  -l, --lock-tables   备份之前锁定所有表（默认on，关闭使用--skip-lock-tables）
  --log-error=name    将警告和错误写入到指定文件
  --master-data[=]   将二进制日志文件的名称和位置写入到输出信息里
  --max-allowed-packet= 发送到服务器或从服务器接收的最大数据包长度
  --net-buffer-length= TCP/IP和套接字通信的缓冲区大小	 
  --no-autocommit     将每个表的INSERT语句包含在SET autocommit = 0和COMMIT语句内	
  -n, --no-create-db  不要写CREATE DATABASE语句
  -t, --no-create-info 不要编写重新创建每个表的CREATE TABLE语句	
  -d, --no-data       不备份数据
  -N, --no-set-names  与--skip-set-charset相同	
  --opt  --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset的简写
  --order-by-primary  备份按主键或第一个唯一索引排序的每个表的行	
  -p, --password[=name] 连接服务器时使用的密码
  -P, --port=        连接服务器的端口
  --protocol=name     protocol名(tcp, socket, pipe, memory).
  -q, --quick         一次从服务器检索表的行（默认on，关闭使用--skip-quick）
  -Q, --quote-names   反引号字符内的引号标识符（默认on，关闭使用--skip-quote-names）	
  --replace           编写REPLACE语句而不是INSERT语句	
  -r, --result-file=name 直接输出到给定文件
  -R, --routines      从备份的数据库中备份存储的例程(函数和存储过程).
  --set-charset       将SET NAMES default_character_set添加到输出（默认on，关闭使用--skip-set-charset）
  --set-gtid-purged[=name] 是否将SET @@ GLOBAL.GTID_PURGED添加到输出
  --single-transaction 从服务器备份数据之前发出BEGIN SQL语句	
  --dump-date         如果给出--comments，则将转储日期包括为“转储完成于”注释（默认on，关闭使用--skip-dump-date）
  --skip-opt         关闭--opt设置的选项：--add-drop-table, --add-locks,--create-options, --quick, --extended-insert,--lock-tables, --set-charset, and --disable-keys.
  -S, --socket=name   指定连接socket
  --server-public-key-path=name 包含RSA公钥的文件的路径名	
  --get-server-public-key 从服务器请求RSA公钥
  --ssl-mode=name     与服务器连接的所需SSL	
  --ssl-ca=name       包含受信任的SSL证书颁发机构列表的文件	
  --ssl-capath=name   包含受信任的SSL证书颁发机构证书文件的目录	
  --ssl-cert=name     包含X.509证书的文件	
  --ssl-cipher=name   连接加密的允许密码	
  --ssl-key=name      包含X.509密钥的文件	
  --ssl-crl=name      包含证书吊销列表的文件	
  --ssl-crlpath=name  包含证书吊销列表文件的目录	
  --tls-version=name  允许的TLS协议进行加密连接，允许的值有: TLSv1, TLSv1.1, TLSv1.2, TLSv1.3
  --ssl-fips-mode=name 是否在客户端启用FIPS模式，允许的值有: OFF, ON, STRICT
  --tls-ciphersuites=name 允许的TLSv1.3密码套件用于加密连接
  -T, --tab=name     产生制表符分隔的数据文件
  --tables            覆盖--databases或-B选项
  --triggers          备份触发器（默认on，关闭使用--skip-triggers）
  --tz-utc            将SET TIME_ZONE ='+ 00:00'添加到转储文件（默认on，关闭使用--skip-tz-utc）
  -u, --user=name     连接服务器时要使用的MySQL用户名	
  -v, --verbose       详细信息
  -V, --version       显示版本信息并退出	
  -w, --where=name    仅备份给定WHERE条件选择的行	
  -X, --xml           产生XML输出	
  --plugin-dir=name   安装插件的目录
  --default-auth=name 身份验证插件使用
  --enable-cleartext-plugin 启用明文身份验证插件
  -M, --network-timeout 增加网络超时以允许大表备份（默认on，关闭使用--skip-network-timeout）
  --show-create-table-skip-secondary-engine 从CREATE TABLE语句中排除SECONDARY ENGINE子句	
  --compression-algorithms=name 用于服务器连接的允许压缩算法	
  --zstd-compression-level= 与使用zstd压缩的服务器的连接的压缩级别，有效区间1-22，默认值3.

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)
column-statistics                       TRUE
comments                                TRUE
compatible                              (No default value)
compact                                 FALSE
complete-insert                         FALSE
compress                                FALSE
create-options                          TRUE
databases                               FALSE
default-character-set                   utf8mb4
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)
ignore-error                            (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                                  (No default value)
server-public-key-path                  (No default value)
get-server-public-key                   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)
tls-version                             (No default value)
tls-ciphersuites                        (No default value)
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)
enable-cleartext-plugin                 FALSE
network-timeout                         TRUE
show-create-table-skip-secondary-engine FALSE
compression-algorithms                  (No default value)
zstd-compression-level                  3

1. 导出表

• 导出表结构和数据：以下2个导出命令是等效的。

[root@chengyu ~]# mysqldump -uroot -p --databases dbcy --tables tb_cylog>/home/mysqlbak/cylog01.sql
Enter password: 
[root@chengyu ~]# mysqldump -uroot -p dbcy tb_cylog>/home/mysqlbak/cylog02.sql
Enter password: 
[root@chengyu ~]# ll /home/mysqlbak/
total 8
-rw-r--r-- 1 root root 3886 Jun 23 16:26 cylog01.sql
-rw-r--r-- 1 root root 3886 Jun 23 16:26 cylog02.sql

• 只导出数据：-t等价于—no-create-info，只导出数据，而不添加CREATE TABLE语句。

[root@chengyu ~]# mysqldump -uroot -p --databases dbcy --tables -t tb_cylog>/home/mysqlbak/cylog03.sql
Enter password: 
[root@chengyu ~]# ll /home/mysqlbak/
total 12
-rw-r--r-- 1 root root 3886 Jun 23 16:26 cylog01.sql
-rw-r--r-- 1 root root 3886 Jun 23 16:26 cylog02.sql
-rw-r--r-- 1 root root 3305 Jun 23 16:37 cylog03.sql

• 只导出表结构： -d等价于-–no-data，不导出任何数据，只导出数据库表结构。

[root@chengyu ~]# mysqldump -uroot -p --databases dbcy --tables -d tb_cylog>/home/mysqlbak/cylog04.sql
Enter password: 
[root@chengyu ~]# ll /home/mysqlbak/
total 16
-rw-r--r-- 1 root root 3886 Jun 23 16:26 cylog01.sql
-rw-r--r-- 1 root root 3886 Jun 23 16:26 cylog02.sql
-rw-r--r-- 1 root root 3305 Jun 23 16:38 cylog03.sql
-rw-r--r-- 1 root root 2815 Jun 23 16:38 cylog04.sql

• 导出带where条件的表数据：以下两个等效

[root@chengyu ~]# mysqldump -uroot -p --databases dbcy --tables -t tb_cylog --where='tid<2'>/home/mysqlbak/cylog05.sql
[root@chengyu ~]# mysqldump -uroot -p --databases dbcy --tables -t tb_cylog --where 'tid<2'>/home/mysqlbak/cylog05.sql

2. 导出数据库

  mysqldump使用的限制条件：

• 默认情况下，mysqldump不备份performance_schema和sys schema，需要备份的话用–databases来明确指定，performance_schema备份时也使用了–skip-lock-tables参数；
• mysqldump不备份INFORMATION_SCHEMA；
• mysqldump不备份InnoDB CREATE TABLESPACE语句；
• mysqldump不备份NDB集群ndbinfo信息数据库；
  mysqldump includes statements to recreate the general_log and slow_query_log tables for dumps of the mysql database. Log table contents are not dumped.
• mysqldump包含用于为数据库备份重建general_log和 slow_query_log表的语句，不备份日志表内容。

2.1 导出所有数据库

  –set-gtid-purged控制是否在备份文件中输出生成@@GLOBAL.GTID_PURGED语句，即控制写入备份文件的全局事务ID（GTID，Global Transaction ID，开启GTID后，当在主库上提交事务或者被从库应用时，可以定位和追踪每一个事务）信息 ，默认值为AUTO。

[root@chengyu ~]# mysqldump -uroot -p --set-gtid-purged=off --all-databases>/home/mysqlbak/all01.sql
[root@chengyu ~]# mysqldump -uroot -p --set-gtid-purged=off -A>/home/mysqlbak/all02.sql
# 如果没有开启GTID，则执行如下备份会报错
[root@chengyu ~]# mysqldump -uroot -p --set-gtid-purged=on -A>/home/mysqlbak/all03.sql
Enter password: 
Error: Server has GTIDs disabled.

2.2 导出指定的数据库

# dbcy04.sql与dbcy05.sql的差别在于前者多了创建数据库dbcy的语句
[root@chengyu ~]# mysqldump -uroot -p --databases dbcy>/home/mysqlbak/dbcy04.sql
Enter password: 
[root@chengyu ~]# mysqldump -uroot -p dbcy>/home/mysqlbak/dbcy05.sql
Enter password: 

2.3 主从环境下不锁表全库备份

  –master-data：值为2，则该CHANGE MASTER TO语句将写为SQL注释；值为1，则该语句不作为注释写入，并在重新加载备份文件时生效，默认值为1；–master-data选项自动关闭–lock-tables。

  –single-transaction：将事务隔离模式设置为可重复读， 在导出数据之前执行START TRANSACTION，–single-transaction选项和–lock-tables选项是互斥的，因为LOCK TABLES会导致任何未决事务被隐式提交，要备份大表，建议–single-transaction与–quick选项联合使用。

[root@chengyu ~]# mysqldump -uroot -p --set-gtid-purged=off --single-transaction --master-data=2 -A>/home/mysqlbak/all03.sql
[root@chengyu ~]# mysqldump -uroot -p --set-gtid-purged=off --single-transaction --master-data=1 -A>/home/mysqlbak/all04.sql

  差异如下：

3. MySQL导入

  简单的导入：

• MySQL命令行导入

# 指定dbcy库执行导入
[root@chengyu ~]# mysql -uroot -p dbcy </home/mysqlbak/dbcy05.sql
# 全库导入
[root@chengyu ~]# mysql -uroot -p< /home/mysqlbak/all04.sql

• source导入

[root@chengyu ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 Source distribution

Copyright (c) 2000, 2020, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use dbcy
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> source /home/mysqlbak/dbcy05.sql;

或者：

[root@chengyu ~]#  mysql -uroot -p -e "source /home/mysqlbak/dbcy05.sql" dbcy

2020年6月28日

  端午假期，我们的房子开启二次美化，五年周期性断舍离：果断抛掉已有的大沙发、茶几、电视柜，定制小尺寸沙发、设计客厅书柜、约了厨房生活阳台封装、客厅阳台重新布局、瓷砖美缝工作也正式展开，3天72个小时，充实美好。

  想起给孩子念的《世界上最大的房子》，还有《舌尖上的中国》里那句“广厦千间，夜眠仅需六尺；家财万贯，日食不过三餐”，在追逐的路上，要摒弃无尽的欲求，坚守纯粹，何其难！但两个人一起，就没那么难……