截短清空 mysql_mysql 错误日志，二进制日志及截取及清理, 初识gtid

最新推荐文章于 2022-11-14 21:00:00 发布

weixin_39858132

最新推荐文章于 2022-11-14 21:00:00 发布

阅读量297

点赞数

文章标签：截短清空 mysql

本文链接：https://blog.csdn.net/weixin_39858132/article/details/113200165

版权

mysql提供的工具类日志种类：

1.错误日志(log_error)

用来记录启动\关闭\日常运行过程中,状态信息,警告,错误。默认是开启的

1.1 错误日志配置

1 默认就是开启的: /数据路径下/hostname.err2 查看错误日志位置：select@@log_error;3

4 手工指定位置:5 vim /etc/my.cnf6 log_error=/var/log/mysql.log7 log_timestamps=system8 重启生效9

11 show variables like 'log_error';

1.2 日志内容查看

1 主要关注[ERROR],看上下文

2. binlog(binary logs):二进制日志

2.1 作用

1 备份恢复必须依赖二进制日志2 主从环境必须依赖二进制日志3 5.x以上版本默认都没有开启二进制日志，需要手动配置来启用

2.2 binlog配置 (5.7必须加server_id)

1 注意：MySQL默认是没有开启二进制日志的。2

3 基础参数查看:4 开关状态: select@@log_bin;5 日志路径及名字：select@@log_bin_basename;6 服务ID号: select@@server_id;7 二进制日志格式: select@@binlog_format;8 双一标准之二: select @@sync_binlog;

2.2.1 创建日志目录

1 mkdir /data/binlog2 chown -R mysql.mysql /data/binlog

2.2.2 修改配置文件

1 vim /etc/my.cnf2

4 server_id=6 (取值1-65535) 5.6中，单机可以不需要此参数5

6 # log_bin有两种配置方式，如下讲解7 log_bin=1只打开二进制日志开关，文件存放在默认的位置8 log_bin=/data/binlog/mysql-bin 开启二进制日志，按照路径生成二进制文件，mysql-bin为指定的文件名前缀。9 指定路径后会在其下额外生成个mysql-bin.index文件，其内存放的是二进制文件的名，便于统计10

11 binlog_format=row 5.7默认的配置，可省略

2.2.3 重启数据库生效

2.2.4 参数说明

server_id=3306主要是在主从复制过程中必须要加的,但是在5.7版本中,要用以下参数(log_bin),开启binlog日志,即使是单机也是必加的

log_bin=/data/binlog/mysql-bin

(1)开启二进制日志功能

(2)设置二进制日志目录及名称前缀

binlog_format=row

binlog中记录dml语句的记录格式

2.3 binlog记录了什么?

2.3.0 引入

1 binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。

2.3.1 记录SQL语句种类

DDL ：原封不动的记录当前DDL(statement语句方式)。

DCL ：原封不动的记录当前DCL(statement语句方式)。

DML ：只记录已经提交的事务DML

2.3.2 DML三种记录格式，仅对dml语句有效

binlog_format=xx(binlog的记录格式)参数影响,取值如下：

(1)statement(5.6默认)SBR(statement based replication) ：语句模式原封不动的记录当前DML。

(2)ROW(5.7默认值) RBR(ROW based replication) 　　　　　　：记录数据行的变化(用户看不懂，需要工具分析)

(3)mixed(混合)MBR(mixed based replication)模式　　　　：以上两种模式的混合

2.3.3 三种记录格式如何选取：

1 SBR与RBR模式的对比：2

4 STATEMENT(SBR)：可读性较高，日志量少，但是不够严谨5 ROW(RBR) ：可读性很低，日志量大，足够严谨，一些高可用环境中的新特性要依赖RBR模式6

7 示例：8 update t1 set xx=xx where id>10

9 SBR会把update语句记录下来，10 RBR会把所有受影响的行的变化状态给记录下来11

12 解析：13 为什么说SBR不严谨或某些情况下不准确呢？14 insert into t1 values(1,'zs',now())这种场景下使用now获取时间场景15

17 我们建议使用：row记录模式

2.4 event(事件)是什么?

2.4.1 事件的简介

1 二进制日志的最小记录单元2

4 对于DDL,DCL,一个语句就是一个event5 对于DML语句来讲:只记录已提交的事务。6

7 例如以下列子,就被分为了4个event8 begin; 120 - 340事件19 DML1 340 - 460事件210 DML2 460 - 550事件311 commit; 550 - 760 事件4

2.4.2 event的组成

1 三部分构成:2 (1) 事件的开始标识3 (2) 事件内容4 (3) 事件的结束标识5 Position:6 开始标识: at 194

7 结束标识: end_log_pos 254

8 194? 254?

9 某个事件在binlog中的相对位置号10 位置号的作用是什么？11 为了方便我们截取事件

2.5 日志文件查看

2.5.1 查看日志的开启情况

log_bin参数设置的路径,可以找到二进制日志

1 Master [(none)]>show variables like '%log_bin%';2 +---------------------------------+------------------------------+

3 | Variable_name | Value |

4 +---------------------------------+------------------------------+

5 | log_bin | ON |

6 | log_bin_basename | /data/binlog/mysql-bin |

7 | log_bin_index | /data/binlog/mysql-bin.index |

8 | log_bin_trust_function_creators | OFF |

9 | log_bin_use_v1_row_events | OFF |

10 | sql_log_bin | ON |

11 +---------------------------------+------------------------------+

12 6 rows in set (0.01 sec)

2.5.2 查看一共多少个binlog

1 Master [(none)]>show binary logs;2 +------------------+-----------+

3 | Log_name | File_size |

4 +------------------+-----------+

5 | mysql-bin.000001 | 154 |

6 +------------------+-----------+

7 1 row in set (0.01sec)8

10 # 手动创建出(刷出更多二进制日志文件)11 Master [(none)]>flush logs;12 Query OK, 0 rows affected (0.03sec)13

14 Master [(none)]>flush logs;15 Query OK, 0 rows affected (0.01sec)16

17 Master [(none)]>show binary logs;18 +------------------+-----------+

19 | Log_name | File_size |

20 +------------------+-----------+

21 | mysql-bin.000001 | 201 |

22 | mysql-bin.000002 | 201 |

23 | mysql-bin.000003 | 154 |

24 +------------------+-----------+

25 3 rows in set (0.00 sec)

2.5.3 查看mysql正在使用的日志文件

1 Master [(none)]>show master status;2 +------------------+----------+--------------+------------------+-------------------+

4 +------------------+----------+--------------+------------------+-------------------+

5 | mysql-bin.000003 | 154 | | | |

6 +------------------+----------+--------------+------------------+-------------------+

7 Master [(none)]>

file：当前MySQL正在使用的文件名

Position：最后一个事件的结束位置号

2.6 日志内容查看

2.6.1 event查看

1 Master [binlog]>show binlog events in 'mysql-bin.000003';2 +------------------+-----+----------------+-----------+-------------+----------------------------------------+

4 +------------------+-----+----------------+-----------+-------------+----------------------------------------+

5 | mysql-bin.000003 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |

6 | mysql-bin.000003 | 123 | Previous_gtids | 6 | 154 | |

7 | mysql-bin.000003 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

8 | mysql-bin.000003 | 219 | Query | 6 | 319 | create database binlog |

9 | mysql-bin.000003 | 319 | Anonymous_Gtid | 6 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |

10 | mysql-bin.000003 | 384 | Query | 6 | 486 | use `binlog`; create table t1 (id int) |

11 +------------------+-----+----------------+-----------+-------------+----------------------------------------+

12 前两行不用关注，是mysql固定的头格式，用于表示版本等信息。

13 Log_name：binlog文件名14 Pos：开始的position *****

15 Event_type：事件类型16 Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息17 Server_id：mysql服务号标识18 End_log_pos：事件的结束位置号 *****

19 Info：事件内容*****

20 补充:21 SHOW BINLOG EVENTS22 [IN 'log_name']23 [FROM pos]24 [LIMIT [offset,] row_count]25 [root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004'" |grep drop

2.6.2 binlog文件内容详细查看

普通查看：

mysqlbinlog /data/mysql/mysql-bin.000006格式化查看，也难以读懂，但比普通查看好多了

mysqlbinlog--base64-output=decode-rows -vvv /data/binlog/mysql-bin.000003使用-d参数来指定库名来从日志中查看内容，binlog是库名

mysqlbinlog-d binlog /data/binlog/mysql-bin.000003[root@db01 binlog]# mysqlbinlog--start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00' /data/binlog/mysql-bin.000004

2.7 基于Position号进行日志截取

核心就是找截取的起点和终点--start-position=321

--stop-position=513mysqlbinlog--start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql

案例: 使用binlog日志进行数据恢复

模拟:1.

[(none)]>create database binlog charset utf8;2.

[(none)]>use binlog;

[binlog]>create table t1(id int);3.

[binlog]>insert into t1 values(1);

[binlog]>commit;

[binlog]>insert into t1 values(2);

[binlog]>commit;

[binlog]>insert into t1 values(3);

[binlog]>commit;4.

[binlog]>drop database binlog;

恢复:

[(none)]>show master status ;

[(none)]>show binlog events in 'mysql-bin.000004';

[root@db01 binlog]# mysqlbinlog--start-position=1227 --stop-position=2342 /data/binlog/mysql-bin.000004 >/tmp/bin.sql

[(none)]>set sql_Log_bin=0; 临时关掉二进制log功能，不记录以下sql, 用完记得再打开， set sql_log_bin=1

[(none)]>source /tmp/bin.sql

面试案例:1. 备份策略每天全备,有全量的二进制日志2.业务中一共10个库,其中一个被误drop了3. 需要在其他9个库正常工作过程中进行数据恢复

2.7.1 思考：

以上的恢复手段缺陷很大，因为我们很容易的就能找到建库到删库的所有起始号码，

实际生产中我们库很可能是很多年前就创建了，并且多个库下表的记录都是混杂在一起的，

此时用以上手段很难恢复，需要大量的时间去整理该库的所有二进制日志，显然不现实。

解决：

我们可以先使用备份把库恢复到最近一次备份的时间点，然后再配合二进制的恢复手段来

恢复出上个时间点到删库之前的数据。

mysql没有提供指定表查看二进制记录的功能，但提供有指定库名来查看该库中所有表的二进制记录，命令参考： 2.6.2 binlog文件内容详细查看

2.8 binlog日志的GTID新特性

2.8.1 GTID 介绍

5.6 版本新加的特性,5.7中做了加强5.6中不开启,没有这个功能.5.7中的GTID,即使不开也会有自动生成

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'

2.8.2. GTID(Global Transaction ID)

是对于一个已提交事务进行编号，并且是一个全局唯一的编号。

DDL和DCL每一条语句就是一个事务，就会有一个对应的编号。

GTID组成形式： server_uuid:TID

7E11FA47-31CA-19E1-9E56-C43AA21293967:1-3

server_uuid在数据库初始化完成，第一次启动的时候就会自动生成，存放于数据目录中的auto.cnf文件中

TID是一个从1开始的递增数字，其具有幂等性，幂等性见2.8.4

GTID的配置和开启

vim /etc/my.cnf

gtid-mode=on　　开启gtid模式

enforce-gtid-consistency=true　　强制gtid一致性

GTID开启只影响后续发生的事务，开启之前的不受影响。配置好后启动生效

查看GTID的表现

重启第一次show master status时最后一列的值为空，该列就是gtid的编号

Master [(none)]>create database gtid charset utf8;

Query OK,1 row affected (0.01sec)

Master [(none)]>show master status ;+------------------+----------+--------------+------------------+----------------------------------------+

+------------------+----------+--------------+------------------+----------------------------------------+

+------------------+----------+--------------+------------------+----------------------------------------+

1 row in set (0.00sec)

Master [(none)]>use gtid

Database changed

Master [gtid]>create table t1 (id int);

Query OK,0 rows affected (0.01sec)

Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+

+------------------+----------+--------------+------------------+------------------------------------------+

+------------------+----------+--------------+------------------+------------------------------------------+

1 row in set (0.00sec)

Master [gtid]>create table t2 (id int);

Query OK,0 rows affected (0.01sec)

Master [gtid]>create table t3 (id int);

Query OK,0 rows affected (0.02sec)

Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+

+------------------+----------+--------------+------------------+------------------------------------------+

+------------------+----------+--------------+------------------+------------------------------------------+

1 row in set (0.00sec)

Master [gtid]>begin;

Query OK,0 rows affected (0.00sec)

Master [gtid]>insert into t1 values(1);

Query OK,1 row affected (0.00sec)

Master [gtid]>commit;

Query OK,0 rows affected (0.00sec)

Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+

+------------------+----------+--------------+------------------+------------------------------------------+

+------------------+----------+--------------+------------------+------------------------------------------+

1 row in set (0.00sec)

Master [gtid]>begin;

Query OK,0 rows affected (0.00sec)

Master [gtid]>insert into t2 values(1);

Query OK,1 row affected (0.00sec)

Master [gtid]>commit;

Query OK,0 rows affected (0.01sec)

Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+

+------------------+----------+--------------+------------------+------------------------------------------+

+------------------+----------+--------------+------------------+------------------------------------------+

1 row in set (0.00 sec)

2.8.3. 基于GTID进行查看binlog

具备GTID后,截取查看某些事务日志:--include-gtids--exclude-gtids

mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' /data/binlog/mysql-bin.000004

mysqlbinlog--include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4' /data/binlog/mysql-bin.000004

使用--exclude-gtids可指定需要排除的gtid号，可一次排除多个，只需使用逗号隔开多个gtid号即可。include-gtids也可这样使用

2.8.4 GTID的幂等性

开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了

幂等性会影响binlog恢复和主从复制

就想恢复?怎么办?

--skip-gtids　　可用来忽略gtid幂等性，注意，只要开启了gtid不管是否使用incluede还是position号来截取二进制日志，都需要加这个参数。

mysqlbinlog--include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:4' /data/binlog/mysql-bin.000004 /data/binlog/mysql-bin.000004

set sql_log_bin=0;

source/tmp/binlog.sqlset sql_log_bin=1;

故障恢复演练

创建库

创建表

写入数据

递交

删除库

show master status;

show binlog events in 'mysql-bin.000004'; 查看从创建库到删除库的gtid号，以下以1-6来演示

mysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' /data/binlog/mysql-bin.000004 > /xx/back.sql

登陆数据库先禁用二进制日志，然后source截取出的文件

set sql_log_bin=0

source /xx/back.sql 此处会报错提示找不到database

思考为什么失败

我们导出的恢复文件就是从系统中导出的，系统中记录有1-6的事务，结合GTID的幂等性就可明白了，因为系统认为1-6号事务已执行过，就不会再执行了。

解决办法，我们可在导出二进制日志时让其忽略幂等性，

--skip-gtids 在导出时忽略原有的gtid，恢复时生成新的gtid

mysqlbinlog --skip-gtids --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' /data/binlog/mysql-bin.000004 > /xx/back.sql使用新导出的back.sql再次到数据库执行即可。

2.9二进制日志其他操作

2.9.1 自动清理日志

show variables like '%expire%';

expire_logs_days00表示永不清理

自动清理时间,是要按照全备周期+1

set global expire_logs_days=15;

永久生效:

my.cnf

expire_logs_days=15;

企业建议,至少保留两个全备周期+1的binlog

2.9.2 手工清理

# 清除3天前的

PURGE BINARY LOGS BEFORE now() - INTERVAL 3day;

# 清除到10之前的，也就是删掉了1-9

PURGE BINARY LOGS TO'mysql-bin.000010';

注意:不要手工 rm binlog文件1. my.cnf binlog关闭掉,启动数据库2.把数据库关闭,开启binlog,启动数据库

删除所有binlog,并从000001开始重新记录日志

# 删掉二进制文件后，后缀标号也不会从1开始，如果6位数用满了可使用如下命令重新从1开始。主从时千万不要使用

reset master; 主从关系中，主库执行此操作，主从环境必崩

2.9.3 日志是怎么滚动

1. flush logs;

2. 重启mysql也会自动滚动一个新的

3. 日志文件达到1G大小(max_binlog_size)　　max_binlog_size | 10737418244. 备份时,加入参数也可以自动滚动

weixin_39858132

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
截短清空 mysql_mysql 错误日志，二进制日志及截取及清理, 初识gtid

mysql提供的工具类日志种类：1.错误日志(log_error)用来记录启动\关闭\日常运行过程中,状态信息,警告,错误。默认是开启的1.1 错误日志配置1 默认就是开启的: /数据路径下/hostname.err2 查看错误日志位置：select@@log_error;34 手工指定位置:5 vim /etc/my.cnf6 log_error=/var/log/mysql.log7 log...
复制链接

扫一扫