作用：确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行 redo log 执行重做，达到事务一致性。
1

作用：保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复和读取已提交隔离级别就是通过mvcc+undo实现。
1

作用：mysql本身启动，停止，运行期间发生的错误信息
1

作用：记录执行时间过长的sql语句，时间默认值为10s，可以配置，只记录执行成功，超过设置时间的sql语句，都会被存在到慢查询日志中。

还可以提醒优化，对于查询慢的语句进行操作（比如是网络问题，还是未添加索引等）
1
2
3

作用：用于基于日志形式的数据恢复。用于主从复制，实现主从同步，记录的内容是：数据库中执行的sql语句
1

作用：用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放。
1

记录数据库的操作明细，默认关闭，开启后会降低数据库的性能
1

日志文件开启关闭日志的位置
vim /etc/my.cnf

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

#登入mysql
mysql -u root -p[密码]
 
#查看通用查询日志是否开启
show variables like 'general%';	

#查看二进制日志是否开启
show variables like 'log_bin%';		

#查看慢查询日功能是否开启
show variables like '%slow%';		

#查看慢查询时间设置
show variables like 'long_query_time';	

#在数据库中设置开启慢查询的方法
set global slow_query_log=ON;									
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

mysqldump -uroot -p123 --databases info01 > /opt/info01.sql
#备份一个数据库，info01。
mysqldump -uroot -p123 --detabases info01 info02 > /opt/info01-info02.sql
#备份两个数据库，info01，info02

mysqldump -uroot -p123 --all-databases >/opt/all-database.sql
#备份所有的数据库
1
2
3
4
5
6
7

mysql -uroot -p123 -e 'drop database info01;'
#使用-e的方法进入数据库执行删除数据库的操作
mysql -uroot -p123 -e 'show databases;'
#使用-e的方法进行显示数据库的数量

mysql -uroot -p123 

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

使用source进行恢复数据库

mysql -uroot -p123 -e 'drop database info01;'
#删除数据库info01
mysql -uroot -p123 -e 'drop database info02;'
#删除数据库info02
mysql -uroot -p123 -e 'show databases;'
#查看info01和info02数据库是否被删除

mysql -uroot -p123 -e 'source /opt/info01-onfo02.sql;'
#使用source进行恢复
1
2
3
4
5
6
7
8
9

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

• mysqldump严格来说属于温备份，会需要对标进行写入锁定

• 在全量备份与恢复实验中，加上–databases表示指定备份的是这个数据库以及下面所有表，如果不加–database时，表示只备份这个数据库下面的表，并不备份数据库。

加–databases时实验

mysqldump -uroot -p123 --databases info01 > /opt/info01.sql
#全量备份，加上--databases，指定备份数据库，以及下面所有的表

mysql -uroot -p123
drop databases info01;
#进入数据库，进行删除数据库

source /opt/info01.sql
#恢复数据库
show databases;
1
2
3
4
5
6
7
8
9
10

不加–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
#创建了数据库后，进入数据库，再进行恢复。
1
2
3
4
5
6
7
8
9
10
11
12
13

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 "^$";
#查看备份文件过滤掉--开头、/开头的和空行
1
2
3
4
5
6
7
8

不加-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;'
1
2
3
4
5
6
7
8

第二种：使用source恢复

mysql -uroot -p123;
#登录数据库

use info01;
show tables;
#进入数据库查看所有的表

drop table ydq;
#删除ydq表

source /opt/info01-ydq01.sql;
#使用source命令执行恢复表
1
2
3
4
5
6
7
8
9
10
11
12

四、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-bin
binlog_format = MIXED
#二进制日志有三种不同的记录格式：STATEMENT（基于SQL语句）、ROW（基于行）、MIXED（混合模式），默认为STATEMENT
server-id = 1
#server-id 为服务的序号，在MySQL主备、高可用中需要指定服务的序号

systemctl restart mysqld
#改为配置文件需要重启服务
1
2
3
4
5
6
7
8
9
10

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
#直接复制二进制文件到/opt

mysqlbinlog  --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;
#刷新二进制日志文件(为了不重启，进行刷新，这样二进制文件会重新生成一个，相当于增量备份)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

日志文件内容解析(需要关注的点)
at 127                 #起始位置
220810 23:15:25       #起始时间
create database ydq;   #具体操作
at 130                 #结束位置
220810 13:15:50       #结束时间

at 138                 #表示下一个任务开始的时间
220810 23:16:10
use ydq;
at 140
1
2
3
4
5
6
7
8
9
10
11

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

mysqldump -uroot -p123 kgc ky20> /opt/ky20-$(date -%F).sql
#使用mysqldump进行完全备份数据库

mysqladmin -u root -p flush-logs
#可每天进行增量备份，生成新的二进制日志文件(例如:mysql-bin.00004)
1
2
3
4
5

先进行完全备份

在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表中的另一条数据)
1
2
3
4
5
6
7
8
9
10

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

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

2.2.1 增量备份

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

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

先进行完全备份当前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目录下，方便查看，查看到第三条执行语句的位置点：5976

mysql -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 -p

mysqlbinog --no-defaults --start-position='754' /usr/local/mysql/data/mysql-bin.000002|mysql -uroot -p
#以上两条语句组合实现恢复从754的位置到623的位置结束
1
2
3
4
5
6
7
8
9
10
11
12
13
14

那么就需要查看备份的日志文件中，查看第三条语句的节点是结束位置的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
3
4
5
6
7
8
9

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

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

四、总结

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（时间点）来进行恢复