Monitoring percona Error-log using shell script

vi /backups/alertlog_monotor.sh


#!/bin/bash
SERNAME=srv1.xyz.com
SERPUBIP=192.168.72.50
SERVER=`hostname`                      #### Sets the server name for the email
WEEKDAY=`date '+%w%H%M'`               #### Sets the number value of the day of the week
DATE_VAR=`date '+%Y_%m_%d'`

Error_log_loc=/data/mysql
egrep 'Error' $Error_log_loc/$SERNAME.err |sort -u >  $Error_log_loc/$SERNAME_ALERTLOG.txt
cat $Error_log_loc/$SERNAME.err >> $Error_log_loc/$SERNAME_Archived_ALERTLOG.txt

cat /dev/null > $Error_log_loc/$SERNAME.err

if [ -s "$Error_log_loc/$SERNAME_ALERTLOG.txt" ] ; then
cat $Error_log_loc/$SERNAME_ALERTLOG.txt | mail -s "URGENT -ERROR in Alert Log File for $SERNAME ($SERPUBIP) at `date` " ssdas@24x7-itsupport.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

if [[ $WEEKDAY -eq 00015 ]]; then
        mv $Error_log_loc/$SERNAME_Archived_ALERTLOG.txt $Error_log_loc/$SERNAME_Archived_ALERTLOG_${DATE_VAR}.log
        gzip $Error_log_loc/$SERNAME_Archived_ALERTLOG_${DATE_VAR}.log
fi

exit 0

:wq (save & exit)

Now Schedule the above script in crontab for every 15mins.

crontab -l

*/15 * * * * /backups/alertlog_monotor.sh

Oracle Alert log monitoring using shell script

Vi /backups/alertlog_monotor.sh

#!/bin/bash
export ORACLE_SID=kmioradb
export ORAENV_ASK=NO
. /home/oracle/.bash_profile
SERNAME=oradb.kminfosystems.com
SERPUBIP=192.168.72.50
SERVER=`hostname`                      #### Sets the server name for the email
WEEKDAY=`date '+%w%H%M'`               #### Sets the number value of the day of the week
DATE_VAR=`date '+%Y_%m_%d'`

Alert_log_loc=/u01/app/oracle/diag/rdbms/kmioradb/kmioradb/trace/
# Check for the existence of ORA- in the alert log and email/page on error
egrep 'ORA-|error|TNS' $Alert_log_loc/alert_$ORACLE_SID.log |sort -u >  $Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt  #### Output file with ORA- errors
cat $Alert_log_loc/alert_$ORACLE_SID.log >> $Alert_log_loc/archived_alert_$ORACLE_SID.log
cat /dev/null >  $Alert_log_loc/alert_$ORACLE_SID.log

if [ -s "$Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt" ] ; then
cat $Alert_log_loc/$ORACLE_SID_OUT_ALERTLOG.txt | mail -s "URGENT -ERROR in Oracle Alert Log File for $SERNAME ($SERPUBIP) at `date` " soumya@gmail.com
fi

# Weekly alert log datestamp and compress (Sunday 00:15)

if [[ $WEEKDAY -eq 00015 ]]; then
        mv $Alert_log_loc/archived_alert_${ORACLE_SID}.log $Alert_log_loc/archived_alert_${ORACLE_SID}_${DATE_VAR}.log
        gzip $Alert_log_loc/archived_alert_${ORACLE_SID}_${DATE_VAR}.log
fi

exit 0

:wq (save & exit)


Now Schedule the above script in crontab for every 15mins.

crontab -l

*/15 * * * * /backups/alertlog_monotor.sh

How to migrate mysql users from one server to another?

Step 1: Get a List of MySQL Users from the source server

[root@db01 tmp]# mysql -B -N -uroot -p -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql > /tmp/mysql_all_users.txt
Enter password: redhat
[root@db01 tmp]# cat /tmp/mysql_all_users.txt
'CitLivehappy'@'%'
'brightonlands'@'%'
'brochure'@'%'
'carver'@'%'
'CitLivehappy'@'localhost'
'brightonlands'@'localhost'
'brochure'@'localhost'
'carver'@'localhost'


Step 2:- Obtain a List of User Privileges

Fire the next command in the terminal to get the privileges:
[root@db01 tmp]# cd /tmp/
[root@db01 tmp]# while read line; do mysql -B -N -uroot -predhat -e "SHOW GRANTS FOR $line"; done < mysql_all_users.txt > mysql_all_users_sql.sql

Basically, the above command reads every individual line in mysql_all_users.txt and tries to get privileges for each user and paste it on mysql_all_users_sql.sql file.
This file will contain the SQL queries to obtain users along with their privileges.

[root@db01 tmp]# cat mysql_all_users_sql.sql
GRANT USAGE ON *.* TO 'CitLivehappy'@'%' IDENTIFIED BY PASSWORD '*1983DE33EA67561F7C46D591CC6F15FD7BF1E'
GRANT ALL PRIVILEGES ON `Cit-Live-bewealthynhappy`.* TO 'CitLivehappy'@'%'
GRANT ALL PRIVILEGES ON `cit_cvbflive`.* TO 'CitLivehappy'@'%'
GRANT USAGE ON *.* TO 'brightonlands'@'%' IDENTIFIED BY PASSWORD '*69DA4F2138FA03F66C29ACDCD7FED678FA'
GRANT ALL PRIVILEGES ON `Cit-Live-brightonlandscaping`.* TO 'brightonlands'@'%'
GRANT USAGE ON *.* TO 'brochure'@'%' IDENTIFIED BY PASSWORD '*EABF42F3B310313FD230662388030CA895BEB'
GRANT ALL PRIVILEGES ON `Cit-Sub-e-brochure`.* TO 'brochure'@'%'
GRANT USAGE ON *.* TO 'carver'@'%' IDENTIFIED BY PASSWORD '*682687FC5D596EB5B7CF4002EDC1ADBE3'
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding-blog`.* TO 'carver'@'%'
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding`.* TO 'carver'@'%'
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia-blog`.* TO 'carver'@'%'
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia`.* TO 'carver'@'%'

We can see from mysql_all_users_sql.sql that all the users information along with the privileges and the script is almost ready.
Only thing the above script missing is a ";" at the end of every sql command.

We will add ";" at the end of each sql command
[root@db01 tmp]# sed -i 's/$/;/' mysql_all_users_sql.sql

[root@db01 tmp]# cat mysql_all_users_sql.sql
GRANT USAGE ON *.* TO 'CitLivehappy'@'%' IDENTIFIED BY PASSWORD '*1983DE33EA6756C9BE6591CC6F15FD7BF1E';
GRANT ALL PRIVILEGES ON `Cit-Live-bewealthynhappy`.* TO 'CitLivehappy'@'%';
GRANT ALL PRIVILEGES ON `cit_cvbflive`.* TO 'CitLivehappy'@'%';
GRANT USAGE ON *.* TO 'brightonlands'@'%' IDENTIFIED BY PASSWORD '*69DA4F2138FA03F66C29ACDCD6E99678FA';
GRANT ALL PRIVILEGES ON `Cit-Live-brightonlandscaping`.* TO 'brightonlands'@'%';
GRANT USAGE ON *.* TO 'brochure'@'%' IDENTIFIED BY PASSWORD '*EABF42F3B310313FD23CA750C895BEB';
GRANT ALL PRIVILEGES ON `Cit-Sub-e-brochure`.* TO 'brochure'@'%';
GRANT USAGE ON *.* TO 'carver'@'%' IDENTIFIED BY PASSWORD '*682687FC5D596EB5B7CF4002EDC1BBE3';
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding-blog`.* TO 'carver'@'%';
GRANT ALL PRIVILEGES ON `Cit-Live-carverwelding`.* TO 'carver'@'%';
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia-blog`.* TO 'carver'@'%';
GRANT ALL PRIVILEGES ON `Cit-Live-carverengindia`.* TO 'carver'@'%';

Now add "flush privileges" to reload the grant tables.
[root@server1 tmp]# echo "flush privileges;" >> mysql_all_users_sql.sql

Step 3: Now lets import the sql file into new server.

Transfer this sql file into destination(new) server and fire up the below command.

[root@db02 tmp]# mysql -u root -ppassword < mysql_all_users_sql.sql