日志的类型与作用：

MySQL的日志默认保存位置为/usr/local/mysql/data
日志类型与作用:
1.redo重做日志：达到事务一致性（每次重启会重做）
作用:确保日志的持久性，防止在发生故障，脏页未写入磁盘。重启数据库会进行redolog执行重做，达到事务一致性
2.undo回滚日志
作用:保证数据的原子性，记录事务发生之前的一个版本，用于回滚，innodb事务可重复读和读取已提交，隔离级别就是通过mvcc+undo实现
3.errorlog错误日志⭐⭐⭐
作用:Mysql本身启动，停止，运行期间发生的错误信息
4.slow query log慢查询日志 ⭐⭐⭐
作用:记录执行时间过长的sql，时间阈值（默认10s）可以配置，只记录执行成功
作用2（超过默认时间提醒需要优化）
5.bin log二进制日志⭐⭐⭐⭐
作用:用于主从复制，实现主从同步
记录的内容是:数据库中执行的sql语句
6.relay log 中继日志⭐⭐⭐⭐
作用:用于数据库主从同步，将主库发来的bin log保存在本地，然后从库进行回放
ps：主从复制是另外两种核心架构的基础
读写分离和MHA ( mysql ha)
7.general log普通日志
作用:记录数据库的操作明细，默认关闭，开启后会降低数据库性能

开启日志方式

进入配置文件vim /etc/my.cnf
在[mysqld]最后添加，修改结束重启systemctl restart mysqld，自动刷新文件并生成mysql-bin.index文件

#二进制日志开启后，重启mysql 会在目录中查看到二进制日志
cd /usr/local/mysql/data
ls
mysql-bin.o00001
#开启二进制日志时会产生一个索引文件及一个索引列表
索引文件:记录更新语句
索引文件刷新方式:
1、重启mysql的时候会更新索引文件，用于记录新的更新语句
2、刷新二进制日志
mysgl-bin.index:二进制日志文件的索引

1、错误日志，用来记录当MysQL启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/ local/mysql/data/mysql_error. log  #指定日志的保存位置和文件名
2、通用查询日志，用来记录MysQL的所有连接和语句，默认是关闭的
general_log=ON
general_log_file=/usr/ local/mysql/data/mysql_general.log   #指向位置
3、二进制日志(binlog)，用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可町用于数据恢复，默认己开启
log-bin=mysql-bin
或
log_bin=mysql-bin
4、中继日志
一般情况下它在Mysql主从同步（复制)、读写分离集群的从节点开启。主节点一般不需要这个日志
5、慢查询日志，用来记录所有执行时间超过long query time秒的语句，可以找到哪些查询语句执行时间长，以便于提醒优化，默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log  #指定存放位置
long_query_time=5     #（永久）设置超过5秒执行的语句被记录，缺省时为10秒

查看日志是否开启

show variables like 'general%'; #查看通用查询日志是否开启
show variables like 'log_bin%'; #查看二进制日志是否开启
show variab[leslike '%slow号';  #查看慢查询日功能是否开启
show variables like 'long query time'; #查看慢查询日志时间设置
set global slow_query_log=ON;   #在数据库中设置开启慢查询的方法
PS : variables表示变量like表示模糊查询

百分号用法：

XXX%   以XXX为开头的字段
%XXX   以XXX为结尾的字段
%XXX%  只要出现XXX字段的都会显示出来 
XXX    精准查询

数据备份的重要性：

1)、在生产环境中，数据得安全性至关重要
2)、任何数据的丢失都可能产生严重的后果
3)、造成数据丢失的原因
●程序错误
●认为操作错误
●运算错误
●磁盘故障
●不可抗力因素或者盗窃等
●恶意攻击rm -rf

备份类型：

1、物理备份
数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题的时候需要快速恢复的大型重要数据库。
物理备份又可以成为冷备份（脱机备份）、热备份（连接备份）和温备份
①冷备份(脱机备份）:是在关闭数据库的时候进行的(tar)
②热备份(联机备份):数据库处于运行状态，依赖于数据库的日志文件(mysqldump)
③温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
2 、逻辑备份⭐⭐⭐
逻辑备份是对数据库逻辑组件的备份.表示为逻辑数据库结构这种类型的备份适用于可以编辑数据值或表结构
从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份
①完全备份
每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长
②差异备份
备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起,备份数据量会越来越大。恢复数据时只需要恢复上次的完全备份与最佳的一次差异备份
③增量备份
只有那些在上次完全备份或者增量备份后被修改的文件才会被备份以上次完整备份或上次增量备份的时间为时间点，仅备份期间内的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完全备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失

备份方式比较：

如何选择逻辑备份策略或频率？

如何选择逻辑备份策略(频率)？
合理值区间：⭐⭐⭐
全备:一周一次的全备，全备的时间需要在不提供业务的时间区间进行 可以在PM 10点、 AM 5:00之间进行全备
增量:3天/2天/1天 一次增量备份
差异:选择特定的场景进行备份
备份内容存放位置：一个处理（NFS）提供额外空间给与mysql服务器用

常见的备份方法：

1、物理冷备
备份时数据库处于关闭状态,直接打包数据库文件(tar)，备份速度快，恢复时也是最简单的
2、专用备份工具mysqldump_或mysqlhotcopy、mysqldump
mysqldump ：常用的逻辑备份工具
mysqlhotcopy ：仅拥有备份MyISAM和 ARCHIVE表的功能
3、启用二进制日志进行增量备份
进行增量备份，需要刷新二进制日志
4、第三方工具备份
免费的MysQL热备份软件 Percona xtraBackup mysqlbackup

MysQL完全备份优缺点：

是对整个数据库、数据库结构和文件结构的备份保存的是备份完成时刻的数据库
是差异备份与增量备份的基础
1、优点:
备份与恢复操作简单方便
2、缺点:
数据存在大量的重复占用大量的备份空间备份与恢复时间长

数据库完全备份分类：

1、物理冷备份与恢复
关闭MysQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MysQL目录即可
2、 mysqldump备份与恢复
MySQL自带的备份工具，可方便实现对MysQL的备份可以将指定的库、表导出为SQL脚本
使用命令mysql导入备份的数据

实验操作：

环境准备：
use kgc;
create table if not exists info1 (id int(4) not null auto_increment,name varchar(10) not null,age char(10) not null,hobby varchar(50),primary key(id));
insert into info1 values(1,'user1',20,'running');
insert into info1 values(2,'user2',30,'singing');

mysql完全备份与恢复

mysql完全备份与恢复
InnoDB 存储引擎的数据库在磁盘上存储成三个文件:
db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。1.物理冷备份与恢复
systemctl stop mysqld
yum -y install xz
压缩备份
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
mv /usr/local/mysql/data/ /opt/
解压恢复
tar Jxvf /opt/mysql_all_2020-11-22.tar.xz -C /usr/local/mysql/data/
cd /usr/local/mysql/data
mv /usr/local/mysql/data/* ./

2、mysqldump备份与恢复：
(1)、完全备份一个或多个完整的库(包括其中所有的表)
mysqldump -u root -p[密码] --databases 库名1[库名2] ... >/备份路径/备份文件名.sql  #导出的就是数据库脚本文件
示例:
mysqldump -u root -p123 --databases kgc > /opt/kgc.sql#备份一个kgc库
mysqldump -u root -p123    --databases mysql kgc > /opt/mysql-kgc.sql #备份mysql与 kgc两个库
(2)、完全备份MysQL服务器中所有的库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql例:
mysqldump -u root -p --all-databases > /opt/all.sql
(3)、完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1][表名2] ...> /备份路径/备份文件名.sql
mysqldump -u root -p [-d] kgc info1 info2 > /opt/kgc_infol.sql
#使用"-d"选项，说明只保存数据库的表结构
#不使用"一d"选项，说明表数据也进行备份
(4)查看备份文件
grep -v "^--" /opt/kgc_info1.sql | grep -v "^/" | grep -v "^$"

恢复数据库：
使用mysqldump导出的文件，可使用导入的方法
source命令   mysql命令
1、使用source恢复数据库的步骤
登录到MySQL数据库
执行source备份sql脚本的路径
source恢复的示例:
mysql [(none)]> source /backup/all-data.sql
2、使用mysql命令恢复数据
mysql -u 用户名 -p [密码] < 库备份文件脚本的路径
mysql命令恢复的示例：
mysql-u root -p < /backup/all-data.sql

mysql完全备份与恢复是否添加–databases差异

mysqldump 严格来说属于温备份，会需要对表进行写入的锁定
在全量备份与恢复实验中，假设现有ky11库，ky11库中有一个test表，需要注意的一点为：
① 当备份时加 --databases ,表示针对于ky11库
备份命令
mysqldump -uroot -p123123 --databases school > /opt/school_01.sql 备份库后
恢复命令过程为：
mysql -uroot -p123123 
drop database ky11;
exit
mysql -uroot -p123123 < /opt/ky11_01.sql
② 当备份时不加 --databases，表示针对ky11库下的所有表
备份命令
mysqldump -uroot -p123123 ky11 > /opt/ky11_all.sql
恢复过程：
mysql -u root -p123123
drop database ky11;
create database ky11;
exit
mysql -u root -p123123 ky11 < /opt/ky11_02.sql 查看ky11_01.sql 和ky11_02.sql 
主要原因在于两种方式的备份（前者会从"create databases"开始,而后者则全是针对表格进行操作） 

mysql增量备份及恢复

MySQL数据库增量恢复
1.一般恢复
将所有备份的二进制日志内容全部恢复
2.基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
发生错误节点之前的一个节点，上一次正确操作的位置点停止
3.基于时间点恢复
跳过某个发生错误的时间点实现数据恢复
在错误时间点停止，在下一个正确时间点开始

mysql增量备份

操作步骤：
1.开启二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=MIXED      #可选，指定二进制日志(binlog)的记录格式为MIXED（混合输入）
server-id=1              #可加可不加该命令systemctl restart mysqld 

解释：
二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT
1、STATEMENT（基于SQL语句）:
每一条涉及到被修改的sql 都会记录在binlog中
缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题
2、ROW(基于行)
只记录变动的记录，不记录sql的上下文环境
缺点:如果遇到update......set....where true 那么binlog的数据量会越来越大
create table info 
insert into info values(1,'zhangsan');
insert into info values(2,'lisi');
delete id=zhangsan
3、MIXED 推荐使用
一般的语句使用statement，函数使用ROW方式存储。

查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
① mysqlbinlog --no-defaults /opt/mysql-bin.000002
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
#--base64-output=decode-rows：使用64位编码机制去解码（decode）并按行读取（rows）
#-v: 显示详细内容
#--no-defaults : 默认字符集（不加会报UTF-8的错误）
PS: 可以将解码后的文件导出为txt格式，方便查阅
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > /opt/mysql-bin.000002二进制日志中需要关注的部分
1、at ：开始的位置点 
2、end_log_pos：结束的位置
3、时间戳： 210712 11:50:30 
4、SQL语句

2.进行完全备份(增量备份时基于完全备份的，所以我们直接完全备份数据库)

[root@mysql data]# mysqldump -uroot -p school info > /opt/kgc_info1_$(date +%F).sql
[root@mysql data]# mysqldump -uroot -p123123 school > /opt/school_all_$(date +%F).sql

3.可每天进行增量备份操作，生成新的二进制日志文件(例如:mysql-bin.000002)

mysqladmin -u root -p flush-logs

4.插入新数据，以模拟数据的增加或变更
PS:在第一次完全备份之后刷新二进制文件，在第二个二进制文件中记载着"增量备份的数据"

mysql> use ky11;
mysql> create table test1 (id int(4),name varchar(4));
mysql> insert into test1 values(1,'one');
mysql> insert into test1 values(2,'two');
mysql> select * from test1;

5.再次生成新的二进制日志文件(例如:mysql-bin.000003)

mysqladmin -u root -p flush-logs
之前的步骤4的数据库操作会保存到mysql-bin.000002文件中，之后我们测试删除ky11库的操作会保存在mysql-bin.000003文件中 （以免当我们基于mysql-bin.000002日志进行恢复时，依然会删除库）

增量恢复

1、 一般恢复

(1)、模拟丢失更改的数据的恢复步骤（直接使用恢复即可）
① 备份ky11库中test1表
mysqldump -uroot -p123123 ky11 test1 > /opt/ky11_test11.sql
② 删除ky11库中test1表
drop table ky11.test1;
③ 恢复test1表
mysql -uroot -p ky11 < info-2020-08-31.sql #查看日志文件
[root@mysql data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

① 模拟丢失所有数据
drop database ky11;
mysql> exit
② 基于mysql-bin.000002恢复
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000003 > /opt/mysql_00003

2、断点恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002
例:
# at 302
#201122 16:41:16
插入了"user3"的用户数据# at 623
#201122 16:41:24 
插入了"user4"的用户数据

(1)、基于位置恢复

① 插入三条数据
mysql> use ky11;
mysql> insert into test1 values(3,'true');
mysql> insert into test1 values(4,'f');
mysql> insert into test1 values(5,'t');
需求：以上id =4的数据操作失误，需要跳过
② 确认位置点，刷新二进制日志并删除test1表
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000003
960 停止 
1066 开始
mysqladmin -uroot -p123123 flush-logs  #刷新日志
mysql> drop table ky11.test1;    删除表
③ 基于位置点恢复
仅恢复到操作 ID 为“623"之前的数据，即不恢复"user4"的数据
mysqlbinlog --no-defaults --stop-position='1793' /opt/mysql-bin.000002 | mysql -uroot -p
960 停止 
1066 开始
仅恢复"user4"的数据，跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-position='623' /opt/mysql-bin.000002 | mysql -uroot -p
恢复从位置为400开始到位置为623为止
mysqlbinlog --no-defaults --start-position='400' --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p     

(2)、基于时间点恢复

mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码仅恢复到16:41:24 之前的数据，即不恢复"user4"的数据
mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p 仅恢复"user4"的数据，跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-datetime='2020-11-2216:41:24' /opt/mysql-bin.000002 | mysql -uroot -p如果恢复某条SQL语之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start