extractingindividual table or schema from all backup file in mysql

vi restore_indivudual_table.sh

#!/bin/bash
file=`pwd`
FILE="$file/input.log"
#FILE=/tmp/check/script/input.log
if [ -f $FILE ];
then
   echo "File $FILE exists"
   rm -rf $FILE
else
   echo "File $FILE does not exists"
fi
# Check if there is input to this script
if [ $# -lt 1 ]; then # $# contains the total number of arguments to the script
  echo "Usage: $0 [argument1 2 3...] are mentioned below " # $0 is the executed command
  echo -e " \t\t\t $0 -f <file name> -s <schema> -t <optinally tablename if require>"
  exit 1
fi
while getopts f:hb:s:t: opt
do
   case "$opt" in
      f) profile=$OPTARG
         echo $cell;;
      h) usage;;
      s) info=$OPTARG ;;
      t) table=$OPTARG ;;
      \?) usage;;
   esac
done
rm ${info}.sql ${table}.sql
echo "Profile ------------> $profile ============== INFO----------$info"
grep -n 'CREATE DATABASE' $profile > input.log
val=`cat input.log|grep $info|cut -f1 -d ":"`
initial_value=$val
echo "The initial Value after Regex is $initial_value"
value=`cat input.log|sed -n "/${info}/{n;p;}"|cut -f1 -d ":"`
final_value=`expr $value - 1`
echo "The next Value after Regex is $final_value"
head -18 $profile > ${info}.sql
sed -n "${initial_value},${final_value}p" $profile >> ${info}.sql
tail -11 $profile >> ${info}.sql

if [ -z "${table}" ]
then
echo table is not set at all
else
echo "${table} is for creating table"
grep -n "DROP TABLE" ${info}.sql > table.log
tval=`cat table.log|grep -w $table|cut -f1 -d ":"`
initial_tvalue=$tval
echo "The initial Value after Regex is $initial_tvalue"

#tvalue=`cat table.log|sed -n "/${table}/{n;p;}"|cut -f1 -d ":"`
tvalue=`cat table.log|sed -n "/\<${table}\>/{n;p;}"|cut -f1 -d ":"`
final_tvalue=`expr $tvalue - 1`
echo "The next Value after Regex is $final_tvalue"
sed -n "${initial_tvalue},${final_tvalue}p" ${info}.sql >> ${table}.sql
fi
rm input.log


###steps for execution the above scripts ######
####for extracting individual schema
sh restore_indivudual_table.sh -f /home/user1/backup1.sql -s testschema
######for extracting individual table#######
sh restore_indivudual_table.sh -f /home/user1/backup1.sql -s testschema -t table1

Change the huge table ( alter table ) with zero down time

Percona Toolskit is the best tool for the archiving this
1. steps for installing
wget http://www.percona.com/redir/downloads/percona-toolkit/2.1.1/percona-toolkit-2.1.1-1.noarch.rpm
2. rpm -ivh percona-toolkit-2.1.1-1.noarch.rpm
3 sometimes the dependencies for downloading are as follows:
wget http://www.percona.com/redir/downloads/percona-toolkit/2.1.1/percona-toolkit-2.1.1-1.noarch.rpm
wget ftp://mirror.switch.ch/pool/1/mirror/scientificlinux/5rolling/x86_64/SL/perl-TermReadKey-2.30-5.el5.x86_64.rpm
wget ftp://mirror.switch.ch/pool/2/mirror/fedora/linux/updates/15/x86_64/perl-DBD-MySQL-4.018-7.fc15.x86_64.rpm

NOTE: PARTITONS WILL NOT WORK IF WE HAVE FOREIGN KEY CONSTRAINTS
3. some of the examples are as follows
### just adding the column
pt-online-schema-change --alter="add column stat1 tinyint "  D=ledger,t=account_balance  --alter-foreign-keys-method='auto'--execute
### justing adding the two column and one column having foreign key
pt-online-schema-change --alter="add column operation_test4 varchar(50),add foreign key xyz4(operation_test4) references operation(type), add status5 varchar(10) default 'yes'"  D=wallet,t=transaction  --alter-foreign-keys-method='drop_swap' --execute
### Altering the ordinary table with partitions
pt-online-schema-change --alter="PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (1000000),PARTITION p1 VALUES LESS THAN  (5000000),PARTITION p2 VALUES LESS THAN  (10000000),PARTITION p3 VALUES LESS THAN  (50000000),PARTITION p4 VALUES LESS THAN MAXVALUE)" D=wallet,t=transaction_test2 --execute
### this is for prompting password
pt-online-schema-change --alter="PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (1000000),PARTITION p1 VALUES LESS THAN  (5000000),PARTITION p2 VALUES LESS THAN  (10000000),PARTITION p3 VALUES LESS THAN  (50000000),PARTITION p4 VALUES LESS THAN MAXVALUE)" -u kiran --ask-pass ,D=wallet,t=transaction_test2 --execute
### this is for hostname
pt-online-schema-change --alter="PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (1000000),PARTITION p1 VALUES LESS THAN  (5000000),PARTITION p2 VALUES LESS THAN  (10000000),PARTITION p3 VALUES LESS THAN  (50000000),PARTITION p4 VALUES LESS THAN MAXVALUE)" -u kiran -h 10.10.8.141 --ask-pass ,D=wallet,t=transaction_test2 --execute
### this is for username and password
NOTE: PARTITONS WILL NOT WORK IF WE HAVE FOREIGN KEY CONSTRAINTS FOR THE TABLE
pt-online-schema-change --alter="PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (1000000),PARTITION p1 VALUES LESS THAN  (5000000),PARTITION p2 VALUES LESS THAN  (10000000),PARTITION p3 VALUES LESS THAN  (50000000),PARTITION p4 VALUES LESS THAN MAXVALUE)" -u kiran -h 10.10.8.141 -p kiran@123 ,D=wallet,t=transaction_test2 --execute

The disadvanges of this tools is that if we have the trigger already then this tool does not work
The limitations are as follows:
pt-online-schema-change --alter="add column stat1 tinyint "  D=ledger,t=ppacounts_data  --alter-foreign-keys-method='auto'
The table `ledger`.`account_balance` has triggers.  This tool needs to create its own triggers, so the table cannot already have triggers.
[root@sniper kiran.chinta]#  pt-online-schema-change --alter="add column stat1 tinyint "  D=veles,t=ppacounts_data  --alter-foreign-keys-method='auto'
The table `veles`.`ppacounts_data` has triggers.  This tool needs to create its own triggers, so the table cannot already have triggers.
 pt-online-schema-change --alter="add column stat1 tinyint "  D=veles,t=transaction_log  --alter-foreign-keys-method='auto'
The table `veles`.`transaction_log` has triggers.  This tool needs to create its own triggers, so the table cannot already have triggers.

For more information about the tools
http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html