【MySql】Mysql之备份与恢复

 使用source进行恢复数据库

mysql -uroot -p123 -e 'drop database info01;'#删除数据库info01mysql -uroot -p123 -e 'drop database info02;'#删除数据库info02mysql -uroot -p123 -e 'show databases;'#查看info01和info02数据库是否被删除mysql -uroot -p123 -e 'source /opt/info01-onfo02.sql;'#使用source进行恢复

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

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

 加–databases时实验

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

不加–databases时实验

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

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

2.2.1 备份指定库中的部分表

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

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

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

 不加-d选项备份表

加-d选项备份表

2.2.2 恢复指定库中的部分表

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

第一种：使用命令mysql恢复

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

 第二种：使用source恢复

mysql -uroot -p123;#登录数据库use info01;show tables;#进入数据库查看所有的表drop table ydq;#删除ydq表source /opt/info01-ydq01.sql;#使用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 开启二进制功能

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

1.3 查看二进制文件的内容

开启了二进制文件，会在/data目录下生成二进制日志，但是使用cat查看的时候出现乱码，所有需要指定编码格式和解码进行查看

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

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

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

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

先进行完全备份

 在ky20表中创建一条语句

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

 进行增量备份

3、 增量恢复

3.1 增量恢复之一般恢复

3.1 模拟丢失所有数据的恢复步骤

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

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

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

2.2.1 增量备份

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

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

 先进行完全备份当前ky20表

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

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

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

2.2.1 基于位置恢复-at 

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

那么就需要查看备份的日志文件中，查看第三条语句的节点是结束位置的at是多少

查看第三条语句中的结束位置点：5976

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

再基于位置节点进行恢复

2.2.2 基于时间恢复

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

mysql -uroot -p123 kgc < /opt/kgc-ky20-两条语句.sql#先进行全量恢复，(现在有两条数据)mysqlbinlog --no-defaults --stop-datetime='220811 15:11:57' /usr/local/mysql/data/mysql-bin.000002 |mysql -uroot -p#仅恢复到第三条语句结束的时候--------------------------------下面不属于实验内容--------------------------------------mysqlbinlog --no-defaults --start-datetime='220811 15:14:42' /usr/local/mysql/data/mysql-bin.000002 |mysql -uroot -p#恢复的数据从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 库名
• 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（时间点）来进行恢复

来源地址：https://blog.csdn.net/ZWH9991/article/details/132556238