认识 MySQL物理文件(2)-木庄网络博客

当前第2页返回上一页

# 查看错误日志记录位置
show variables like "log_error";
# 在 MySQL 中，可以使用 mysqladmin 命令来开启新的错误日志，以保证 MySQL 服务器上的硬盘空间。
# mysqladmin 命令的语法如下：
mysqladmin -uroot -p flush-logs
# 执行该命令后，MySQL 服务器首先会自动创建一个新的错误日志，然后将旧的错误日志更名为 filename.err-old 。可以手动直接删除。
 
#配置文件中配置
[mysqld]
log-error=dir/{filename}复制代码

2.4 二进制日志

二进制日志（Binary Log）也可叫作变更日志（Update Log），是 MySQL 中非常重要的日志。主要用于记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，不包含数据记录查询操作。

# 查看 binary log 日志是否开启，binary log日志默认关闭
show variables like "log_bin";
# 在MySQL中可以再配置文件中开启二进制文件日志
[mysqld]
log-bin=dir/{filename}复制代码

其中，dir 参数指定二进制文件的存储路径；filename 参数指定二进制文件的文件名，其形式为 filename.number，number 的形式为 000001、000002 等，每次重启 MySQL 服务后，都会生成一个新的二进制日志文件，这些日志文件的文件名中 filename 部分不会改变，number 会不断递增。

二进制日志的格式有三种：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

每一条会修改数据的 sql 语句会记录到 binlog 中。优点是并不需要记录每一条 sql 语句和每一行的数据变化，
减少了 binlog 日志量，节约 IO ，提高性能。缺点是在某些情况下会导致 master-slave 中的数据不一致(如
 sleep() 函数，  last_insert_id() ，以及 user-defined functions(udf) 等会出现问题)复制代码

② ROW模式（RBR）

不记录每条 sql 语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下
的存储过程、或 function 、或 trigger 的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是
alter table的时候会让日志暴涨。复制代码

③ MIXED模式（MBR）

1 2	`以上两种模式的混合使用，一般的复制使用 STATEMENT 模式保存 binlog ，对于 STATEMENT 模式无法复制的操作使用` `ROW 模式保存 binlog ， MySQL 会根据执行的 SQL 语句选择日志保存方式。复制代码`

binlog复制配置

在mysql的配置文件 my.cnf 或中，可以通过一下选项配置 binary log

binlog_format          = MIXED                 //binlog日志格式，mysql默认采用statement，建议使用mixed 
log-bin                = mysql-bin             //binlog日志文件 
expire_logs_days       = 7                     //binlog过期清理时间 
max_binlog_size        = 100m                  //binlog每个日志文件大小 
binlog_cache_size      = 4m                    //binlog缓存大小 
max_binlog_cache_size  = 512m                  //最大binlog缓存大小
server-id = 1复制代码

2.5二进制文件基本操作

可以使用如下命令查看 MySQL 中有哪些二进制日志文件：show binary logs
show master status 命令用来查看当前的二进制日志；
二进制日志使用二进制格式存储，不能直接打开查看。如果需要查看二进制日志，使用 show binlog events in 'mysql-bin.000001'; 命令。
删除二进制文件
1. 使用 RESET MASTER 语句可以删除的所有二进制日志
2. 每个二进制日志文件后面有一个 6 位数的编号，如 000001。使用 PURGE MASTER LOGS TO 'filename.number' 语句，可以删除指定二进制日志的编号之前的日志
3. 使用 PURGE MASTER LOGS TO 'yyyy-mm-dd hh:MM:ss' 语句，可以删除指定时间之前创建的二进制日志

使用二进制文件恢复数据

创建数据库

CREATE TABLE `33hao_activity`  (
  `activity_id` mediumint(9) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `activity_title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '标题',
  `activity_type` enum('1','2') CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '活动类型 1:商品 2:团购',
  `activity_banner` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '活动横幅大图片',
  `activity_style` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '活动页面模板样式标识码',
  `activity_desc` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '描述',
  `activity_start_date` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '开始时间',
  `activity_end_date` int(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT '结束时间',
  `activity_sort` tinyint(1) UNSIGNED NOT NULL DEFAULT 255 COMMENT '排序',
  `activity_state` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '活动状态 0为关闭 1为开启',
  PRIMARY KEY (`activity_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '活动表' ROW_FORMAT = Compact;复制代码

新增 2 条数据

1 2	INSERT INTO `33hao_activity` VALUES (1, `'2017年跨年满即送活动',` `'1',` `'05364373801675235.jpg',` `'default_style',` `'', 1483113600, 1483286400, 0, 0);` INSERT INTO `33hao_activity` VALUES (2, `'转盘抽奖弹窗',` `'1',` `'06480453986921327.jpg',` `'',` `'转盘抽奖弹窗', 1594656000, 1594915200, 0, 0);复制代码`

删除数据
1
drop table `33hao_activity`;复制代码
恢复数据
- 根据节点需要我们查看日志文件提供给我们的数据库创建，表创建，数据新增等时创建的语句节点，从而恢复数据。
  1
  mysqlbinlog --start-position=154 --stop-position=2062 D:/phpstudy_pro/Extensions/MySQL5.7.26/data/mysql-bin.000001 | mysql -uroot -p复制代码
- 根据时间恢复数据
  1
  mysqlbinlog --start-datetime='2020-09-27 22:22:22' --stop-datetime='2020-09-27 22:30:00' /www/server/data/mysql-bin.000036 | mysql -uroot -p复制代码
- 直接执行binlog日志
  1
  mysqlbinlog /www/server/data/mysql-bin.000036 | mysql -uroot -p复制代码