Attachment 'init_mysql_slave.pl'

Download

#!/usr/bin/perl
#
###
#
# init_mysql_slave - initialise a new slave with the replication databases
# Copyright (C) 2008 Anchor Systems - http://www.anchor.com.au/
#
# 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 the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA 02111-1307, USA.
#
###

# Creates a new MySQL replication slave from the master or another slave.
# Note that the username and password provided for the remote source
# are expected to have the following privileges:
# SUPER, SELECT, LOCK TABLES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE
#
#
# The local database is connected to as the root user with options from
# /root/.my.cnf
#
# Usage: see the contents of the usage() subroutine.

use strict;
use warnings;

use DBI;
use Getopt::Long;
use Switch '__';        # Allow case statements. Bonus points for anime smiley.

sub usage {
        print <<USAGE
Usage:
$0 --source HOST --sourcetype [slave|master] --user USERNAME --pass PASSWORD
                 --database DATABASE(,DATABASE,...) --repluser REPLUSERNAME
                 --replpass REPLPASSWORD --replsource REPLHOST

--source HOST                        Specify the source of the data by hostname or IP
--sourcetype [slave|master]          Specify that the source is a master or a slave
--user USERNAME                      Username to be used with the remote source
--pass PASSWORD                      Password to be used with the remote source
--database DATABASE(,DATABASE,...)   Name of the database to be synchronised
--repluser REPLUSERNAME              Username to be used for local replication configuration
--replpass REPLPASSWORD              Password to be used for local replication configuration
--replsource REPLHOST                Host to be used with local replication configuration
                                     after the initialisation is performed

The --database option can either be specified multiple times, or with a
comma-separated list of database names to copy multiple databases from
the source.

USAGE
}

sub debug (@) {
  print @_ if -t STDOUT;
}

sub connect_db {
        eval {
                # Connect the local database
                $main::localdbh = DBI->connect($main::localdsn, "", "", {'RaiseError' => 1});

                # Connect the remote database
                debug "CONNECT $main::remotedsn\n";
                debug "USERNAME $main::user\n";
                debug "PASSWORD $main::pass\n";
                $main::remotedbh = DBI->connect($main::remotedsn, $main::user, $main::pass, {'RaiseError' => 1});
        };
        if ($@) {
                warn $@;
                cleanup(1);
        };
}

sub setup_local {
        # Disable the local slave thread to prevent existing replication settings interfering
        eval {
                debug "STOP SLAVE\n";
                $main::localdbh->do("STOP SLAVE");
        };
        if ($@) {
                warn $@;
                cleanup(2);
        };
}

sub conclude_local {
        # Start the local slave thread again
        debug "START SLAVE\n";
        $main::localdbh->do("START SLAVE")
                or warn "Error restarting the slave thread: ", $DBI::errstr;
}

sub setup_remote {
        # Flush the tables and lock
        debug "FLUSH TABLES WITH READ LOCK\n";
        eval {
                $main::remotedbh->do("FLUSH TABLES WITH READ LOCK");
        };
        if ($@) {
                warn $@;
                cleanup(3);
        };

        # Check what the source is
        if ($main::sourcetype eq "slave") {
                my $slavesth;
                eval {
                        debug "SHOW SLAVE STATUS\n";
                        $slavesth = $main::remotedbh->prepare("SHOW SLAVE STATUS");
                        $slavesth->execute;
                };
                if ($@) {
                        warn $@;
                        cleanup(3);
                };

                # There should be one row of show slave status data, no more, no less           
                my $slaverows = $slavesth->rows;
                if ($slaverows != 1) {
                        warn "--sourcetype slave specified but this source doesn't have any slave configuration. Exiting.\n";
                        $slavesth->finish;
                        cleanup(3);
                }
                else {
                        # Grab the data row as a hash
                        if (!(defined(my $hash_ref = $slavesth->fetchrow_hashref))) {
                                warn "Error retrieving slave status data: ", $DBI::errstr;
                                $slavesth->finish;
                                cleanup(3);
                        }
                        else {
                                $main::binlogname = $$hash_ref{'Relay_Master_Log_File'};
                                $main::binlogpos = $$hash_ref{'Exec_Master_Log_Pos'};
                        }
                }
                $slavesth->finish;
        }
        elsif ($main::sourcetype eq "master") {
                my $mastersth;
                eval {
                        debug "SHOW MASTER STATUS\n";
                        $mastersth = $main::remotedbh->prepare("SHOW MASTER STATUS");
                        $mastersth->execute;
                };
                if ($@) {
                        warn $@;
                        cleanup(3);
                };

                # There should be one row of show master status data, no more, no less
                my $masterrows = $mastersth->rows;
                if ($masterrows != 1) {
                        warn "--sourcetype master specified but this source doesn't have any master configuration. Exiting.\n";
                        $mastersth->finish;
                        cleanup(3);
                }
                else {
                        # Grab the data row as a hash
                        if (!(defined(my $hash_ref = $mastersth->fetchrow_hashref))) {
                                warn "Error retrieving master status data: ", $DBI::errstr;
                                $mastersth->finish;
                                cleanup(3);
                        }
                        else {
                                $main::binlogname = $$hash_ref{'File'};
                                $main::binlogpos = $$hash_ref{'Position'};
                        }
                }
                $mastersth->finish;
        }
        else {
                # This should be unreachable
                debug "Source is not master or slave... the universe must be collapsing.\n";
                cleanup(3);
        }

        # Debug out the binlog information we have gathered
        debug "binlogname $main::binlogname\n";
        debug "binlogpos $main::binlogpos\n";
}

sub copy_data {
        # perform a mysqldump on the remote, piping it into the local mysql directly
        eval {
                debug "/usr/bin/mysqldump -B -C --add-drop-database --opt --skip-disable-keys --skip-lock-tables -h $main::source -u $main::user -p$main::pass --databases " . join(' ', @main::database) . " | /usr/bin/mysql\n";
                system("/usr/bin/mysqldump -B -C --add-drop-database --opt --skip-disable-keys --skip-lock-tables -h $main::source -u $main::user -p$main::pass --databases " . join(' ', @main::database) . " | /usr/bin/mysql");
        };
        if ($@) {
                warn $@;
                cleanup(3);
        };
}

sub start_localrepl {
        # Change local replication parameters
        eval {
                debug "CHANGE MASTER TO MASTER_HOST=\'$main::replsource\',MASTER_USER=\'$main::repluser\',MASTER_PASSWORD=\'$main::replpass\',MASTER_LOG_FILE=\'$main::binlogname\',MASTER_LOG_POS=$main::binlogpos\n";
                $main::localdbh->do("CHANGE MASTER TO MASTER_HOST=\'$main::replsource\',MASTER_USER=\'$main::repluser\',MASTER_PASSWORD=\'$main::replpass\',MASTER_LOG_FILE=\'$main::binlogname\',MASTER_LOG_POS=$main::binlogpos");
        };
        if ($@) {
                warn $@;
                cleanup(3);
        };
};

sub conclude_remote {
        # unlock tables
        debug "UNLOCK TABLES\n";
        $main::remotedbh->do("UNLOCK TABLES")
                or warn "Error unlocking remote tables: ", $DBI::errstr;
}

sub disconnect_db {
        # Disconnect the database connections
        if (defined($main::localdbh)) { $main::localdbh->disconnect() or warn "Error disconnecting local database: ", $DBI::errstr; };
        if (defined($main::remotedbh)) { $main::remotedbh->disconnect() or warn "Error disconnecting remote database: ", $DBI::errstr; };
}

sub cleanup {
        # Cleanup actions. Cascaded depending on point of failure.
        switch (shift) {
                case __ > 2 { conclude_remote(); next; }
                case __ > 1 { conclude_local(); next; }
                case __ > 0 { disconnect_db(); }
        }
        exit 1;
}

# Get command line options
our $source;
our $sourcetype;
our $user;
our $pass;
our $repluser;
our $replpass;
our $replsource;
our @database;
if (!GetOptions('source=s' => \$source,
                'sourcetype=s' => \$sourcetype,
                'user=s' => \$user,
                'pass=s' => \$pass,
                'repluser=s' => \$repluser,
                'replpass=s' => \$replpass,
                'replsource=s' => \$replsource,
                'database=s' => \@database
                )) {
        usage();
        exit 1;
}

# Check the parameters make sense
if (!(defined($source) && defined($sourcetype) && defined($user) && defined($pass) && defined($repluser) && defined($replpass) && defined($replsource) && defined(@database))) {
        usage();
        exit 1;
} elsif (!($sourcetype eq "master" || $sourcetype eq "slave") ) {
        usage();
        exit 1;
}

# Turn the list of databases into usable format
@database = split(/,/,join(',',@database));

# Set up the DSNs to the databases. Local database connects as root using default options.
our $localdsn = "DBI:mysql:$database[0];mysql_read_default_file=/root/.my.cnf;mysql_connect_timeout=5";
our $remotedsn = "DBI:mysql:$database[0];host=$source;mysql_connect_timeout=5";
our $localdbh;
our $remotedbh;

# Variables for storing the master binlog name and position
our $binlogname;
our $binlogpos;

# Make the connections
connect_db();

# Setup the local server
setup_local();

# Setup the remote server
setup_remote();

# Dump the data on remote, enter it into the local
copy_data();

# Return remote server to normal operation
conclude_remote();

# Set up local replication parameters
start_localrepl();

# Start up local server slave threads
conclude_local();

# Disconnect from both databases
disconnect_db();

exit 0;

Attached Files

To refer to attachments on a page, use attachment:filename, as shown below in the list of files. Do NOT use the URL of the [get] link, since this is subject to change and can break easily.
  • [get | view] (2013-05-31 11:14:57, 8.0 KB) [[attachment:IPaddr2_vlan.sh]]
  • [get | view] (2013-05-31 11:14:58, 361.3 KB) [[attachment:LCA2008-talk.odp]]
  • [get | view] (2013-05-31 11:14:58, 832.2 KB) [[attachment:LCA2008-talk.pdf]]
  • [get | view] (2013-05-31 11:14:57, 3.7 KB) [[attachment:alter_mysql_slave]]
  • [get | view] (2013-05-31 11:14:57, 3.7 KB) [[attachment:alter_mysql_slave.sh]]
  • [get | view] (2013-05-31 11:14:57, 1.5 KB) [[attachment:authkeys]]
  • [get | view] (2013-05-31 11:14:57, 1.5 KB) [[attachment:authkeys.txt]]
  • [get | view] (2013-05-31 11:14:58, 1.1 KB) [[attachment:check-drbd.sh]]
  • [get | view] (2013-05-31 11:14:57, 11.4 KB) [[attachment:check_replication.pl]]
  • [get | view] (2013-05-31 11:14:57, 6.1 KB) [[attachment:cib.xml.template]]
  • [get | view] (2013-05-31 11:14:58, 8.0 KB) [[attachment:db-export-mysqlstandby.pl]]
  • [get | view] (2013-05-31 11:14:57, 3.8 KB) [[attachment:drbd.conf.txt]]
  • [get | view] (2013-05-31 11:14:58, 1.3 KB) [[attachment:drbddisk.sh]]
  • [get | view] (2013-05-31 11:14:57, 1.5 KB) [[attachment:ha.cf.txt]]
  • [get | view] (2013-05-31 11:14:58, 9.0 KB) [[attachment:init_mysql_slave.pl]]
  • [get | view] (2013-05-31 11:14:58, 1.2 KB) [[attachment:logd.cf.txt]]
  • [get | view] (2013-05-31 11:14:58, 2.2 KB) [[attachment:lvs-helper.sh]]
  • [get | view] (2013-05-31 11:14:57, 1.8 KB) [[attachment:my.cnf.txt]]
  • [get | view] (2013-05-31 11:14:57, 2.2 KB) [[attachment:myslave.cnf.txt]]
  • [get | view] (2013-05-31 11:14:57, 5.5 KB) [[attachment:mysqld.sh]]
  • [get | view] (2013-05-31 11:14:58, 2.0 KB) [[attachment:mysqlslave.cnf.txt]]
  • [get | view] (2013-05-31 11:14:57, 5.7 KB) [[attachment:mysqlslaved.sh]]
  • [get | view] (2013-05-31 11:14:57, 3.5 KB) [[attachment:write_heartbeat.pl]]

You are not allowed to attach a file to this page.