Master_log_pos in replication

[Sanjeev Sagar]

Hello All,

 

I am having problem in finding out the exact position in master bin log
file in replication setup for point-in-time recovery process. Let me
explain the problem in detail.

 

I have log_position table on all slaves, see the definition below

 

       Table: log_position

Create Table: CREATE TABLE `log_position` (

  `host` varchar(60) NOT NULL default '',

  `time_stamp` timestamp(14) NOT NULL,

  `log_file` varchar(32) default NULL,

  `log_pos` int(11) default NULL,

  `master_host` varchar(60) default NULL,

  `master_log_file` varchar(32) default NULL,

  `master_log_pos` int(11) default NULL,

  PRIMARY KEY  (`host`,`time_stamp`)

) TYPE=MyISAM

 

I am running a script every minute to insert a record into this table by
using the information from "show slave status". 

 

my $mastersql="SHOW MASTER STATUS";

my $slavesql="SHOW SLAVE STATUS";

 


my $masterinfo = $conn->Hash($mastersql) if (defined $conn);

my $slaveinfo = $conn->Hash($slavesql) if (defined $conn);

 


my $logfile = $masterinfo->{File} ;

my $logpos = $masterinfo->{Position} ;

my $masterhost = $slaveinfo->{Master_Host} ;

my $masterlogfile = $slaveinfo->{Relay_Master_Log_File} ;

my $masterlogpos = $slaveinfo->{Exec_master_log_pos} ;

 

 

Also I am taking log snapshot every hour to my backup server.

 

I am in a situation where every minute I get 3000 transactions in my
database, mostly inserts.

 

When I am doing a point-in-time recovery, I am not able to see the
correct position in master_log_file during that minute interval.

 

My question is that is there any way which can help me if I have to
locate the position in master_log_file during that one minute interval.
I do not have option of running the script 30 sec. We are looking for
something where there is a unique identifier for every record in binlog
file. 

 

Any idea or help will be highly appreciable.

 

Regards,