Documentation Home
MySQL 9.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
Man Pages (TGZ) - 259.0Kb
Man Pages (Zip) - 366.2Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


MySQL 9.0 Reference Manual  /  ...  /  NDB Cluster Replication: Automating Synchronization of the Replica to the Source Binary Log

25.7.9.1 NDB Cluster Replication: Automating Synchronization of the Replica to the Source Binary Log

It is possible to automate much of the process described in the previous section (see Section 25.7.9, “NDB Cluster Backups With NDB Cluster Replication”). The following Perl script reset-replica.pl serves as an example of how you can do this.

Press CTRL+C to copy
#!/user/bin/perl -w # file: reset-replica.pl # Copyright (c) 2005, 2020, Oracle and/or its affiliates. All rights reserved. # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # You should have received a copy of the GNU General Public License # along with this program; if not, write to: # Free Software Foundation, Inc. # 59 Temple Place, Suite 330 # Boston, MA 02111-1307 USA # # Version 1.1 ######################## Includes ############################### use DBI; ######################## Globals ################################ my $m_host=''; my $m_port=''; my $m_user=''; my $m_pass=''; my $s_host=''; my $s_port=''; my $s_user=''; my $s_pass=''; my $dbhM=''; my $dbhS=''; ####################### Sub Prototypes ########################## sub CollectCommandPromptInfo; sub ConnectToDatabases; sub DisconnectFromDatabases; sub GetReplicaEpoch; sub GetSourceInfo; sub UpdateReplica; ######################## Program Main ########################### CollectCommandPromptInfo; ConnectToDatabases; GetReplicaEpoch; GetSourceInfo; UpdateReplica; DisconnectFromDatabases; ################## Collect Command Prompt Info ################## sub CollectCommandPromptInfo { ### Check that user has supplied correct number of command line args die "Usage:\n reset-replica >source MySQL host< >source MySQL port< \n >source user< >source pass< >replica MySQL host< \n >replica MySQL port< >replica user< >replica pass< \n All 8 arguments must be passed. Use BLANK for NULL passwords\n" unless @ARGV == 8; $m_host = $ARGV[0]; $m_port = $ARGV[1]; $m_user = $ARGV[2]; $m_pass = $ARGV[3]; $s_host = $ARGV[4]; $s_port = $ARGV[5]; $s_user = $ARGV[6]; $s_pass = $ARGV[7]; if ($m_pass eq "BLANK") { $m_pass = '';} if ($s_pass eq "BLANK") { $s_pass = '';} } ############### Make connections to both databases ############# sub ConnectToDatabases { ### Connect to both source and replica cluster databases ### Connect to source $dbhM = DBI->connect( "dbi:mysql:database=mysql;host=$m_host;port=$m_port", "$m_user", "$m_pass") or die "Can't connect to source cluster MySQL process! Error: $DBI::errstr\n"; ### Connect to replica $dbhS = DBI->connect( "dbi:mysql:database=mysql;host=$s_host", "$s_user", "$s_pass") or die "Can't connect to replica cluster MySQL process! Error: $DBI::errstr\n"; } ################ Disconnect from both databases ################ sub DisconnectFromDatabases { ### Disconnect from source $dbhM->disconnect or warn " Disconnection failed: $DBI::errstr\n"; ### Disconnect from replica $dbhS->disconnect or warn " Disconnection failed: $DBI::errstr\n"; } ###################### Find the last good GCI ################## sub GetReplicaEpoch { $sth = $dbhS->prepare("SELECT MAX(epoch) FROM mysql.ndb_apply_status;") or die "Error while preparing to select epoch from replica: ", $dbhS->errstr; $sth->execute or die "Selecting epoch from replica error: ", $sth->errstr; $sth->bind_col (1, \$epoch); $sth->fetch; print "\tReplica epoch = $epoch\n"; $sth->finish; } ####### Find the position of the last GCI in the binary log ######## sub GetSourceInfo { $sth = $dbhM->prepare("SELECT SUBSTRING_INDEX(File, '/', -1), Position FROM mysql.ndb_binlog_index WHERE epoch > $epoch ORDER BY epoch ASC LIMIT 1;") or die "Prepare to select from source error: ", $dbhM->errstr; $sth->execute or die "Selecting from source error: ", $sth->errstr; $sth->bind_col (1, \$binlog); $sth->bind_col (2, \$binpos); $sth->fetch; print "\tSource binary log file = $binlog\n"; print "\tSource binary log position = $binpos\n"; $sth->finish; } ########## Set the replica to process from that location ######### sub UpdateReplica { $sth = $dbhS->prepare("CHANGE REPLICATION SOURCE TO SOURCE_LOG_FILE='$binlog', SOURCE_LOG_POS=$binpos;") or die "Prepare to CHANGE REPLICATION SOURCE error: ", $dbhS->errstr; $sth->execute or die "CHANGE REPLICATION SOURCE on replica error: ", $sth->errstr; $sth->finish; print "\tReplica has been updated. You may now start the replica.\n"; } # end reset-replica.pl