5. mysqldump -逻辑备份工具

客户端通用参数

-u -p  -S  -h -P  

本地备份:

mysqldump -uroot -p -S /tmp/mysql.sock

远程备份:

mysqldump -uroot -p -h 10.0.0.51 -P3306

备份专用参数

-A 全备参数
-B db1 db2 db3 备份多个单库
备份单个或多个表
-R      备份存储过程及函数
--triggers  备份触发器
-E      备份事件
-F 在备份开始时,刷新一个新binlog日志
--master-data=2
以注释的形式,保存备份开始时间点的binlog的状态信息
功能：
（1）在备份时，会自动记录，二进制日志文件名和位置号
（2） 自动锁表（FTWRL）
（3）如果配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行“热“”备，
实际上是实现   快照备份。
--single-transaction
 innodb 存储引擎开启热备(快照备份)功能 

案例：

1. 5.6 100+G 有MyISAM表，做大批量DML，mysqldump备份数据库出现hang住。
2. 5.6 3000w表做DDL，改数据类型，备份期间，锁严重。
   master-data可以自动加锁
   （1）在不加–single-transaction ，启动所有表的温备份，所有表都锁定
   （2）加上–single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能

–set-gtid-purged=auto
auto , on
off
使用场景:

1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. auto , on:在构建主从复制环境时需要的参数配置
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=ON >/data/backup/full.sql
--max-allowed-packet=#
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M
>/data/backup/full.sql
--max-allowed-packet=#
The maximum packet length to send to or receive from server

例子：

mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F-%T).sql.gz

mysqldump备份的恢复方式（在生产中恢复要谨慎，恢复会删除重复的表）

set sql_log_bin=0;
source /backup/full_2018-06-28.sql

注意：

1、mysqldump在备份和恢复时都需要mysql实例启动为前提。
2、一般数据量级100G以内，大约15-45分钟可以备份成功，但恢复时间通常需要5-10倍时间，数据量
级很大很大的时候（PB、EB）
3、mysqldump是覆盖形式恢复的方法。
一般我们认为，在同数据量级，物理备份要比逻辑备份速度快.
逻辑备份的优势:
1、可读性强
2、压缩比很高

2.5.12使用mysqldump批量导出

mysqldump -u username -p'xxx' -T target_dir db_name tb_name [option];

其中option参数是以下几种可选参数：

--fields-terminated-by 'string' 字段分隔符
--fields-enclosed-by 'char' 字段引用符
--fields-optionally-enclosed-by 'char' 字段引用符，只在char,varchar,text等字段
类型上生效
--fields-escaped-by 'char' 转义字符
--lines-terminated-by 'string' 记录结束符，即换行符

mkdir /data/backup/
chown mysql.mysql /data/backup -R
mysqldump -uroot -p123 -h 10.0.0.53 --single-transaction --master-data=2 --
triggers --routines --events --fields-terminated-by ',' --fields-enclosed-
by '"' world -T /data/backup/

2.5.13 使用mysqlimport批量导入

mysqlimport -uroot -p 'xxx' [--local] db_name order_tab.txt [iption]

参数：

--fields-terminated-by=name  指定字段分隔符
--fields-enclosed-by=name 指定字段引用符
--fields-optionally-enclosed-by=name  指定字段引用符，但只在char、varchar、text字段上使用引用符
--fields-escaped-by=name  指定转义字符
--lines-terminated-by=name  指定行记录结束符（换行符）
--ignore-liens=number  忽略前几行
--low-priority 碰到有其他线程update操作操作的表与导入操作表相同时，延迟执行导入操作
-i, --ignore 如果碰到唯一键冲突就忽略冲突行导入
-r, --replace 如果碰到唯一键冲突就覆盖冲突行导入
-L, --local 从客户端主机加载数据文本文件
-C, --compress 在C/S模型之间使用压缩传输数据
-c, --columns=name 指定需要导入哪些列，与load data语句中一样需要指定表定义中真实的列名，有多个列名时使用逗号分隔
--default-character-set=name 设置使用该选项指定的字符集来解析文本文件中的内容
-h, --host 指定导入server的主机IP
-p, --password[=name] 指定导入server的用户密码
-P, --port=# 指定导入server的监听端口
--use-threads=# 指定多少个线程并发执行load data语句（实测单表时指定多线程时要比单线程要快，由于数据量小，测试出来的差别并不大，官方并没有说明是基于什么级别的并发，\
只写了一句：Load files in parallel using N threads，推测可能是基于类似mydumper的并
发，但是多表导入时指定多线程就明显比单线程要快很多）
-u, --user=name 指定导入server的用户名
-d, --delete 指定导入操作之前先把表清空（实测重复导入时加了这个选项之后可以正常执行，，
通过解析binlog发现，发现binlog中记录的第二次和第一次导入的语句完全相同是，\
第二次导入时如果发现表中有冲突数据，就先执行的不带where条件的delete，所有表先delete掉，
然后再执行load data语句导入数据，另外，当与replace一起使用时，忽略replace选项）

mysqlimport用法演示示例

单表

mysqlimport -uroot -p123 -h10.0.0.51 world /data/backup/city.txt

多表

mysqlimport -uroot -p123 -h10.0.0.51 --replace world /data/backup/*.txt

多表导入时可以使用参数–use-threads指定多个线程

mysqlimport -uroot -p123 -h10.0.0.51 --replace --use-threads=8 world
/data/backup/*.txt