MySQL安全----日志管理(一)
MySQL安全----日志管理(一)
简介
MySQL的日志有主要有四种，会记录不同的操作行为，分别是----二进制日志、错误日志、查询日志、慢查询日志。开启日志是MySQL安全的必要手段之一，但是会影响MySQL的性能，所以要学会日志管理，根据实际的业务需求来选择日志。
详解
二进制日志
二进制文件主要记录MySQL数据库的变化，包含了所有更新了数据或者潜在更新了数据（例如没有匹配任何一行的UPDATE）的语句；还包含了每个更新数据库的语句的执行时间信息，使用二进制日志的目的是最大可能的恢复数据库和进行MySQL的主从复制，因为二进制日志包含备份后进行的所有更新。 MySQL整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。二进制日志属于Server层，所以所有引擎都可以使用。 开启二进制日志可以有效防止数据丢失，因为数据丢失可以使用二进制日志还原数据。
日志格式
binlog 有三种格式： Statement（Statement-Based Replication,SBR）：每一条会修改数据的 SQL 都会记录在 binlog 中。 Row（Row-Based Replication,RBR）：不记录 SQL 语句上下文信息，仅保存哪条记录被修改。 Mixed（Mixed-Based Replication,MBR）：Statement 和 Row 的混合体。
Statement | Statement 模式只记录执行的 SQL，不需要记录每一行数据的变化，因此极大的减少了 binlog 的日志量，避免了大量的 IO 操作，提升了系统的性能。但是，正是由于 Statement 模式只记录 SQL，而如果一些 SQL 中 包含了函数，那么可能会出现执行结果不一致的情况。比如说 uuid() 函数，每次执行的时候都会生成一个随机字符串，在 master 中记录了 uuid，当同步到 slave 之后，再次执行，就得到另外一个结果了。所以使用 Statement 格式会出现一些数据一致性问题。 |
|---|---|
Row | 从 MySQL5.1.5 版本开始，binlog 引入了 Row 格式，Row 格式不记录 SQL 语句上下文相关信息，仅仅只需要记录某一条记录被修改成什么样子了。Row 格式的日志内容会非常清楚地记录下每一行数据修改的细节，这样就不会出现 Statement 中存在的那种数据无法被正常复制的情况。不过 Row 格式也有一个很大的问题，那就是日志量太大了，特别是批量 update、整表 delete、alter 表等操作，由于要记录每一行数据的变化，此时会产生大量的日志，大量的日志也会带来 IO 性能问题。 |
Mixed | 从 MySQL5.1.8 版开始，MySQL 又推出了 Mixed 格式，这种格式实际上就是 Statement 与 Row 的结合。在 Mixed 模式下，系统会自动判断 该 用 Statement 还是 Row：一般的语句修改使用 Statement 格式保存 binlog；对于一些 Statement 无法准确完成主从复制的操作，则采用 Row 格式保存 binlog。Mixed 模式中，MySQL 会根据执行的每一条具体的 SQL 语句来区别对待记录的日志格式，也就是在 Statement 和 Row 之间选择一种。 |
相关命令
- 开启二进制日志
my.ini中[mysqld]组下面有几个设置是关于二进制日志的(windows)：
#启动同时设置日志文件存放路径
log-bin[=PATH/[FILENAME]]
#定义了mysql清除过期日志的时间，即二进制日志自动删除的天数。默认值为0，表示“没有自动删除”。
expire_logs_days=10
#定义了单个文件的大小限制，如果二进制日志写入的内容大小超出给定值，日志就会发生滚动（关闭当前文件，重新打开一个新的日志文件）。
max_binlog_size=100MOther
- 查看二进制日志是否开启
SHOW VARIABLES LIKE '%log_%' - 查看二进制文件数量
SHOW BINARY LOGS;
当mysql服务重新启动一次，会创建一个“.000001”为后缀的日志文件，并且后缀名加1递增；如果日志长度超过了max_binlog_size的上限（默认是1GB）也会创建一个新的日志文件show binary logs语句可以查看当前二进制日志文件个数和文件名。
- 生成新二进制文件
FLUSH LOGS;
使用此命令将会结束当前日志文件（不是删除），重新创建一个日志文件记录（后缀名递增）。
- 查看二进制文件内容
show binlog events;
查看全部二进制文件内容。
show binlog events in 'mysql-bin.000001';
查看单个二进制文件内容。
mysqlbinlog "\mysql-bin.000001" >binlog.txt
mysqlbinlog查看二进制日志，mysqlbinlog是一个单独的exe，需要在命令行里执行。
注意：配置环境变量；文件路径，相对路径和绝对路径都可以。
- 删除二进制文件
RESET MASTER;
删除所有二进制文件。
PURGE MASTER/BINARY LOGS TO 'log_name'
删除文件名编号比指定文件名编号小的所有日志文件。（MASTER或者BINARY都行）例如：PURGE MASTER LOGS TO 'log_name'
PURGE MASTER/BINARY LOGS BEFORE 'date'
删除指定日期以前的所有日志文件。（MASTER或者BINARY都行）
- 暂时停止二进制日志
SET sql_log_bin=0/1
设置0是暂停二进制日志，1是恢复二进制日志。 如果在mysql的配置文件配置启动了二进制日志，mysql会一直记录二进制日志，修改配置文件，可以停止二进制日志，但是需要重启mysql数据库。mysql提供了暂时停止二进制日志的功能。当然，重启MySQL将会根据配置文件配置来决定是否启动二进制日志。
- 二进制日志还原数据库
mysqlbinlog [option] filename |mysql -uuser -ppassword
option是一些可选项，filename是日志文件名。
--start-datetime | --stop-datetime | --start-position | --stop--position |
|---|---|---|---|
恢复数据库起始时间点 | 结束时间点 | 恢复数据库开始位置 | 结束位置 |
例如，恢复mysql数据库到2022年10月1日15：27：48时的状态：
mysqlbinlog --stop-datetime="2022-10-1 15:27:48 " D:\mysql\log\binlog\binlog.000008 |mysql -u user -p password
错误日志
错误日志文件包含了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。 在默认情况下，错误日志会记录到数据库的数据目录下。如果没有在配置文件中指定文件名，则文件名默认为(主机名.err)。
相关命令
- 启动错误日志
my.ini中[mysqld]组下面有几个设置是关于二进制日志的(windows)：
#启动同时设置日志文件存放路径
log-error=[path/[file_name]]Other
- 查看错误日志
show variables LIKE 'log_error';
查看错误日志存储路径。mysql错误日志是以文本文件形式存储的，可以使用文本编辑器直接查看mysql错误日志。
3、删除错误日志
mysql的错误日志以文本文件的形式存储在文件系统中，可以直接删除错误日志文件。也可以使用命令：
FLUSH LOGS;
注意：
mysql5.7.7以前的版本，flush logs可以将错误日志文件重命名为filename.err_old，并创建新的日志文件。
mysql5.7.7开始，flush logs只是重新打开日志文件，并不做日志备份和创建的操作。所以手动删除错误日志文件后需要执行这条命令重新创建文件，或者手动创建同名文件。