目录

1.Mysql日志管理

1.1为什么需要日志

1.2日志作用

1.3数据丢失或破坏的原因

1.4常见日志类型之错误日志

1.5常见日志类型之通用查询日志

1.6常见日志类型之慢查询日志

1.7常见日志类型之二进制日志

1.8常见日志类型之事务日志

2.MySQL备份

2.1备份类型

2.2逻辑备份优缺点

2.3备份内容

2.4备份工具

1.Mysql日志管理

1.1为什么需要日志

1.用于排错
2.用来做数据分析
3.了解程序的运行情况，了解MySQL的性能

1.2日志作用

在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，这样情况下，就必须保证数据的安全性和完整性，则需要使用日志来查看或者恢复数据。

1.3数据丢失或破坏的原因

1.误删除数据库
2.数据库工作时，意外断电或程序意外终止
3.由于病毒造成的数据库损坏或丢失
4.文件系统损坏后，系统进行自检操作
5.升级数据库时，命令语句不严格
6.设备故障等等
7.自然灾害
8.盗窃

1.4常见日志类型之错误日志

错误日志记录以下信息：
        服务器启动和关闭过程中的信息
        服务器运行过程中的错误信息
        事件调度器运行一个时间是产生的信息
        在从服务器上启动从服务器进程是产生的信息

# 查看错误日志位置及文件名，一般以主机名.err方式命名 mysql> show variables like 'log_error'; +---------------+--------------+ | Variable_name |    Value     | +---------------+--------------+ | log_error     | .\LEGION.err | +---------------+--------------+ 1 row in set, 1 warning (0.01 sec)

1.5常见日志类型之通用查询日志

1.查看设置状态

mysql> show global variables like "%general_log%"; +------------------+------------+ | Variable_name    |    Value   | +------------------+------------+ | general_log      |    OFF     | # 默认关闭 | general_log_file | LEGION.log | # 存储位置 +------------------+------------+ 2 rows in set, 1 warning (0.00 sec)

2.启动记录查询日志

mysql> set global general_log=on; mysql> show global variables like "%general_log%"; +------------------+------------+ | Variable_name    |    Value   | +------------------+------------+ | general_log      |      ON    | | general_log_file | LEGION.log | +------------------+------------+ 2 rows in set, 1 warning (0.00 sec) C:\ProgramData\MySQL\MySQL Server 8.0\Data\LEGION.log # 注意这是临时更改，若永久更改则在my.ini中配置general-log=1，并重启服务 # 注意可以在my.ini中设置general_log_file=路径/文件名 的形式设置永久更改存储位置

 3.设置日志记录类型（表/文本文件/空）

mysql> show variables like "%log_output%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output    |  FILE | +---------------+-------+ # 注意可以在my.ini中设置log-output={TABLE|FILE|NONE}类型

要启用通用查询日志，需要至少配置general-log=1，log-output={TABLE|FILE}
general_log_file如果没有指定，默认名是：主机名.log
默认通用查询日志是不开启的，因为会消耗大量的磁盘空间、CPU以及内存，所以当需要通过查询日志还原操作场景准确定位问题时可以短时间开启

4.关闭日志

mysql> set global general_log=0; mysql> show global variables like "%general_log%"; +------------------+------------+ | Variable_name    |    Value   | +------------------+------------+ | general_log      |     OFF    | | general_log_file | LEGION.log | +------------------+------------+

1.6常见日志类型之慢查询日志

慢日志记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询
解释：若某条查询语句的执行时间过长，超过了设定的阈值则就会记录到慢日志中，可以对其进行后期select语句的优化
1.查看慢查询日志状态

mysql> show global variables like "%slow_query_log%"; +---------------------+-----------------+ | Variable_name       |       Value     | +---------------------+-----------------+ | slow_query_log      |        ON       | | slow_query_log_file | LEGION-slow.log | +---------------------+-----------------+

2. 开启慢查询日志

mysql> set global slow_query_log=1; # 0为关闭 C:\ProgramData\MySQL\MySQL Server 8.0\Data\LEGION-slow.log # 注意：为了服务器调优，建议开启

3.慢日志的时间阈值

mysql> show global variables like "long_query_time"; +-----------------+-----------+ | Variable_name   |    Value  | +-----------------+-----------+ | long_query_time | 10.000000 | # 默认10秒最小为0,精度可以到微秒 +-----------------+-----------+

1.7常见日志类型之二进制日志

二进制日志作用：
1.记录所有更改数据的语句（insert、update、delete等），不记录查询语句
2.用于主从复制，因为从服务器需要到主服务器里拷贝二进制日志，然后根据二进制日志的内容去执行SQL语句，从而达到主从服务器里的数据一模一样；
3.用于恢复数据
4.日志审计的场景：用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入攻击（mysql注入攻击：黑客可以提交一段数据库查询代码，根据程序返回的结果，获得某些想要得到的数据）

1.查看二进制日志状态

mysql> show variables  like "%log_bin%";

2.日志查看

# 查看有哪些二进制日志文件： mysql> show binary logs; # 显示名称、容量单位字节、加密与否 # 查看当前正在使用的是哪一个二进制日志文件 mysql> show master status; # 查看二进制日志内容： mysql> show binlog events in "LEGION-bin.000008"; # 使用命令mysqlbinlog查看二进制日志内容 # 打开命令提示符 C:\Users\Administrator>cd C:\ProgramData\MySQL\MySQL Server 8.0\Data C:\ProgramData\MySQL\MySQL Server 8.0\Data>mysqlbinlog LEGION-bin.000008 > test.sql

3.分割日志：新的操作会记录到新的日志文件中

PS C:\Users\Administrator> mysqladmin flush-logs -u root -p Enter password: ****** PS C:\Users\Administrator> mysql -u root -p Enter password: mysql> create database mydb12_journal; # 新建数据库 mysql> show master status; # 打开命令提示符 C:\Users\Administrator>cd C:\ProgramData\MySQL\MySQL Server 8.0\Data C:\ProgramData\MySQL\MySQL Server 8.0\Data>mysqlbinlog LEGION-bin.00000？ 

4.日志刷新

作用：通过刷新日志进行更新日志，对缓存数据进行磁盘I/O，并强制 mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）
由于日志的记录不是直接写入日志文件中而是使用日志缓存的方式，当频繁执行多条修改的sql语句时，为了避免对磁盘频繁I/O，会将日志记录写入到内存的特殊空间即日志缓存中，之后每隔一个固定时间间隔将缓存的日志写入到磁盘文件中。

刷新日志命令格式

mysql> flush logs # 会产生新日志文件 mysql> show master status;   # 在shell中，通过mysqladmin命令执行日志刷新： mysqladmin flush-logs -u 账户名 -p mysqladmin refresh -u 账户名 -p   PS C:\Users\Administrator> mysqladmin flush-logs -uroot -p Enter password: ****** PS C:\Users\Administrator> mysql -u root -p Enter password: ****** mysql> show master status;

1.8常见日志类型之事务日志

作用：记录InnoDB等支持事务的存储引擎执行事务时产生的日志
事务：是一种机制，一个操作序列，它包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求。要么都执行，要么都不执行
解释：比如在区块链技术中，支出时本地账本会记录同时同步全网，收入时也会记录并同步，若执行支出时系统崩溃则会出现问题，采用事务日志记录见执行的事务以便与数据回滚等操作

2.MySQL备份

2.1备份类型

1.根据服务器状态，可以分为热备份、温备份、冷备份
        热备份：读、写不受影响
        温备份：仅可以执行读操作
        冷备份：离线备份；读、写操作均中止
2.从对象来分，可以分为物理备份与逻辑备份
        物理备份：复制数据文件
        逻辑备份：将数据导出至文本文件中
3.从数据收集来分，可以完全备份、增量备份、差异备份
        完全备份：备份全部数据；
        增量备份：仅备份上次完全备份或增量备份以后变化的数据；
        差异备份：仅备份上次完全备份以来变化的数据；

2.2逻辑备份优缺点

在备份速度上两种备份要取决于不同的存储引擎
        物理备份的还原速度非常快。但是物理备份的最小粒度只能做到表
        逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理
        逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高
        逻辑备份也对保持数据的安全性有保证
逻辑备份的缺点：
        逻辑备份要对RDBMS产生额外的压力，而裸备份无压力
        逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩
        逻辑备份可能会丢失浮点数的精度信息

2.3备份内容

数据文件 日志文件（比如事务日志，二进制日志） 存储过程，存储函数，触发器 配置文件（十分重要，各个配置文件都要备份） 用于实现数据库备份的脚本，数据库自身清理的Crontab等……

2.4备份工具

MySQL自带的备份工具--mysqldump，是mysql数据库管理系统，自带的逻辑备份工具，支持完全备份+增加备份，速度相对较慢，适合中小型数据库支持所有引擎，备份策略：第一次完全备份，每天一次增量备份，每周再做一次完全备份，如此一直重复

1.全备语法

mysqldump -u用户名 --password=密码 -A > 绝对路径\备份文件.sql

2.备份库和表

mysqldump -u用户名 --password=密码 数据库名 表1 表2 > 绝对路径\备份文件.sql

 使用mysqldump工具实现全量备份

# 原理：必须先做全量备份，使得数据库有一个基准还原点，在做增量备份，还原差异数据 # 注意：在命令提示符中处理，不要在powershell中 mysql>\q # 切换到命令提示符终端，完成全备 C:\Users\Administrator> mysqldump -u root --password=123456 -A > C:\back1.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. # 警告表示将密码显示在屏幕上不安全，备份的盘符一定要和MySQL安装目录在同一个盘符中， 要不然会提示：拒绝访问 # 切换终端到mysql C:\Users\Administrator> mysql -uroot -p Enter password: ****** mysql> drop database mydb1_test; # 删库跑路 mysql> drop database mydb2_stuinfo; …… mysql> show databases; # 查看数据库 mysql> source C:\back1.sql # 全库恢复

局部备份恢复

# 注意：在命令提示符中处理，不要在powershell中 mysql>\q # 切换到命令提示符终端，完成全备 C:\Users\Administrator> mysqldump -u root --password=123456 -B mydb1_test > C:\back2.sql # 切换终端到mysql C:\Users\Administrator> mysql -uroot -p Enter password: ******