Masking the database from proddb and removing the definer clause after taking up and changing the DB Names

Masking the database from proddb and removing the definer clause after taking up and changing the DB Names

1.) Massking the database
2.) removing the definer clause
3.) Changing the DBname in the backup file

vi masking.sh

#!/bin/sh
mydate=`date '+%b%d'`
prefix="prd_"
user='kiran'
password='kiran'
port='3306'
# run the masking commands on proddb1
# take the backup, remove definer

mysql -u$user -p$password proddb1 -P$port << proddb1
set foreign_key_checks =0;
select max(id) into @cardid from table1 ;
select max(id)  into @userid from user ;
set @cardid = @cardid + 100 ;
set @userid = @userid + 100 ;
update transaction_log   set Card_ID = Card_ID + @cardid, Second_Card_ID   = Second_Card_ID   + @cardid;                                            
update transaction_log_details  set Card_ID = Card_ID + @cardid;                                       
update transaction_summary set Card_ID = Card_ID + @cardid, xyz =reverse(xyz);                                            
 
### run the update commands you want to update
proddb1
mysql -u$user -p$password proddb2 -P$port << proddb2
set foreign_key_checks =0;
select max(id) into @accountid from table1 ;
set @accountid = @accountid + 200 ;
update table1 set id= id + @accountid ;
update table2 set account = account + @accountid;     
update transaction set masked_number='XXXX';
update transaction_log set masked_number='XXXX';
##run the update command here
proddb2
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",
          "(C) Giuseppe Maxia, 2009\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
# backup the DB
mysqldump -u$user -p$password --databases proddb1 proddb2  --routines --single-transaction | perl dumper.pl --delete > all_backup.sql
# "sed" is used to replace the current DB name with "prd_" or any other word.
sed -i '/^CREATE DATABASE\|^USE/{
s/proddb1/'$prefix'proddb1/;
s/proddb2/'$prefix'proddb2/}' all_backup.sql
grep '^CREATE DATABASE' all_backup.sql | head -10

# all_backup.sql file from the current folder can be safely send to development Team

### if we want to replace the definer then we have to replace like this

 perl /home/dumper.pl mailto:--replace='root@%60localhost%60'