【MySql】Mysql之备份与恢复

目次
一、mysql日记概述
1、日记类型与作用
2、日记配置文件 
3、日记配置文件的查询 
二、备份的类型❤
1、物理备份
1.1 冷备份
1.2 热备份
1.3 温备份
2、逻辑备份❤
2.1 完全备份
2.2 差别备份
2.3 增量备份
2.5 如何选择逻辑备份计谋
3、常见的备份方法
3.1 物理冷备
3.2 专门备份工具
3.3 启用二进制进行增量备份
三、MYsql完全备份与恢复
1、物理冷备份与恢复
2、mysqldump备份与恢复（温备份）
2.1 完全备份和恢复一个或多个数据库
2.1.1 备份恢复一个或多个库或全部库
2.1.2 恢复一个库或多个库
2.1.3 加-databases和不加的区别实行
2.2 完全备份和恢复指定库中的部门表
2.2.1 备份指定库中的部门表
2.2.2 恢复指定库中的部门表
四、mysql增量备份与恢复❤❤
1、二进制文件介绍
1.1 二进制日记记录的格式
1.2 开启二进制功能
1.3 检察二进制文件的内容
 2、 增量备份（完备+增备）
3、 增量恢复
3.1 增量恢复之一样平常恢复
3.1 模仿丢失全部数据的恢复步调
2.2 增量恢复之断点恢复实行
2.2.1 增量备份
2.2.1 基于位置恢复-at 
2.2.2 基于时间恢复
四、总结

数据备份的重要性

• 在生产环境汇总，数据的安全性至关重要
  
• 任何数据的丢失都大概产生要种的后果
  

造成数据丢失的原因

• 程序错误
  
• 以为操作错误
  
• 运算错误
  
• 灾难（火灾、地动）、偷窃
  

一、mysql日记概述

mysql的日记默认生存位置为：/usr/local/mysql/data 下面
1、日记类型与作用

1.1 redo （重做日记）
达到事务一致性（每次重启都会重做）
   作用：确保日记的长期性，防止在发生故障，脏页未写入磁盘。重启数据库会进行 redo log 执行重做，达到事务一致性。
  1.2 undo（回滚日记）
   作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复和读取已提交隔离级别就是通过mvcc+undo实现。
  ❣️1.3 errorlog（错误日记）
   作用：mysql本身启动，停止，运行期间发生的错误信息
  ❣️1.4 slow query log（慢查询日记）
   作用：记录执行时间过长的sql语句，时间默认值为10s，可以配置，只记录执行成功，凌驾设置时间的sql语句，都会被存在到慢查询日记中。 还可以提醒优化，对于查询慢的语句进行操作（比如是网络问题，照旧未添加索引等） 
  ❣️ 1.5 bin log （二进制日记）
   作用：用于基于日记形式的数据恢复。用于主从复制，实现主从同步，记录的内容是：数据库中执行的sql语句
  ❣️1.6 relay log（中继日记）
    作用：用于数据库主从同步，将主库发来的bin log生存在当地，然后从库进行回放。
  1.7 general log（平常日记） 
   记录数据库的操作明细，默认关闭，开启后会降低数据库的性能
  2、日记配置文件 

1. 日志文件开启关闭日志的位置
2. vim /etc/my.cnf
4. #错误日志
5. log-error=/usr/local/mysql/data/mysql_error.log       
6.    
7. #通用查询日志
8. general_log=ON
9. general_log_file=/usr/local/mysql/data/mysql_general.log
10.    
11. #二进制日志
12. log-bin=mysql-bin       
13.    
14. #慢查询日志
15. slow_query_log=ON
16. slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
17. long_query_time=5
19. 2、修改完成需要重新mysql服务
20. systemctl restart mysqld.service

复制代码

当我们重启了myslq服务之后，会在数据库目次下，天生以下日记文件

当我们重新启动mysql服务，二进制文件会重新天生一个新的二进制文件

3、日记配置文件的查询 

1. #登入mysql
2. mysql -u root -p[密码]
4. #查看通用查询日志是否开启
5. show variables like 'general%';       
7. #查看二进制日志是否开启
8. show variables like 'log_bin%';               
10. #查看慢查询日功能是否开启
11. show variables like '%slow%';               
13. #查看慢查询时间设置
14. show variables like 'long_query_time';       
16. #在数据库中设置开启慢查询的方法
17. set global slow_query_log=ON;                                                                       

复制代码

检察平常日记是否开启

检察二进制日记是否开启

 检察慢查询日记是否开启

在数据库中关闭慢查询日记

二、备份的类型❤

数据库备份可以物理备份和逻辑备份
物理备份是对数据库操作体系的物理文件（如数据文件、日记文件等）的备份，这种类型的备份实用于在出现问题的时候需要快速恢复的大型重要数据库。
1、物理备份

物理备份又可以分为冷备份（脱机备份）、热备份（毗连备份）、温备份。
1.1 冷备份

是在关闭数据库的时候进行的备份，可以直接（tar）

• 关闭mysql数据库
  
• 使用tar下令直接打包数据库文件夹
  
• 直接更换现有mysql目次即可（/usr/local/mysql/data 数据目次）
  

1.2 热备份

数据库处于运行状态，依赖数据库的日记文件（第三方工具：mysqlhotcopy）
1.3 温备份

数据库锁定表格（不可写入但可读）的状态下进行备份操作。（mysqldump备份）

• 将制定的库、表到处为sql脚本
  
• 使用下令mysql带入备份的数据
  

2、逻辑备份❤

逻辑备份是对数据库逻辑组件的备份，表示为逻辑数据库布局，这种类型的备份实用于可以编辑数据值或布局。
从数据库的备份计谋角度来看，备份又可以分为完全备份，差别备份和增量备份。

2.1 完全备份

每次对数据进行完整备份，即对整个数据库、数据库布局和文件布局的备份，生存的是备份完成时候的数据库，是差别备份与增量备份的基础完全备份的备份，与恢复操作都非常便便，但是是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长。
上风

• 备份恢复操作简单
  

劣势

• 随着备份的次数越来越多，占用的磁盘空间和资源也较多。
  
• 数据存在大量的重复
  

2.2 差别备份

备份那些自从上次完全备份之后被修改过的全部文件，备份的时间 节点是从上次完整备份起 ，备份数据量会越来越大。 恢复数据时只需要恢复上次的完全备份与最佳的一次差别备份。
上风

• 恢复时只需要恢复全备文件和就近一次备份文件
  

劣势

• 数据会越来越大
  

2.3 增量备份

只有那些在上次完全备份或者增量备份后被修改的文件才会被备份以 上次完整备份或上次增量备份的时间为时间点 ，仅备份期间内的数据变化，因而备份的数据量小，占用空间小，备份速率快。但恢复时， 需要从上一次的完整备份开始到最后一次增量备份之间的全部增量依次恢复，如中间某次的备份数据粉碎，将导致数据的丢失

上风

• 没有重复的备份数据，备份数据量不大，所需时间短
  

上风

• 没有重复的备份数据，备份数据量不大，所需时间短
  

2.5 如何选择逻辑备份计谋

完全备份： 一周进行一次全备，区内的时间需要在不提供业务的时间区间进行，晚上10点到早上5点进行全备。
增量备份： 一天一次增量备份
差别备份： 1/2/3天一次增量备份
3、常见的备份方法

3.1 物理冷备

备份时数据库处于关闭状态、直接打包数据库文件。

• 长处：备份速率快，恢复时也是最简单的
  
• 缺陷：数据的时实性较差，不能再业务正常运行时进行tar备份，
  

3.2 专门备份工具

mysqldump ：常用的逻辑备份工具（mysql自带）
mysqlhotcopy：仅拥有备份myisam和ARCHIVE表
3.3 启用二进制进行增量备份

进行增量备份，热备份软件：Percona、XtraBackuo、 mysqlbackup
三、MYsql完全备份与恢复

1、物理冷备份与恢复

原理：将数据库文件进行压缩到别的目次备份，恢复时，直接解压会以前的目次即可。

2、mysqldump备份与恢复（温备份）

首先在创建两个数据库（info01、info02），在每个数据库中插入一张表，并插入数据。
2.1 完全备份和恢复一个或多个数据库

2.1.1 备份恢复一个或多个库或全部库

备份

1. mysqldump -uroot -p123 --databases info01 > /opt/info01.sql
2. #备份一个数据库，info01。
3. mysqldump -uroot -p123 --detabases info01 info02 > /opt/info01-info02.sql
4. #备份两个数据库，info01，info02
6. mysqldump -uroot -p123 --all-databases >/opt/all-database.sql
7. #备份所有的数据库

复制代码

2.1.2 恢复一个库或多个库

恢复有两种方法，

• 如果是使用mysqldump备份的文件，那么可以使用mysql导入的方法恢复
  
• 尚有一种方法就是使用source下令在数据库中加载备份的目次即可
  

使用mysql下令进行导入恢复

1. mysql -uroot -p123 -e 'drop database info01;'
2. #使用-e的方法进入数据库执行删除数据库的操作
3. mysql -uroot -p123 -e 'show databases;'
4. #使用-e的方法进行显示数据库的数量
6. mysql -uroot -p123 </opt/info01.sql
7. #使用mysql命令恢复数据库info01
8. mysql -uroot -p123 -p 'show databases;'
9. #查看数据库中是否恢复成功

复制代码

 使用source进行恢复数据库

1. mysql -uroot -p123 -e 'drop database info01;'
2. #删除数据库info01
3. mysql -uroot -p123 -e 'drop database info02;'
4. #删除数据库info02
5. mysql -uroot -p123 -e 'show databases;'
6. #查看info01和info02数据库是否被删除
8. mysql -uroot -p123 -e 'source /opt/info01-onfo02.sql;'
9. #使用source进行恢复

复制代码

2.1.3 加-databases和不加的区别实行

• mysqldump严格来说属于温备份，会需要对标进行写入锁定
  
• 在全量备份与恢复实行中，加上–databases表示指定备份的是这个数据库以及下面全部表，如果不加–database时，表示只备份这个数据库下面的表，并不备份数据库。
  

 加–databases时实行

1. mysqldump -uroot -p123 --databases info01 > /opt/info01.sql
2. #全量备份，加上--databases，指定备份数据库，以及下面所有的表
4. mysql -uroot -p123
5. drop databases info01;
6. #进入数据库，进行删除数据库
8. source /opt/info01.sql
9. #恢复数据库
10. show databases;

复制代码

 

不加–databases时实行

1. mysqldump -uroot -p123 info01 > /opt/info01.sql
2. #没有加上--databases，表示只是备份info01数据库下面所有的表
4. mysql -uroot -p123;
5. drop database info01;
6. #进入数据库删除info01数据库
8. source /opt/info01.sql
9. #进行报错，因为没有指定数据库
10. crate database info01;
11. use info01;
12. source /opt/info01.sql
13. #创建了数据库后，进入数据库，再进行恢复。

复制代码

 

2.2 完全备份和恢复指定库中的部门表

2.2.1 备份指定库中的部门表

备份数据表的话，其中也有是否加-d选项的区别

• 加了-d表示指备份数据表的布局
  
• 不加-d表示备份数据表的布局以及数据
  

1. mysqldump -uroot -p123 info01 ydq >/opt/info01_ydq.sql
2. #备份info01数据库中的ydq表(表的数据也会备份)
4. mysqldump -uroot -p123 -d info01 ydq >/opt/info01_ydq.sql
5. #备份info01数据库中的ydq表(加了-d选项，表示指保存ydq的表结构)
7. grep -v "^--" /opt/ifco01-ydq.sql |grep -v "^/" |grep -v "^$";
8. #查看备份文件过滤掉--开头、/开头的和空行

复制代码

 不加-d选项备份表

加-d选项备份表

2.2.2 恢复指定库中的部门表

恢复同样跟上面的恢复库文件一样，可以使用两种方法进行恢复
第一种：使用下令mysql恢复

1. mysql -uroot -p123 -e 'drop table info01.ydq;'
2. #删除info01数据库中的表
3. mysql -uroot -p123 -e 'show tables info01.ydq'
4. #查看info01数据库中的表
6. mysql -uroot -p123 info < /opt/ifo01-ydq.sql
7. #将info01库中的ydq表进行恢复
8. mysql -uroot -p123 -e 'show tables info01.ydq;'

复制代码

 第二种：使用source恢复

1. mysql -uroot -p123;
2. #登录数据库
4. use info01;
5. show tables;
6. #进入数据库查看所有的表
8. drop table ydq;
9. #删除ydq表
11. source /opt/info01-ydq01.sql;
12. #使用source命令执行恢复表

复制代码

四、mysql增量备份与恢复❤❤

mysql数据库增量恢复有，一样平常恢复、基于位置恢复、基于时间点恢复
一样平常恢复

• 将全部备份的二进制日记内容全部恢复
  

基于位置恢复

• 数据库在某一时间点大概既有错误的操作也有正确的操作
  
• 可以基于准确的位置跳过错误的操作
  
• 发生错误节点之间的一个节点，上一次正确操作的位置点停止
  

基于时间点恢复

• 跳过某个发生错误的时间点实现数据恢复
  
• 在错误时间带你停止，在下一个正确时间带你开始
  

1、二进制文件介绍

1.1 二进制日记记录的格式

二进制日记由三种差别的记录格式
①STTATEMENT（基于sql语句）

• 每一条涉及到被修改的sql语句都会记录在binlog中
  

缺点

• 日记量过大，如sleep（）函数，last_insert_id()>（聚合函数）,以及user-definedfuctions(udf)、垂从复制等架构记录日记时会出现问题
  

总结：

• 增删改查通过sql语句来实现记录，如果用高并发大概会出错，大概时间差别或者延迟，大概不是我们想像的恢复大概你先删除或在修改，大概会倒过来。准去率低。
  

② ROW（基于行）

• 只记录变更的记录，不记录sql语句的上下文环境
  

缺点

• 如果遇到update…set…where true 那么binlog的数据量会越来越大。
  

总结

• update、delete多行数据其作用，来用行记录下来，值计量变更的记录，不记录sql的上下文环境，比如sql语句记录一行，但是ROW就大概记录10行，但是准确性高，高并发的时候由于操作量能变低，所以记录都记下来。
  

③MIXED（肴杂模式）推荐使用

• 上面两种的肴杂版本，都进行记录
  
• 一样平常的语句使用statement，函数使用ROW方式存储
  

1.2 开启二进制功能

1. vim /etc/my.conf
2. [mysqld]
3. log-bin=mysql-bin
4. binlog_format = MIXED
5. #二进制日志有三种不同的记录格式：STATEMENT（基于SQL语句）、ROW（基于行）、MIXED（混合模式），默认为STATEMENT
6. server-id = 1
7. #server-id 为服务的序号，在MySQL主备、高可用中需要指定服务的序号
9. systemctl restart mysqld
10. #改为配置文件需要重启服务

复制代码

 

1.3 检察二进制文件的内容

开启了二进制文件，会在/data目次下天生二进制日记，但是使用cat检察的时候出现乱码，全部需要指定编码格式和解码进行检察

1. mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000001 > /opt/mysql-bin2.000001
2. #把日志文件复制到/opt目录下慢慢看,可以直接使用cat查看
4. 或者
5. cp mysql-bin.000001 /opt
6. #直接复制二进制文件到/opt
8. mysqlbinlog  --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000001
9. #--no-defaults : 默认字符集(不加会报UTF-8的错误)
10. #--base64-output=decode-rows: 使用64位编码机制去解码(decode)并按行读取(rows)
11. #-v: 显示详细内容
13. mysqladmin -uroot -p123 flush-logs;
14. #刷新二进制日志文件(为了不重启，进行刷新，这样二进制文件会重新生成一个，相当于增量备份)

复制代码

1. 日志文件内容解析(需要关注的点)
2. at 127                 #起始位置
3. 220810 23:15:25       #起始时间
4. create database ydq;   #具体操作
5. at 130                 #结束位置
6. 220810 13:15:50       #结束时间
8. at 138                 #表示下一个任务开始的时间
9. 220810 23:16:10
10. use ydq;
11. at 140

复制代码

  

  

   2、 增量备份（完备+增备）

1. mysqldump -uroot -p123 kgc ky20> /opt/ky20-$(date -%F).sql
2. #使用mysqldump进行完全备份数据库
4. mysqladmin -u root -p flush-logs
5. #可每天进行增量备份，生成新的二进制日志文件(例如:mysql-bin.00004)

复制代码

先进行完全备份

 在ky20表中创建一条语句

• 经过下面的操作，ky20表的内容已发生了改变。
  

 进行增量备份

3、 增量恢复

3.1 增量恢复之一样平常恢复

3.1 模仿丢失全部数据的恢复步调

1. drop table kgc.ky20;
2. #模拟ky20表中的所有数据全部丢失。
4. 现在需要还原ky20表，且还要恢复里面的两条数据
6. mysql -uroot -p123 kgc < /opt/kgc-ky20.sql
7. #先进行完会恢复，(但是完全恢复的文件中，ky20表中只有一条数据)
9. mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p123
10. #基于mysql-bin.000001日志文件恢复(恢复ky20表中的另一条数据)

复制代码

2.2 增量恢复之断点恢复实行

下面介绍二进制文件重要的内容，位置点用于基于位置点恢复，时间戳用于基于时间恢复。
 

2.2.1 增量备份

在前面加过在日记文件中有一些位置标示：at，代表一个位置点，背面恢复时，也可以基于位置点进行恢复。

1. mysqldump -uroot -p123 kgc ky20>/opt/kgc-ky20-两条语句.sql
2. #完全备份ky20表(表中只有两条数据)
4. mysql -uroot -p123 flush-logs;
5. #刷新二进制日志文件

复制代码

 先进行完全备份当前ky20表

然后在想数据库中创建了2条新的数据(现在有4条数据)

现在进行增量备份4条数据的日记文件 
 

现在有人不小心删除了与ky20表相关的东西，导致数据不完整，现在客户只想恢复三条语句，最后一条语句不想要了。

2.2.1 基于位置恢复-at 

1. mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/mysql-bin.000002
2. #将日志文件重定向到/opt目录下，方便查看，查看到第三条执行语句的位置点：5976
4. mysql -uroot -p123 kgc < /opt/kgc-ky20-两条语句.sql
5. #先进行全量恢复，(现在有两条数据)
7. mysqlbinlog --no-defaults --stop-position='5976' /usr/local/mysql/data/mysql-bin.000002 |mysql -uroot -p
8. #仅恢复到第三条语句结束的时候
10. ----------------------------下面与实验无关--------------------------------
11. mysqlbinlog --no-defaults --stop-position='968' /usr/local/mysql/data/mysql-bin.000002|mysql -uroot -p
13. mysqlbinog --no-defaults --start-position='754' /usr/local/mysql/data/mysql-bin.000002|mysql -uroot -p
14. #以上两条语句组合实现恢复从754的位置到623的位置结束

复制代码

那么就需要检察备份的日记文件中，检察第三条语句的节点是竣事位置的at是多少

检察第三条语句中的竣事位置点：5976

先进行完全备份恢复到2条数据 
 

再基于位置节点进行恢复

2.2.2 基于时间恢复

基于时间恢复和基于位置恢复是基本类似。现在使用基于时间用来恢复第三条语句

1. mysql -uroot -p123 kgc < /opt/kgc-ky20-两条语句.sql
2. #先进行全量恢复，(现在有两条数据)
4. mysqlbinlog --no-defaults --stop-datetime='220811 15:11:57' /usr/local/mysql/data/mysql-bin.000002 |mysql -uroot -p
5. #仅恢复到第三条语句结束的时候
7. --------------------------------下面不属于实验内容--------------------------------------
8. mysqlbinlog --no-defaults --start-datetime='220811 15:14:42' /usr/local/mysql/data/mysql-bin.000002 |mysql -uroot -p
9. #恢复的数据从220811 15:14:42 开始进行恢复

复制代码

 全备恢复到两条语句的位置

进行完全恢复和时间戳恢复 

四、总结

1、备份的类型
物理备份、逻辑备份
2、物理备份：冷备、温备、热备
①冷备： 需要关闭mysql服务，或者确保mysql服务在进行tar备份时，没有客户端的写入操作
②温备： mysqldump，这个是mysql自带的备份工具
特性： 逐表备份，每备份一张表时，会先drop删除，然后重新create创建表布局，然后再解锁表
（仅锁定写的操作，但可读），进行insert语句的备份，备份完成后，进行unlock解锁，然后继续备份下一个。
③热备： xtrabackup或mysqlhotcopy等热备工具（第三方），在mysql正常运行时，进行备份。
3、逻辑备份：全量备份、增量备份、差别备份
①全量备份： tar压缩、mysqldump -u -p --all-databases
②增量备份： 重要使用bin-log，来舒心天生新的增备的日记文件，可以通过：mysqladmin -u -p fiush-logs 来刷新天生新的增备的日记文件，同时可以团结crontan，完成主动刷新。
留意： 再进行基于二进制文件的备份恢复时，有须要的话，需要先回复完备的数据，再逐个恢复增备的数据，直到恢复至我们需要恢复的数据为止。
③差别备份： 重要备份一次完备，背面修改的数据全部基于完备进行恢复。（相当于快照）
4、日记的生存类型
①肴杂模式Mixed（发起使用）： 记录行和sql
②基于行ROWS： 只记录被修改的行的记录
③基于sql（默认）： 记录修改内容的执行语句
5、mysql恢复的方式
**①基于冷备（tar）形式：**t ar zxvf解压打包的/usr/local/mysql/data数据下的内容
②基于自带的温备工具： 使用mysqldump进行备份后，可使用两种方式进行恢复

• mysql -u -p 库名 </opt/mysql_all.sql（备份文件的路径）
  
• mysql -u -p -e ’ source /opt/mysql_all.sql’ 直接使用source进行恢复
  

③基于日记：bin-log

• 首先开启二进制日记的配置log-bin=mysql-bin ；log_format=MIXED
  
• 然后再/usr/local/mysql/data 目次下，会天生mysql-bin.00000x的二进制文件进行恢复
  
• 接着使用mysqlbinlog --no-defaults 二进制日记路径|mysql -u -p 基于完整的二进制日记文件进行恢复。
  

基于位置点和时间点进行恢复

• 使用mysqlbinlog --no-defaults --base64-output-decode-rows -v 二进制日记文件路径，检察正确和需要跳过的错误操作的at（position位置带点）和datetime（时间点）来进行恢复
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。