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