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

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

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

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