Chinta Kiran's Blog: June 2011

Wednesday, 22 June 2011

Semi synchronous replication in mysq 5.5

Thursday, 16 June 2011

Masking the database from proddb and removing the definer clause after taking up and changing the DB Names

Masking the database from proddb and removing the definer clause after taking up and changing the DB Names

1.) Massking the database
2.) removing the definer clause
3.) Changing the DBname in the backup file

vi masking.sh

#!/bin/sh
mydate=`date '+%b%d'`
prefix="prd_"
user='kiran'
password='kiran'
port='3306'
# run the masking commands on proddb1
# take the backup, remove definer

mysql -u$user -p$password proddb1 -P$port << proddb1
set foreign_key_checks =0;
select max(id) into @cardid from table1 ;
select max(id) into @userid from user ;
set @cardid = @cardid + 100 ;
set @userid = @userid + 100 ;
update transaction_log   set Card_ID = Card_ID + @cardid, Second_Card_ID   = Second_Card_ID   + @cardid;
update transaction_log_details set Card_ID = Card_ID + @cardid;
update transaction_summary set Card_ID = Card_ID + @cardid, xyz =reverse(xyz);

### run the update commands you want to update
proddb1
mysql -u$user -p$password proddb2 -P$port << proddb2
set foreign_key_checks =0;
select max(id) into @accountid from table1 ;
set @accountid = @accountid + 200 ;
update table1 set id= id + @accountid ;
update table2 set account = account + @accountid;
update transaction set masked_number='XXXX';
update transaction_log set masked_number='XXXX';
##run the update command here
proddb2
cat > dumper.pl << "heredoc"
#!/usr/bin/perl
use strict;
use warnings;
use Getopt::Long qw(:config no_ignore_case );
my $replace = undef;
my $delete = undef;
my $help    = 0;
GetOptions (
   'replace|r=s' => \$replace,
   'delete|d'    => \$delete,
   'help|h'      => \$help,
) or help('unrecognized options');
help() if $help;
if ($delete and $replace) {
    help( 'you must choose EITHER "delete" OR "replace". Not both');
}
$delete = 1 unless $replace;
while (my $line = <STDIN>) {
    if ($delete) {
        $line =~ s{(/\*!\d+ )\s*definer\s*=\s*\S+}{$1}i;
    }
    elsif ($replace) {
        $line =~ s{(/\*!\d+\s+definer\s*=\s*)(\S+)}{$1 $replace}i;
    }
    print $line;
}
sub help {
    my ($msg) = @_;
    if ($msg) {
        print "*** $msg\n";
    }
    print "dump_filter - mysqldump filter \n",
          "(C) Giuseppe Maxia, 2009\n",
          "removes/changes DEFINER clauses from MySQL dumps\n",
          "USAGE: dump_filter [options]\n",
          "    -d|--delete    removes the DEFINER clauses\n",
          "    -r|--replace=s replaces every DEFINER clause with the \n",
          "                   new value provided\n",
          "    -h|--help      This text\n";
    exit(1);
}
heredoc
# backup the DB
mysqldump -u$user -p$password --databases proddb1 proddb2 --routines --single-transaction | perl dumper.pl --delete > all_backup.sql
# "sed" is used to replace the current DB name with "prd_" or any other word.
sed -i '/^CREATE DATABASE\|^USE/{
s/proddb1/'$prefix'proddb1/;
s/proddb2/'$prefix'proddb2/}' all_backup.sql
grep '^CREATE DATABASE' all_backup.sql | head -10

# all_backup.sql file from the current folder can be safely send to development Team

### if we want to replace the definer then we have to replace like this

perl /home/dumper.pl mailto:--replace='root@%60localhost%60'

Logging only failed queries using proxy

logging only failed queries using proxy

vi failed.query.lua

local log_file = '/home/mysql.log'
local fh = io.open(log_file, "a+")

function read_query(packet)
if string.byte(packet) == proxy.COM_QUERY then
query = string.sub(packet, 2)
proxy.queries:append(1, packet, {resultset_is_needed = true} )
return proxy.PROXY_SEND_QUERY
end
end

function read_query_result (inj)
local res = assert(inj.resultset)

-- if res.query_status == proxy.MYSQLD_PACKET_ERR then

if (res.query_status == proxy.MYSQLD_PACKET_ERR) or (res.warning_count > 0) then

local query = string.sub(inj.query, 2)
local err_code = res.raw:byte(2) + (res.raw:byte(3) * 256)
local err_sqlstate = res.raw:sub(5, 9)
local err_msg = res.raw:sub(10)

fh:write(string.sub(inj.query, 2), "\n")
fh:write(res.raw:sub(10), "\n")
fh:flush()

print("Query Received -", query)
print("Query Error code -", err_code)
print("Query Error Sqlstate -", err_sqlstate)
print("Query Error message -", err_msg)
print("Query warnings -", res.warning_count)

end
end

This is the command to run the proxy
sh /kiran/mysql/mysql-proxy-0.8.1-linux-rhel5-x86-64bit/bin/mysql-proxy --plugins=proxy
--proxy-lua-script=/kiran/mysql/mysql-proxy-0.8.1-linux-rhel5-x86-64bit/share/doc/mysql-proxy/failed-query.lua
>> /home/failed_query.log &

For more details the url is as follows:
http://dev.mysql.com/tech-resources/articles/proxy-gettingstarted.html

Wednesday, 15 June 2011

Steps for installing mysql in different ways

MySQL is a small, fast and highly configurable DBMS. It supports a number of different table fileformats and different engines
MySQL is not ACID compliant except for innodb engines.
To connect to remote mysql database mysql has different engine called as federated engine.
The different tools available in mysql are as follows:
mysqld - MySQL server daemon
safe_mysqld - Server process monitor
mysqlaccess - Tool for creating MySQL users
mysqladmin - Utility for mysql administering
mysqldump - This tool is used to take the backup of mysqldatabases
mysql - Command line interface to connect to MySQL
mysqlshow - List all MySQL database and the count

For installing mysql on linux machines we can install mysql in three different ways.
Of all the three methods "RPM Installation" is the easiest method
1.) Binary Installation
2.) source distribution
3.) RPM Installation

A.) steps for intalling Binary Installation.

1.) create mysql user and group.
# groupadd mysql
# useradd -g mysql mysql
# cd /usr

2. download the mysql
http://dev.mysql.com/downloads/mysql/5.1.html

3.) Then unzip the file
# gunzip < /usr/mysql-VERSION.tar.gz | tar xvf -
# ln -s full-path-to-mysql-VERSION mysql
# cd mysql
# scripts/mysql_install_db
# chown -R root .
# chown -R mysql data
# chgrp -R mysql .
# bin/mysqld_safe --user=mysql &

B.) steps for intalling Source distribution.

1.) create mysql user and group.
# groupadd mysql
# useradd -g mysql mysql

2. download the mysql
http://dev.mysql.com/downloads/mysql/5.1.html

3. unzip the file
gunzip < mysql-5.0.45.tar.gz | tar -xvf -

4. let say it is under folder (/data01/sources/mysql-5.0.45/)

cd /data01/sources/mysql-5.0.45/
To check options to configure, see
./configure --help
For older versions, enabling federated,
./configure --prefix=/data01/lamp/mysql --enable-thread-safe-client --with-federated-storage-engine
For latest versions, enabling federated is as below
./configure --prefix=/data01/lamp/mysql --enable-thread-safe-client –with-plugins=innobase,myisam,federated
Make
Make install

Follow the below steps to configure mysql as service.
cp /data01/sources/mysql-5.0.45/support-files/my-huge.cnf /etc/my.cnf
cp /data01/sources/mysql-5.0.45/support-files/mysql.server /etc/rc.d/init.d/mysql
cd /etc/rc.d/init.d/
chmod 755 mysql
Installing database for the “mysql” User
cd /data01/lamp/mysql
chown -R mysql .
chgrp -R mysql .
bin/mysql_install_db --user=mysql
chown -R root .
chown -R mysql var
bin/mysqld_safe --user=mysql &
To Configure mysql as service, follow the below steps
cd /usr/bin/
ln -s /data01/lamp/mysql/bin/mysql .
ln -s /data01/lamp/mysql/bin/mysql_config .
ln -s /data01/lamp/mysql/bin/mysqldump .
ln -sf -s /data01/lamp/mysql/bin/mysql .
cd /usr/lib/
ln -sf /data01/lamp/mysql/lib/mysql/lib* .
cd /usr/include/
mkdir mysql
cd /usr/include/mysql/
ln -sf /data01/lamp/mysql/include/mysql/* .

C.) steps for intalling using "RPM" Installation.

1.) create mysql user and group.
# groupadd mysql
# useradd -g mysql mysql

2. download the mysql
http://dev.mysql.com/downloads/mysql/5.1.html

3.) Let say you have donwloaded the following RPM (mysql-5.5.11 version).
MySQL-client-5.5.11-1.linux2.6.x86_64.rpm
MySQL-devel-5.5.11-1.linux2.6.x86_64.rpm
MySQL-server-5.5.11-1.linux2.6.x86_64.rpm
MySQL-shared-5.5.12-1.linux2.6.x86_64.rpm
perl-DBD-MySQL-4.018-1.wc.x86_64.rpm

4.) Run the above RPM packages you have downloaded.
The command is as follows:

rpm -iUh MySQL-devel-5.5.11-1.linux2.6.x86_64.rpm
rpm -iUh MySQL-shared-5.5.12-1.linux2.6.x86_64.rpm
rpm -iUh MySQL-client-5.5.11-1.linux2.6.x86_64.rpm
rpm -iUh MySQL-server-5.5.11-1.linux2.6.x86_64.rpm
rpm -iUh perl-DBD-MySQL-4.018-1.wc.x86_64.rpm

5.) if we want to check the rpm pacages has installed correctly

rpm -qa | grep -i mysql

MySQL-shared-5.5.12-1.linux2.6
perl-DBD-MySQL-4.018-1.wc
MySQL-devel-5.5.11-1.linux2.6
MySQL-server-5.5.11-1.linux2.6
MySQL-client-5.5.11-1.linux2.6

6.) check wheather the mysql is instance is working or not

/etc/init.d/mysql status

7.) start the mysql instance if not started.
/etc/init.d/mysql start.

8.) check wheather my.cnf exists or not in /etc/my.cnf ( if not exists copy the my.cnf file and fine tune the parameters)
locate my-huge
/usr/share/doc/MySQL-server-5.5.11/my-huge.cnf
cp /usr/share/doc/MySQL-server-5.5.11/my-huge.cnf /etc/my.cnf

9.) if my.cnf parameters have changed restart the mysql server.
/etc/init.d/mysql restart
/etc/init.d/mysql status

Tuesday, 14 June 2011

steps for removing definer clause whie taking backup

steps for removing the definer clause in mysqlbackup.

When we take the mysql backup we have some unwanted users in the definer clause.
We want to remove the definer clause in mysql.

1. Take the backup.

mysqldump -u kiran -pkiran --all-databases --single-transaction > mysqldump.sql

vi dumper.pl

#!/usr/bin/perl
use strict;
use warnings;
use Getopt::Long qw(:config no_ignore_case );

my $replace = undef;
my $delete = undef;
my $help    = 0;

GetOptions (
   'replace|r=s' => \$replace,
   'delete|d'    => \$delete,
   'help|h'      => \$help,
) or help('unrecognized options');

help() if $help;

if ($delete and $replace) {
    help( 'you must choose EITHER "delete" OR "replace". Not both');
}

$delete = 1 unless $replace;

while (my $line = <STDIN>) {
    if ($delete) {
        $line =~ s{(/\*!\d+ )\s*definer\s*=\s*\S+}{$1}i;
    }
    elsif ($replace) {
        $line =~ s{(/\*!\d+\s+definer\s*=\s*)(\S+)}{$1 $replace}i;
    }
    print $line;
}

sub help {
    my ($msg) = @_;
    if ($msg) {
        print "*** $msg\n";
    }
     print "dump_filter - mysqldump filter \n",
          "(C) Test Definer, 2009\n",
          "removes/changes DEFINER clauses from MySQL dumps\n",
          "USAGE: dump_filter [options]\n",
          "    -d|--delete    removes the DEFINER clauses\n",
          "    -r|--replace=s replaces every DEFINER clause with the \n",
          "                   new value provided\n",
          "    -h|--help      This text\n";
    exit(1);
}

3.) Remove the definer clause

cat mysqldump.sql | perl dumper.pl --delete > mysqldump-ver01.sql

Saturday, 11 June 2011

encryption and decryption in mysql

In mysql we can encrypt and decrypt the data in many ways.
some of the examples are as follows:

1.) Encode and decode in mysql

mysql> select ENCODE('guessme', 'kiran');
+----------------------------+
| ENCODE('guessme', 'kiran') |
+----------------------------+
| ÕD]10÷8                      |
+----------------------------+
1 row in set (0.00 sec)

mysql> select decode('ÕD]10÷8','kiran');
+---------------------------+
| decode('ÕD]10÷8','kiran')   |
+---------------------------+
| guessme                   |
+---------------------------+
1 row in set (0.00 sec)

#######################################################33

2.) AES_ENCRYPT and DES_ENCRYPT
mysql> create table app (user varchar(100),password varchar(100));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into app values('user,',AES_ENCRYPT('text1','samplekey'));
Query OK, 1 row affected (0.01 sec)

mysql> select * from app;
+-------+-------------------------+
| user | password                |
+-------+-------------------------+
| user, | QWâÃâºÂ¿1{3_pÃ¾yk        |
+-------+-------------------------+
1 row in set (0.00 sec)

mysql> select user, AES_DECRYPT(password,'samplekey') from app;

+-------+-----------------------------------+
| user | AES_DECRYPT(password,'samplekey') |
+-------+-----------------------------------+
| user, | text1                             |
+-------+-----------------------------------+
1 row in set (0.00 sec)

Steps for setting Master-Master Relationship in mysql

# setting up Master-Master Relationship.
Lest say we have two systems system1 AND system2
we need to set up the replication ( Master-Master replication).
We need to consider some of the basic points before setting mysql
1. We should be able to ping on both servers (from system1 to system2 and from system2 to system1)
2. we should also enable the port 3306 on both servers ( generally mysql runs on 3306 port).
3. system time should be same on system1 and system2. ( if it is different we should change the mysql time and should be same on both mysql ).
4. mysql server_id should be different.
5. Binary logging should be enable on both servers.
6. try to change the auto-increment-increment and auto-increment-offset in my.cnf for more details see on the following
steps.

1. install mysql on both servers on system1 and system2.

on system1 follow the following steps:
A) in my.cnf we need to add the following. ( changing server-id,auto_increment and auto_increment offset)
The file structure is as follows:
vi /etc/my.cnf
[...]
[mysqld]
server-id = 1
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
master-host = system2
master-user = slave
master-password = slave
log-bin = /var/log/mysql
log-bin = /var/log/mysql/mysql-bin.log
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
B.) restart the mysql
/etc/init.d/mysql restart
C.) connect to mysql:
mysq > GRANT REPLICATION SLAVE ON *.* TO 'slave' IDENTIFIED BY 'slave';
FLUSH PRIVILEGES;
quit;
D.) mysqldump -ukiran -pkiran --all-databases --single-transaction -R --master-data=1 > /home/kiran.chinta/system1-June10.sql
E.) copy the /home/kiran.chinta/system1-June10.sql to system2

ON system2 follow the following steps:
A.) in my.cnf we need to add the following. ( changing server-id,auto_increment and auto_increment offset)
The file structure is as follows:
[...]
server-id = 2
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 2
master-host = system1
master-user = slave
master-password = slave
log-bin= /var/log/mysql/mysql-bin.log
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
B.) restart the mysql
/etc/init.d/mysql restart
C.) restore the mysql which is taken from system1.

mysql -u kiran -pkiran < /home/kiran.chinta/system1-June10.sql
mysql> flush privileges;
to know the binary log position run the command as follows:
head -30 /home/kiran.chinta/system1-June10.sql
We will get the MASTER TO MASTER_LOG_FILE and MASTER_LOG_POS which is as follows:

-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000001', MASTER_LOG_POS=98;

D.) starting the slave
mysq> CHANGE MASTER TO MASTER_HOST='system1', MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
mysql> start slave;
mysql> show slave status \G
We need to carefully observe the three output "slave_IO and Slave_SQL" both should be YES. which are as follows

     Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            Seconds_Behind_Master: 0

E) mysql> show master status;
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     98   |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Go to system 2
run the following commands
mysql> mysq> CHANGE MASTER TO MASTER_HOST='system2', MASTER_USER='slave', MASTER_PASSWORD='slave', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=98;
mysql> start slave;
mysql> show slave status \G
We need to carefully observe the three output "slave_IO and Slave_SQL" both should be YES. which are as follows

     Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
            Seconds_Behind_Master: 0

Now the master master replication is complete:

some of the important parameter in my.cnf file are as follows:

replicate-do-db
replicate-ignore-db
binlog-do-db
replicate-ignore-table
skip-slave-start
log-slave-updates
log-expire-days

some of the important command in mysql are as follows:
-- skip the sql-statement when there is a bad query
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
--start and stop the repliction until specified binary log position:
mysql> START SLAVE UNTIL MASTER_LOG_FILE='bin-log.000010', MASTER_LOG_POS=130223;

For more details about the replication give below are the URL:
http://dev.mysql.com/doc/refman/5.5/en/replication.html
http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

Wednesday, 8 June 2011

Setting the timezone in different format in mysql

Steps for changing the timezone:

First we need to run the mysql_tzinfo_to_sql program, provided with the MySQL .
mysql_tzinfo_to_sql reads the operating system time zone files and generates SQL statements from them.
The SQL statements are then processed by mysql and loads the data int the time zone tables.

To run mysql_tzinfo_to_sql successfully,we should know the server machine's operating system
time zone files are stored; check for a directory with a name similar to /usr/share/zoneinfo.

1. First try to find the mysql_tzinfo_to_sql utility

locate mysql_tzinfo_to_sql
shell> /usr/bin/mysql_tzinfo_to_sql

2. Initially we need to insert the records in mysql tables "time_zone" and "time_zone_name".
The output is as follows:

mysql> select * from time_zone;
Empty set (0.00 sec)

mysql> select * from time_zone_name;
Empty set (0.00 sec)

3. For inserting the records in time_zone the command is as follows:

shell > mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u kiran -pkiran mysql

4.just make sure the records are inserted on the two tables.

mysql> select count(*) from mysql.time_zone;
+----------+
| count(*) |
+----------+
|     1690 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from mysql.time_zone_name;
+----------+
| count(*) |
+----------+
|     1690 |
+----------+

4.a) run the following command to know more about the data

select * from time_zone_transition_type;
select * from time_zone_transition;
select * from time_zone_name;
select * from time_zone;

5. Now say for example we want to change the mysql timezone to UTC.
for example the system timezone is in IST , but we want the mysqltime to be in UTC format.

we need to modify in my.cnf file as follows:

[mysqld]
default_time_zone=UTC

[mysqld_safe]
timezone=UTC

6. we need to restart the mysql.

6. connect to mysql and check the date.
we can see different time in mysql and systemtime

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2011-06-08 06:22:13 |
+---------------------+
1 row in set (0.01 sec)

mysql> exit
Bye
[root@hostname kiran.chinta]# date
Wed Jun 8 11:52:18 IST 2011

7. checking for mysql timezone ;

mysql> show variables like 'time%';
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| time_format   | %H:%i:%s   |
| time_zone     | UTC        |
| timed_mutexes | OFF        |
| timestamp     | 1307514250 |
+---------------+------------

mysql> SELECT CONVERT_TZ('2007-10-28 23:00:00', '-5:00', '+0:00');
+-----------------------------------------------------+
| CONVERT_TZ('2007-10-28 23:00:00', '-5:00', '+0:00') |
+-----------------------------------------------------+
| 2007-10-29 04:00:00                                 |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select CONVERT_TZ(now(), '+0:00', 'SYSTEM');
+--------------------------------------+
| CONVERT_TZ(now(), '+0:00', 'SYSTEM') |
+--------------------------------------+
| 2011-06-08 06:37:08                  |
+--------------------------------------+
1 row in set (0.00 sec)

To know more about the timezone given below are the URL

http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

Tuesday, 7 June 2011

generating federated tables dynamically

Generating federated tables dynamically.

1. writing the shell scripts fed1.sh

vi fed1.sh

#!/bin/sh
USER=kiran
DATABASE_NAME=datamodel
password1=kiran@123
# The default connection string to use
CONNECTION="mysql://username:secret@10.10.10.10:3306/test2"
# Get the list of table names
TABLE_LIST=`mysql -u$USER -p$password1 $DATABASE_NAME -e 'SHOW TABLES \G' | sed -n "/^Tables_in_${DATABASE_NAME}:/s/^.*: //p"`
#echo $TABLE_LIST
# For each table in the list
for TABLE in $TABLE_LIST; do
# Get the SHOW CREATE TABLE
mysqldump -u$USER -p$password1 --no-data $DATABASE_NAME $TABLE | sed "s!^) ENGINE=.*!) ENGINE=FEDERATED CONNECTION='${CONNECTION}/${TABLE}';!"
done

2) step run the fed.sh file

sh fed1.sh > fed-tables.sql

3) remove the unwanted lines

sed '/*!40/d' fed-tables.sql > fed-tables1.sql

4) execute the sql file

mysql -u kiran -pkiran databasename < fed-tables1.sql

Taking backup and restoring the db with different name

Taking the backukup

mysqldump --all-databases --single-transaction --master-data=1 > all-db-backup.sql
after the backup is complete
We need to rename the db for example the sakila db has to rename to test_sakila.

sed -i '/^CREATE DATABASE\|^USE/{
s/onedb/test_one/;
s/testdb/prd_test/;
s/prod_kiran/test_kiran/;
s/sakila/test_sakila/;
s/test/test_test/}' all-db-backup.sql

## then on the test server run the following command.
mysql -u kiran -pkiran < /home/kiran/all-db-backup.sql

Monday, 6 June 2011

Monitoring the Disk Space and sending Alerts on mysql database server.

Given below are some of the files to check the diskspace issue:

df -h
df -HP
du -hc /home/* --max-depth=1

## this command will give the files which are occupying more than 300MB space
find / -type f -size +300000k -exec ls -lh {} \; | awk '{ print $9 ": " $5 }'

# to monitor the disk space and sending alert mails
vi diskspace.sh

# change the path for output files
path='/home/kiran.chinta'
# alert email with hostname alias
myhostname=`hostname`
ADMIN="kiran.chinta@gmail.com"
# set alert level 80% as default if no user input found
ALERT=${1:-80}
mydate=`date '+%d %b %H:%M'`
df -HP | grep -vE '^Filesystem|tmpfs|cdrom' | awk '{ print $5 " " $1 }' | while read output;
do
echo $output
usep=$(echo $output | awk '{ print $1}' | cut -d'%' -f1 )
partition=$(echo $output | awk '{ print $2 }' )
if [ $usep -ge $ALERT ]; then
# the message that will be written to a file, mail, SMS, Pop-up and Twitter
# the word space is black listed by SMS gateway
mymessage="$(hostname) running out of disk $usep percent full of $partition as on $mydate"
#find / -type f -size +600000k -exec ls -lh {} \; | awk '{ print $9 ": " $5 }' >> $path/testdisk.txt
# write to a file and email
echo "$mymessage" > $path/testdisk.txt 2>> $path/testdisk_err.txt
echo "the following files are occupying more space" >> $path/testdisk.txt 2>> $path/testdisk_err.txt
find / -type f -size +600000k -exec ls -lh {} \; | awk '{ print $9 ": " $5 }' >> $path/testdisk.txt
echo "$mymessage" | mail -s "$myhostname disk full " $ADMIN
fi
done

### put the above one in cronjob this will check every hour and every day if the space is more than 85% full

# disk space
00 */12 * * * /bin/sh -xv /home/kiran.sh/diskspace.sh 85 1>/home/kiran.chinta/disk_alert_succ.txt 2>>/home/kiran.chinta/disk_alert_err.txt

Taking weekly backup using mysqldump and put on cronjob

The code is as follows:

vi mysqlbackup.sh

#!/bin/bash
### MySQL Server Login Info ###
MUSER="kiran"
MPASS="kiran"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
BAK="/home/kiran.chinta/mysqlbackup"
GZIP="$(which gzip)"
NOW=$(date +"%d-%m-%Y")
#NOW=`date +"%d-%m-%Y"`
dir=$BAK/$NOW
echo $dir
mkdir $dir
BAK=$dir
echo $NOW
##if we want to ignore some dbs
##DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases' | egrep -v "(Database|mysql|information_schema)")"
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for DB in $DBS
do
#FILE=$BAK/$DB.$NOW-$(date +"%T").sql
FILE=$BAK/$DB-$NOW.sql
echo $FILE
$MYSQLDUMP -u $MUSER -p$MPASS -h $MHOST -R $DB > $FILE
tar -czvf $dir.tar.gz $dir
#echo $db
done
#echo $MYSQLDUMP
#echo $MUSER
#mysqldump -u $MUSER -p$MPASS -h $MHOST -R $DB | $GZIP -9 > $FILE

Putting the backupjob in crontab on a weekly basis( run on every monday)

00 01 * * 1 sh -xf /home/kiran.chinta/dailybackup.sh > /home/kiran.chinta/weekly-backup.log 2>> /home/kiran.chinta/weekly-backup.err

Sunday, 5 June 2011

Login and logof triggers in mysql using init_connect.

mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP,
EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE,
SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, UPDATE ON *.* TO 'foo'@'x.x.x.1' IDENTIFIED BY 'bar';
The following command will start the connection logging.

mysql> SET GLOBAL init_connect='create table if not exists test.connect(db_name varchar(100), user_with_ip varchar(100),
user_permission varchar(100), connect_id varchar(100), connect_time datetime);
insert into test.connect select @@hostname as db_name, user() as user_with_ip, current_user() as user_permission, connection_id() as connect_id, now() as connect_time';
We can check if the initialization script is active.
mysql> show variables like '%connect%';

to see more about the init_connect give below is the urls:

http://planet.mysql.com/entry/?id=26710

Taking hot (online) backup using xtrabackup using perconal tools

Introduction

Percona XtraBackup is a tool for quickly taking backups of database machines and loading them onto new machines. It is much faster than taking a MySQL dump and importing the backup, but slower than using Rsync. However, unlike the Rsync method, XtraBackup can be used with a live production machine as the data source.

Instructions

1. Make sure that XtraBackup is installed on both machines:

which xtrabackup

/usr/bin/xtrabackup

* If it's not installed, run the following to install it:

wget http://www.percona.com/downloads/XtraBackup/XtraBackup-1.0/RPM/rhel5/xtrabackup-1.0-56.rhel5.x86_64.rpm

rpm -ihv xtrabackup-1.0-56.rhel5.x86_64.rpm

2. Create a folder to hold the backup:

mkdir /root/BACKUP

3.Take a backup from the current database:

/usr/bin/innobackupex-1.5.1 --slave-info --password=password /root/BACKUP

4. Wait for the backup to finish. When it finishes, a status message displays:

Backup completed OK\!

5. Identify the MySQL data directory. This is the datadir parameter in the MySQL configuration file:

grep datadir /etc/my.cnf

datadir=/var/lib/mysql/data

6. If there is space, move the MySQL data directory to a backup location. (Otherwise, delete it.)

mv data_dirdat_adir_OLD

* data_dir is the value of the datadir parameter you found earlier.

7. Create a new MySQL folder and copy the contents of the backup into it:

mkdir data_dir

mv /backup/backup_dir/* data_dir

* The backup_dir is the folder containing the backup, for example 2010-03-20_19-00-43.

8. Restore the backup using XtraBackup:

xtrabackup --defaults-file=/etc/my.cnf --use-memory=mem --prepare --password=password --target-dir=data_dir

* mem is the amount of memory to use. For machines with 96 GB of RAM, use 64G. For machines with 48 GB of RAM, use 32G.

* password is the MySQL root password

9. Run the same command again to create the ibdata and iblogfiles

xtrabackup --defaults-file=/etc/my.cnf --user-memory=mem --prepare --password=password --target-dir=data_dir

10. Fix the file attributes so that they belong to the mysql user

chown -R mysql:mysql data_dir

11. Start MySQL.

12. Set up replication again. You need the credentials and the binlog position on the master:

1. If the machine was previous replicating from the same master you can get the credentials from the old master.info file:

cat data_dir_old/master.info

Otherwise, you should already know the replication credentials.

2. The binlog position is in the xtrabackup_binlog_info file:

cat data_dir/xtrabackup_binlog_info

3. After you have the necessary information, start a MySQL session and set the master information:

change master to master_host='IP', master_user='user', master_password='password', master_log_file='log_file', master_log_pos=log_pos;

* IP is the internal IP address of the master

* user is the replication user

* password is the password for the replication user

* log_file is the binlog file

* log_pos is the binlog position on the master, for example 308760965.

4. Start the slave thread

start slave;

5. Wait for the machine to catch up on replication.

To know more about percona tools:

Given below are the urls:

http://www.percona.com/downloads/

Friday, 3 June 2011

compare the structure of two tables or two databases:

### compare the structures between two different databases

There are two ways to compare the structure of two databases.

one is by linux and other by stored procedure in mysql.

Method1:

diff <(mysqldump -hsystem -ukiran -pkiran test myinfo --skip-extended-insert) <(mysqldump -hsystem2 -ukiran -pkiran test myinfo --skip-extended-insert)
--side-by-side --suppress-common-lines --width=690 | more

method2:

USE test;
DROP PROCEDURE IF EXISTS comparetwodb;
DELIMITER |
CREATE PROCEDURE comparetwodb( vdb1 VARCHAR(64), vdb2 VARCHAR(64) )
BEGIN

DROP TEMPORARY TABLE IF EXISTS desc1,desc2;
CREATE TEMPORARY TABLE desc1
SELECT
    t1.table_schema,
    t1.table_name,
    t1.table_type,
    t1.engine,
    c1.column_name,
    c1.ordinal_position,
    c1.column_type,
    c1.column_default,
    c1.is_nullable,
    c1.column_key
FROM information_schema.tables t1
JOIN information_schema.columns c1 USING (table_schema,table_name)
WHERE t1.table_schema=vdb1
ORDER BY t1.table_name,c1.column_name;

CREATE TEMPORARY TABLE desc2
SELECT
    t1.table_schema,
    t1.table_name,
    t1.table_type,
    t1.engine,
    c1.column_name,
    c1.ordinal_position,
    c1.column_type,
    c1.column_default,
    c1.is_nullable,
    c1.column_key
FROM information_schema.tables t1
JOIN information_schema.columns c1 USING (table_schema,table_name)
WHERE t1.table_schema=vdb2
ORDER BY t1.table_name,c1.column_name;

SELECT
    TableName,column_name,MIN(SchemaName),table_type,engine,
    ordinal_position,column_type,column_default,is_nullable,column_key
FROM (
    SELECT
      a.table_schema AS SchemaName,a.table_name AS TableName,a.table_type,a.engine,
      a.column_name,a.ordinal_position,a.column_type,a.column_default,a.is_nullable,a.column_key
    FROM desc1 a
    UNION ALL
    SELECT
      b.table_schema AS SchemaName,b.table_name AS TableName,b.table_type,b.engine,
      b.column_name,b.ordinal_position,b.column_type,b.column_default,b.is_nullable,b.column_key
    FROM desc2 b
) AS tmp
GROUP BY TableName,table_type,engine,column_name,ordinal_position,column_type,column_default,is_nullable,column_key
HAVING COUNT(*) = 1
ORDER BY TableName,column_name,SchemaName;

DROP TEMPORARY TABLE desc1, desc2;

END |
DELIMITER ;

Call it as follows:

CALL comparetwodb('db1','db2');

Suppose if we want to compare the structure of two tables the query is as follows:

SELECT
MIN(TableName) AS 'Table',
column_name AS 'Column',
ordinal_position AS 'Position'
FROM (
SELECT
    'transaction_table' as TableName,
    column_name,
    ordinal_position
FROM information_schema.columns AS i1
WHERE table_schema='test' AND table_name='testtable'
UNION ALL
SELECT
    'transaction_log_table' as TableName,
    column_name,
    ordinal_position
FROM information_schema.columns AS i2
WHERE table_schema='test' AND table_name='testtable_log'
) AS tmp
GROUP BY column_name
HAVING COUNT(*) = 1
ORDER BY ordinal_position;