MySQL 备份恢复 (一)

备份类型

热备

在数据库正常业务时,备份数据,并且能够一致性恢复（只能是innodb）
对业务影响非常小

温备

锁表备份,只能查询不能修改（myisam）
影响到写入操作

冷备

关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.
业务停止

备份方式及工具介绍

逻辑备份工具

基于SQL语句进行备份
mysqldump       
mysqlbinlog

物理备份工具

基于磁盘数据文件备份
xtrabackup(XBK) ：percona 第三方
MySQL Enterprise Backup（MEB）

逻辑备份和物理备份的比较

mysqldump (MDP)

优点：
1.不需要下载安装
2.备份出来的是SQL，文本格式，可读性高,便于备份处理
3.压缩比较高，节省备份的磁盘空间

缺点：
4.依赖于数据库引擎，需要从磁盘把数据读出
然后转换成SQL进行转储，比较耗费资源，数据量大的话效率较低
建议：
100G以内的数据量级，可以使用mysqldump
超过TB以上，我们也可能选择的是mysqldump，配合分布式的系统
1EB  =1024 PB =1000000 TB

xtrabackup(XBK)

优点：
1.类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高
缺点：
2.可读性差
3.压缩比低，需要更多磁盘空间
建议：
>100G<TB

备份策略

备份方式：
全备:全库备份，备份所有数据
增量:备份变化的数据
逻辑备份=mysqldump+mysqlbinlog
物理备份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog
备份周期:
根据数据量设计备份周期
比如：周日全备，周1-周6增量

备份专用基本参数

-A 全备参数

mkdir -p /data/backup

mysqldump -uroot -pKlvchen_123 -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. 

# 补充:
# 1.常规备份是要加 --set-gtid-purged=OFF,解决备份时的警告
mysqldump -uroot -pKlvchen_123 -A  --set-gtid-purged=OFF >/backup/full.sql
# 2.构建主从时,做的备份,不需要加这个参数
mysqldump -uroot -pKlvchen_123 -A  --set-gtid-purged=ON  >/backup/full.sql

-B db1 db2 db3 备份多个单库

# 说明：生产中需要备份，生产相关的库和 MySQL 库, -B 选项会加上建库语句
mysqldump -uroot -pKlvchen_123 -B bbb klvchen >/data/backup/bbb_klvchen.sql

备份单个或多个表

# 备份 klvchen 数据库下的 t1,t2 表
mysqldump -uroot -pKlvchen_123 klvchen t1 t2 >/data/backup/t1_t2.sql
# 以上备份恢复时:必须库事先存在,并且 use 才能source恢复

高级参数应用

-R: 备份存储过程及函数; --triggers: 备份触发器; -E: 备份事件

mysqldump -uroot -pKlvchen_123 -A -R -E --triggers >/data/backup/full.sql

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

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

--master-data=2

以注释的形式,保存备份开始时间点的binlog的状态信息
mysqldump -uroot -p  -A  -R --triggers --master-data=2   >/back/world.sql
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

auto, on, off 
使用场景:
1. --set-gtid-purged=OFF,可以使用在日常备份参数中.
mysqldump -uroot -pKlvchen_123 -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
2. auto , on:在构建主从复制环境时需要的参数配置
mysqldump -uroot -pKlvchen_123 -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

# 注
开启gtid后，备份的时候使用
--set-gtid-purged=ON
备份出来的数据，导入到其他数据库里，是不会写入bin-log日志的。但是会执行gtid语句，而且这些gtid语句的uuid是备份这数据数据库的uuid

--set-gtid-purged=OFF
会写入bin-log, gtid会是导入数据的那台mysql的uuid

--max-allowed-packet=

The maximum packet length to send to or receive from server.

mysqldump -uroot -pKlvchen_123 -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql

select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|            134217728 |
+----------------------+

实现所有表的单独备份

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