Restore Slave

• Tweet
• 

        // shell script to restore slave data from backup (zipped) file. Start slave from the position mentioned in the dump.

#!/bin/sh

echo -n "Have you checked the disk space and my.cnf file? (yes or no) : "

read myresponse
myresponse="$(echo ${myresponse} | tr 'A-Z' 'a-z')"
if [[ "$myresponse" != "yes" ]]; then

echo "Check disk space "
echo " "
echo "### make sure that the general, binary and slow logs of slave are NOT enabled ###"
echo " "

exit 99
else

#declare variables
user='root'
password='root@123'
location='/home/application/'
filename='backup_full_101.sql.zip'
masterhost='10.10.10.10'
masteruser='slave_user'
masterpassword='slave_passwd'

#comments

# drop current databases from slave
time mysqlshow -u$user -p$password |  egrep -v 'information_schema|performance_schema|mysql|Databases' | awk '{print "mysql -u'"$user"' -p'"$password"' -Bse \"drop database", $2, "\""}' | sh


# create dumper file to change definers
cat > dumper.pl << "heredoc"
#!/usr/bin/perl
use strict;
use warnings;
use Getopt::Long qw(:config no_ignore_case );

my $replace = undef;
my $delete  = undef;
my $help    = 0;

GetOptions (
   'replace|r=s' => \$replace,
   'delete|d'    => \$delete,
   'help|h'      => \$help,
) or help('unrecognized options');

help() if $help;

if ($delete and $replace) {
    help( 'you must choose EITHER "delete" OR "replace". Not both');
}

$delete = 1 unless $replace;

while (my $line = <STDIN>) {
    if ($delete) {
        $line =~ s{(/\*!\d+ )\s*definer\s*=\s*\S+}{$1}i;
    }
    elsif ($replace) {
        $line =~ s{(/\*!\d+\s+definer\s*=\s*)(\S+)}{$1 $replace}i;
    } 
    print $line;
}

sub help {
    my ($msg) = @_;
    if ($msg) {
        print "*** $msg\n";
    }
    print "dump_filter - mysqldump filter \n",
          "removes/changes DEFINER clauses from MySQL dumps\n",
          "USAGE: dump_filter [options]\n",
          "    -d|--delete    removes the DEFINER clauses\n",
          "    -r|--replace=s replaces every DEFINER clause with the \n",
          "                   new value provided\n",
          "    -h|--help      This text\n";
    exit(1);
}

heredoc

# set up backup file called today.sql
cd $location
unzip -o $filename
mv -f \- today1.sql
cat today1.sql | perl dumper.pl --replace='root@`localhost`' > today2.sql

echo "set long_query_time=100000;" > header.sql

cat header.sql today2.sql > today.sql

# restore data from today.sql
time mysql -u$user -p$password -f < today.sql

# find and change the log position
# find the log position
myslave=`head -40 today.sql | grep 'CHANGE MASTER TO ' | sed "s/CHANGE MASTER TO/CHANGE MASTER TO MASTER_HOST='"$masterhost"', MASTER_USER='"$masteruser"', MASTER_PASSWORD='"$masterpassword"', /g" | sed 's/^--//g'`

# start slave from the log position found in the backup file
mysql -u$user -p$password  -e"stop slave;"
mysql -u$user -p$password  -e"reset slave;"

mysql -u$user -p$password  -e"$myslave"

mysql -u$user -p$password  -e"start slave;"
mysql -u$user -p$password  -e"show slave status\G"

# remove temporary intermediate file
> today1.sql

fi

Tags:
• forge
• MySQL
• oksoft
• shell_script