mysql的备份与恢复

一 mysql 备份的类型

冷备份：关闭数据、停止业务
温备份：加锁备份
热备份：在先备份，不会影响到也正常运行

二备份方式

1. 逻辑备份：基于SQL语句的备份

（1）mysqldump建库、建表、数据插入
（2）基于二进制日志：数据库的所有变化类的操作
（3）基于复制的备份：将二进制日志实时传送到另一台机器并且恢复

2. 物理备份

（1）xtrabackup进行物理备份
（2）拷贝数据文件（冷备）

三备份工具

（1）mysqldump
　　　　mysql原生自带很好用的逻辑备份工具
（2）mysqlbinlog
　　　　实现binlog备份的原生态命令
（3）xtrabackup
　　 precona公司开发的性能很高的物理备份工具

四 mysqldump备份工具使用

　　优点：逻辑备份工具，都是SQL语句，都是文本格式，便于查看和编辑，更便于压缩
　　缺点：备份效率较慢

1.mysqldump常用参数

　　-u -p -h -S -P

2. 全库备份

　　-A

mysqldump -uroot -p123456 -A >/backup/full.sql

3. 单库备份

例子：

mysqldump -uroot -p123456 -B ysl >/backup/ysl.sql

mysqldump -uroot -p123456  ysl >/backup/ysl1.sql

-B 增加建库（create）及（use库）的语句后面恢复时，不需要手动创建库和use

不加 -B 后面恢复时，要先创库 use库

另外 -B 选项可以实现，同时备份多个库，备份到一个文件中

mysqldump -uroot -p123456 -B ysl test >/backup/ysl_test.sql

如果不加 -B 就是备份 ysl库下 test表

mysqldump 库1 表1 表2 表3 > 库1.sql

4. 生产环境备份额外参数

　　-R　　　　备份存储过程和函数数据
　　--triggers 　　备份触发器数据

mysqldump -uroot -p123456  -A  -R --triggers   >/backup/ysl.sql

-F, --flush-logs 　　刷新binlog日志，为了方便将来二进制日志截取时的起点缺点有多少个库就会刷新多少次binlog日志

--master-data={1|2} 告诉你备份后时刻的binlog位置 2 注释 1 非注释,要执行(主从复制)

锁表:适合所有引擎(myisam,innodb)
　　-x, --lock-all-tables
　　-l, --lock-tables

　　--single-transaction 对innodb引擎进行热备
　　通过快照的方式实现热备

压缩备份:

#压缩备份:

mysqldump -uroot -p123 -A  -R --triggers --master-data=2 --single-transaction |gzip >/backup/all_$(date +%F).sql.gz

解压：

　　gunzip all_2019-09-16.sql

5. mysqldump备份恢复

使用source命令进行恢复:

mysql>set sql_log_bin=0;

mysql> source /backup/xxx.sql;

五. 模拟故障并恢复（mysqldump + mysqlbinlog）

背景环境：

    正在运行的网站系统，mysql数据库，数据量25G，日业务增量10-15M。

备份方式：

    每天23:00点，计划任务调用mysqldump执行全备脚本

故障时间点：

    上午10点，误删除了一个表

-------

思路：

    1、断开业务，防止对数据库二次伤害，挂出维护页面

    2、搭建备用库，恢复全备

    3、截取昨天晚上23:00之后到上午10点误删除操作之前的二进制日志

    4、恢复到备用库，验证数据可用性和完整性

    5、两种方案恢复前端应用

        5.1 备用库导出误删除的表，导入到生产库，开启业务

        5.2 直接将应用切割到备用库，替代生产库，开启业务

1. 原始数据创建

create database ysl   charset utf8mb4;

create table student(id int, name varchar(20)) engine=innodb;

use ysl;

insert into student values (1,'y1');

insert into student values (2,'y2');

insert into student values (3,'y3');

commit;

2. 晚上23点到了开始备份数据

mysqldump -uroot -p123456 -A  -R --triggers --master-data=2  --single-transaction | gzip  >/backup/all_$(date +%F).sql.gz

3.模拟第二天（23:00-10:00）业务对数据的修改

insert into student values (4,'zhang33');

insert into student values (5,'li44');

insert into student values (6,'wang54');

commit;

4. 模拟故障

drop table student;

5、恢复

1) 准备全量并获取到备份文件中的binlog的截取起点

cd /backup/; gunzip all_2019-09-17.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='my-bin.000002', MASTER_LOG_POS=852779;

-- 找到全量截取起点

-- 找到   结束点

show  binlog events in 'my-bin.000002';

2）截取二进制日志

mysqlbinlog   --start-position=852779  --stop-position=853177 /data/binlog/my-bin.000002 >/backup/binlog.sql

3)恢复全备+binlog

set sql_log_bin=0;

source /backup/all_2019-09-17.sql

source /backup/binlog.sql

六. Xtrabackup物理备份工具使用

https://www.cnblogs.com/augustyang/p/11535355.html

巴特西

第七章 mysql的备份与恢复

mysql的备份与恢复

一 mysql 备份的类型

二备份方式

1. 逻辑备份：基于SQL语句的备份

2. 物理备份

三备份工具

四 mysqldump备份工具使用

1.mysqldump常用参数

2. 全库备份

3. 单库备份

4. 生产环境备份额外参数

5. mysqldump备份恢复

五. 模拟故障并恢复（mysqldump + mysqlbinlog）

1. 原始数据创建

2. 晚上23点到了开始备份数据

3.模拟第二天（23:00-10:00）业务对数据的修改

4. 模拟故障

5、恢复

1) 准备全量并获取到备份文件中的binlog的截取起点

2）截取二进制日志

3)恢复全备+binlog

六. Xtrabackup物理备份工具使用

最新文章

热门文章

巴特西

第七章 mysql的备份与恢复

mysql的备份与恢复

一 mysql 备份的类型

二 备份方式

1. 逻辑备份：基于SQL语句的备份

2. 物理备份

三 备份工具

四 mysqldump备份工具使用

1.mysqldump常用参数

2. 全库备份

3. 单库备份

4. 生产环境备份额外参数

5. mysqldump备份恢复

五. 模拟故障并恢复（mysqldump + mysqlbinlog）

1. 原始数据创建

2. 晚上23点到了 开始备份数据

3.模拟第二天（23:00-10:00）业务对数据的修改

4. 模拟故障

5、恢复

1) 准备全量 并获取到备份文件中的binlog的截取起点

2）截取二进制日志

3)恢复全备+binlog

六. Xtrabackup物理备份工具使用

最新文章

热门文章

二备份方式

三备份工具

2. 晚上23点到了开始备份数据

1) 准备全量并获取到备份文件中的binlog的截取起点