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