huge pages in mysql

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

Delete duplicate request

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

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

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

script using MEB

cat MEB_BKP.sh
#!/bin/bash
#########################################################################################################################
#  This script is designed to Take MEB of MySql Databases.
#  Author :
#  Description : Backup using MEB
#  Pre Req :
#  Usage   :
#  History : Created :
#########################################################################################################################
#########################################################################################################################
# Reserved for History and updates
#
#
#
#
#
#########################################################################################################################
BDATE=`date +%d%m%y`
DATE=`date +%d%m%y_%H%M`
. $HOME/scripts/variables.env
PATH=$MYSQL_HOME/bin:$PATH:.
IP=`hostname -i`
HOST=`hostname`
STMAIL="alert@mydomain.com"
DUMP_DIR="$BKPDIR/PHYBKP"
CR_DUMP_DIR="${DUMP_DIR}/${BDATE}"
LOGDIR="$BKPDIR/PHYBKPLOG"
mkdir -p $DUMP_DIR $LOGDIR
LOG_FILE="${LOGDIR}/mysql_hot_bkp_${DATE}.log"
echo "Script started at ${DATE}">>${LOG_FILE}
if [ -d ${CR_DUMP_DIR} ];
        then
        echo "executing Incremental backup script at ${DATE}">>${LOG_FILE}
        START_DATE=`date +%d%m%y_%H%M`
        $MYSQL_HOME/bin/mysqlbackup --defaults-file=${MYSQL_HOME}/my.cnf --socket=$SOCK -u${RT_USR} -p${RT_PASSWD}  \
        --incremental --incremental-base=dir:${DUMP_DIR}/${BDATE} \
        --incremental-backup-dir=${CR_DUMP_DIR}/${DATE}_INC backup
        STATUS=`tail -100 ${CR_DUMP_DIR}/${DATE}_INC/meta/*inc_backup.log|grep "mysqlbackup completed OK"|wc -l`
        END_DATE=`date +%d%m%y_%H%M`
                if [ $STATUS -ge 1 ];
                        then
                        echo "Database $IP backed up successfully at $END_DATE ">>${LOG_FILE}
                        echo "******************************************************************" >> ${LOG_FILE}
                        $MYSQL_HOME/bin/mysql -u${BKP_USR} -p${BKP_PASSWD} -h ${BKP_SERVER} -e "use backuplog;insert into meb_bkp(IP_Address,host_name,dump_dir,Start_Time,End_Time,Status) values('$IP','$HOST','${CR_DUMP_DIR}/${DATE}_INC backup','$START_DATE','$END_DATE','Success');"
                else
                        echo "Backup of database $IP failed!!! Kindly check">>${LOG_FILE}
                        echo "******************************************************************" >> ${LOG_FILE}
                        $MYSQL_HOME/bin/mysql -u${BKP_USR} -p${BKP_PASSWD} -h ${BKP_SERVER} -e "use backuplog;insert into meb_bkp(IP_Address,host_name,dump_dir,Start_Time,End_Time,Status) values('$IP','$HOST','${CR_DUMP_DIR}/${DATE}_INC backup','$START_DATE','$END_DATE','Failed');"
                fi
else
        echo "executing Base backup script at ${DATE}">>${LOG_FILE}
        START_DATE=`date +%d%m%y_%H%M`
        $MYSQL_HOME/bin/mysqlbackup --socket=$SOCK -u${RT_USR} -p${RT_PASSWD} --backup-dir=${CR_DUMP_DIR} backup-and-apply-log
        STATUS=`tail -100 ${CR_DUMP_DIR}/meta/*backup_apply_log.log|grep "mysqlbackup completed OK"|wc -l`
        END_DATE=`date +%d%m%y_%H%M`
                if [ $STATUS -ge 1 ];
                        then
                        echo "Database $IP backed up successfully at $END_DATE ">>${LOG_FILE}
                        echo "******************************************************************" >> ${LOG_FILE}
                        $MYSQL_HOME/bin/mysql -u${BKP_USR} -p${BKP_PASSWD} -h ${BKP_SERVER} -e "use backuplog;insert into meb_bkp(IP_Address,host_name,dump_dir,Start_Time,End_Time,Status) values('$IP','$HOST','${CR_DUMP_DIR}','$START_DATE','$END_DATE','Success');"
                else
                        echo "Backup of database $IP failed!!! Kindly check">>${LOG_FILE}
                        echo "******************************************************************" >> ${LOG_FILE}
                        $MYSQL_HOME/bin/mysql -u${BKP_USR} -p${BKP_PASSWD} -h ${BKP_SERVER} -e "use backuplog;insert into meb_bkp(IP_Address,host_name,dump_dir,Start_Time,End_Time,Status) values('$IP','$HOST','${CR_DUMP_DIR}','$START_DATE','$END_DATE','Failed');"
                fi
echo "CHECKING SUCESS/FAILURE" >> ${LOG_FILE}
fi
find ${DUMP_DIR}/* -maxdepth 1 -type d -mtime +1 -exec rm -r "{}" \;


===========================================


cat variables.env
MYSQL_HOME=/mysqldata/mysqlbinary/mysql
SOCK='/mysqldata/data/mysqld.sock'
BKPDIR=/mysqlbackup
RT_USR=root
RT_PASSWD='somepassword'
BKP_USR='backupuser'
BKP_PASSWD='somepassword'
BKP_SERVER='backupserverip'

Script using mysqldump

cat backup_latest.sh
#!/bin/bash
#########################################################################################################################
#  This script is designed to Take backup of MySql Databases.
#  Author :
#  Description : Backup using mysqldump
#  Pre Req :
#  Usage   :
#  History : Created :
#########################################################################################################################
#########################################################################################################################
# Reserved for History and updates
#
#
#
#
#
#########################################################################################################################
DATE=`date +%d%m%y_%H%M`
mkdir -p $HOME/scripts
chmod -R 755 $HOME/scripts
. $HOME/scripts/newvariables.env
PATH=$MYSQL_HOME/bin:$PATH:.
IP=`/sbin/ifconfig | grep inet | head -1 | awk -F ":" '{print $2}' | awk '{print $1}'`
HOST=`hostname`
STMAIL="abc@domain.com"
mkdir -p ${DUMP_DIR}
mkdir -p ${DUMP_DIR}/${DATE}
mkdir -p ${LOGDIR}
CHKSOCK=`ls -ltr $SOCK|grep -iv such|wc -l`
CR_DUMP_DIR="${DUMP_DIR}/${DATE}"
LOG_FILE="${LOGDIR}/Jio_mysql_backup_${DATE}.log"
touch $LOG_FILE
  if [ "${BKP_SERVER}" = "" ] ; then
    echo "ERROR: 'BKP_SERVER' field is null.  Exiting..." >> $LOG_FILE
    exit 1
  elif [ "${USR}" = "" ] ; then
    echo "ERROR: 'USR' field is null.  Exiting..." >> $LOG_FILE
        exit 1
  elif [ "${PWD}" = "" ] ; then
    echo "ERROR: 'Password' field is null.  Exiting..." >> $LOG_FILE
        exit 1
  elif [ "${USR}" = "" ] ; then
    echo "ERROR: 'USR' field is null.  Exiting..." >> $LOG_FILE
        exit 1
 elif [ ! -d "$DUMP_DIR" ]; then
    echo "ERROR: 'DUMP_DIR: ' does not exist. Exiting..." >> $LOG_FILE
        exit 1
 elif [ ! -d "$LOGDIR" ]; then
    echo "ERROR: 'LOGDIR: ' does not exist. Exiting..." >> $LOG_FILE
        exit 1
 elif [ $CHKSOCK -le 0 ]; then
        echo "Script started at ${DATE}">>${LOG_FILE}
        DBLIST=`$MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -e "show databases" | grep -vE '^Databa|schema$|^test|found$'`
        $MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -e "show databases"| grep -vE '^Databa|schema$|^test|found$' >> ${LOG_FILE}
        for db in ${DBLIST}
        do
        START_DATE=`date +%d%m%y_%H%M`
        echo "******************************************************************" >> ${LOG_FILE}
        echo "Starting Backup of $db at $START_DATE" >> ${LOG_FILE}
        $MYSQL_HOME/bin/mysqldump -u${USR} -p${PWD} --single-transaction --triggers --routines --events $db > ${CR_DUMP_DIR}/${db}-${START_DATE}.sql
        echo "DUMP_FILE" for $db is ${CR_DUMP_DIR}/${db}-${START_DATE}.sql.gz >> ${LOG_FILE}
        STATUS=`tail -100 ${CR_DUMP_DIR}/${db}-${START_DATE}.sql|grep "Dump completed"|wc -l`
        END_DATE=`date +%d%m%y_%H%M`
        gzip ${CR_DUMP_DIR}/${db}-${START_DATE}.sql
        DUMP="${CR_DUMP_DIR}/${db}-${START_DATE}.sql.gz"
                if [ $STATUS -ge 1 ];
                        then
                        echo "Database $db backed up successfully at $END_DATE ">>${LOG_FILE}
                        echo "******************************************************************" >> ${LOG_FILE}
                        $MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -h ${BKP_SERVER} -e "use backuplog;insert into nosql_backup(IP_Address,host_name,Db_Name,dump_file,Start_Time,End_Time,Status) values('$IP','$HOST','${db}','$DUMP','$START_DATE','$END_DATE','Success');"
                else
                        echo "Backup of database $db failed!!! Kindly check">>${LOG_FILE}
                        echo "******************************************************************" >> ${LOG_FILE}
                        $MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -h ${BKP_SERVER} -e "use backuplog;insert into nosql_backup(IP_Address,host_name,Db_Name,dump_file,Start_Time,End_Time,Status) values('$IP','$HOST','${db}','$DUMP','$START_DATE','$END_DATE','Failed');"
                fi
        done
elif [ $CHKSOCK -ge 1 ]; then
        echo "Script started at ${DATE}">>${LOG_FILE}
        DBLIST=`$MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -S$SOCK -e "show databases" | grep -vE '^Databa|schema$|^test|found$'`
        $MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -S$SOCK -e "show databases" >> ${LOG_FILE}
        for db in ${DBLIST}
        do
        START_DATE=`date +%d%m%y_%H%M`
        echo "******************************************************************" >> ${LOG_FILE}
        echo "Starting Backup of $db at $START_DATE" >> ${LOG_FILE}
        $MYSQL_HOME/bin/mysqldump -u${USR} -p${PWD} -S$SOCK --single-transaction --triggers --routines --events $db > ${CR_DUMP_DIR}/${db}-${START_DATE}.sql
        echo "DUMP_FILE" for $db is ${CR_DUMP_DIR}/${db}-${START_DATE}.sql.gz >> ${LOG_FILE}
        STATUS=`tail -100 ${CR_DUMP_DIR}/${db}-${START_DATE}.sql|grep "Dump completed"|wc -l`
        END_DATE=`date +%d%m%y_%H%M`
        gzip ${CR_DUMP_DIR}/${db}-${START_DATE}.sql
        DUMP="${CR_DUMP_DIR}/${db}-${START_DATE}.sql.gz"
                if [ $STATUS -ge 1 ];
                        then
                        echo "Database $db backed up successfully at $END_DATE ">>${LOG_FILE}
                        echo "******************************************************************" >> ${LOG_FILE}
                        $MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -h ${BKP_SERVER} -e "use backuplog;insert into nosql_backup(IP_Address,host_name,Db_Name,dump_file,Start_Time,End_Time,Status) values('$IP','$HOST','${db}','$DUMP','$START_DATE','$END_DATE','Success');"
                else
                        echo "Backup of database $db failed!!! Kindly check">>${LOG_FILE}
                        echo "******************************************************************" >> ${LOG_FILE}
                        $MYSQL_HOME/bin/mysql -u${USR} -p${PWD} -h ${BKP_SERVER} -e "use backuplog;insert into nosql_backup(IP_Address,host_name,Db_Name,dump_file,Start_Time,End_Time,Status) values('$IP','$HOST','${db}','$DUMP','$START_DATE','$END_DATE','Failed');"
                fi
        done
  fi
echo "CHECKING SUCESS/FAILURE" >> ${LOG_FILE}
TOTAL=`echo $DBLIST|wc -w`
SUCESS=`cat ${LOG_FILE}|grep -i successfully|wc -l`
FAILURE=`cat ${LOG_FILE}|grep -i failed|wc -l`
echo "TOTAL=$TOTAL,SUCCESS=$SUCESS,FAILED=$FAILURE" >> ${LOG_FILE}
mailx -s "MYSQL BACKUP(${IP}),TOTAL=$TOTAL,SUCCESS=$SUCESS,FAILED=$FAILURE" -r alert@mydomain.com $STMAIL < ${LOG_FILE}
find ${LOGDIR}/*.log -maxdepth 1 -type f -mtime +60 -exec rm -r "{}" \;
find ${DUMP_DIR}/* -maxdepth 1 -type d -mtime +60 -exec rm -r "{}" \;




====================================================


cat newvariables.env
MYSQL_HOME=/mysqldata/mysqlbinary/mysql
LOGDIR=/mysqlbackup/output
DUMP_DIR=/mysqlbackup/dump
SOCK='/mysqldata/data/mysqld.sock'
USR='backupuser'
PWD='somepassword'
BKP_SERVER=''

Galera

https://www.datavail.com/blog/why-choose-a-galera-based-clustering-solution-for-mysql/

MySQL Cluster FAQ or HA

https://www.mysql.com/products/cluster/faq.html


https://dzone.com/articles/tech-talk-series-part-4-wrap-up-achieving-high-ava

Master - Master replication

http://www.ryadel.com/en/mysql-master-master-replication-setup-in-5-easy-steps/

Golden Gate

http://www.orafaq.com/wiki/Advanced_Replication_FAQ#What_happens_if_two_or_more_sites_change_the_same_data.3F

MySQL server Hardware spec recommendation

https://www.webmasterworld.com/databases_sql_mysql/4465652.htm


https://www.linuxquestions.org/questions/linux-newbie-8/minimum-hardware-requirement-for-mysql-db-4175437370/




https://serverfault.com/questions/246125/mysql-hardware-specs




check side suggestions also




https://stackoverflow.com/questions/359384/how-do-you-determine-the-hardware-needed-for-a-server


https://www.percona.com/resources/technical-presentations/choosing-hardware-mysql-percona-live-washington-dc-2012


https://dba.stackexchange.com/questions/15030/mysql-recommended-hardware

My-cnf recommended setting

https://dba.stackexchange.com/questions/130809/mysql-my-cnf-recommended-settings?rq=1

Store Procedure in MySQL

afsad

What is AWK FS or NR?

http://www.thegeekstuff.com/2010/01/8-powerful-awk-built-in-variables-fs-ofs-rs-ors-nr-nf-filename-fnr/?ref=binfind.com/web

How does MySQL Replication really work?

https://www.percona.com/blog/2013/01/09/how-does-mysql-replication-really-work/


https://stackoverflow.com/questions/4642018/how-does-master-master-replication-work


https://dzone.com/articles/how-does-mysql-replication

What is Battery Backed Caching?

https://serverfault.com/questions/401488/non-volatile-cache-raid-controllers-what-kind-of-protection-is-there-against-nv




https://serverfault.com/questions/227241/what-purpose-does-the-battery-serve-on-a-battery-backed-caching-raid-controller




https://www.google.co.in/search?q=battery+backed+cache+raid+controller&safe=active&dcr=0&tbm=vid&source=lnms&sa=X&ved=0ahUKEwjf0OeQod_WAhUFT48KHSVtAQsQ_AUICygC&biw=1829&bih=921&dpr=1.05

MySQL 5.7 installation from Binaries

MYSQL 5.7 Installation Steps.

sudo chown -R mysql:mysql /mysqldata /mysqlbinlog /mysqltemp
mkdir -p /mysqldata/data /mysqldata/config /mysqldata/software /mysqldata/mysqlbinary
mkdir -p /mysqlbinlog/slowlog /mysqlbinlog/binlog
sudo cp /home/cloud-user/mysql-advanced-5.7.18-linux-glibc2.5-x86_64.tar.gz /mysqldata/mysqlbinary
sudo chown -R mysql:mysql /mysqldata/mysqlbinary


cd /mysqldata/mysqlbinary
tar -zxvf mysql-advanced-5.7.18-linux-glibc2.5-x86_64.tar.gz
mv mysql-advanced-5.7.18-linux-glibc2.5-x86_64 mysql
scp 10.144.160.183:/mysqldata/config/my.cnf /mysqldata/config/my.cnf

/mysqldata/mysqlbinary/mysql/bin/mysqld --initialize --user=mysql --basedir=/mysqldata/mysqlbase --datadir=/mysqldata/data


Note :- After intialization last line it will create complex password like  g,PAyic-l6g1.


E.g. A temporary password is generated for root@localhost: g,PAyic-l6g1.


/mysqldata/mysqlbinary/mysql/bin/mysqld_safe --defaults-file=/mysqldata/config/my.cnf  &


Note :- Above step will bring up mysql service, can be verified by ps -ef | grep -i mysqld.


Once up kindly login to the mysql with complex password mentioned above.
/mysqldata/mysqlbinary/mysql/bin/mysql -u root -p -S /mysqldata/data/mysql.sock


Note :- Socket file path can be verified by mysqld process.
Once logged into the mysql before running any other command please set password as,


SET PASSWORD = PASSWORD('N3wC0mple#P@ssw0rd');
once done you should be able to excute other commands as well.

MySQL 5.6 Installation from Binaries

MySQL 5.6 version Installation from Binaries

Step 1:

Create MySQL User on the server first.

userdel mysql;

groupadd -g 9251 mysql;

useradd -m -d /home/mysql -c "DB Users" -s /bin/bash -u 8251  -g 9251 mysql;

echo 'mysql' | passwd --stdin mysql;

echo 'mysql ALL=(ALL) NOPASSWD : ALL' >/etc/sudoers;

cat /etc/sudoers | grep -i mysql;

id mysql

Step 2:

Install MySQL

Download binaries from MySQL site and ensure the following path and pre-requisites are met.

Following mount points to be created :-

/mysqldata                 --  500GB

/mysqlbackup            --  500GB

/mysqlbinlog             --  300G

/mysqltemp               --   100G

/OSwatcher              --   50G

/oracle                       --    50G

Need “mysql” & “oracle” users (OS) to be created. From “mysql”, “oracle” user & it should not expire. Crontab access should be enabled for mysql user. Need root access so that can install MTOP, INNOTOP, OSWATCHER & other utilities. Need Mailx & SMTP service should be enabled for all servers. We need to setup open files limit and open process limit with below format.

cat /etc/security/limits.conf

mysql           soft     nproc       65536

mysql           hard    nproc       65536

mysql           soft     nofile        65536

mysql           hard    nofile        65536 

Follow the recommendation for kernel tuning and hardware also like swapiness and battery-backed cached RAID etc

Step 2:

Switch to mysql user and perform following  steps to install MySQL from binaries.

cd /mysqldata

mkdir data config software mysqlbinary

cd /mysqlbinlog

mkdir slowlog binlog  #will prefer to create table to can manager slow log more effectively

cp /tmp/p24372314_56_Linux-x86-64.zip /mysqldata/software/

cd  /mysqldata/software

unzip p24372314_56_Linux-x86-64.zip

mv mysql-advanced-5.6.32-linux-glibc2.5-x86_64.tar.gz ../mysqlbinary/

cd /mysqldata/mysqlbinary/

tar -zxvf mysql-advanced-5.6.32-linux-glibc2.5-x86_64.tar.gz

mv mysql-advanced-5.6.32-linux-glibc2.5-x86_64 mysql

vi /mysqldata/config/my.cnf

cd /mysqldata/mysqlbinary/mysql

./scripts/mysql_install_db --defaults-file=/mysqldata/config/my.cnf

Start MySQL

 ./bin/mysqld_safe --defaults-file=/mysqldata/config/my.cnf  &

Create alias for mysql login and start/stop service to manage

============================================================================
Login , shutdown and Startup  commands
============================================================================

Login -   mysql -u root -p -S /mysqldata/data/mysqld.sock

Shutdown -  ./bin/mysqladmin -u root -p -S /mysqldata/data/mysqld.sock shutdown

==============

Conf file

==============

[client]
port            = 3306
socket          = /data/mysqldata/mysqld.sock

[mysqld_safe]
socket          = /data/mysqldata/mysqld.sock
nice            = 0

[mysqld]
server-id = 1
character-set-server=utf8
port            = 3306
pid-file        = /data/mysqldata/mysqld.pid
socket          = /data/mysqldata/mysqld.sock
datadir         = /data/mysqldata
log-error       = /data/mysqldata/myqlerr.log
basedir         = /data/mysqlbinary/mysql/
tmpdir          = /data/mysqltemp

max_allowed_packet = 100M

skip-name-resolve

# Max Connections
max_connections=100

# Per Thread Buffers
read_buffer_size=1M
join_buffer_size=1M
sort_buffer_size=1M
read_rnd_buffer_size = 1M

# MyISAM buffers
key_buffer_size=16M

# Temporary Table
tmp_table_size=16M
max_heap_table_size=16M

# File Related
core-file
local-infile=1
secure_file_priv =/tmp/
open-files-limit=60000

### Changes for Mysql 5.6 ###
innodb_checksum_algorithm=INNODB
table_open_cache=3000
innodb_open_files=5000
table_definition_cache=3000
thread_cache_size=32

###  Slow query Log ###
long_query_time=1
slow_query_log=1
slow_query_log_file=/data/mysqldata/slowquery.log

##InnoDB Settings
innodb_buffer_pool_size=4G
innodb_purge_threads=1
innodb_buffer_pool_instances=6
innodb_thread_concurrency=64
innodb_log_file_size=1024M
innodb_log_buffer_size=16M
max_binlog_size=1G

## Mysql suggested Parameter
innodb_stats_on_metadata=0
innodb_flush_method=O_DIRECT
innodb_flush_log_at_trx_commit=1
max_connect_errors=20000
wait_timeout=180
interactive_timeout=180
log-warnings=2
log-raw=OFF
skip-grant-tables=FALSE
symbolic-links=0
plugin-load-add=validate_password.so
sql_mode=STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION

##Replication related
log-bin=/data/mysqldata1/mysql-bin
slave-compressed-protocol=1
binlog_format=MIXED
expire_logs_days=1
master_info_repository=TABLE

##MBSS parameters##
log-warnings = 2
log-raw = OFF
secure_auth=ON
local-infile=1
sql_mode=STRICT_ALL_TABLES,NO_AUTO_CREATE_USER
skip-grant-tables = FALSE
master_info_repository=TABLE
plugin-load=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
validate_password_length=14
validate_password_mixed_case_count=1
validate_password_number_count=1
validate_password_special_char_count=1
validate_password_policy=MEDIUM

Kernel Tuning for MySQL - Does it really required?

When I being interviewed for a MySQL DBA position


Avoid Swappiness in MySQL
Swapping is process that occurs when system moves part of memory to a special disk space called “swap”. The event usually appears when your system runs out of physical memory and instead of freeing up some RAM, the system pushed the information into disk. As you might have guess the disk is much slower than your RAM.
By default the option is enabled:
vm.swappiness = 60
sysctl -w vm.swappiness=0
numactl --interleave=all
Kernel IO Scheduling Tuning
echo noop > /sys/block/sda/queue/scheduler
InnoDB Thread tuning
Swappiness
NUMA
Huge Pages