Ask Daddy!! Tech Blog

Tuesday, October 31, 2017

huge pages in mysql

https://www.linkedin.com/pulse/configuring-huge-pages-mysql-server-red-hat-linux-juan-soto/

Sunday, October 29, 2017

cat /duplicate_request_delete.sh
#!/bin/sh
DB="somedb"
DBUSER="someuser"
DBPWD="somepassword"
#DBHOST="localhost"
DATAPATH="somepath"
################################################# End configurable settings
query () {
COUNTRES=`$MYSQL -e "$SQL" $DB 2>/tmp/$$`
}
query_time () {
COUNTRES_TIME=`$MYSQL -e "$SQL1" $DB 2>/tmp/$$`
}

# Define the base mysql command.
MYSQL="mysql -u$DBUSER -p$DBPWD --batch --skip-column-names"
#MYSQL="mysql --batch --skip-column-names"
query "$MYSQL"
query_time "$MYSQL"
######################Total number of rows###############################################
SQL1="select CURRENT_TIMESTAMP FROM DUAL;"
query_time "$SQL1"
SQL="select count(*) FROM duplicate_request;"
query "$SQL"
echo "Time=$COUNTRES_TIME" > $DATAPATH/mysql_duplicate_request_output.txt
echo "Total Rows=$COUNTRES" >> $DATAPATH/mysql_duplicate_request_output.txt
echo "-------------------------------------------------------" >> $DATAPATH/mysql_duplicate_request_output.txt

######################CHECK THE NUMBER OF ROWS PRESENT BEFORE DELETION###############################################
SQL1="select CURRENT_TIMESTAMP FROM DUAL;"
query_time "$SQL1"
SQL="select count(*) FROM duplicate_request WHERE create_time < DATE_SUB(NOW(),INTERVAL 24 HOUR);"
query "$SQL"
echo "Time=$COUNTRES_TIME" >> $DATAPATH/mysql_duplicate_request_output.txt
echo "Number of rows more than 24 hrs before deletion=$COUNTRES" >> $DATAPATH/mysql_duplicate_request_output.txt
echo "-------------------------------------------------------" >> $DATAPATH/mysql_duplicate_request_output.txt
########################DELETION OF RECORDS###########################################
SQL1="select CURRENT_TIMESTAMP FROM DUAL;"
query_time "$SQL1"
SQL="DELETE FROM duplicate_request WHERE create_time < DATE_SUB(NOW(),INTERVAL 24 HOUR);"
query "$SQL"
#echo "Time=$COUNTRES_TIME" >> $DATAPATH/mysql_duplicate_request_output.txt
#echo "Number of rows after deletion=$COUNTRES" >> $DATAPATH/mysql_duplicate_request_output.txt

########################LATEST NUMBER OF RECORD###########################################
SQL1="select CURRENT_TIMESTAMP FROM DUAL;"
query_time "$SQL1"
SQL="select COUNT(*) FROM duplicate_request WHERE create_time < DATE_SUB(NOW(),INTERVAL 24 HOUR);"
query "$SQL"
echo "Time=$COUNTRES_TIME" >> $DATAPATH/mysql_duplicate_request_output.txt
echo "Number of rows less than 24 hours=$COUNTRES" >> $DATAPATH/mysql_duplicate_request_output.txt
echo "-------------------------------------------------------" >> $DATAPATH/mysql_duplicate_request_output.txt
#cat $DATAPATH/mysql_duplicate_request_output.txt
cat $DATAPATH/mysql_duplicate_request_output.txt | mailx -s "TPA Duplicate Request Deletion Script from Enterprise Production server" -S smtp=someip -S from="somemailid@somedomain.com" somemailid@somedomain

Wednesday, October 18, 2017

Daily backup script

[mysql@localhost]$ cat mysqldaily.sh
#!/bin/bash
# direcory initiations
BACKUP="/backup/"
ICR="/backup/ICR"
# Create non existing directories
[ ! -d $BACKUP ] && mkdir -p $BACKUP
[ ! -d $ICR ] && mkdir -p $ICR

dailyback(){
[ ! -d $ICR/$(date +%F) ] && mkdir -p $ICR/$(date +%F)
    #cd /var/lib/mysql/
    find /var/lib/mysql/ -mmin -60 | grep ssgbin-log.* >> filelist
    for f in $(cat filelist)
      do
        rsync -avh "$f" $ICR/$(date +%F)
      done
    rm -rf filelist
    #scp $ICR/$(date +%b)/$(date +%D) backup@remoteserverip:/
    #rsync -avzhe ssh $ICR/$(date +%b) backup@remoteserverip:$ICR/ >> $RSYCLOG
    find /backup/ICR/* -type d -ctime +10 -exec rm -rf {} \;
}
diskcheck(){
    rm output
    df -H | grep -vE '^Filesystem|tmpfs|cdrom' | awk '{ print $5 " " $1 }' | grep /dev/sda1 >> output
    DISK=$(cut -d'%' -f1 output)
    if [ $DISK -ge 6 ];
     then
       echo " Alert "
     fi
}
# Main function
main (){
           dailyback
#           diskcheck
}
main

Mysql weekly backup script

[mysql@localhost]$ cat mysqlweekly.sh
# It takes mysqldump all DBS on the servrer on Sunday
# All other days it takes backup of binary log files
#It keeps backups on same server. Once script is verified, scripts can be configured to copy backups into a remote# server.
#!/bin/bash
# direcory initiations
DBUSER='backupuser'
DBPASS='somepassword'
MYSQLCMD="/usr/bin/MySQL"
MYSQLDMP="/usr/bin/mysqldump"
FULLBACK="/backup/FULL"
STATUSFILE="/var/logs/mysql/stat.txt"
# Create non existing directories
[ ! -d $FULLBACK ] && mkdir -p $FULLBACK
mkdir -p /var/logs/mysql
# Function to take full backup of database
fullback(){
# Remove two weeks old backups.
find $FULLBACK -mtime +14 -type f -exec rm -f {} \;
rm -f /opt/logs/log.txt
# Store all default DBs into a variable
IGNORDB="
   performance_schema
   information_schema
   lost+found
   test
   mysql
   "
# Store all DBs on the server to a variable
   DBL=$(mysql -u$DBUSER -p$DBPASS -Bse 'show databases')
   for db in $DBL
   do
      CH="yes"
      # Check DB is default one
      if [ "$IGNORDB" != "" ]; then
         for i in $IGNORDB
            do
              if [ "$i" == "$db" ]; then
               CH="no"
              fi
         done
      fi
      if [ "$CH" == "yes" ]; then
                    $MYSQLDMP --lock-all-tables -u$DBUSER -p$DBPASS $db | gzip > $FULLBACK/$db$(date +"%m-%d-%y").sql.gz
            if [ "$?" -eq "0" ]
            then
              echo "$(date) $db backup for is OK" >> $STATUSFILE
            else
               echo " $(date) ##### WARNING: ##### $db backup failed" >> "$$STATUSFILE"
            fi
      fi
   done

}

main (){
            fullback
}
main

Mysqldump command

mysqldump -uroot -p --max_allowed_packet=1024M --single-transaction --events --routines --triggers dbname | gzip -9 > dbname-full-bkp-$(date +%Y-%m-%d-%H.%M.%S).sql.gz

Binlog sync script

0 * * * * /var/lib/mysql/scripts/binlog_bkp.sh > /var/lib/mysql/scripts/binlog_bkp.err
[MySQL@localhost ~]$ cat /var/lib/mysql/scripts/binlog_bkp.sh
binlog_dir=/data/mysql
backup_dir=/mysqlbackup/dump/binlog/
LOG_FILE=/tmp/binlog.log
binlog_format=mysqld-bin
mkdir -p ${backup_dir}
rsync -av ${binlog_dir}/${binlog_format}* ${backup_dir}/ |tee -a ${LOG_FILE}
if [ $? -ne 0 ]; then
mailx -s "BINLOG_BACKUP Failed in `hostname` " -r mysqlDL @somedomain.com mysqlDL @somedomain.com < ${LOG_FILE}
fi

Sunday, October 8, 2017

How MySQL deal with conflict Data

http://www.clusterdb.com/mysql-cluster/active-active-replication-performance-improvements-operational-enhancements-some-of-whats-available-in-the-new-mysql-cluster-7-4-1-dmr

http://www.oracle.com/us/products/mysql/mysql-cluster-ds-152857.pdf