mysql日志管理、备份与恢复

一 MySQL 日志

（一），日志位置

MySQL 的日志默认保存位置为 /usr/local/mysql/data

（二）配置文件中与日志相关的代码

vim /etc/my.cnf 在 [mysqld] 下

1，错误日志

用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
og-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秒的语句，可以找到哪些查询语句执行时间长，以便提醒优化，默认是关闭的

s1ow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5 #设置超过5秒执行的语句被记录，缺省时为10秒

以下供你复制

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

（三）查询日志功能是否开启

1，命令查看相关日志

systemctl restart mysqld
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;         #在数据库中设置开启慢查询的方法

PS:variables 表示变量 like 表示模糊查询

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

（四）二进制日志

1，何为二进制日志

开启二进制日志时会产生一个日志文件及一个索引列表

1.1 日志文件

mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002

1.2 索引文件

mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表

2，二进制日志记录三种格式

2.1 基于"语句"记录：statement

基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下），日志量较少

2.2 基于"行"记录：row

基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式

2.3 混合模式：mixed

混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

3,二进制日志相关的服务器变量

sql_log_bin=ON|OFF：
#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项
log_bin=mysql-bin      默认是关闭
#指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以
binlog_format=STATEMENT|ROW|MIXED：
#二进制日志记录的格式，mariadb5.5默认STATEMENT
max_binlog_size=1073741824：
#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值
binlog_cache_size=4m 
#此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m 
#限制用于缓存多事务查询的字节大小。
sync_binlog=1|0：
#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘
expire_logs_days=N：
#二进制日志可以自动删除的天数。 默认为0，即不自动删除

4,离线查看二进制日志

mysqlbinlog：二进制日志的客户端命令工具，支持离线查看二进制日志

4.1二进制日志事件的格式

# at 328
#151105 16:31:40 server id 1 end_log_pos 431   Query   thread_id=1     
exec_time=0     error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1446712300/*!*/;
CREATE TABLE tb1 (id int, name char(30))
/*!*/;  
事件发生的日期和时间：151105 16:31:40
事件发生的服务器标识：server id 1
事件的结束位置：end_log_pos 431
事件的类型：Query 
事件发生时所在服务器执行此事件的线程的ID：thread_id=1
语句的时间戳与将其写入二进制文件中的时间差：exec_time=0
错误代码：error_code=0
事件内容：
GTID：Global Transaction ID，mysql5.6以mariadb10以上版本专属属性：GTID

（四）补充事务日志

1，Innodb事务日志相关配置

show variables like '%innodb_log%';
innodb_log_file_size   50331648 #每个日志文件大小  字节
innodb_log_files_in_group 2     #日志组成员个数
innodb_log_group_home_dir ./ #事务文件路径

2，事务日志性能优化

2.1 查看事务日志级别

select @@innodb_flush_log_at_trx_commit;
#查看默认值

2.2 事务日志级别对比

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务后执行刷新到磁盘。这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失

级别	0	1	2
安全性	较高	最高	最高
性能	最高	最差	较高

2.3 修改事务日志级别

set  global innodb_flush_log_at_trx_commit=2;   修改参数
select @@innodb_flush_log_at_trx_commit;        查看

二数据备份

（一）数据备份的重要性

1、备份的主要目的是灾难恢复
2、在生产环境中，数据的安全性至关重要
3、任何数据的丢失都可能产生严重的后果

（二）造成数据丢失的原因

程序错误
人为操作错误
运算错误
磁盘故障
灾难 (如火灾、地震) 和盗窃

（三）备份类型

1，物理备份

物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题的时候需要快速恢复的大型重要数据库。
物理备份又可以成为冷备份（脱机备份）、热备份（连接备份）和温备份

1.1冷备份 (脱机备份)

是在关闭数据库的时候进行的（tar）

1.2热备份 (联机备份)

数据库处于运行状态，依赖于数据库的日志文件（mysqlhotcopy mysqlbackup）

1.3 温备份

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

2，逻辑备份⭐⭐⭐

逻辑备份是对数据库逻辑组件的备份.表示为逻辑数据库结构
这种类型的备份适用于可以编辑数据值或表结构

（四）备份类型（贰）

从数据库的备份策略角度来看，备份又可分为完全备份、差异备份和增量备份

1，完全备份

每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长

1.1 完全备份架构图

1.2 完全备份过程

每次都进行完全备份，会导致备份文件占用据巨大的空间，并且有大量的重复数据

1.3 完全备份数据恢复

恢复时直接把文件导入进去即可

2，差异备份

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

2.1 差异备份架构图

2.2 差异备份过程

上次差异备份，都被会备份上一次完全备份之后的数据，可能会出现备份重复数据，导致占用大额外的磁盘空间

2.3 差异备份数据恢复

恢复时先恢复完全备份再恢复导入差异备份的数据

3，增量备份

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

3.1 增量备份架构图

3.2 增量备份过程

每次增量备份数据都是备份在上一次完全备份或者增量备份之后的数据不会出现重复数据;也不会占用额外的磁盘空间

3.3 增量备份恢复数据

恢复数据，需要按照次序恢复完全备份和增量备份的数据

（五）如何选择逻辑备份策略（频率）

合理值区间⭐⭐⭐
一周一次的全备，全备的时间需要在不提供业务的时间区间进行 PM 10点 AM 5:00之间进行全备
增量：3天/2天/1天一次增量备份
差异：选择特定的场景进行备份
一个处理（NFS）提供额外空间给与mysql 服务器用

（六）常见的备份方法

1、物理冷备

备份时数据库处于关闭状态，直接打包数据库文件（tar）
备份速度快，恢复时也是最简单的

2、专用备份工具 mysqldump 或 mysqlhotcopy

mysqldump 常用的逻辑备份工具
mysqlhotcopy 仅拥有备份 MyISAM 和 ARCHIVE 表

3、启用二进制日志进行增量备份

进行增量备份，需要刷新二进制日志
MySQL支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份（包含自上次完全备份或增量备份以来发生的数据修改），需要刷新二进制日志。

4，第三方工具备份

免费的MySQL 热备份软件 Percona XtraBackup mysqlbackup

三 MySQL完全备份

是对整个数据库、数据库结构和文件结构的备份
保存的是备份完成时刻的数据库
是差异备份与增量备份的基础

（一）优缺点

1、优点：
备份与恢复操作简单方便

2、缺点：
数据存在大量的重复
占用大量的备份空间
备份与恢复时间长

（二）数据库完全备份分类

1、物理冷备份与恢复

关闭MySQL数据库

使用tar命令直接打包数据库文件夹

注意date +%F 中间要有空格

直接替换现有MySQL目录即可

2、mysqldump备份与恢复

MySQL自带的备份工具，可方便实现对MySQL的备份
可以将指定的库、表导出为SQL 脚本
使用命令mysq|导入备份的数据

mysqldump 常见通用选项

-A, --all-databases 
#备份所有数据库，含create database
-B, --databases db_name…  
#指定备份的数据库，包括create database语句
-E, --events：
#备份相关的所有event scheduler
-R, --routines：
#备份所有存储过程和自定义函数
--triggers：
#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
-d, --no-data    
#只备份表结构,不备份数据,即只备份create table 
-t, --no-create-info 
#只备份数据,不备份表结构,即不备份create table 
-n,--no-create-db 
#不备份create database，可被-A或-B覆盖
--flush-privileges 
#备份mysql或相关时需要使用
-f, --force       
#忽略SQL错误，继续执行
--hex-blob        
#使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY，BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick     
#不缓存查询，直接输出，加快备份速度

2.1 备份库、

mysqldump -u root -p --databases sanguo > /opt/sanguo.sql #备份一个sanguo库

mysqldump -u root -p --databases mysql sanguo > /opt/mysql-sanguo.sql #备份mysql与 sanguo两个库

mysqldump -u root -p --all-databases > /opt/all.sql #完全备份 MySQL 服务器中所有的库

2.2 备份表

mysqldump -u root -p sanguo wuli > /opt/sanguo_wuli.sql #备份sanguo 库下的wuli表

mysqldump -u root -p sanguo wuli meili > /opt/sanguo_wuli_meili.sql #备份sanguo 库下的wuli 和 meili 表

2.2 只备份表结构不备份数据

加-d 只备份结构

mysqldump -u root -p -d sanguo wuli > /opt/sanguo_wuli.sql

查看备份文件（得到表结构）
grep -v "^--" /opt/kgc_info1.sql | grep -v "^/" | grep -v "^$"

（三）生产环境自动实现定时备份

在生产环境中，可以使用Shell脚本自动实现定时备份（时间频率需要确认）

0 1 * * 6 /usr/local/mysql/bin/mysqldump -uroot -pabc123 kgc info1 > ./kgc_infol_$(date +%Y%m%d).sql ;
/usr/local/mysql/bin/mysqladmin -u root -p flush-logs

四 Mysql 完全恢复

（一）恢复表

有两个方法

1，source备份文件

2，将备份文件重定向

1，重定向方法恢复表

1.1备份 sanguo库下的wuli 表

1.2 删除 sanguo库下的wuli 表

mysql -e 相当于进入mysql 页面调用命令

注意：此处格式库名. 表名

1.3可以看到武力表被删除了

1.4 重定向将备份的表导给sanguo 库

1.5 可以看到武力表回来了

2，source 方法恢复表

2.1 步骤如上删除魅力表

2.2 去到表对应的数据库里

source 表的备份文件

2.3 可以看到wuli 表回来了

（二）恢复库

在备份库时不加 --databases 表示针对库下的所有表，所以在恢复库时，要先创建对应的库，不然这些表没有家了

1，加 --databases 备份库再恢复库

1,1 备份 sanguo 库

1.2 删库

1.3 恢复库

1.4 可以看到我们的库回来啦

2，不加 --databases 备份库再恢复库

恢复库之前先创建库

create database sanguo;

五 MySQL数据库增量恢复

（一） MySQL数据库增量恢复类别

1.一般恢复

将所有备份的二进制日志内容全部恢复

2.基于位置恢复

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

3.基于时间点恢复

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

（二）MySQL 增量备份

1，开启二进制日志功能

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

2，二进制日志(binlog)有3种不同的记录格式

STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

① STATEMENT（基于SQL语句）

每一条涉及到被修改的sql 都会记录在binlog中
缺点：日志量过大，如sleep()函数，last_insert_id()>，以及user-defined fuctions（udf）、主从复制等架构记录日志时会出现问题

总结：增删改查通过sql语句来实现记录，如果用高并发可能会出错，可能时间差异或者延迟，可能不是我们想想的恢复可能你先删除或者在修改，可能会倒过来。准确率低（可能你删了第四行数据恢复在第10行）

② ROW(基于行)

只记录变动的记录，不记录sql的上下文环境
缺点:如果遇到update......set....where true 那么binlog的数据量会越来越大

总结：update、delete以多行数据起作用，来用行记录下来，
只记录变动的记录，不记录sql的上下文环境，
比如sql语句记录一行，但是ROW就可能记录10行，但是准确性高，高并发的时候由于操作量，性能变低比较大所以记录都记下来，

③ MIXED 推荐使用

一般的语句使用statement，函数使用ROW方式存储。

（三）查看二进制日志文件的内容

cp /usr/local/mysql/data/mysql-bin.000002 /opt/

#将二进制文件复制到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的错误）

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002 > /opt/mysql-bin.000002

# 可以将解码后的文件导出为txt格式，方便查阅

（四）刷新刷新二进制日志

mysqladmin -uroot -p123123 flush-logs

当用此刷新二进制日志

之前所有的操作都保存在 001文件中 002文件是空白的

因此我们可以根据时间或者节点去恢复我们想要的数据

（五）断点恢复

看at

1，stop 与start

如果恢复某条SQL语之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start

即stop就是停了恢复到这个数据停就是恢复这个数据之前的数据（该数据不恢复）

start 开始从这个数据开始恢复（该数据不恢复）

2，具体命令

#仅恢复到操作 ID 为“623"之前的数据，即不恢复"user4"的数据
mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p

#仅恢复"user4"的数据，跳过"user3"的数据恢复
mysqlbinlog --no-defaults --start-position='623' /opt/mysql-bin.000002 | mysql -uroot -p

mysqlbinlog --no-defaults --start-position='400' --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p #恢复从位置为400开始到位置为623为止

（六）按时间恢复

1，stop 与start

2，具体命令

#仅恢复到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-22 16:41:24' /opt/mysql-bin.000002 | mysql -uroot -p

mysqlbinlog [--no-defaults] --start-datetime='年-月-日小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
恢复从某某时间到某某时间的数据

（七）实例演示断点恢复与按时间恢复

要求:假设id3 是故障数据不需要恢复

按时间戳恢复id3 id4的数据按断点恢复id1 id2的数据

1，表格数据如下

2，刷新日志文件

3，将日志文件拷贝到opt 方便观察

4，删表

5，查看001 日志文件

小技巧：可以将解码后的文件导出为txt格式，方便查阅这样也能搜索想要的内容

6，根据需求找到数据

找到id1 的数据

id2 的数据

id3 的数据

id4 的数据

id 5 的数据

7 首先需求是需要id1 id2 的数据我们按照断点 stop id3 就是恢复数据到id3 (id3 不恢复)

这边报错因为我们把创建库这一步也复制了（我们只是把表删了库还在）

8，找到创建表的段点上一个断点at 4914

就是start 4912 stop6322

9，可以看到不仅表回来了 id1 id2 数据也回来了

10 按时间戳 id3的时间戳是 240325 19:14：58 因为不要id3 所以start该时间

注意！此处为了保险起见 start的时间最好是写入id3 数据之后的时间19:15:23 因为写入id3数据的时间为19:14:58 有多个时间都是这个

11，恢复19:15:23 之后的时间

12，可以看到数据按照我们的要求恢复了

六总结

1，物理冷备份

关闭mysql 服务 tar 命令打包data目录恢复就是直接解压tar包

2，逻辑备份

mysqldump -u -p --databases 库1 库2 > xxx.sql

mysqldump -u -p --all-databases > xxx.sql

mysqldump -u -p 库1 表1 表2 > xxx.sql

3，完全恢复

mysql -u -p < xxx.sql 恢复库

mysql -u -p 库名 < xxx.sql 恢复表

4，增量备份

1，开启二级制日志，设置二进制日志格式MIXED

2，进行一次完全备份，可每周备份一次，通过crontab-e

3、使用mysqladmin -uroot -p

flush-logs刷新分割出二进制日志文件，由于刷新之前的数据都会记录在老的二进制日志里

4、可以通过mysqlbinlog --no-defaults --base64-output=decode-rows -v二进制日志文件名查看日志内容

5、可以通过mysqlbinlog --no-defaults 二进制日志文件名 mysql -uroot

5，如何恢复

5.1位置恢复

5.2 时间恢复