Shell Scripts for Mysql Enterprise backup using cron jobs

  
   vi full_meb.sh
  
   #!/bin/bash
   ### MySQL Server Login Info ###
   MUSER="kiran"
   MPASS="kiran"
   MHOST="localhost"
   MYSQL="$(which mysql)"
   MYSQLDUMP="$(which mysqldump)"
   DEFAULTFILE="/fs0/mysql/instance/testserver/conf/my.cnf"
   SOCK1="/fs0/mysql/instance/testserver/run/mysql.sock"
  BAK="/fs0/kiran/meb/fullbackup"
  INCBAK="/fs0/kiran/meb/incbackup"
  MNTFULL="/mnt/nsddbbackup/backup/testserver/backup_meb/fullbackup/"
  MNTINC="/mnt/nsddbbackup/backup/testserver/backup_meb/incbackup/"

NODE=$(echo ${TAPE_BACKUP_NODE:-$(uname -n)} | awk -F. '{print $1}')

  #GZIP="$(which gzip)"
  #NOW=$(date +"%d-%m-%Y")
  #NOW=`date +"%d-%m-%Y"`

  BACKUP_TYPE=$1

  #setInst lportal
  #export PATH=$PATH:/fs0/mysql/meb/meb-3.8.0-el6-x86-64bit/bin
 
  export PATH=$PATH:/fs0/mysql/meb/meb-3.9.0-linux2.6-x86-64bit/bin


  if [ $BACKUP_TYPE = "FULL" ]
  then

  mysqlbackup --defaults-file=$DEFAULTFILE --user=$MUSER -p$MUSER --socket=$SOCK1 --backup-dir=$BAK --compress --with-timestamp backup

  RC=$?
  if [ "$RC" != 0 ]
  then
   # echo "Export of ($MYSQL_DBNAME) on node $NODE failed during mysql dump schema  $DB"
   # $BACKUP_COM_DIR/bkup_notification.sh FAILURE "$NODE:$PROCNAME Export failed return code $RC."

   echo "full backup using MEB failed on $NODE"  | mail -s "failed to create backup on $NODE server" kirch02x.com
   # exit 2
  fi

  ### copying to full backup to mount drive

  cpfile=$(ls -lrt $BAK  | tail -1 | awk '{ print $9}')

  cp -R $BAK/$cpfile $MNTFULL
  echo $BAK
  echo $MNTFULL

  fi

  if [ $BACKUP_TYPE = "FRSTINC" ]
  then

  destdir=$(ls -lrt $BAK | tail -1 | awk '{ print $9}')

  echo $destdir


  mysqlbackup --defaults-file=$DEFAULTFILE --user=$MUSER -p$MPASS --socket=$SOCK1 --incremental --incremental-backup-dir=$INCBAK --incremental-base=dir:$BAK/$destdir --with-timestamp backup

  RC=$?
  if [ "$RC" != 0 ]
  then
   # echo "Export of ($MYSQL_DBNAME) on node $NODE failed during mysql dump schema  $DB"
   # $BACKUP_COM_DIR/bkup_notification.sh FAILURE "$NODE:$PROCNAME Export failed return code $RC."

   echo "first incremental backup MEB failed on $NODE"  | mail -s "failed to create the first incremental backup on $NODE server" kirch02x.com
  fi

  ### copying the first increment  backup to mount drive

  cpfile=$(ls -lrt $INCBAK  | tail -1 | awk '{ print $9}')

  cp -R $INCBAK/$cpfile $MNTINC
  echo $cpfile
  echo $INCBAK
  echo "the incremental backup is" $MNTINC


  fi


  if [ $BACKUP_TYPE = "INC" ]
  then
  destdir=$(ls -lrt $INCBAK | tail -1 | awk '{ print $9}')
  echo $destdir

  mysqlbackup --defaults-file=$DEFAULTFILE --user=$MUSER -p$MPASS --socket=$SOCK1 --debug --verbose  --incremental --incremental-backup-dir=$INCBAK --incremental-base=dir:$INCBAK/$destdir --with-timestamp backup


  RC=$?
  if [ "$RC" != 0 ]
  then
   # echo "Export of ($MYSQL_DBNAME) on node $NODE failed during mysql dump schema  $DB"
   # $BACKUP_COM_DIR/bkup_notification.sh FAILURE "$NODE:$PROCNAME Export failed return code $RC."

   echo "subsequent incremental backup MEB failed on $NODE"  | mail -s "failed to create the subsequent incremental backup on $NODE server" kirch02x.com
  fi

 ### copying the first increment  backup to mount drive
 cpfile=$(ls -lrt $INCBAK  | tail -1 | awk '{ print $9}')
 cp -R $INCBAK/$cpfile $MNTINC
 echo $cpfile
 echo $INCBAK
 echo "the incremental backup is" $MNTINC
echo $NODE
 fi
# echo for deleting toe older backup files
#find $MNTFULL/* -type d -mtime +15 -exec rm -rf {} \ ; 2> /dev/null
find /mnt/nsddbbackup/backup/testserver/backup_meb/incbackup/* -type d -mtime +10 -exec rm -rf {} \;  2> /dev/null
find /mnt/nsddbbackup/backup/testserver/backup_meb/fullbackup/* -type d -mtime +7 -exec rm -rf {} \;  2> /dev/null

## echo deleting the locat backup files older than 7 days
find /fs0/kiran/meb/fullbackup/* -type d -mtime +7 -exec rm -rf {} \;  2> /dev/null
find /fs0/kiran/meb/incbackup/* -type d -mtime +7 -exec rm -rf {} \;  2> /dev/null



######### the cronjob is as folllows ###########
00 01 * * * /fs0/kiran/scripts/full_meb.sh FULL
00 06 * * * /fs0/kiran/scripts/full_meb.sh FRSTINC
00 12,18,23 * * * /fs0/kiran/scripts/full_meb.sh INC
###################################

MYSQL USAGE per connection

root@(none)]> SELECT ( @@key_buffer_size
    -> + @@query_cache_size
+ @@innodb_buffer_pool_size
    -> + @@innodb_buffer_pool_size
    -> + @@innodb_additional_mem_pool_size
    -> + @@innodb_log_buffer_size
    -> + @@max_connections * ( @@read_buffer_size
    -> + @@read_rnd_buffer_size
    -> + @@sort_buffer_size
    -> + @@join_buffer_size
    -> + @@binlog_cache_size
    -> + @@thread_stack
    -> + @@tmp_table_size )
    -> ) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;



WE can also create the stored procedure as follows:



DELIMITER $$
DROP PROCEDURE IF EXISTS test $$
CREATE PROCEDURE test ()
BEGIN

DECLARE var VARCHAR(100);
DECLARE val VARCHAR(100);
DECLARE done INT;
#Variables for storing calculations
DECLARE GLOBAL_SUM DOUBLE;
DECLARE PER_THREAD_SUM DOUBLE;
DECLARE MAX_CONN DOUBLE;
DECLARE HEAP_TABLE DOUBLE;
DECLARE TEMP_TABLE DOUBLE;

DECLARE CUR_GBLVAR CURSOR FOR SELECT * FROM information_schema.GLOBAL_VARIABLES;
#### Ref: http://bugs.mysql.com/bug.php?id=49758
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
SET GLOBAL_SUM=0;
SET PER_THREAD_SUM=0;
SET MAX_CONN=0;
SET HEAP_TABLE=0;
SET TEMP_TABLE=0;
OPEN CUR_GBLVAR;
mylp:LOOP
FETCH CUR_GBLVAR INTO var,val;
IF done=1 THEN
LEAVE mylp;
END IF;
IF var in ('key_buffer_size','innodb_buffer_pool_size','innodb_additional_mem_pool_size','innodb_log_buffer_size','query_cache_size') THEN
#Summing Up Global Memory Usage
SET GLOBAL_SUM=GLOBAL_SUM+val;
ELSEIF var in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','thread_stack','max_allowed_packet','net_buffer_length') THEN
#Summing Up Per Thread Memory Variables
SET PER_THREAD_SUM=PER_THREAD_SUM+val;
ELSEIF var in ('max_connections') THEN
#Maximum allowed connections
SET MAX_CONN=val;
ELSEIF var in ('max_heap_table_size') THEN
#Size of Max Heap tables created
SET HEAP_TABLE=val;
#Size of possible Temporary Table = Maximum of tmp_table_size / max_heap_table_size.
ELSEIF var in ('tmp_table_size','max_heap_table_size') THEN
SET TEMP_TABLE=if((TEMP_TABLE>val),TEMP_TABLE,val);
END IF;
END LOOP;
CLOSE CUR_GBLVAR;
#Summerizing:
select "Global Buffers" as "Parameter",CONCAT(GLOBAL_SUM/(1024*1024),' M') as "Value" union
select "Per Thread",CONCAT(PER_THREAD_SUM/(1024*1024),' M') union
select "Maximum Connections",MAX_CONN union
select "Total Memory Usage",CONCAT((GLOBAL_SUM + (MAX_CONN * PER_THREAD_SUM))/(1024*1024),' M') union
select "+ Per Heap Table",CONCAT(HEAP_TABLE / (1024*1024),' M') union
select "+ Per Temp Table",CONCAT(TEMP_TABLE / (1024*1024),' M') ;
END $$
DELIMITER ;

for calling the stored procedure:
mysql> call test();


The url is as follows:

https://dev.mysql.com/doc/refman/5.0/en/memory-use.html

Archiving the tables using percona toolkit

1) pre requisite for the running the scripts.
2) first we need to install percona took kit on the server.

steps for running the job is as follows:
sh kir1.sh 1> succes.txt 2> error.txt


### for running the scripts as job
00 01 * * * /fs0/kiran/scripts/kir1.sh >/fs0/kiran/meb/logs/archive.`date +\%Y\%m\%d\%H\%M`.log 2>&1


vi kir1.sh
mysql -u root -proot -Bse "select concat(table_schema,',',table_name) from information_schema.tables where table_schema='kiran' and table_name like 'kiran%'; " > dname.txt
OLD_IFS=$IFS
IFS=$'\n'
for x in `cat dname.txt`
do
#echo $x
var1=`echo $x | cut -d ',' -f1`
var2=`echo $x | cut -d ',' -f2`
var3="kiran4"
createtable="CREATE TABLE  IF Not Exists $var3.$var2 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, entered datetime NOT NULL, uid int(10) unsigned NOT NULL,  rid int(10) unsigned NOT NULL,start datetime NOT NULL, result smallint(6) NOT NULL, type tinyint(3) unsigned NOT NULL DEFAULT '0', rtime mediumint(6) unsigned DEFAULT NULL,ctime mediumint(6) unsigned DEFAULT NULL,  ptime mediumint(6) unsigned DEFAULT NULL, dtime mediumint(6) unsigned DEFAULT NULL,  dsize int(10) unsigned DEFAULT NULL, user int(10) unsigned DEFAULT NULL, loc char(2) DEFAULT NULL, ip int(10) unsigned DEFAULT NULL, ip6 varbinary(16) DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=archive DEFAULT CHARSET=utf8;"
createdatabase=" create datiabase if not exists $var3;"

echo $createdatabase
echo $createtable
mysql -u root -proot -Bse " $createdatabase"
mysql -u root -proot -Bse " $createtable "
#/usr/bin/pt-archiver --charset utf8 --bulk-delete --bulk-insert --delayed-insert --low-priority-delete --txn-size 0 --analyze s --limit 10000 --sleep-coef 0.5 --statistics --source h=host1,D=kiran,u=kiran,p=kiran,t=$var2 ----dry-run  --dest h=host1,D=kiran2,u=kiran,p=kiran,t=$var2 ----dry-run --nocheck-columns --columns id,entered,uid,rid,start,result,type,rtime,ctime,ptime,dtime,dsize,user,loc,ip6 --where 'entered < date(from_unixtime(1403859783)) and year(entered)=year(from_unixtime(1403859783))'
#/home/mysql/percona-toolkit-2.2.4/bin/pt-archiver --no-check-charset --bulk-delete --bulk-insert --delayed-insert --low-priority-delete --txn-size 0 --analyze s --limit 10000 --sleep-coef 0.5 --statistics --source h=host1,D=kiran,u=kiran,p=kiran,t=$var2 --dry-run  --dest h=host1,D=kiran2,u=kiran,p=kiran,t=$var2 --dry-run --nocheck-columns --columns id,entered,uid,rid,start,result,type,rtime,ctime,ptime,dtime,dsize,user,loc,ip6 --where 'entered < date(from_unixtime(1403859783)) and year(entered)=year(from_unixtime(1403859783))'

#/home/mysql/percona-toolkit-2.2.4/bin/pt-archiver --no-check-charset --bulk-delete --bulk-insert --delayed-insert --low-priority-delete --txn-size 0 --analyze s --limit 10000 --sleep-coef 0.5 --statistics --source h=host1,D=kiran,u=kiran,p=kiran,t=$var2   --dest h=host1,D=kiran2,u=kiran,p=kiran,t=$var2 --nocheck-columns --columns id,entered,uid,rid,start,result,type,rtime,ctime,ptime,dtime,dsize,user,loc,ip6 --where 'entered < date_add((date_add(now(), interval -120 day)) , interval -day(date_add(now(), interval -120 day)) Day)'

echo "the table name is " $var1
echo "the table name is " $var2
#/home/mysql/percona-toolkit-2.2.4/bin/pt-archiver --no-check-charset --bulk-delete --bulk-insert --delayed-insert --low-priority-delete --txn-size 0 --analyze s --limit 10000 --sleep-coef 0.5 --statistics --source h=host1,D=kiran,u=kiran,p=kiran,t=$var2  --dest h=host1,D=kiran2,u=kiran,p=kiran,t=$var2 --nocheck-columns --columns id,entered,uid,rid,start,result,type,rtime,ctime,ptime,dtime,dsize,user,loc,ip6 --where 'entered < date(from_unixtime(1403859783)) and year(entered)=year(from_unixtime(1403859783))'
#echo "$var1  and $var2"
#echo $x
done



 

query to find the non alphabets characters in mysql using regular expression

mysq> create table house ( eno int not null auto_increment primary key, name varchar(100));

mysql > insert into house(name) values ('kiran1$');
mysql > insert into house(name) values ('kiran$$$');
mysql > insert into house(name) values ('ki###ran');
mysql > insert into house(name) values ('kiranan');
mysql > insert into house(name) values ('###kran');


mysql> select * from house where name NOT REGEXP '^[A-Za-z0-9]+$';
+-----+----------+
| eno | name     |
+-----+----------+
|   1 | kiran1$  |
|   2 | kiran$$$ |
|   3 | ki###ran |
|   5 | ###kran  |
+-----+----------+
4 rows in set (0.00 sec)

Example 1 is as follows:

SELECT col1, col2 FROM table1
WHERE col0 REGEXP '^[0-9]+IFJ[0-9]*';
In the regular expression in this example, the ^ indicates that the pattern must start at the beginning of this string.
Next, [0-9] indicates that a number from 0 to 9 is required next.
The plus sign following it means that one or more numbers are expected.
After this, the actual letters IFJ must be matched.
Finally, because of the asterisk after the [0-9],
zero or more numbers may follow.
This SQL statement will match an identifier like 123IFJ and 123IFJ456, but not IFJ123.



Below is a table listing the regular expression operators available:
* zero or more of preceding character
+ one or more of preceding character
{n} number of occurences of preceding character
{n,n} from first to second number of occurences of preceding character
^ beginning of string
$ end of string
. any one character
[...] characters within brackets
[^...] not the characters within brackets
...|... alternative patterns
For more details the url is as follows:

http://dev.mysql.com/doc/refman/5.5/en/regexp.html
http://dev.mysql.com/doc/refman/5.5/en/pattern-matching.html

query to find the non alphabets characters in mysql using regular expression

Executing multiple sql files and know the errors and warning messages after executing commands mysql

First all the script files and path should store in one file as follows

The script_list.txt contains the following files

cat /home/kiran.chinta/prodscripts/script_list.txt

testdb1 /home/scripts/insert1.sql
testdb2 /home/scripts/scripts2.sql
testdb3 /home/scripts/testsql.sql

vi script.sh


#!/bin/sh
# script to generate the correct commands to execute the sql statements from a text file
# the text file script_list.txt should start with schema_name followed by a space and then script_file_path
# disable echo command and the double quotes to execute the statements
myhost='127.0.0.1'
myuser='kiran'
mypass='kiran@123'
myport='3306'
mylog='/home/kiran.chinta/prodscripts/logs'
mkdir -p $mylog
cat /home/kiran.chinta/prodscripts/script_list.txt | while read -r myschema filename
do
if [ -s $filename ];then
mybase=`basename $filename`
time mysql -h$myhost -u$myuser -p$mypass $myschema -vvv -f --show-warnings  < $filename > "$mylog"/"$myschema"_"$mybase"_success.txt 2> "$mylog"/"$myschema"_"$mybase"_err.txt
#echo "$myschema $filename  "$mylog"/"$myschema"_"$mybase"_success.txt "$mylog"/"$myschema"_"$mybase"_err.txt  "
else
echo " FILE $filename DOES NOT EXIST"
fi


### for executing the above scripts the command is as follows
sh script.sh > success.txt 2> failure.txt

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

keeping the slave always delayed

often DBA wll delete or drop table and will replicate to slave very fast.
In order to avoid that we should always have one slave delayed.
 for that we have two scripts :

########first script############
vi /home/kiran.chinta/scripts/checkslave.sh
#!/bin/sh
MUSER="kiran"
MPASS="kiran"
MHOST="db1"
mysql -u $MUSER -p$MPASS -h $MHOST -Bse "show master status" > /home/kiran.chinta/scripts/slavepos.txt

#### second scripts #############
vi /home/kiran.chinta/scripts/slavedetail.sh
#!/bin/sh
#tail -l /home/kil -l /home/kiran.chinta/slavepos.txt |  while read -r fname bpos
#ran.chinta/slavepos.txt |  while read -r fname bpos
#do
#echo $fname
#echo $bpos
#done
fname=`tr -s ' '  < slavepos.txt | awk -F ' ' '{print $1}'`
bpos=`tr -s ' '  < slavepos.txt | awk -F ' ' '{print $2}'`
user=dba
pass=dba
echo "$fname"
echo "$bpos"
mysql -u $user -p$pass -Bse "START SLAVE IO_THREAD UNTIL MASTER_LOG_FILE='$fname', MASTER_LOG_POS=$bpos"

After creating the two scripts
We should have the following crons

##### give below should run on the cronts #################
#getting the master status
30 02 * * * sh -xf /home/kiran.chinta/scripts/checkslave.sh  > checksucc.txt 2>checkfail.txt
#updating the slave status
30 22 * * * sh -xf /home/kiran.chinta/scripts/slavedetail.sh  > slavedetail_succ.txt 2>slavedetail_fail.txt

query to find the maximum time based on the each businessurl

create table test1 ( time_in_sec bigint, transid,businessurl);

The table data are as folows:

mysql> select * from tmp_test;
+-------------+---------+-------------+
| time_in_sec | transid | businessurl |
+-------------+---------+-------------+
| 10 | 1 | htt:/test1 |
| 20 | 2 | htt:/test1 |
| 33 | 3 | htt:/test1 |
| 33 | 4 | htt:/org1 |
| 33 | 5 | htt:/org1 |
| 100 | 6 | htt:/org1 |
| 100 | 6 | htt:/org2 |
+-------------+---------+-------------+

query stage1: (we want some of time based on transid and businessurl)

mysql> select sum(time_in_sec), transid,businessurl from tmp_test group by transid,businessurl;
+------------------+---------+-------------+
| sum(time_in_sec) | transid | businessurl |
+------------------+---------+-------------+
| 10 | 1 | htt:/test1 |
| 20 | 2 | htt:/test1 |
| 33 | 3 | htt:/test1 |
| 33 | 4 | htt:/org1 |
| 33 | 5 | htt:/org1 |
| 100 | 6 | htt:/org1 |
| 100 | 6 | htt:/org2 |
+------------------+---------+-------------+
7 rows in set (0.00 sec)


from the above query we want the output of maximum time for each businessurl


the output should be as follows:

33 | 4 | htt:/org1
100 | 6 | htt:/org2


solution:

the above query can be writte in two ways:

1)
select
max(total) total,
cast(substring_index(group_concat(transid ORDER BY total desc), ',',1) as unsigned) transid,
businessurl from
(
select
sum(time_in_sec) total, transid, businessurl
from
tmp_test group by transid,businessurl ) Z
group by businessurl


2)
DROP TEMPORARY TABLE IF EXISTS tmp_table2;
CREATE TEMPORARY TABLE tmp_table2 AS SELECT sum(time_in_sec) total, transid, businessurl FROM tmp_test GROUP BY transid,businessurl;

CREATE INDEX in_business_total
  ON tmp_table2(businessurl, total);

SELECT Z.total,
      Z.businessurl,
      (SELECT min(t2.transid)
         FROM tmp_table2 t2
        WHERE t2.businessurl = Z.businessurl AND t2.total = Z.total)
         transid
 FROM (SELECT max(t.time_in_sec) total, t.businessurl
         FROM tmp_test t
       GROUP BY t.businessurl) Z;

installing and configurations using ZRM tool for mysql databases

##### for download and installing zrm recovery tools:
for downloading the zrm-recovery toos
 wget http://www.zmanda.com/downloads/community/ZRM-MySQL/2.2/RPM/MySQL-zrm-2.2.0-1.noarch.rpm
installing the rpm packages as follows:
rpm 0-ivh -zrm-2.2.0-1.noarch.rpm

check for rpm packages
 ls -lh /usr/bin/mysql-zrm*
-rwxr-x--- 1 mysql mysql 3.5K Feb  4  2010 /usr/bin/mysql-zrm
-rwxr-x--- 1 mysql mysql 1.8K Feb  4  2010 /usr/bin/mysql-zrm-abort-backup
-rwxr-x--- 1 mysql mysql  70K Feb  4  2010 /usr/bin/mysql-zrm-backup
-rwxr-x--- 1 mysql mysql 1.6K Feb  4  2010 /usr/bin/mysql-zrm-check
-rwxr-x--- 1 mysql mysql 2.9K Feb  4  2010 /usr/bin/mysql-zrm-extract-backup
-rwxr-x--- 1 mysql mysql 1.8K Feb  4  2010 /usr/bin/mysql-zrm-getconf
-rwxr-x--- 1 mysql mysql 4.4K Feb  4  2010 /usr/bin/mysql-zrm-list
-rwxr-x--- 1 mysql mysql  12K Feb  4  2010 /usr/bin/mysql-zrm-manage-backup
-rwxr-x--- 1 mysql mysql 2.7K Feb  4  2010 /usr/bin/mysql-zrm-migrate-file-ownership
-rwxr-x--- 1 mysql mysql  13K Feb  4  2010 /usr/bin/mysql-zrm-parse-binlogs
-rwxr-x--- 1 mysql mysql 4.9K Feb  4  2010 /usr/bin/mysql-zrm-purge
-rwxr-x--- 1 mysql mysql  29K Feb  4  2010 /usr/bin/mysql-zrm-reporter
-rwxr-x--- 1 mysql mysql  31K Feb  4  2010 /usr/bin/mysql-zrm-restore
-rwxr-x--- 1 mysql mysql  13K Feb  4  2010 /usr/bin/mysql-zrm-scheduler
-rwxr-x--- 1 mysql mysql 8.1K Feb  4  2010 /usr/bin/mysql-zrm-verify-backup
The configuration files are located on
ls -lh /etc/mysql-zrm/
-rwxrwx--- 1 mysql mysql  12K Feb  4  2010 mysql-zrm.conf
-rwxrwx--- 1 mysql mysql   48 Feb  4  2010 mysql-zrm-release
-rwxrwx--- 1 mysql mysql 3.2K Feb  4  2010 mysql-zrm-reporter.conf
-rw-rw---- 1 mysql mysql  596 Feb  4  2010 RSS.header

After sucessfull configuration and installation
we want to take the full backup using ZRM Tools:
cd /etc/mysql-zrm
mkdir fullbackup
cp mysql-zrm.conf fullbackup/
cd fullbackup
ls -lh
-rwxr-x--- 1 mysql mysq 9.0k  Feb  4  2010 mysql-zrm.conf
change the configuration file on mysql-zrm.conf
The changes are as follows:
backup-level=0
backup-mode=logical
user="user1"
password="user1"
destination=/var/lib/mysql-zrm

steps for taking the backup (full backup)
mysql-zrm-scheduler --now --backup-set fullbackup

for more options for mysq-zrm are as follows:
mysql-zrm --action --help
USAGE:
--action <backup|restore|schedule-backup|report|list|getconf|setconf|purge|parse-binlogs|check|manage-backup|verify-backup|extract-backup|abort-backup>
Help on individual action items can be obtained by specifying --help with the required action.
For example: 'mysql-zrm --action backup --help'
mysql-zrm --action --help

1) restoring the full backup
mysql-zrm --action restore --backup-set fullbackup --source-directory /var/lib/mysql-zrm/fullbackup/20120215114946/

2) steps for restoing the individual database from full backup.
first go to the index file
then change the logical-databses (db1 db2) etc
vi index
backup-set=fullbackup
backup-date=20120215114946
mysql-server-os=Linux/Unix
backup-type=regular
host=localhost
backup-date-epoch=1329286786
mysql-zrm-version=ZRM for MySQL Community Edition - version 2.2.0
mysql-version=5.5.19-log
backup-directory=/var/lib/mysql-zrm/fullbackup/20120215114946
backup-level=0
logical-databases=db1 db2
next-binlog=mysql-bin.000004
backup-size=478.09 MB
read-locks-time=00:00:19
flush-logs-time=00:00:00
backup-time=00:00:19
backup-status=Backup succeeded
mysql-zrm --action restore --backup-set fullbackup --source-directory /var/lib/mysql-zrm/fullbackup/20120215114946/ --databases "db1"

More about the zrm utilities:
The url  is as follows:
http://www.zmanda.com/quick-mysql-backup.html

Query to find the latest address details based on created dated ( one person can have multiple addresses)

The person table structure is as follows:

CREATE TABLE person
(
  id         int(11) AUTO_INCREMENT,
  fname      varchar(100),
  lastname   varchar(100),
  PRIMARY KEY(id)
);
The address table structure is as follows:

CREATE TABLE address(id int(11) AUTO_INCREMENT,  person_id int(11), city varchar(100), state varchar(100), zip varchar(10), created_date datetime, PRIMARY KEY(id), CONSTRAINT  fk_person_id FOREIGN KEY (person_id) REFERENCES person(id) , KEY(person_id, created_date));

to increase the performance we can create the index as follows:
create index idx1 on address (person_id, created_date)

The query is as follows:

SELECT *,
      city,
      state,
      zip,
      created_date
 FROM (SELECT p.*,
              (SELECT id
                 FROM address
                WHERE person_id = p.id
               ORDER BY person_id DESC, created_date DESC
                LIMIT 1)
                 latest_adr_id
         FROM person p
   ) Z,
      address adr
WHERE Z.latest_adr_id = adr.id;

Query to find the count based on 15 minutes on datetime column

mysql> show create table test3 \G
*************************** 1. row ***************************
       Table: test3
Create Table: CREATE TABLE `test3` (
  `eno` int(11) NOT NULL AUTO_INCREMENT,
  `created_date` datetime DEFAULT NULL,
  `errno` int(11) DEFAULT NULL,
  PRIMARY KEY (`eno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


+-----+---------------------+-------+
| eno | created_date        | errno |
+-----+---------------------+-------+
|   1 | 2011-12-19 11:00:28 |   100 |
|   2 | 2011-12-19 11:14:31 |   101 |
|   3 | 2011-12-19 11:14:38 |   103 |
|   4 | 2011-12-19 11:00:28 |   100 |
|   5 | 2011-12-19 12:00:59 |   100 |
|   6 | 2011-12-19 11:15:06 |   101 |
|   7 | 2011-12-19 11:15:33 |   100 |
|   8 | 2011-12-19 11:15:34 |   100 |
|   9 | 2011-12-19 11:30:28 |   100 |
|  10 | 2011-12-19 11:30:28 |   100 |
+-----+---------------------+-------+
10 rows in set (0.00 sec)

output Method 1:


mysql> SELECT
    -> DATE(created_date) AS Date,
    -> CONCAT(
    -> HOUR(created_date), ':',
    -> CASE
    -> WHEN MINUTE(created_date) BETWEEN 0 AND 14 THEN '00'
    -> WHEN MINUTE(created_date) BETWEEN 15 AND 29 THEN '15'
    -> WHEN MINUTE(created_date) BETWEEN 30 AND 44 THEN '30'
    -> WHEN MINUTE(created_date) BETWEEN 45 AND 59 THEN '45'
    -> END
    -> )AS Per15min, COUNT(*) as 'Error Counter'
    -> FROM test3
    -> where errno=100
    -> GROUP BY Per15min;
+------------+----------+---------------+
| Date       | Per15min | Error Counter |
+------------+----------+---------------+
| 2011-12-19 | 11:00    |             2 |
| 2011-12-19 | 11:15    |             2 |
| 2011-12-19 | 11:30    |             2 |
| 2011-12-19 | 12:00    |             1 |
+------------+----------+---------------+
4 rows in set (0.00 sec)




method2:

mysql> select x.created_date,x.hdate,count(errno),x.minute1,if(x.minute1=1,
    -> concat(x.hdate, "-", x.hdate, ":15") ,
    -> if(x.minute1=2,concat(x.hdate,":15", "-" , x.hdate,":30"),
    -> if(x.minute1=3,concat(x.hdate,":30", "-", x.hdate,":45"),
    -> if(x.minute1=4,concat(x.hdate,":45", "-", x.hdate + 1),"xx")))) as xxx  from
    -> (
    ->  select date(created_date) as created_date ,hour(created_date) hdate ,errno ,case when minute(created_date) < 15 then 1
    ->  when minute(created_date) < 30 then 2 when minute(created_date) < 45 then 3 when minute(created_date) < 59 then 4 end as minute1
    ->  from test3   where errno =100
    ->  ) x
    ->  group by x.created_date,x.hdate,x.minute1;
+--------------+-------+--------------+---------+-------------+
| created_date | hdate | count(errno) | minute1 | xxx         |
+--------------+-------+--------------+---------+-------------+
| 2011-12-19   |    11 |            2 |       1 | 11-11:15    |
| 2011-12-19   |    11 |            2 |       2 | 11:15-11:30 |
| 2011-12-19   |    11 |            2 |       3 | 11:30-11:45 |
| 2011-12-19   |    12 |            1 |       1 | 12-12:15    |
+--------------+-------+--------------+---------+-------------+
4 rows in set (0.00 sec)

SELECT
DATE(created_date) AS Date,
CONCAT(
HOUR(created_date), ':',
CASE
WHEN MINUTE(created_date) BETWEEN 0 AND 14 THEN '00'
WHEN MINUTE(created_date) BETWEEN 15 AND 29 THEN '16'
WHEN MINUTE(created_date) BETWEEN 30 AND 44 THEN '31'
WHEN MINUTE(created_date) BETWEEN 45 AND 59 THEN '46'
END,
' TO ',
HOUR(created_date), ':',
CASE
WHEN MINUTE(created_date) BETWEEN 0 AND 14 THEN '15'
WHEN MINUTE(created_date) BETWEEN 15 AND 29 THEN '30'
WHEN MINUTE(created_date) BETWEEN 30 AND 44 THEN '45'
WHEN MINUTE(created_date) BETWEEN 45 AND 59 THEN '59'
END
) AS Per15min, COUNT(*) as 'Error Counter'
FROM test3
GROUP BY Per15min;
--- OUTPUT ---
+------------+----------------+---------------+
| Date | Per15min | Error Counter |
+------------+----------------+---------------+
| 2011-12-19 | 11:16 TO 11:30 | 1 |
| 2011-12-19 | 11:31 TO 11:45 | 1 |
| 2011-12-19 | 11:46 TO 11:59 | 3 |
| 2011-12-19 | 12:00 TO 12:15 | 1 |
| 2011-12-19 | 12:16 TO 12:30 | 2 |
| 2011-12-19 | 12:31 TO 12:45 | 1 |
| 2011-12-19 | 12:46 TO 12:59 | 2 |
| 2011-12-19 | 13:00 TO 13:15 | 1 |
+------------+----------------+---------------+
The easies way of writing the query is as follows:

#####15 minutes query #######
SELECT date(creation_date) as day1,HOUR(creation_date) AS hour,
CONCAT(LPAD(FLOOR(MINUTE(creation_date)/15)*15, 2, '0'), '-',LPAD((FLOOR(MINUTE(creation_date)/15)+1)*15,2,'0')) AS range1,
COUNT(*) AS 'ErrorCounter'
FROM   transaction
WHERE DATE(creation_date) = '2011-06-26' 
GROUP BY day1, hour,range1
ORDER BY day1, hour,range1 ;

### 5 minutes query ####

SELECT date(creation_date) as day1,HOUR(creation_date) AS hour,
CONCAT(LPAD(FLOOR(MINUTE(creation_date)/5)*5, 2, '0'), '-',LPAD((FLOOR(MINUTE(creation_date)/5)+1)*5,2,'0')) AS range1,
COUNT(*) AS 'ErrorCounter'
FROM   transaction
WHERE DATE(creation_date) = '2011-06-26'
GROUP BY day1, hour,range1
ORDER BY day1, hour,range1 ;

steps for configurations SSL in mysql

1.) Step one SSL should be enabled if it is not enabled then we need to enable "have_openssl" and "have_ssl"
mysql> show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.00 sec)

2.) in my.cnf add ssl below [mysqld] as follow and restart the server.
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
#### ssl configurations
ssl

3.) after restart the server check the ssl is enabled or not
mysql> SHOW variables LIKE '%ssl%';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| have_openssl  | YES                       |
| have_ssl      | YES                       |
| ssl_ca        |
| ssl_capath    |                           |
| ssl_cert      |                           |
| ssl_cipher    |
| ssl_key       |                           |
+---------------+---------------------------+
7 rows in set (0.00 sec)

4.) creating the certificates.
 for creating the certificates we first need to install openssl
 check wheather openssl is installed or not
 openssl
OpenSSL>
5. creating the certficates.
shell> mkdir -p /etc/mysql/cert 
shell> cd /etc/mysql/cert
Create CA certificate:
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 9999 -key ca-key.pem > ca-cert.pem
Create server certificate:
shell> openssl req -newkey rsa:2048 -days 9999 -nodes -keyout server-key.pem > server-req.pem
shell> openssl x509 -req -in server-req.pem -days 9999 -CA ca-cert.pem -CAkey ca-key.pem –set_serial 01 > server-cert.pem
Create client certificate:
shell> openssl req -newkey rsa:2048 -days 9999 -nodes -keyout client-key.pem > client-req.pem
shell> openssl x509 -req -in client-req.pem -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
verify the certificates
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
Now we have following  certificates/keys got created.
ca-cert.pem
ca-key.pem
client-cert.pem
client-key.pem
client-req.pem
server-cert.pem
server-key.pem
server-req.pem

6.) now we need to add certificates in my.cnf as follows:
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
#### ssl configurations
ssl
ssl-ca = /etc/cert/ca-cert.pem
ssl_capath = /etc/cert
ssl-cert = /etc/cert/server-cert.pem
ssl-key = /etc/cert/server-key.pem
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
ssl-ca    =   /etc/cert/ca-cert.pem
ssl-cert  =  /etc/cert/client-cert.pem
ssl-key   = /etc/cert/client-key.pem
7.) restart the mysql
shell> /etc/init/mysql restart
 run the command as follows:
mysql> SHOW variables LIKE '%ssl%';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| have_openssl  | YES                       |
| have_ssl      | YES                       |
| ssl_ca        | /etc/cert/ca-cert.pem     |
| ssl_capath    | /etc/cert                 |
| ssl_cert      | /etc/cert/server-cert.pem |
| ssl_cipher    |                           |
| ssl_key       | /etc/cert/server-key.pem  |
+---------------+---------------------------+
7 rows in set (0.00 sec)

9.) given the grant privileges uing ssl.
GRANT ALL ON *.* TO 'test123'@'localhost' IDENTIFIED BY 'test123' REQUIRE SSL;

10.) suppose we want to connect to another server (from system2).
copy the ca-cert.pem,client-cert.pem,client-key.pem to sytem2
11.) go  to system2 type the following command
mysql -u test123 -h system1 --ssl-ca=/etc/cert1/ca-cert.pem --ssl-cert=/etc/cert1/client-cert.pem --ssl-key=/etc//cert1/client-key.pem -p
mysql>
In this way the ssl configuration is established.
for vericication the command is as follows:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.13, for Linux (x86_64) using readline 5.1
Connection id:          25
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.13 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 23 hours 50 min 36 sec
Threads: 2  Questions: 10559  Slow queries: 0  Opens: 1261  Flush tables: 1  Open tables: 42  Queries per second avg: 0.123
If we want to set up replication using ssl keys.
The commands are as follows:

mysql> GRANT REPLICATION SLAVE ON *.* TO "repl"@"10.%" IDENTIFIED BY 'XXXXXX'  REQUIRE SSL;
mysql> FLUSH PRIVILEGES;
the replication command are as follows:

CHANGE MASTER TO
MASTER_HOST=,
MASTER_USER='repl',
MASTER_PASSWORD='',
MASTER_LOG_FILE='',
MASTER_LOG_POS= ,
MASTER_SSL=1,
MASTER_SSL_CA = '/etc/mysql/cert/ca-cert.pem',
MASTER_SSL_CERT = '/etc/mysql/cert/client-cert.pem',
MASTER_SSL_KEY = '/etc/mysql/cert/client-key.pem';

For more details the url are as follows:
http://dev.mysql.com/doc/refman/5.5/en/secure-using-ssl.html

META-DATA queries in MYSQL

1.) display all the table name and tableschemas
select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,TABLE_ROWS from information_Schema.tables;

2) displaying the table name and the column name
select TABLE_SCHEMA,TABLE_NAME, group_concat(COLUMN_NAME) colname,count(COLUMN_NAME) cnt  from information_schema.columns
where table_schema='test' group by TABLE_SCHEMA,TABLE_NAME ;
3) dislaying all the views and their structure .
select TABLE_SCHEMA,TABLE_NAME from information_schema.views where table_Schema like 'test'
select TABLE_SCHEMA,TABLE_NAME,VIEW_DEFINITION from information_schema.views where table_Schema like 'test' \G
4. display all the triggers and their structure
select TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_Timing,ACTION_STATEMENT from information_Schema.triggers \G
 select TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_MANIPULATION,EVENT_OBJECT_TABLE,ACTION_Timing from information_Schema.triggers;
5. indexes
select table_schema,table_name,index_name,index_type,column_name from information_schema.STATISTICS where table_schema='test';
select table_schema,table_name,COLUMN_NAME,index_name,SEQ_IN_INDEX,NON_UNIQUE from information_schema.STATISTICS where table_schema='test';
select table_schema,table_name,COLUMN_NAME,group_concat(index_name),count(index_name) from information_schema.STATISTICS where table_schema='test' group by table_schema,table_name,COLUMN_NAME;
6.) routines
select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_BODY from information_Schema.routines;
select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,ROUTINE_BODY,Routine_definition from information_Schema.routines;
7.) Primary key and their foreign  keys
SELECT CONCAT( table_name, '.', column_name, ' -> ', referenced_table_name, '.', referenced_column_name ) AS list_of_fks  FROM INFORMATION_SCHEMA.key_column_usage  WHERE referenced_table_schema = 'test' AND referenced_table_name IS NOT NULL  ORDER BY table_name, column_name;

8) count all the table and column count and record count
mysqlshow -u kiran -pkiran@123  test --count
mysqlshow -u kiran -pkiran@123  --count