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
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值