taking mysqlbackup using LVM snap shots

for taking mysqlbackup using lvm snapshots:

 we need to follow 5 steps are follows.

1. connect to mysql and give the command in mysql "flush table with read lock" so that all the connections are temporarly halts
 FLUSH LOGS;
 show slave status\G
 show master status\G
 2.) create the LVM snapshots
 3.) mounting the LVM snapshots
 4.) unmounting the lvm snapshots
 5.)removing the snap shots

The detail steps are as follows:

###########step 1 and 2 ###############
/usr/bin/mysql -uroot -proot  <<SQL_EOF
FLUSH TABLES WITH READ LOCK;
FLUSH LOGS;
show slave status\G
show master status\G
SYSTEM /usr/sbin/lvcreate --snapshot --size=5G  --name=backup-test /dev/volumegroup/fs0/
UNLOCK TABLES;
SQL_EOF
##########step 3 #################
#first create the folder
mkdir /var/tmp/mysql-data1
#mouting the snap shot to temp folder we have created above
/bin/mount -o ro /dev/volumegroup/backup-test /var/tmp/mysql-data1
########step 4  unmounting the snap shots ##############
/bin/umount /var/tmp/mysql-data1

##### step 5  removing the snap shots  ################
/usr/sbin/lvremove -f /dev/volumegroup/backup-test

############for more details the url is as follows
https://raw.github.com/nmilford/scripts/master/MySQL/makeMysqlSnapshot.sh
### to know more about LVM snapshot
http://www.howtoforge.com/linux_lvm

Taking dump using mysql Enterprise backup tool (MEB)

####For taking the full backup (we need to create the directory /root/backup for storing the backup files).

bin/mysqlbackup --user=root --password=xxxx --port=3306 --with-timestamp --backup-dir=/root/backup backup

####For taking the first incremental backup  after taking full backup(need to give the path of full backup directory 2012-11-14_01-21-15)

bin/mysqlbackup --user=root --password=xxxx --incremental --incremental-base=dir:/root/backup/2012-11-14_01-21-15  --with-timestamp --incremental-backup-dir=/root/backup backup

#### For taking the second incremental backup  after taking full backup(need to give the path of previous incremental backup directory 2012-11-14_01-21-30)

./mysqlbackup -u root --with-timestamp --incremental --incremental-base=dir:/root/backup/2012-11-15_11-18-20 --incremental-backup-dir=/root/backup backup

#### /mysqlbackup -u root --with-timestamp --incremental --incremental-base=dir:/root/backup/2012-11-15_11-30-48 --incremental-backup-dir=/root/backup backup

For restoring the backup

first stop the mysql and remove the mysql data directory

The commands are as follows:

restoring the full backup-file

### first applying the log

/root/meb/meb-3.8.0-rhel4-x86-32bit/bin/mysqlbackup --backup-dir=/root/backup/2012-11-15_10-49-10 apply-log

## restoring the full backup

/root/meb/meb-3.8.0-rhel4-x86-32bit/bin/mysqlbackup --defaults-file=/home/mysql/instance/inst3306/conf_old/my.cnf --backup-dir=/root/backup/2012-11-15_10-49-10 --datadir=/home/mysql/instance/inst3306/data copy-back

#appending the incremental backups to the full backups

/root/meb/meb-3.8.0-rhel4-x86-32bit/bin/mysqlbackup --incremental-backup-dir=/root/backup/2012-11-15_11-35-25 --backup-dir=/root/backup/2012-11-15_10-49-10 apply-incremental-backup

/root/meb/meb-3.8.0-rhel4-x86-32bit/bin/mysqlbackup --incremental-backup-dir=/root/backup/2012-11-15_11-30-48 --backup-dir=/root/backup/2012-11-15_10-49-10 apply-incremental-backup

/root/meb/meb-3.8.0-rhel4-x86-32bit/bin/mysqlbackup --incremental-backup-dir=/root/backup/2012-11-15_11-18-20 --backup-dir=/root/backup/2012-11-15_10-49-10 apply-incremental-backup

For more information for the Mysql enterprise backup the url is as follows:

http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/mysqlbackup.html

http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/backup-subcommands-apply-log.html

http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/backup-subcommands-backup.html#option_meb_backup-and-apply-log

user defined exceptions using signal command

drop table if exists emp;
create table emp (empno int, sal decimal, ename varchar(100));

insert into emp values (1,1000,'A');
insert into emp values (2,2000,'B');
insert into emp values (3,3000,'C');
insert into emp values (4,4000,'D');

DELIMITER $$
drop trigger if exists  maintain_min_sal$$
CREATE  TRIGGER `maintain_min_sal` BEFORE UPDATE ON `emp`
    FOR EACH ROW
   BEGIN
    DECLARE zero_multiply CONDITION FOR SQLSTATE '45000';
    -- DECLARE EXIT HANDLER FOR zero_multiply SET @error = 'salary cannot be decremented';
    IF NEW.sal < OLD.sal THEN
  signal zero_multiply SET message_text = 'salary cannot be decremented';
 END IF;
END$$
DELIMITER ;

mysql> update emp set sal=300 where empno=1;
ERROR 1644 (45000): salary cannot be decremented

query to find alll the the duplicate records

## the table syntax is as follows:
mysql> show create table house \G
*************************** 1. row ***************************
       Table: house
Create Table: CREATE TABLE `house` (
  `houseno` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `tin_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`tin_id`),
  KEY `idx_name` (`name`),
  KEY `idx_name1` (`name`,`houseno`),
  KEY `idx_house2` (`houseno`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from house;
+---------+------+--------+
| houseno | name | tin_id |
+---------+------+--------+
|       1 | a    |      2 |
|       1 | a    |      4 |
|       2 | a    |      6 |
|       3 | b    |      8 |
|       3 | c    |     22 |
|       3 | c    |     24 |
|       4 | c    |     10 |
|       4 | c    |     12 |
|       4 | c    |     14 |
|       4 | c    |     16 |
|       4 | c    |     18 |
|       4 | c    |     20 |
+---------+------+--------+
12 rows in set (0.00 sec)
mysql> select max(tin_id),min(tin_id),houseno,name from house group by houseno,name having count(*)>
1;
+-------------+-------------+---------+------+
| max(tin_id) | min(tin_id) | houseno | name |
+-------------+-------------+---------+------+
|           4 |           2 |       1 | a    |
|          24 |          22 |       3 | c    |
|          20 |          10 |       4 | c    |
+-------------+-------------+---------+------+
3 rows in set (0.00 sec)

## if we want only tin_id the query is as follows:
SELECT h1.tin_id FROM house h1 WHERE EXISTS (SELECT NULL FROM house h2 WHERE h1.houseno = h2.houseno AND h1.name = h2.name AND h2.tin_id <> h1.tin_id) ;
+--------+
| tin_id |
+--------+
|      2 |
|      4 |
|     22 |
|     24 |
|     10 |
|     12 |
|     14 |
|     16 |
|     18 |
|     20 |
+--------+
10 rows in set (0.00 sec)

## if we want to see the set of all duplicate records the query is as follows:
SELECT h1.* FROM house h1 WHERE EXISTS (SELECT NULL FROM house h2 WHERE h1.houseno = h2.houseno AND h1.name = h2.name AND h2.tin_id <> h1.tin_id);
+---------+------+--------+
| houseno | name | tin_id |
+---------+------+--------+
|       1 | a    |      2 |
|       1 | a    |      4 |
|       3 | c    |     22 |
|       3 | c    |     24 |
|       4 | c    |     10 |
|       4 | c    |     12 |
|       4 | c    |     14 |
|       4 | c    |     16 |
|       4 | c    |     18 |
|       4 | c    |     20 |
+---------+------+--------+
10 rows in set (0.00 sec)
## The other best solution is as follows:

SELECT tin_id
 FROM (SELECT houseno, name
         FROM house
       GROUP BY houseno, name
       HAVING count(*) > 1) temp,
      house h
WHERE temp.houseno = h.houseno AND temp.name = h.name;

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

Avoiding Lock wait time out Issues

when even we are facing the lockwait timeout issues
we need to find who is locking and blocking the thread
The query to find the lock waittime out which mysqlid is locking and blocking the queries
The query is as follows:
SELECT r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b  ON  b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r  ON  r.trx_id = w.requesting_trx_id;

In order to avoid the lock wait time out issue permanantely we have two solutions for this
1. Setting the transaction isolation level to read committed
The command is as follows
set global transaction isolation level read committed;

2. Setting  innodb_locks_unsafe_for_binlog = 1 in my.cnf file and restart mysql
For more inforamtion we can refer the following urls :
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-information-schema-examples.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog
http://en.wikipedia.org/wiki/Isolation_%28database_systems%29