Not the answer you need?
Register and ask your own question!

Is it possible to pause and resume data copy dynamically on pt-online-schema-change?

RyanERyanE EntrantCurrent User Role Beginner
Hello.

When there is a replication lag on slaves, pt-online-schema-change can pause copying data.

In the same way, is it possible to pause and resume copying them dynamically?

Below code is simple and works when I create pause_please.txt file.
I am wondering if I lose data while pausing it.


package NibbleIterator;
sub next()

NIBBLE:

while ( $self->{have_rows} || $self->_next_boundaries() ) {
if ( !$self->{have_rows} ) {
$self->{nibbleno}++;
PTDEBUG && _d('Nibble:', $self->{nibble_sth}->{Statement}, 'params:',
join(', ', (@{$self->{lower} || []}, @{$self->{upper} || []})));
if ( my $callback = $self->{callbacks}->{exec_nibble} ) {
$self->{have_rows} = $callback->(%callback_args);
}
else {
$self->{nibble_sth}->execute(@{$self->{lower}}, @{$self->{upper}});
$self->{have_rows} = $self->{nibble_sth}->rows();
}
PTDEBUG && _d($self->{have_rows}, 'rows in nibble', $self->{nibbleno});
}

if ( $self->{have_rows} ) {
my $row = $self->{nibble_sth}->fetchrow_arrayref();
if ( $row ) {
$self->{rowno}++;
PTDEBUG && _d('Row', $self->{rowno}, 'in nibble',$self->{nibbleno});
return [ @$row ];
}
}

PTDEBUG && _d('No rows in nibble or nibble skipped');
if ( my $callback = $self->{callbacks}->{after_nibble} ) {
$callback->(%callback_args);
}
$self->{rowno} = 0;
$self->{have_rows} = 0;

# =============================================
while(-f "pause_please.txt") {
print "Im sleeping\n";
my $dbh = $self->{Cxn}->dbh();
if ( !$dbh || !$dbh->ping() ) {
eval { $dbh = $self->{Cxn}->connect() }; # connect or die trying
if ( $EVAL_ERROR ) {
#$oktorun = 0; # flag for cleanup tasks
chomp $EVAL_ERROR;
die "Lost connection to " . $self->{Cxn}->name() . " while waiting for "
. "replica lag ($EVAL_ERROR)\n";
}
}
$dbh->do("SELECT 'pt-online-schema-change keepalive'");
sleep(60);
}
# =============================================


}

PTDEBUG && _d('Done nibbling');
if ( my $callback = $self->{callbacks}->{done} ) {
$callback->(%callback_args);
}

Comments

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.