Mysql- Master - Slave Replication setup.

Below steps should be done in same outage. Take Master server down and take backup and restore to the slave side to make sure they both are look-alike as per Data.


1) Add below parameters in /etc/my.cnf for Master-side Mysql Instance and for Slave-side Mysql Instance repectively.

Master-side Configuration:
# To enable MySQL replication, you need to enable binary logging.

sync_binlog=1  # enable "1" if replication is used for master else comment this to disable
binlog_checksum = NONE
binlog_format = STATEMENT
log-bin=/mysql/log/hostname-bin.log
log-bin-index=/mysql/log/hostname-bin.index

Slave-side Configuration:

skip-slave-start   # it will not start replication automatically when mysql is started.
relay-log=/mysql/log/slave-relay-bin
relay-log-index=/mysql/log/slave-relay-bin.index


2) stop mysql on Master side and Slave side.
systemctl stop mysqld

3) start mysql on Master side and Slave side.
4) On Master-Side:
   create user 'repl_user'@'Slave-IP' identified by 'Password#';
   grant replication slave on *.* to 'repl_user'@'Slave-IP' identified by 'Password#';
   flush privileges;
   FLUSH TABLES WITH READ LOCK;
   show variables like 'sql_log_bin';
   set sql_log_bin=0;  --- to disable binary logging    we are doing this to disable replication for sometime till we setup slave with master backup and config etc.
   -- when ready to run command on step 5 on Slave-side
   unlock tables;
   reset master;
   show master status\G; --- note down the details for below command on slave side.
 
     
5) On Slave_side:
CHANGE MASTER TO MASTER_HOST='MAster-IP',MASTER_USER='repl_user',MASTER_PASSWORD='Password#',MASTER_LOG_FILE='hostname-bin.000001',MASTER_LOG_POS=50;
start slave;
show slave status\G;
show processlist\G; ---- check and see if you see replication process is running on both sides.

6) create a dummy table(and insert rows) on Master-Side and see if it got created on Slave-side.

MySQL - Session Alert script

Below scripts are needed to find the Current Sessions live in Mysql instance:


Two sql scripts you need in the same folder as shell script:- which are in RED color in the script.

1) session_count.sql

tee ./logs/session.log
select count(*) from information_schema.processlist;
notee;

2) session_details.sql

tee ./logs/session_details.html
select user,SUBSTR(HOST,1,INSTR(HOST,':')-1) as "Host",db,command,state,count(*) as "Number of sessions" from information_schema.processlist group by user,SUBSTR(HOST,1,INSTR(HOST,':')-1),db,command,state;
notee;

Code for the Shell Script:-

**************************************************

#!/bin/ksh

#########################################################################
# PROGRAM NAME: session_count.sh   for MySQL
#
# USAGE: ./session_count.sh
#
# Siva
#########################################################################

COMMAND=$0
PGM=${COMMAND##*/}
DATE=$(date "+%Y%m%d%H%M")
hostname=`hostname -s`
MAILTO=Name@domain.com
PWD=/scripts/sessions  # put script dir path if you want to run from "crontab"
repdir=$PWD
LOG=$repdir/logs

if [ ! -d $LOG ]; then
mkdir  $LOG
else
rm $LOG/session.log
if [ -f $LOG/session_details.html ]; then
rm $LOG/session_details.html
fi
fi

####### ******************** Below list of changes are req:

conf_file=/etc/my.cnf
BACKUP_USER=root
BACKUP_PASS=`cat /oracle/.orapasswd/.backupusr`

Vald_sess_C=1  ---- Set the Valid sessions number for the mark
log_ret=15

Logfile=mysql_Session_"$hostname"_$DATE.log
echo " started $DATE" >>$LOG/$Logfile

#### Changes are done:

####### ******************** run Active file:

if [ -f $LOG/bkp_active_file ]
 then
   echo "previous mysql session_count.sh script is still running. confirm before running again" | mailx -s "Mysql-Session Status: $hostname - Failed to execute" $MAILTO
   exit 0
fi

###

touch $LOG/bkp_active_file

mysql -Ns -u root -p$BACKUP_PASS < $repdir/session_count.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1

####### ******************** Check the session count:

Check_Sess_C=`cat $LOG/session.log`

if [[ $Check_Sess_C > $Vald_sess_C ]]
then
mysql -H -u root -p$BACKUP_PASS < $repdir/session_details.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1

####### ******************** Email Output to Mail:

( echo "Subject: AUTO-Gen - HOST: $hostname - MySQL Sessions are High - Details: "
  echo "To: OracleDBAs@unfi.com"
  echo "From:  <$hostname> "
   echo "MIME-Version: 1.0"
   echo "Content-Type: text/html; charset=US-ASCII"
echo "<html>"
echo "<body>"
   echo "<font size="+2">" "<font color="red">"
   echo "<u>"
   echo "Action Needed: Mysql sessions are high - Count - $Check_Sess_C ."
   echo "</u>"
   echo "<br/>"
   echo "<font size="-1">" "<font color="black">"
   echo "<pre>"
   cat $LOG/session_details.html
   echo  "<pre>"
   echo "Successful end of $repdir/session_count.sh"
echo "</body>"
echo "</html>"
 ) | /usr/sbin/sendmail $MAILTO

### End of Email

fi

####### ******************** Purge Log directory:

if [ ! -d $LOG ]; then
mkdir  $LOG
else
/usr/bin/find $LOG -type f -mtime +$log_ret -exec rm -f {} \;
fi

####### ******************** Remove run active file:

if [ -f $LOG/bkp_active_file ]
 then
rm $LOG/bkp_active_file
echo " removed run-active file" >> $LOG/$Logfile
fi

exit

### All steps are Done.

*********************************************************************

Hope this helps.................................................

virtual indexes

Thought this would be  handy in testing the performance. By creating virtual indexes in a session you can check whether the performance of the bad sql can be improved or not before we implement it permanently.
this is feature is available from 11g.

ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

create index vir_idx1 on Table_1 (col_1,col_2,col_3) nosegment;

once the Virtual Index is created run the bad sql and check the plan for the performance boost. if this work then create an index permanently.

Hope this helps.....