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

 

转载于:https://www.cnblogs.com/augustyang/p/11529068.html