Recovering if Control file gets corrupted / missing

If any of control file is missing or get corrupted oracle database won't startup. There are two ways to recover from this error.

1. 
   select name from v$controlfile
   This will show all control files with the paths. shutdown database (shutdown immediate) and copy the available control file to the path from where it is lost or corrupted with the same name as above query is showing. After replacing control file, start your database.
2. If RMAN is configured and at least once backup of control file is taken from RMAN. If yes then connect to RMAN as following
   # rman connect target /
   Now once connected with RMAN issue any of below mentioned command.
   RMAN>restore controlfile from 'PATH and NAME of available Controlfile';
   
   RMAN>restore controlfile from autobackup;
   That's it. Just restart your database and monitor alert log file.
   
   Cheers!

How to resize/recreate /tmp parition in linux

Today I am going to explain about how to resize/recreate /temp partition in Linux. Why you need it? Many software like MySQL writes temporary files in /tmp partition and if /tmp is of less space then sometimes software are unable to function properly. Following are the steps I use to fix this problem:

1. umount -l /tmp
2. dd if=/dev/zero of=/usr/tmpDSK bs=1024 count=512000
3. mkfs.ext3 /usr/tmpDSK

Then mount this to /tmp

1.     mount -o loop,rw,noexec,nosuid /usr/tmpDSK /tmp

Now new /tmp partition is created by you may have to give proper permission to your applications to this new /tmp directory. In my case I had to give permission to MySQL and achieved using following commands;

1. usermod -G mysql /tmp
2. chmod 0770 -R /tmp

 That's it and you are done with it.

Shell script that FTP multiple files whose creation time is last 60 minutes

Sometimes you need to write a shell script which can FTP files(specific types) whose creation time is for example in last one hour. I've written following shell script which does the same. I hope it will help you.


#!/bin/bash
cd /opt/archive/
movedFile=`find . '*.csv' -cmin -60 `
HOST=192.168.0.15
USER='user'
PASSWD='xxxx'
for i in $movedFile; do
        echo "Uploading file $i ...."
        /usr/bin/ftp -n $HOST <         quote USER $USER
        quote PASS $PASSWD
        binary
        cd Records
       put $i
       quit
END_SCRIPT
mv $i /root/backup/
done

How to generate CSV in user's home directory through mysql

In this post I will try to explain how you can generate CSVs through MySQL queries in user's home directory so that only that user and MySQL has read/write access on that directory.  For example, there is a 'zaeem' with home directory '/home/zaeem/'. If you try to generate CSVs in /home/zaeem then MySQL gives Errcode: 13 that it doesn't have permission to write on this directory. To resolve this you need to create a group and add both users 'zaeem and 'mysq' to that group and change group of '/home/zaeem' to new group and giver permissions on the directory, restart MySQL and your issue will be resolved. Following are the commands to accomplish that.

1. useradd zaeem
2.  
3. groupadd csv
4. usermod -G csv  mysql
5. usermod -G csv  zaeem
6. chgrp -R csv zaeem/
7. chmod 0770 -R /home/zaeem/
8. service mysql restart
9. Login to MySQL and try to generate CSV in /home/zaeem

Hope this will help. 

Cheers!

Replication Monitor Shell Script

High Performance MySQL: Optimization, Backups, Replication, and MoreHigh Performance MySQLMySQL Transactions and Replication HandbookThere should be automatic way to initiate alerts if MySQL replication has stopped working because it is impossible for human being to monitor replication all the time and fix it if there is any issue. Following is the shell script which is very useful.




status=0
MasterHost="192.168.0.14"
SlaveHost="192.168.0.12"
emails="zaeem@zaeem.com" #multiple emails space separated
DownSubject="Replication status - Down"
GoodSubject="Replication status - Good"
GoodMessage="Everything regarding MySQL replication on $SlaveHost is good.\nHave a great day!\n\n"

#Grab the lines for each and use Gawk to get the last part of the string(Yes/No)
SQLresponse=`mysql -u root --password=password test -e "show slave status \G" |grep -i "Slave_SQL_Running"|gawk '{print $2}'`
IOresponse=`mysql -u root --password=password test -e "show slave status \G" |grep -i "Slave_IO_Running"|gawk '{print $2}'`
# Sometimes there is scheduled backup which takes backup by stopping slave but if this script finds slave stopped then it start it immediately. So when there is backup going on a file is created under tmp partition and this scripts keep checking it and if file exist it assume backup is going on so it doesn't start slave, otherwise it start slave.
FILE=/tmp/backup.back
if [ -f $FILE ];
then
echo "Backup is in progress"
echo -e "Backup is taking place on 192.168.0.12"
else

if [ "$SQLresponse" = "No" ]; then
       error="Replication on the slave MySQL server($SlaveHost) has stopped working.\nSlave_SQL_Running: No\n. \nTrying to start it\n"
       status=1
       mysqladmin --user=root --password=password start-slave
fi

if [ "$IOresponse" = "No" ]; then
      error="Replication on the slave MySQL server($SlaveHost) has stopped working.\nSlave_IO_Running: No\n. \nTrying to start it\n"
      status=1
      mysqladmin --user=root --password=password start-slave
fi

# If the replication is not working
if [ $status = 1 ]; then
      for address in $emails; do
               # echo -e $error | mail -s $DownSubject $address
                echo -e $error | mail -s "Replication is Down" $address
                echo "Replication down, sent email to $address"
      done
fi

# If the replication is working fine
if [ $status = 0 ]; then
for address in $emails; do
                echo -e $GoodMessage | mail -s $GoodSubject $address
                 echo "Replication is up, still sent email to $address"
      done
fi
fi


You can schedule this script to run after each five minutes to make sure that replication is up and running all the times.

High Performance MySQL: Optimization, Backups, Replication, and More
MySQL High Availability: Tools for Building Robust Data Centers
High Performance MySQL
MySQL Transactions and Replication Handbook

Command to calculate number of rows in a file

Sometimes there are so many files and you want to count number of rows in each file the a command or a shell script is handy to get it done otherwise its almost impossible to count number of rows in file. Following are some ways to calculate this.

1. wc -l *.csv (This command will count rows per csv file)
2. #!/bin/bash
   rows=0;
   for f in `*.CSV`;
     do
       let rows+=`wc $f | awk '{print $1}'`
   
     done
   
   echo "Total rows=$rows"

I hope this small tutorial is of help to you guys.

[Advanced ]Mysqldump, a database backup/restore utility

I will try to cover all possible methods to take backup and restore backup using mysqldump utility.


How to take database backup using mysqldump
mysqldump -u[username] -p[password] [database name] > dum_file_name.sql
e.g
mysqldump -uroot -ppassword mysql > mysql.sql

How to take backup of multiple databases at the same time

mysqldump -u[username] -p[password] [database 1 name]..[database 2 name]
e.g

mysql -uroot -ppassword DB1 DB2 DB3

How to take backup all the databases
mysqldump -u[username] -p[password] -all-databases > [dumpfile.sql]

How to take backup of specific table of a database

mysqldump -u[username] -p[password] [database name] [table Name] > dumpFIle.sql

e.g.

mysqldump -uroot -ppassword mysql user > user.sql

How to backup of table structures:

mysqldump -d -uroot -ppassword database01 > dumpfile.sql

How to restore backup
Database backup can be restored using mysqldump as well as mysql command.

mysql -u[user] -p[password] database name < dumpfile.sql

e.g.
mysql -uroot -ppassword mysql < mysql.sql

I hope you find this article helpful.

Suggestions are always welcome :)