mysql 数据库DBA课程07_01 备份恢复

逻辑备份_mysqldump
1、在备份恢复中的职责
1.1、备份策略设计要考虑的内容：
     备份周期：根据数据量来评估，比如，70G的数据，每天全备，mysqldump
     备份方式：全备，增量
     备份工具：mysqldump , Xtrabckup(percona公司的) mysqlbinlog
逻辑备份：全备：Mysqldump 增量：binlog备份，直接把文件拷走，（flush logs,cp）把除了最后一个日志拷走
物理备份：Xtrabackup，自带，全备 增量备份
++++++++++
1.2、检查备份可用性
    crontab -l ------>查看备份脚本，找到备份路径，看备份日志，检查，备份的文件的数据大小、以及内容。
1.3、定期的恢复演练
1.4、数据恢复
    只要备份和日志是完整的，恢复到故障之前的时间点（快速）
1.5、数据迁移
    mysql------->mysql
    其他平台------->mysql
    mysql-------->其他平台
    操作系统不同的迁移
2、备份的介绍
     备份的类型
热备：数据库正在运行的时候备份，对业务影响最小。（不锁表备份）只有 InnoDB引擎支持
温备：需要长时间锁表备份 ### MyISAM 引擎支持
冷备：需要业务关闭
3、备份工具使用
3.1、mysqldump
连接数据库参数：-u -p -S(大s) -h -P(大p)
备份的基础参数：-A ：全部库 -B : 单库或者，多库的备份 库 表---->备份一库的某个表

mysqldump -uroot -p -A >/backup/full.sql 全备所有库
mysqldump -uroot -p --set-gtid-purged=OFF -A >/backup/full_nogtid.sql


备份多个库：（-B 后面只能接 库 ，不能接 表）
mysqldump -uroot -p --set-gtid-purged=OFF -B world zabbix ysj >/backup/multidb.sql
mysqldump -uroot -p -B world zabbix ysj >/backup/multidb.sql

备份某个 库的某个表
mysqldump -uroot -p --set-gtid-purged=OFF world city country >/backup/table.sql
mysqldump -uroot -p world city country >/backup/table.sql

mysqldump -uroot -p world >/tmp/world.sql #### 只要是world库下面的所有表，都单独备份
备份出来的内容里面，没有，建库语句 和 use 库 语句
++++++++++++++
mysqldump -uroot -p -B world >/tmp/world.sql
  -B ，备份出来的是有，建库语句 和 use 库 语句。+++++++++++++++++++++++++++++++
特殊备份参数：
-R：备份存储过程和函数，这个参数就是检查有没有自定义的存储过程和函数，有就备份，没有就不备份。
-E：事件，相当于linux当中的计划任务。功能同上，有就备份，没有就不备份
--triggers：触发器，功能同上，有就备份，没有就不备份。

++++++++++
--master-data=2
****** 默认等于 0 ，2，以注释的形式记录二进制日志信息，1，以命令记录
    (1)、记录，备份时的，起点(position)和 binlog文件名
--CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=808
    (2)、自动锁表
    隐含了 锁表 的功能，锁表有两种情况，
加 --single-transaction, 对于Innodb 表不锁表备份（快照备份），其他引擎的表，还是要锁表，还是温备
    不加，--single-transaction就是全局锁表，进行温备，这时候可能会阻塞我们一些修改性的业务。

公司 60G数据，每天全备，备份工具mysqldump
周三，下午2点，数据库损坏。

问，你有什么好的恢复思路：

首先，找到最近的（周二）全备恢复，
然后，截取全备到损坏之前的binlog
最后，恢复binlog
--single-transaction
*******
对于InnoDB的表，进行，一致性的快照备份，不锁表备份，类似于热备。
真正的热备，在备份时，会把备份开始到结束那段时间内产生的新的数据一起备份。xtrabackup可以做到

如果面试问Mysqldump 怎么实现热备的？

回答：是这样，我们公司一般备份就是加两个核心参数，一个是--master-data=2，一个是--single-transaction ,mysqldump所谓的热备，其实是通过--single-transcation来实现的，它的核心理念就是在备份的时候，会生成一个单独的事务，保存它当前备份时候的一个一致性快照，备份的是快照里面的内容，所以说不会阻塞其他事务的变更，这就是mysqldump的所谓的热备，实际上是一种快照技术来实现的，基于的是MVCC的这种机制，但是只有InnoDB的表才具备这个功能。

4、恢复案例：
4.1、背景环境：
正在运行的网站系统,mysql-5.7.20 数据库，数据量50G,日业务增量1-5M。
4.2、备份策略：
每天23：00点，计划任务调用mysqldump 执行全备脚本
4.3、故障时间点：
年底故障演练：模拟周三上午10点误删除数据库，并进行恢复。

4.4、思路：
(1)、停业务，挂维护页，避免数据的二次伤害
(2)、找一个临时库，恢复周二23：00全备
(3)、截取周二23:00-------到 周三10点，误删除之间的binlog，恢复到临时库
(4)、测试可用性和完整性
(5)、重启业务
方法一、直接使用时时库顶替原生产库，前端应用割接到新库。
方法二、将误删除的表从临时库中导出，再导入到原生产库。
(6)、开启业务

处理结果：经过20分钟的处理，最终业务恢复正常

4.5、故障模拟演练

4.5.1、准备数据
create database backup charset utf8mb4;
use backup;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;



4.5.2、模拟周二 23：00 全备
mysqldump -uroot -p -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip >/bak/full_$(date +%F).sql.gz
4.5.3、模拟周二 23：00 到周三 10点之间数据变化
use backup;
insert into t1 values(11),(22),(33);
create table t2(id int);
insert into t2 values(11),(22),(33);
commit;



4.5.4、模拟故障，删除backup库
drop database backup;
4. 6、恢复过程
4.6.1、准备临时数据库（本机上的一个多实例 3307，可以是其他服务器。）
systemctl start mysqld3307
mysql -S /data/3307/mysql.sock
4.6.2、准备备份的数据
(1)、准备全备数据
cd /bak/
gzip -d full_2020-05-01.sql.gz
(2)、截取二进制日志
vim /bak/full_2020-05-01.sql
起点：position

终点：position
登录原数据库：
show masster status;
show binlog events in 'mysql-bin.000004';



cd /data/binlog/
mysqlbinlog --skip-gtids
--start-position=808 --stop-position=1573 mysql-bin.000004>/bak/bin.sql

只要你的数据库开启了gtid，导出日志的时候，都要加--skip-gtids

4.6.3、恢复备份到 临时 库
mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /bak/full_2020-05-01.sql;
source /bak/bin.sql;
set sql_log_bin=0;
4.6.4、将故障表从临时库中导出，并恢复到生产
mysqldump -S /data/3307/mysql.sock -B backup >/bak/bak.sql

再登录到原库
set sql_log_bin=0;
source /bak/bak.sql;
set sql_log_bin=1;

5、练习
(1)、创建一个数据库 oldboy
(2)、在oldboy 下创建一张表t1
(3)、插入5行任意数据
(4)、全备
(5)、插入三行数据，任意修改3行数据，删除1行数据。
(6)、删除所有数据
(7)、再t1表中再插入5行新数据，修改3行数据。
需求：跳过第六步恢复表数据
+++++++++++++++++++++++++++++++++
(1)、(2)、(3)
create database oldboy charset utf8mb4;
use oldboy;
create table t1(id int);
insert into t1 values(1),(2),(3),(4),(5);
commit;


全备：
(4)、mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers >/bak/full.sql

(5)、insert into t1 values(6),(7),(8);
commit;


update t1 set id=10 where id >5;
commit;


delete from t1 where id=5;
commit;


(6)、delete from t1;
commit;


(7)、insert into t1 values(1),(2),(3),(4),(5);
commit;


update t1 set id=10 where id>2;
commit;


++++++++++++++++==

恢复数据：
查看日志，确定，起点
vim /bak/full.sql

SET @@GLOBAL.GTID_PURGED='b3060a95-5e86-11ea-bdb9-000c29e6dd30:1-25'; ##意思，该备份已经具备了1-25号事务了，所以我们的binlog起点应该是在，26开始。

结束点是在最后的，31号事务
(5)、插入三行数据（26），任意修改3行数据（27），删除1行数据（28）。
(6)、删除所有数据（29）
(7)、再t1表中再插入5行新数据（30），修改3行数据（31）。

根据5、6、7、操作，删除所有数据 的事务号是，29
show binlog events in 'mysql-bin.000004';

所以我们要截取的日志是，26-31 跳过，29

导出binlog
cd /data/binlog/
mysqlbinlog --skip-gtids
--include-gtids='b3060a95-5e86-11ea-bdb9-000c29e6dd30:26-31' --exclude-gtids='b3060a95-5e86-11ea-bdb9-000c29e6dd30:29' mysql-bin.000004 >/bak/full_bin.sql

开始恢复数据：
set sql_log_bin=0;
source /bak/full.sql;
source /bak/full_bin.sql;
set sql_log_bin=1;
use oldboy;
select * from t1;

++++++++++++++++==

6、扩展参数：

--set-gtid-purged=AUTO/ON 默认为auto，自动判断，一般会加上gtid号。构建主从时，要使用这个参数
SET @@GLOBAL.GTID_PURGED='b3060a95-5e86-11ea-bdb9-000c29e6dd30:1-25';

--set-gtid-purged
作用就是会不会在备份文件中出现，SET @@GLOBAL.GTID_PURGED='b3060a95-5e86-11ea-bdb9-000c29e6dd30:1-25';
--set-gtid-purged=OFF (仅是做普通的备份恢复时，可以添加。)

备份的文件中没有SET @@GLOBAL.GTID_PURGED='b3060a95-5e86-11ea-bdb9-000c29e6dd30:1-25';

在构建主从时，必须要使用AUTO/ON
++++++++++++++++++++========================

--max_allowed_packet=128M # 控制的是 备份时传输数据包的大小，几千万行的大表备份时会用到，默认的数据包大小不够，这时要调整这个参数。
最好是在备份的时候加上这个参数;
mysqldump -uroot -p -A -R --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip >/bak/full_$(date +%F).sql.gz

+++++++++++++++++======
Mysql 5.7 导出数据的限制

vi /etc/my.cnf
secure-file-priv=
不写表示空，就是可以导出到任何地方，也可以写具体路径。

concat 组合语句
select concat("alter table ",table_schema," ",table_name," discard tablespace;") from information_schema.tables where table_schema='world' into outfile '/tmp/world_discard.sql';

vim /tmp/world_discard.sql

mysql -uroot -p </tmp/ world_discard.sql #实现了批量操作，也可以用source 命令
将查询的结果导出到一个文件，一般是导出的是 csv格式的。
select * from city into outfile '/tmp/city.csv';