SHOW_BINLOG_EVENTS()¶
说明¶
获得指定Binlog文件的详细内容。
参数¶
startpos
int 类型。要查看日志的起始位置，一般先获取 currentlsn，再执行sql语句。从 currentlsn 的位置开始读取，不可以随意设定，否则会导致读取出错。
skip_num
int 类型。跳过的事务个数。默认是0，会读取所有的事务。
print_num
int 类型。扫描输出事务个数。默认是0，会输出所有的事务。
相关属性¶
SHOW_BINLOG_EVENTS可以显示如下属性列：
EVENT_TYPE
事件的类型。
PHXID
事件所属的物理事务号。
USER_NAME
当前用户名。
SCHEMA_NAME
事件对应的模式。
TABLE_NAME
事件对应的表名。
EVENT_ID
事件的ID。
INSTANCE_NUMBER
共享存储数据库集群的实例ID，非集群时该列返回值为0。
END_LOG_POS
从 startpos 开始读取，当前事件所在redo日志的起始位置。
NEXT_LOG_POS
下一次需要读取日志的起始位置。类似于startpos。
CSF
表示上个event是否结束，延续sql的标志。
EVENT_INFO
当前数据库与Binlog的版本信息。
REDO_SQL
事件对应的sql语句。
EVENT_TIME
事件生成的时间。
示例¶
示例1: 查看show_binlog_events
alter database archivelog;
drop table test_show;
drop table temp_lsn;
drop table binloginfo;
drop procedure purge_binlog_file;
create table temp_lsn(log_lsn BIGINT);
create table binloginfo(EVENT_TYPE VARCHAR(20), PXID INT8, USER_NAME VARCHAR(128), SCHEMA_NAME VARCHAR(128), TABLE_NAME VARCHAR(128), EVENT_ID int, INSTANCE_NUMBER int,END_LOG_POS int8, NEXT_LOG_POS int8, CSF int1, EVENT_INFO text, REDO_SQL text, EVENT_TIME TIMESTAMP);
create or replace procedure purge_binlog_file() as
DECLARE
pos BIGINT;
BEGIN
SELECT * INTO pos FROM temp_lsn;
INSERT INTO binloginfo select * FROM show_binlog_events(pos,0,0) where redo_sql not like '%TEMP_LSN%';
END;
/
alter system;
insert into temp_lsn(select current_lsn from v$database);
create table test_show(a text);
insert into test_show values(1);
insert into test_show values(repeat('a',80));
exec purge_binlog_file();
select * from binloginfo;
EVENT_TYPE(varchar) |PXID(bigint) |USER_NAME(varchar) |SCHEMA_NAME(varchar) |TABLE_NAME(varchar) |EVENT_ID(int) |INSTANCE_NUMBER(int) |END_LOG_POS(bigint) |NEXT_LOG_POS(bigint) |CSF(tinyint) |EVENT_INFO(text) |REDO_SQL(text) |EVENT_TIME(timestamp) |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN |281492156579841 |SYSDBA | | |1 |0 |23151104 |23153567 |0 |BEGIN |0 |2024-09-23 16:32:11 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN |281496451547137 |SYSDBA | | |1 |0 |23153664 |23158964 |0 |BEGIN |65536 |2024-09-23 16:32:11 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY |281496451547137 |SYSDBA | | |2 |0 |23153664 |23158964 |0 |create table test_show|create table test_sh|2024-09-23 16:32:11 |
| | | | | | | | | |(a text) SYSDBA |ow(a text) | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN |281500746514433 |SYSDBA | | |1 |0 |23159296 |23161723 |0 |BEGIN |0 |2024-09-23 16:32:11 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT_ROW |281500746514433 |SYSDBA |SYSDBA |TEST_SHOW |2 |0 |23159296 |23161723 |0 |flags:STMT_END_F table|insert into "SYSDBA"|2024-09-23 16:32:11 |
| | | | | | | | | |_id=1 (SYSDBA.TEST_SH|."TEST_SHOW" values| |
| | | | | | | | | |OW) |('1'); | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN |281505041481729 |SYSDBA | | |1 |0 |23161856 |23162751 |0 |BEGIN |0 |2024-09-23 16:32:11 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT_ROW |281505041481729 |SYSDBA |SYSDBA |TEST_SHOW |2 |0 |23161856 |23162751 |0 |flags:STMT_END_F table|insert into "SYSDBA"|2024-09-23 16:32:11 |
| | | | | | | | | |_id=1 (SYSDBA.TEST_SH|."TEST_SHOW" values| |
| | | | | | | | | |OW) |('aaaaaaaaaaaaaaaaa| |
| | | | | | | | | | |aaaaaaaaaaaaaaaaaaa| |
| | | | | | | | | | |aaaaaaaaaaaaaaaaaaa| |
| | | | | | | | | | |aaaaaaaaaaaaaaaaaaa| |
| | | | | | | | | | |aaaaaa'); | |
总数目：7
drop table test_show;
drop table temp_lsn;
drop table binloginfo;
drop procedure purge_binlog_file;