当前位置： 首页 > news >正文

彩票网站是怎么做的,重庆工商学校官网,万州集团网站建设,中小企业网站seo文章目录 1.Binlog二进制日志的基本概念1.1.什么是Binlog二进制1.2.Binlog日志的三种记录格式1.3.Binlog日志中Event事件的概念 2.开启MySQL的Binlog二进制日志3.查看Binlog二进制日志中的Event事件信息3.1.查看当前数据库有那些Binlog日志3.2.产生一些DDL/DML语句3.3.观察Binl… 文章目录 1.Binlog二进制日志的基本概念1.1.什么是Binlog二进制1.2.Binlog日志的三种记录格式1.3.Binlog日志中Event事件的概念 2.开启MySQL的Binlog二进制日志3.查看Binlog二进制日志中的Event事件信息3.1.查看当前数据库有那些Binlog日志3.2.产生一些DDL/DML语句3.3.观察Binlog日志中产生的Event事件 4.分析Binlog日志内容的技巧5.读取Binlog日志记录的DML语句详细内容 1.Binlog二进制日志的基本概念 1.1.什么是Binlog二进制 MySQL数据库中的Binlog二进制日志中记录了所有的DDL数据库定义语句和DML数据操纵语句但是在二进制日志中不包括DQL数据查询语句。 Binlog二进制日志会记录数据的所有操作记录几乎是实时的当数据库发生故障导致数据丢失并且也没有备份时可以从Binlog日志中恢复数据库的数据。 Binlog日志的作用 灾难时可恢复数据库中的数据。MySQL主从复制通过Binlog来同步数据。 Binlog日志默认会保留30年内产生的二进制日志并且每当重启MySQL后就会向新的Binlog日志中写入数据。 1.2.Binlog日志的三种记录格式 针对Binlog的日志记录格式有ROW、STATEMENT、MIXED三种。 1ROW格式 ROW格式是MySQL针对Binlog日志的默认记录格式简称RBR是针对行的记录模式对表中的每一行数据变更都进行记录。 ROW格式的记录由于是针对每一行的变化都进行记录就会导致日志量级很大占用部分磁盘空间并且可读性交叉会通过自己的语法格式记录一行数据修改前后的内容虽然可读性差但是记录到日志里的行变化数据是非常准确的事务提交后数据变动前后是什么样子记录到日志里就会是什么样子非常的严谨。 ROW会将数据变化的前后内容都以独特的格式记录在日志中因此当需要通过Binlog进行日志恢复时不会出现由于某个字段是函数写入的值例如时间函数这类的ROW格式的Binlog记录的旧数据是什么样子的不会因为函数的取值的变化而变化保证数据的一致性避免恢复后的数据异常。 ROW格式也是MySQL 5.7默认的格式企业环境中建议使用。 查看ROW格式的Binlog时需要通过mysqlbinlog命令的-v参数将Binlog中的数据重构成我们易读取的SQL语句。 2STATEMENT格式 SYAYEMENT格式是针对SQL语句进行记录的简称SBR当数据有变化时将对应的SQL语句记录到日志中日志里相对于ROW格式来说很少例如当用户删除了表中id1000的数据对于SYAYEMENT格式就只会记录这一条SQL语句而ROW格式会记录每一行数据执行前后的内容。 SYAYEMENT格式的可读性很强但是对于语句中的某个字段值是以函数来取值的就会造成数据从错乱例如时间函数当时写在表中的时间是以now函数获取的那个时候的时间而数据恢复时又是通过now函数获取的则是当前时间就会造成数据不一致。 3MIXED格式 MIXED格式是混合类型的格式即记录STATEMENT格式又会记录ROW格式由MySQL自己判断什么样的数据是以什么样的格式去记录。 通过以下命令可以查看当前数据库Binlog的记录格式。 1.3.Binlog日志中Event事件的概念 Event事件是Binlog二进制日志中记录的最小单元在Binlog中DDL、DCL等语句一个语句就是一个Event事件。 在Binlog日志中对于DML语句来讲只会记录已经提交事务的DML语句 如下所示一组事务大概分成了四个Event事件每个Event事件都有开始标识位和结束标识位作为位置号在起始位置和结束位置内记录的就是事件的内容也就具体SQL。 一个事件的结束标识位和下一个事件的开始标识位是一样的也就表示每一个事件之间是由顺序关联的不会出现偏差例如begin这个事件的开始标识位是120结束标识位是340DML1的开始标识与begin的结束标识是相同的都是340。 这个开始标识和结束标识相当于一个位置号都是顺序记录的。 开始标识 结束标识 begin; 120 340DML1 340 460DML2 460 550 commit; 550 760当我们需要只针对Binlog中部分的事件进行数据恢复就可以通过开始标识和结束标识Position号来进行截取通过Position号截取的内容要比时间截取的更加精准。还是如上所示当我们要截取这个事务的日志数据要进行数据恢复那么就只需要截取开始标识位120到结束标识位760之间的事件信息进行数据恢复即可。 2.开启MySQL的Binlog二进制日志 在MySQL5.7版本中Binlog默认不开启。

mysql show variables like %log_bin%;

| Variable_name | Value |

| log_bin | OFF | #OFF表示关闭binlog | log_bin_basename | | | log_bin_index | | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | ————————————————————–开启Binlog日志。 1.在主配置文件中开启Binlog [rootmysql ~]# vim /etc/my.cnf [mysqld] server_id1 log_binmysql-bin #binlog日志的名称不指定路径默认在数据目录创建mysql-bin只是binlog的前缀 binlog_formatrow #binlog的记录类型 max_binlog_size1G #binlog日志文件的大小2.重启MySQL [rootmysql ~]# systemctl restart mysqld3.查看生成的Binlog日志 [rootmysql ~]# ll /data/mysql/mysql-bin.* -rw-r—– 1 mysql mysql 154 6月 30 17:00 /data/mysql/mysql-bin.000001 -rw-r—– 1 mysql mysql 19 6月 30 17:00 /data/mysql/mysql-bin.index #二进制日志默认从000001开始每当重启一次MySQL就会产生一个新的的binlog日志每当重启一次数据库或者当Binlog日志容量超出最大限制时就会产生一个新的Binlog日志文件在mysql-bin.index文件中记录了当前MySQL实例中所有的Binlog日志文件有哪些以及其路径MySQL在读Binlog日志时首先会读取index这个文件。 3.查看Binlog二进制日志中的Event事件信息 我们可以通过命令来查看当前数据库正在往哪一个Binlog日志文件中写入日志数据并且我们再前面也提到了针对DDL、DCL、DML这类的语句每一条都是以一个事件记录在Binlog日志中的我们可以通过通过命令来查看指定的Binlog日志文件中有那些事件还可以看到事件的内容是什么也就是了解这个事件做了什么操作。 当我们清楚了Event事件之后就可以再通过命令去查看Event事件中的具体内容包括修改数据的SQL等等。 3.1.查看当前数据库有那些Binlog日志 首先来查看一下当前数据库中有那些Binlog日志文件以及正在使用的是哪个Binlog日志文件。 通过以下命令可以查看当前数据库中有哪些Binlog日志文件一般来说最后一行就是我们当前正在使用的Binlog日志文件第二列的File_size并不是说该Binlog日志文件当前的容量有多大这个File_size指的是这个Binlog日志中最大结束标识位是多少也就是Position号偏移量记录到了那里。

mysql show binary logs;

| Log_name | File_size |

| mysql-bin.000001 | 177 | | mysql-bin.000002 | 154 | —————————–通过以下命令可以看到当前数据库正在往哪一个Binlog日志中记录数据当前数据库正在往mysql-bin.000002日志文件中写入数据Position一列记录的就是该日志文件中目前记录到的结束标识位号也就是最大的标识位号默认从4开始1-3作为保留另外前154个标识位号是记录MySQL的一些信息的是MySQL5.7版本独有的特性后面的字段是主从复制中才会用到的。 可以看到这个Position号与前面的File_size是一致的所以更加证明了前面那个命令显示的不是文件大小而是该日志中记录到哪一个结束标识号了。

mysql show master status;

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

| mysql-bin.000002 | 154 | | | | ——————————————————————————-3.2.产生一些DDL/DML语句 产生一些DDL、DML语句方便后面观察Binlog日志中针对每一条语句产生的时间信息。 1.创建个数据库 mysql create database db_2;2.创建个表 mysql use db_2; mysql create table t1 (id int);3.插入一条数据 mysql insert into t1 values (1);3.3.观察Binlog日志中产生的Event事件 通过以下命令可以查看指定Binlog日志中的事件信息每一个事件都对应一条DDL、DCL、DML语句。

mysql show binlog events in mysql-bin.000002;

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

| mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.36-log, Binlog ver: 4 | | mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET SESSION.GTID_NEXT ANONYMOUS | | mysql-bin.000002 | 219 | Query | 1 | 313 | create database db_2 | | mysql-bin.000002 | 313 | Anonymous_Gtid | 1 | 378 | SET SESSION.GTID_NEXT ANONYMOUS | | mysql-bin.000002 | 378 | Query | 1 | 476 | use db_2; create table t1 (id int) | | mysql-bin.000002 | 476 | Anonymous_Gtid | 1 | 541 | SET SESSION.GTID_NEXT ANONYMOUS | | mysql-bin.000002 | 541 | Query | 1 | 613 | BEGIN | | mysql-bin.000002 | 613 | Table_map | 1 | 658 | table_id: 108 (db_2.t1) | | mysql-bin.000002 | 658 | Write_rows | 1 | 698 | table_id: 108 flags: STMT_END_F |

| mysql-bin.000002 | 698 | Xid | 1 | 729 | COMMIT /* xid16 */ |

11 rows in set (0.00 sec)输出的内容每个字段的含义 Log_nameBinlog日志的名称 Pos开始的标识位号默认从4开始1-3是保留的标识位前154个标识位是记录MySQL的一些信息。从上面输出的内容我们可以看到从标识位4-154之间的事件都是关于MySQL信息的一些事件内容。从154之后才是真正用户操作的信息。 Event_type事件的类型主要关心Query类型。 Server_id声明这个时间是由哪个数据库实例产生的主要是在主从复制模式下说明都是由主节点产生的事件。 End_log_pos结束标识位。 Info具体时间的内容DDL语句还好在Info中显示除了具体的DDL操作语句但是像DML类型的操作只是记录了这是一个事务事务内的SQL语句却看不出来是什么如果我们硬要看事务内执行的SQL就需要去读Binlog日志了。 4.分析Binlog日志内容的技巧 通过Binlog的事件我们只能看到DDL执行的语句对于DML语句是看不懂的下面就需要去Binlog日志中读取DML的内容在读取DML内容之前我们先来掌握分析Binlog日志文件的技巧。 通过mysqlbinlog命令分析Binlog日志文件。 [rootmysql ~]# mysqlbinlog /data/mysql/mysql-bin.000002 如下图所示会输出很多很多的内容但是实际上SET开头的语句是没有用的并且占比也比较多我们可以过滤掉。 [rootmysql ~]# mysqlbinlog /data/mysql/mysql-bin.000002 | grep -v SET这样一看就好多了但是也别急刚刚说了标识位4-154都是MySQL内部的信息无需看因此我们从第154个标识位处开始分析就行了一个标识位到另一个标识位之间的内容就是这个Event事件的具体内容DDL语句还是能清晰的看到但是DML语句依然被ROW格式单独声明了还是无法查看不过后面有办法。 在日志中我们可以看到有很多行的注释这个注释也是很有作用的例如这个#220630 22:58:17 server id 1注释就记录了再2022年的6月30日22点58分17秒的时候由server id为1的节点触发了下面的Binlog日志。我们也可以通过注释中的时间截取某个时间段的Binlog日志。 5.读取Binlog日志记录的DML语句详细内容 在前面无论我们是通过事件还是直接读取Binlog日志对于DML语句的部分都是通过ROW格式独特的显示方式展示的让我们无法读懂从而不知道DML做了什么操作。 针对ROW格式独有的显示方式可以通过mysqlbinlog命令的-v参数将其重构成我们能读懂的SQL语句。 [rootmysql ~]# mysqlbinlog -v /data/mysql/mysql-bin.000002 或者使用下面的这个命令通过base64的方式将ROW格式重构成SQL语句。 [rootmysql ~]# mysqlbinlog –base64-outputdecode-rows -vvvv /data/mysql/mysql-bin.000002 将ROW格式的内容重构成SQL语句如下,INSERT语句会产生一个事务我们将重构完关于事务的这个事件内容摘了出来重构成SQL语句之后就会在事务中显示具体的DML SQL语句。

at 541

#220630 22:58:17 server id 1 end_log_pos 613 CRC32 0xfb4afd37 Query thread_id3 exec_time0 error_code0 SET TIMESTAMP1656601097/!/; BEGIN /!/;

at 613

#220630 22:58:17 server id 1 end_log_pos 658 CRC32 0x19bd3592 Table_map: db_2.t1 mapped to number 108

at 658

#220630 22:58:17 server id 1 end_log_pos 698 CRC32 0xce62c766 Write_rows: table id 108 flags: STMT_END_F

INSERT INTO db_2.t1

SET

11 /* INT meta0 nullable1 is_null0 */

at 698

#220630 22:58:17 server id 1 end_log_pos 729 CRC32 0x6f054645 Xid 16 COMMIT/!/;从第10行到12行开头是###的都是DML语句记录的都是INSERT插入数据的语句不过和我们常规的INSERT不同在这里多了一个SET指令SET后面是11这个1表示的是这条数据中的第一个字段等于号后面是这个字段的值整个INSERT连起来的意思就是说在t1表中插入了一条数据插入的这条数据中第一个字段的值为1因为表中只有一个字段因此只显示了一行如果有多个字段会显示多个数字数字是几就表示是第几个字段对应的值。