DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Copy Data With Suffix Or Affix

10.22.2011
| 3250 views |
  • submit to reddit
        // Script to copy the db_name1... db_name4 to destination host 11 from source host 10 with affix or suffix. for e.g. the source schema name "application" can be "prod_application" on the destination host. call the script after making necessary changes to source and destination credentials and check the exit code. The script can also be used to copy data on the same server or from one port to another. 

// # time sh -xv copy_data.sh

// The exit code should be 0 

#!/bin/sh
source_host='source_host_ip'
source_user='root'
source_pass='root@321'
source_port='3306'

dest_host='dest_host_ip'
dest_user='root'
dest_pass='root@123'
dest_port='3306'
dest_afix='start_'
dest_sfix='_end'

replace_string='M2QA'
replace_with='X2QA'

# change only the variables mentioned above and the schema names at the end of the script

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


while read db_name
do

new_db_name=`echo "$db_name" | sed "s/$replace_string/$replace_with/"`
new_db_name="$dest_afix$new_db_name$dest_sfix"

char_set=`mysql -h$source_host -u$source_user -p$source_pass -P$source_port -Bse"select DEFAULT_CHARACTER_SET_NAME from information_schema.SCHEMATA WHERE SCHEMA_NAME = \"$db_name\";"`
if [[ $? -ne 0 ]]; then exit 81; fi

collate=`mysql -h$source_host -u$source_user -p$source_pass -P$source_port -Bse"select DEFAULT_COLLATION_NAME from information_schema.SCHEMATA WHERE SCHEMA_NAME = \"$db_name\";"`
if [[ $? -ne 0 ]]; then exit 91; fi

mysql -h$dest_host -u$dest_user -p$dest_pass -P$dest_port -e" drop DATABASE IF EXISTS $new_db_name ;"
if [[ $? -ne 0 ]]; then exit 111; fi

mysql -h$dest_host -u$dest_user -p$dest_pass -P$dest_port -e" CREATE DATABASE IF NOT EXISTS $new_db_name DEFAULT CHARACTER SET $char_set collate $collate ;"
if [[ $? -ne 0 ]]; then exit 121; fi

# add -d switch next to -R to copy only structure and not data 
mysqldump -h$source_host -u$source_user -p$source_pass -P$source_port -R $db_name | perl dumper.pl --replace='root@`localhost`' |  mysql -f -h$dest_host -u$dest_user -p$dest_pass -P$dest_port $new_db_name

if [[ $? -ne 0 ]]; then exit 131; fi

done << db_heredoc_list
db1
db2
db3
db4
db5
db_heredoc_list

# add the database above in the heredoc section