客户端通用参数
-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 存储引擎开启热备(快照备份)功能
案例:
- 5.6 100+G 有MyISAM表,做大批量DML,mysqldump备份数据库出现hang住。
- 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