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,
