DevOps开发运维
成长之路

MySQL逻辑备份工具mysqldump

mysqldump是一个逻辑备份的客户端工具

客户端通用参数

-u -p -S -h -P 
本地备份:
mysqldump -uroot -p -S /tmp/mysql.sock
远程备份:
mysqldump -uroot -p -h 10.0.0.51 -P3306

备份专用基本参数

-A 全备参数

mysqldump -uroot -p -A >/data/backup/full.sql
time mysqldump -uroot -p123456 -S /tmp/mysql.sock -A >/data/backup/full.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all 
transactions, even those that changed suppressed parts of the database. If you don't want to 
restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases 
--triggers --routines --events. 
扩展:测试机器IO
dd if=/dev/zero of=/tmp/bigfile bs=512 count=1024000
50G数据备份时间大约在15-30min为合理时间

-B 备份多个单库

说明:生产中需要备份,生产相关的库和MySQL库
mysqldump -uroot -p123 -S /tmp/mysql.sock -B oldboy world >/data/backup/full.sql

备份单个或多个表

world数据库下的city,country表
mysqldump -uroot -p world city country >/backup/bak1.sql
以上备份恢复时:必须库事先存在,并且use才能source恢复

备份库表写法与区别

mysqldump -uroot -p -B world>/data/backup/bak.sql
mysqldump -uroot -p world>/data/backup/bak1.sql
vimdiff /data/backup/bak.sql /data/backup/bak1.sql
可以看出上面带-B是备份整个库,不带-B是备份库下面的所有表

对整个数据库下除了sys,performance,information,进行其他数据库下的所有表进行单独备份

select concat("mysqldump -uroot -p123 -S /tmp/mysql.sock ",table_schema," ",table_name," >/data
/backup/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema 
not in ('sys','information_schema','performance_schema') into outfile '/tmp/mysqldump.sh';
sh /tmp/mysqldump.sh

高级参数应用

特殊参数使用(必须要加)

-R(routines) 备份存储过程及函数
--triggers 备份触发器
-E(events) 备份事件
mysqldump -uroot -p -A -R -E --triggers >/data/backup/full.sql

-F 在备份开始时,刷新一个新binlog日志

mysqldump -uroot -p -A -R --triggers -F >/bak/full.sql

–master-data=2

以注释的形式,保存备份开始时间点的binlog的状态信息
mysqldump -uroot -p -A -R --triggers --master-data=2 >/back/world.sql
[root@db01 ~]# grep 'CHANGE' /backup/world.sql 
###-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;
功能:
(1)在备份时,会自动记录,二进制日志文件名和位置号
0 默认值
1 以change master to命令形式,可以用作主从复制
2 以注释的形式记录,备份时刻的文件名+postion号
(2)自动锁表
(3)如果配合--single-transaction,只对非InnoDB表进行锁表备份,InnoDB表进行“热“”备,实际上是实现快照备份。

–single-transaction

innodb 存储引擎开启热备(快照备份)功能 
master-data可以自动加锁
(1)在不加--single-transaction ,启动所有表的温备份,所有表都锁定
(2)加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能

–set-gtid-purged=auto(on,off)

auto,on为默认值,可不加。
使用场景:
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=#M

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=# 服务端接受的最大的包的的大小

备份必加参数

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction 
--set-gtid-purged=OFF >/data/backup/full.sql

实现所有表的单独备份

提示:
information_schema.tables
mysqldump -uroot -p123 world city >/backup/world_city.sql

select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," --master-data=2 
--single-transaction --set-gtid-purged=0 -R -E --triggers>/backup/",table_schema,"_",
table_name,".sql") from information_schema.tables where table_schema not in ('sys',
'information_schema','performance_schema');

备份时优化参数

(1) max_allowed_packet 最大的数据包大小
mysqldump -uroot -p123 -A -R --triggers --set-gtid-purged=OFF --master-data=2 
max_allowed_packet=128M --single-transaction|gzip > /backup/full_$(date +%F).sql.gz
(2) 增加key_buffer_size (临时表有关)
(3) 分库分表并发备份 (作业)
(4) 架构分离,分别备份 (架构拆分,分布式备份)

mysqldump全库备份过程中怎样加锁?加了什么锁?

对于非innodb表(FTWRL)全局锁,对于mysql库下系统表备份的时候,会加global read lock
赞(0)

评论 抢沙发

评论前必须登录!

 

LNMP社群 不仅仅是技术

关于我们网站地图