Showing posts with label INSTALLATION. Show all posts
Showing posts with label INSTALLATION. Show all posts

Wednesday, August 20, 2014

MongoDB Binary Installation on Linux(Red Hat Enterprise Linux, CentOS Linux, Fedora Linux, or a related system)

MongoDB Binary Installation on Linux(Red Hat Enterprise Linux, CentOS Linux, Fedora Linux, or a related system)
                Created By : Amit Gera DBA(MongoDB & MySQL & ORACLE)
Goal:MongoDB Standard Binary Installation on Linux 64 bit through rpm package.

Partition Requirement:-
/mongo_admin:-   Will contains mongo binaries and confirguration file
/mongo_backup:- Will contains mongo backup [i.e dumpfile or export files or import files]
/mongo_data:-     Will contains data files
/mongo_home:-   Will contains profiles , scripts etc
/mongo_logs:-     Will contains log files [i.e trace ,alert & audit logfiles]

Note:-Here I have use name mongoservice_script wrt service name ,As per guidelines we must set it to meaningful name i.e product service name

Prerequisites:-http://myora-dba-notes.blogspot.com/2014/08/mongodb-distribution-formats-different.html

#~~#Step 1:-Download mogodb packages for linux
a.Create downloading destination directory
cmd:-mkdir -p /mongo_admin/mongo_binaries/download
b.Start Downloading at destination directory
If internet is available then use this cmd:-
cd /mongo_admin/mongo_binaries/download/
curl -O  http://downloads.mongodb.org/linux/mongodb-linux-x86_64-2.6.3.tgz
wget -O /mongo_admin/mongo_binaries/download/mongodb-linux-x86_64-2.6.3.tgz  http://downloads.mongodb.org/linux/mongodb-linux-x86_64-2.6.3.tgz

Else download this from other sources and copy here

#~~#Step 2:-
a.Create group and create user in that group for mongo
cmds:-
groupadd mongo_grp
useradd -r -g mongo_grp mongo_user
b.Create directory mention in below configuration file of step 6
cmds:-
mkdir -p /mongo_data/datadir/
mkdir -p /mongo_logs/error_log
chown mongo_user.mongo_grp -R /mongo_data/
chown mongo_user.mongo_grp -R /mongo_logs/
c.Create configuration & binary directories
cmds:-
mkdir -p /mongo_admin/configuration_file
touch /mongo_admin/configuration_file/mongod.conf


#~~#Step 3:-Add following below listed entries into environment file and load it in envrinoment
step a. cmd:-
echo "myadmin="/mongo_admin/"" >> /etc/environment
echo "basedir="/mongo_admin/mongo_binaries/"" >> /etc/environment
echo "myconf="/mongo_admin/configuration_file"" >> /etc/environment
echo "mongo_HOME="/mongo_home/homedir"" >> /etc/environment
echo "myadmin_download="/mongo_admin/mongo_binaries/download"" >> /etc/environment
step b. Run the file with cmd:-source /etc/environment

#~~#Step 4:- Extract binaries 
cmd:-cd $basedir
cmd:- tar zxvf $myadmin_download/mongodb-linux-x86_64-2.6.3.tgz -C /mongo_admin/mongo_binaries/
cmd:- ln -s /mongo_admin/mongo_binaries/mongodb-linux-x86_64-2.6.3 binaries

#~~#Step 5:-Add following below listed entries into environment file and load it in envrinoment
step a. cmd:-
echo "PATH="$PATH:/mongo_admin/mongo_binaries/binaries/bin"" >> /etc/environment
step b. Run the file with cmd:-source /etc/environment

#~~#Step 6: Create configuration file
a. vi /mongo_admin/configuration_file/mongod.conf
b. write below listed information into configuration file and save
# mongod.conf
#where to log
logpath=/var/log/mongodb/mongod.log
logappend=true
# fork and run in background
fork=true
port=27017
dbpath=/mongo_data/datadir

#~~#Step 7:- Now we have to initializes the mongo data directory and creates the system tables that it should contains.
cmd:-cd $basedir/binaries/bin/
cmd:- mongod --dbpath /mongo_data/datadir/

#~~#Step8: There are many ways by which we can start mongo and here we invoke mongo.server script method that use System V-style run directories (that is, /etc/init.d and run-level specific directories),
##Generally, you start the mongod server in one of these ways:
a.Invoke mongod directly. This works on any platform.
b.Invoke mongod , which also tries to determine the proper options for mongod and then runs it with those options. This script is used on Unix and Unix-like systems.
c.Invoke mongod . This script is used primarily at system startup and shutdown on systems that use System V-style run directories (that is, /etc/init.d and run-level specific directories), where it usually is installed under the name mongo. The mongo.server script starts the server by invoking mongod_safe.
d.On Mac OS X, install a separate mongo Startup Item package to enable the automatic startup of mongo on system startup. The Startup Item starts the server by invoking mongo.server.

After initializing db ,Now we are proceding to add service in System V-style run directories

Execution steps:-
-i- First create mongod service start/stop/restartup script
a. vi $basedir/binaries/bin/mongoservice_script
b. write below listed information into mongoservice_script file and save

#!/bin/bash

# mongod - Startup script for mongod

# chkconfig: 35 85 15
# description: Mongo is a scalable, document-oriented database.
# processname: mongod
# config: /etc/mongod.conf
# pidfile: /var/run/mongodb/mongod.pid

. /etc/rc.d/init.d/functions

# things from mongod.conf get there by mongod reading it


# NOTE: if you change any OPTIONS here, you get what you pay for:
# this script assumes all options are in the config file.
CONFIGFILE="/mongo_admin/configuration_file/mongod.conf"
OPTIONS=" -f $CONFIGFILE"
SYSCONFIG="/etc/sysconfig/mongod"

# FIXME: 1.9.x has a --shutdown flag that parses the config file and
# shuts down the correct running pid, but that's unavailable in 1.8
# for now.  This can go away when this script stops supporting 1.8.
DBPATH=`awk -F= '/^dbpath[[:blank:]]*=[[:blank:]]*/{print $2}' "$CONFIGFILE"`
PIDFILE=`awk -F= '/^pidfilepath[[:blank:]]*=[[:blank:]]*/{print $2}' "$CONFIGFILE"`
mongod=${MONGOD-/mongo_admin/mongo_binaries/binaries/bin/mongod}

MONGO_USER=mongo_user
MONGO_GROUP=mongo_grp

if [ -f "$SYSCONFIG" ]; then
    . "$SYSCONFIG"
fi

# Handle NUMA access to CPUs (SERVER-3574)
# This verifies the existence of numactl as well as testing that the command works
NUMACTL_ARGS="--interleave=all"
if which numactl >/dev/null 2>/dev/null && numactl $NUMACTL_ARGS ls / >/dev/null 2>/dev/null
then
    NUMACTL="numactl $NUMACTL_ARGS"
else
    NUMACTL=""
fi

start()
{
  # Recommended ulimit values for mongod or mongos
  # See http://docs.mongodb.org/manual/reference/ulimit/#recommended-settings
  #
  ulimit -f unlimited
  ulimit -t unlimited
  ulimit -v unlimited
  ulimit -n 64000
  ulimit -m unlimited
  ulimit -u 32000

  echo -n $"Starting mongod: "
  daemon --user "$MONGO_USER" "$NUMACTL $mongod $OPTIONS >/dev/null 2>&1"
  RETVAL=$?
  echo
  [ $RETVAL -eq 0 ] && touch /var/lock/subsys/mongod
}

stop()
{
  echo -n $"Stopping mongod: "
  killproc -p "$PIDFILE" -d 300 /usr/bin/mongod
  RETVAL=$?
  echo
  [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/mongod
}

restart () {
        stop
        start
}


RETVAL=0

case "$1" in
  start)
    start
    ;;
  stop)
    stop
    ;;
  restart|reload|force-reload)
    restart
    ;;
  condrestart)
    [ -f /var/lock/subsys/mongod ] && restart || :
    ;;
  status)
    status $mongod
    RETVAL=$?
    ;;
  *)
    echo "Usage: $0 {start|stop|status|restart|reload|force-reload|condrestart}"
    RETVAL=1
esac

exit $RETVAL


-ii) Copy server script and add in system V-style run directories
cmd:-cp $basedir/binaries/bin/mongoservice_script /etc/init.d/mongoservice_script
-iii) add in chmod +x /etc/init.d/mongoservice_script

-iv) Optional if there is any old services which we need to remove :-
cmd:-chkconfig --list | grep old_mongo_servicename
if present then remove with below command
cmd:- chkconfig –del old_mongo_servicename
-v) Adding system startup for /etc/init.d/mongoservice_script ...
cmd:- chkconfig --add mongoservice_script or chkconfig mongoservice_script on

#~~#Step 9: Now you can start mongo with
/etc/init.d/mongoservice_script start
or
/etc/init.d/mongoservice_script start

Note: We can shutdown with
/etc/init.d/mongoservice_script stop
or
/etc/init.d/mongoservice_script stop

MongoDB Distribution Formats / Different types for MongoDB Installation Methods

      MongoDB Distribution Formats / Different types for MongoDB Installation Methods

#~~#MongoDB Distribution Formats / Different types for MongoDB Installation Methods 
There are Two types of Binary Distribution Format for MongoDB Installation
a). Generic Binary Format(Zip arcives or compressed tar )
b). Native Format(RPM packages for Linux, DMG packages for Mac OS X or Ubuntu or Debian system, and PKG packages for Solaris.)


Saturday, April 12, 2014

Applying CPU Patch in a Physical Standby Database Configuration

                                      Oracle patch deployment activity plan
                Applying CPU Patch in a Physical Standby Database Configuration
Activity Plan:-Activity step from 1 to 6 before downtime and Activity onward step7 will be in Downtime
1. Disable log shipping from the Primary
i)Stop recovery at standby:-
a.cmd:-alter database recover managed standby database cancel;
b.verfiry at os level ps -ef |grep mrp
c.verify at db level using alert log file
ii)Defer archive shiping from production
cmd:-alter system set log_archive_dest_state_2=DEFER;

2. Shutdown Standby
a.Stop Listener
cmd:- lsnrctl stop servicename
Note:-To check servicename please find it using cmd:-lsnrctl status
b.Shutdown Database
cmd:-shut immediate

3. Take Backup of Oracle binaries on standby
cmds:-
cd $ORACLE_HOME
cd ../
tar -cvf $ORACLE_HOME/Backupstdby_db_1.09042014 db_1
cd /home/oracle/
tar -cvf $ORACLE_HOME/Backupstdby_oraInventory.09042014 oraInventory

4. Install the CPU patch on Standby server
a.check inventory & version status before proceeding for update
cmd for version:-opatch version
cmd for inventory status:-opatch lsinventory
b.Apply patch(Note:make sure we have exported $ORACLE_HOME/OPatch in PATH ENV VAR)
To apply CPU Patch we require OPatch utility available of that database version or later in order to apply that patch.
Go to path where we have placed both patches CPU Patch: p5689937_10201_Linux-x86-64.zip & Opatch ultility :- & p6880880_102000_Linux-x86-64.zip.(i.e /home/oracle/oracle_patch/)
Note:(pnumber_xxxxx_OS-Bit.zip :number defines patch number & xxxxx defines db version number for which it is valid , OS defines OS Flavour and Bit represent architecture bit of OS)
cd /home/oracle/oracle_patch/
cp -pvr p6880880_102000_Linux-x86-64.zip $ORACLE_HOME/
(Note:above patch will deploy opatch utility)
cd $ORACLE_HOME
mv OPatch OPatch_old
unzip p6880880_102000_Linux-x86-64.zip
cd /home/oracle/oracle_patch/
unzip p5689937_10201_Linux-x86-64.zip
cd 5689937
opatch apply
c.check inventory & version status after updation of patch
cmd for version:-opatch version
cmd for inventory status:-opatch lsinventory

5. Startup Standby in recovery mode (do NOT run any SQL the standby)
a.Start Database
cmds:-
sqlplus / as sysdba
Startup mount
b.Start Listener
cmd:- lsnrctl start servicename
Note:-servicename here belongs to what we have already captured in step 2

6. Re-enable log shipping on standby
i)Resume Recovery
a.cmd:-alter database recover managed standby database using current logfile disconnect;
b.verfiry at os level ps -ef |grep mrp
c.verify at db level using alert log file
ii)Enable archive shiping from production
cmd:-alter system set log_archive_dest_state_2=ENABLE;
iii)Switch logfile on production
alter system switch logfile;
iv)Check both are in sync or not with daily_queries.
select DEST_ID,sequence#,archived,applied,deleted,status from v$archived_log order by 2;

7. Shutdown Primary
a.Stop Listener
cmd:- lsnrctl stop servicename
Note:-To check servicename please find it using cmd:-lsnrctl status
b.Also shutdown oem service if any
cmd:-emctl stop dbconsole
c.Shutdown Database
cmd:-shut immediate

8. Take Backup of Oracle binaries on Primary
cmds:-
cd $ORACLE_HOME
cd ../
tar -cvf $ORACLE_HOME/Backupprim_db_1.09042014 db_1
cd /home/oracle/
tar -cvf $ORACLE_HOME/Backupprim_oraInventory.09042014 oraInventory

9. Install the CPU patch on Primary
a.check inventory & version status before proceeding for update
cmd for version:-opatch version
cmd for inventory status:-opatch lsinventory
b.Apply patch(Note:make sure we have exported $ORACLE_HOME/OPatch in PATH ENV VAR)
To apply CPU Patch we require OPatch utility available of that database version or later in order to apply that patch.
Go to path where we have placed both patches CPU Patch: p5689937_10201_Linux-x86-64.zip & Opatch ultility :- & p6880880_102000_Linux-x86-64.zip.(i.e /home/oracle/oracle_patch/)
Note:(pnumber_xxxxx_OS-Bit.zip :number defines patch number & xxxxx defines db version number for which it is valid , OS defines OS Flavour and Bit represent architecture bit of OS)cd /home/oracle/oracle_patch/
cp -pvr p6880880_102000_Linux-x86-64.zip $ORACLE_HOME/
cd $ORACLE_HOME
mv OPatch OPatch_old
unzip p6880880_102000_Linux-x86-64.zip
cd /home/oracle/oracle_patch/
unzip p5689937_10201_Linux-x86-64.zip
cd 5689937
opatch apply
c.check inventory & version status after updation of patch
cmd for version:-opatch version
cmd for inventory status:-opatch lsinventory

10. Start primary
a.Start Database
cmds:-
sqlplus / as sysdba
Startup
b.Start Listener
cmd:- lsnrctl start servicename
Note:-servicename here belongs to what we have already captured in step 7

11. Run the SQL script for the patch on Primary
a.cmd:-
cd $ORACLE_HOME/cpu/CPUJan2007
sqlplus / as sysdba
@catcpu.sql

b.Use the below SQL command to see all the PSUs/CPUs applied to your database.
cmd:-select * from dba_registry_history ;

12. Re-enable log shipping
i)Resume Recovery
a.cmd:-alter database recover managed standby database using current logfile disconnect;
b.verfiry at os level ps -ef |grep mrp
c.verify at db level using alert log file
ii)Enable archive shiping from production
cmd:-alter system set log_archive_dest_state_2=ENABLE;
iii)Switch logfile on production
cmd:-alter system switch logfile;
iv)Check both are in sync or not with daily_queries.
select DEST_ID,sequence#,archived,applied,deleted,status from v$archived_log order by 2;

13. Monitor the redo apply from Primary to Standby --- this will also upgrade the Standby data dictionary

--Rollback Plan:-
1. Disable log shipping from the Primary
2. Shutdown Standby
3. Take Backup of Oracle binaries on standby
4. Rollback the CPU patch on Standby server
5. Startup Standby in recovery mode (do NOT run any SQL the standby)
6. Re-enable log shipping on standby
7. Shutdown Primary
8. Take Backup of Oracle binaries on Primary
9. Rollback the CPU patch on Primary
10. Start primary
11. Run the SQL Rollback script for the patch on Primary
12. Re-enable log shipping
13. Monitor the redo apply from Primary to Standby --- this will also rollback the Standby data dictionary

Friday, March 21, 2014

Reasons to relocated installation directories of package

                             Reasons to relocated installation directories of package


#~~#RPM has the ability to give users some latitude in deciding where packages are to be installed on
their systems. However, package builders must first design their packages to give users this
freedom.
In other words, an RPM package that can be installed into a different directory is said to be relocatable. Please note that not all RPM packages can be installed into another directory.
That's all well and good, but why would the ability to ``relocate'' a package be all that important?
#~~#Why relocate packages?
1.Avoid disk space problem(e.g default installation location is small in size)
2.This is standard installation method adopted for compliance
3.Save from attacks.

#~~#To check is package relocatable or not on Generic Linux 
cmd:-rpm -qpi package-file-name.rpm | grep Relocations

#~~#rpm options for installing other then default predefined location:-
--relocate OLDPATH=NEWPATH
For relocatable binary packages, translate all file paths that start with OLDPATH in the package relocation hint(s) to NEWPATH. This option can be used repeatedly if several OLDPATHâs in the package are to be relocated.
--prefix NEWPATH
For relocatable binary packages, translate all file paths that start with the installation prefix in the package relocation hint(s) to
NEWPATH.



MySQL Distribution Formats / Different types for MySQL Installation Methods

                  MySQL Distribution Formats / Different types for MySQL Installation Methods 


There are Two types of Distribution Format for MySQL Installation
1.Source distribution
2.Binary distribution

2.Binary Installation:This is further divided into two parts
a). Generic Binary Format(Zip arcives or compressed tar )
b). Native Format(RPM packages for Linux, DMG packages for Mac OS X, and PKG packages for Solaris.)

In most cases, pepole should probably use a binary distribution, if one exists for their platform.
Binary distributions are available in native format for many platforms, such as RPM packages for Linux, DMG packages for Mac OS X, and PKG packages for Solaris.
Distributions are also available in more generic formats such as Zip archives or compressed tar.

Reasons to choose a binary distribution include the following:
• Binary distributions generally are easier to install than source distributions.
• To satisfy different user requirements, we provide several servers in binary distributions. mysqld is an optimized server that is a smaller, faster binary. mysqld-debug is compiled with debugging support.
Each of these servers is compiled from the same source distribution, though with different configuration options. All native MySQL clients can connect to servers from either MySQL version.

Under some circumstances, you may be better off installing MySQL from a source distribution:
• You want to install MySQL at some explicit location. The standard binary distributions are ready to run at any installation location, but you might require even more flexibility to place MySQL components where you want.
• You want to configure mysqld to ensure that features are available that might not be included in the standard binary distributions. Here is a list of the most common extra options that you may want to use to ensure feature availability:
• --with-libwrap
• --with-named-z-libs (this is done for some of the binaries)
• --with-debug[=full]How and When Updates Are Released
For more detail please ref:-http://dev.mysql.com/doc/refman/5.x/en/source-configuration-options.html
• You want to configure mysqld without some features that are included in the standard binary distributions. For example, distributions normally are compiled with support for all character sets. If you want a smaller MySQL server, you can recompile it with support for only the character sets you need.
• You want to use the latest sources from one of the Bazaar repositories to have access to all current bugfixes. For example, if you have found a bug and reported it to the MySQL development team, the bugfix is committed to the source repository and you can access it there. The bugfix does not appear in a release until a release actually is issued.
• You want to read (or modify) the C and C++ code that makes up MySQL. For this purpose, you should get a source distribution, because the source code is always the ultimate manual.
• Source distributions contain more tests and examples than binary distributions.

#~~#What is the Difference Between MySQL Generic Binaries and RPM Binaries for Linux?
Q:-Is it possible to use the generic MySQL binaries on Linux platforms where an operating system specific version exists?
Ans:-The main difference between the generic Linux binaries and the platform specific ones is how the binaries have been packed. The generic binaries are packed in a
tar-ball that can be installed on any platform, including those where a specific version exists. Another difference is that the tar-balls include everything under one
subdirectory whereas for example an RPM will install each file in the location common for the platform.

Q:-What will be the impact on production database in terms of support and performance, if the generic Linux based binary installation is used rather than the
platform specific RPMs?
Ans:-There will not be any performance difference between installing the RPMs and using the generic tar-ball on a given system. The main difference is that the RPM automates the installation and upgrades to a larger degree than using the generic package.

Q:-What is Oracle recommending for selecting which MySQL binaries to use and what is "best practice"?
Ans:-Due to the easier maintenance when using the RPM installations, Oracle recommends using these if possible, although considerations such as installing into a nondefault

location will tend to favor using the tar-ball.

Thursday, February 20, 2014

MySQL Standard Generic Binary Installation from a compressed tar file binary distribution

      MySQL Standard Generic Binary Installation from a compressed tar file binary distribution

                                                  Created By : Amit Gera DBA(MySQL & ORACLE)
Goal: MySQL Standard Generic Binary Installation from a compressed tar file binary distribution
OS:- Centos 6.3 64 Bit
Partition Requirement:-
/mysql_admin:-Will contains mysql binaries and confirguration file
/mysql_backup:-Will contains mysql backup [i.e dumpfile or export files or import files]
/mysql_data:- Will contains data files
/mysql_home:-Will contains profiles , sql scripts etc
/mysql_logs:- Will contains log files [i.e trace ,alert , binary , slow query & general audit logfiles]

Note:-Here I have use name amit wrt service name ,As per guidelines we must set it to meaningful name i.e product service name


Prerequisites:-http://myora-dba-notes.blogspot.in/2014/03/mysql-distribution-formats.html

#~~#Step 1:-Download mysql packages for ubuntu
a.Create downloading destination directory
cmd:-mkdir -p /mysql_admin/mysql_binaries/download
b.Start Downloading at destination directory
If internet is available then use this cmd:-
wget -O /mysql_admin/mysql_binaries/download/mysqlbinaries-5.6.16.tar.gz http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.16-linux-glibc2.5-x86_64.tar.gz

Else download this from other sources and copy here

Note:-Post downloadation step we recommend to verify the integrity of the packages downloaded.
Cmd:- md5sum -t /mysql_admin/mysql_binaries/download/mysqlbinaries-5.6.16.tar.gz
Compare the output of trail cmd with the checksum shown on download url page

#~~#Step 2:-
a.Create group and create user in that group for mysql
cmds:-
groupadd mysql_grp
useradd -r -g mysql_grp mysql_user
b.Create directory mention in below configuration file of step 6
cmds:-
mkdir -p /mysql_data/ibtablespace/
mkdir -p /mysql_data/datadir/
mkdir -p /mysql_data/iblogs
mkdir -p /mysql_logs/slow_logs/
mkdir -p /mysql_logs/error_log
mkdir -p /mysql_logs/replication_log
mkdir -p /mysql_logs/mytmp/
chown mysql_user.mysql_grp -R /mysql_data/
chown mysql_user.mysql_grp -R /mysql_logs/
c.Create configuration & binary directories
cmds:-
mkdir -p /mysql_admin/configuration_file
touch /mysql_admin/configuration_file/my.cnf
mkdir /mysql_admin/mysql_binaries/

#~~#Step 3:-Add following below listed entries into environment file and load it in envrinoment
step a. cmd:-
echo "myadmin="/mysql_admin/"" >> /etc/environment
echo "basedir="/mysql_admin/mysql_binaries/"" >> /etc/environment
echo "myconf="/mysql_admin/configuration_file"" >> /etc/environment
echo "MYSQL_HOME="/mysql_home/homedir"" >> /etc/environment
echo "myadmin_download="/mysql_admin/mysql_binaries/download"" >> /etc/environment
step b. Run the file with cmd:-source /etc/environment

#~~#Step 4:- Extract binaries 
cmd:-cd $basedir
cmd:- tar zxvf $myadmin_download/mysqlbinaries-5.6.16.tar.gz -C /mysql_admin/mysql_binaries/
cmd:- ln -s /mysql_admin/mysql_binaries/mysql-5.6.16-linux-glibc2.5-x86_64 binaries

#~~#Step 5:-Add following below listed entries into environment file and load it in envrinoment
step a. cmd:-
echo "PATH="$PATH:/mysql_admin/mysql_binaries/binaries/bin"" >> /etc/environment
step b. Run the file with cmd:-source /etc/environment

#~~#Step 6: Create configuration file
a. vi /mysql_admin/configuration_file/my.cnf
b. write below listed information into configuration file and save
[client]
port = 3310
socket = /mysql_data/my_amit.sock
# The MySQL server
[mysqld]
user=mysql_user
port = 3310
socket = /mysql_data/my_amit.sock
basedir=/mysql_admin/mysql_binaries/binaries
pid-file =/mysql_data/my_amit.pid
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
tmpdir=/mysql_logs/mytmp/
datadir=/mysql_data/datadir/
innodb_data_home_dir = /mysql_data/ibtablespace/
innodb_mirrored_log_groups=1
innodb_log_group_home_dir = /mysql_data/iblogs
innodb_log_files_in_group=3
innodb_log_file_size=256M
log-bin = /mysql_logs/replication_log/amit-replication
log-error = /mysql_logs/error_log/amit-mysqllog.err
slow_query_log=on
long_query_time=1
slow_query_log_file=/mysql_logs/slow_logs/amit-master-slow.log
default-storage-engine = innodb
innodb_file_per_table

#~~#Step 7:- Now we have to initializes the MySQL data directory and creates the system tables that it should contains.
cmd:-cd $basedir/mysql-5.6.16-linux-glibc2.5-x86_64/
cmd:-scripts/mysql_install_db --user=mysql_user --basedir=/mysql_admin/mysql_binaries/binaries --defaults-file=/mysql_admin/configuration_file/my.cnf

Note: option user=mysql_user is used in order to initialized db with ownership mysql user

There are some third party tool which can help you in creation or tunning of configuration files with respect to inputs mentiones
URL for Percona Configuration Wizard for MySQL is :-https://tools.percona.com/ or https://tools.percona.com/wizard

#~~#Step8: There are many ways by which we can start mysql and here we invoke mysql.server script method that use System V-style run directories (that is, /etc/init.d and run-level specific directories),
##Generally, you start the mysqld server in one of these ways:
a.Invoke mysqld directly. This works on any platform.
b.Invoke mysqld_safe, which tries to determine the proper options for mysqld and then runs it with those options. This script is used on Unix and Unix-like systems.
c.Invoke mysql.server. This script is used primarily at system startup and shutdown on systems that use System V-style run directories (that is, /etc/init.d and run-level specific directories), where it usually is installed under the name mysql. The mysql.server script starts the server by invoking mysqld_safe.
d.On Mac OS X, install a separate MySQL Startup Item package to enable the automatic startup of MySQL on system startup. The Startup Item starts the server by invoking mysql.server.
e.Use the Solaris/OpenSolaris service management framework (SMF) system to initiate and control MySQL startup.

After initializing db ,Now we are proceding to add service in System V-style run directories

Execution steps:-
a.Copy server script and add in system V-style run directories
cmd:-cp /mysql_admin/mysql_binaries/binaries/support-files/mysql.server /etc/init.d/mysql_amit_service
b. add in chmod +x /etc/init.d/mysql_amit_service

b.Optional if there is any old services which we need to remove :-
cmd:-chkconfig --list | grep old_mysql_servicename
if present then remove with below command
cmd:- chkconfig –del old_mysql_servicename
c. Adding system startup for /etc/init.d/mysql_amit_service ...
cmd:- chkconfig --add mysql_amit_service or chkconfig mysql_amit_service on

#~~#Step 9:
Before making link please make sure no my.cnf file exist in /etc directory
If exist then delete that file cmd:- rm -rf /etc/my.cnf and proceed further
cmd:-ln -s /mysql_admin/configuration_file/my.cnf /etc/my.cnf

#~~#Step 10: Now you can start mysql with
/etc/init.d/mysql_amit_service start
or
/etc/init.d/mysql_amit_service start

Note: We can Shutdown with
/etc/init.d/mysql_amit_service stop
or
/etc/init.d/mysql_amit_service stop


Friday, February 14, 2014

MySQL Binary Installation on Ubuntu

                                    MySQL Binary Installation on Ubuntu

                 Created By : Amit Gera DBA(MySQL & ORACLE)
Goal:MySQL Standard Binary Installation on Ubuntu 64 bit through package

Partition Requirement:-
/mysql_admin:-Will contains mysql binaries and confirguration file
/mysql_backup:-Will contains mysql backup [i.e dumpfile or export files or import files]
/mysql_data:- Will contains data files
/mysql_home:-Will contains profiles , sql scripts etc
/mysql_logs:- Will contains log files [i.e trace ,alert , binary , slow query & general audit logfiles]



Note:-Here I have use name amit wrt service name ,As per guidelines we must set it to meaningful name i.e product service name

Prerequisites:-http://myora-dba-notes.blogspot.in/2014/03/mysql-distribution-formats.html

#~~#Step 1:-Download mysql packages for ubuntu
a.Create downloading destination directory
cmd:-mkdir -p /mysql_admin/mysql_binaries/download
b.Start Downloading at destination directory
If internet is available then use this cmd:-
wget -O /mysql_admin/mysql_binaries/download/mysql-5.6.16.deb http://cdn.mysql.com/Downloads/MySQL-5.6/mysql-5.6.16-debian6.0-x86_64.deb
Else download this from other sources and copy here

#~~#Step 2:-
a.Create group and create user in that group for mysql
cmd:-
groupadd mysql_grp
useradd -r -g mysql_grp mysql_user
b.Create directory mention in below configuration file of step 5
cmd:-
mkdir -p /mysql_data/ibtablespace/
mkdir -p /mysql_data/datadir/
mkdir -p /mysql_data/iblogs
mkdir -p /mysql_logs/slow_logs/
mkdir -p /mysql_logs/error_log
mkdir -p /mysql_logs/replication_log
mkdir -p /mysql_logs/mytmp/
chown mysql_user.mysql_grp -R /mysql_data/
chown mysql_user.mysql_grp -R /mysql_logs/
c.Create configuration & binary directories
mkdir -p /mysql_admin/configuration_file
touch /mysql_admin/configuration_file/my.cnf
mkdir /mysql_admin/mysql_binaries/mysqlbinaries-5.6.16

#~~#Step 3:-Add following below listed entries into environment file and load it in envrinoment
step a. cmd:-
echo "PATH="$PATH:/mysql_admin/mysql_binaries/mysqlbinaries-5.6.16/opt/mysql/server-5.6/bin"" >> /etc/environment
echo "myadmin="/mysql_admin/"" >> /etc/environment
echo "basedir="/mysql_admin/mysql_binaries/mysqlbinaries-5.6.16"" >> /etc/environment
echo "myconf="/mysql_admin/configuration_file"" >> /etc/environment
echo "MYSQL_HOME="/mysql_home/homedir"" >> /etc/environment
echo "myadmin_download="/mysql_admin/mysql_binaries/download"" >> /etc/environment
step b. Run the file with cmd:-source /etc/environment

#~~#Step 4:- Install the package with command dpkg -i package-file-name
cmd:- dpkg -i --instdir=$basedir $myadmin_download/mysql-5.6.16.deb

#~~#Step 5:-
a.Check package is installed or not by command dpkg -l | grep 'keywork to be matched'
cmd:- dpkg -l | grep 'libaio1' or dpkg -l \*libaio1\*
Note:- Package information is stored in File Name /var/lib/dpkg/status
b.Install package if not present
cmd:-apt-get install libaio1

#~~#Step 6: Create configuration file
a. vi /mysql_admin/configuration_file/my.cnf
b. write below listed information into configuration file and save
[client]
port = 3310
socket = /mysql_data/my_amit.sock
# The MySQL server
[mysqld]
user=mysql_user
port = 3310
socket = /mysql_data/my_amit.sock
basedir=/mysql_admin/mysql_binaries/mysqlbinaries-5.6.16/opt/mysql/server-5.6
pid-file =/mysql_data/my_amit.pid
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
tmpdir=/mysql_logs/mytmp/
datadir=/mysql_data/datadir/
innodb_data_home_dir = /mysql_data/ibtablespace/
innodb_mirrored_log_groups=1
innodb_log_group_home_dir = /mysql_data/iblogs
innodb_log_files_in_group=3
innodb_log_file_size=256M
log-bin = /mysql_logs/replication_log/amit-replication
log-error = /mysql_logs/error_log/amit-mysqllog.err
slow_query_log=on
long_query_time=1
slow_query_log_file=/mysql_logs/slow_logs/amit-master-slow.log
default-storage-engine = innodb
innodb_file_per_table

#~~#Step 7:- Now we have to initializes the MySQL data directory and creates the system tables that it should contains.
cmd:-
cd $basedir/opt/mysql/server-5.6/
scripts/mysql_install_db --user=mysql_user --basedir=/mysql_admin/mysql_binaries/mysqlbinaries-5.6.16/opt/mysql/server-5.6 --defaults-file=/mysql_admin/configuration_file/my.cnf

Note: option user=mysql_user is used in order to initialized db with ownership mysql user

There are some third party tool which can help you in creation or tunning of configuration files with respect to inputs mentiones
URL for Percona Configuration Wizard for MySQL is :-https://tools.percona.com/ or https://tools.percona.com/wizard


#~~#Step8: There are many ways by which we can start mysql and here we invoke mysql.server script method that use System V-style run directories (that is, /etc/init.d and run-level specific directories),
##Generally, you start the mysqld server in one of these ways:
a.Invoke mysqld directly. This works on any platform.
b.Invoke mysqld_safe, which tries to determine the proper options for mysqld and then runs it with those options. This script is used on Unix and Unix-like systems.
c.Invoke mysql.server. This script is used primarily at system startup and shutdown on systems that use System V-style run directories (that is, /etc/init.d and run-level specific directories), where it usually is installed under the name mysql. The mysql.server script starts the server by invoking mysqld_safe.
d.On Mac OS X, install a separate MySQL Startup Item package to enable the automatic startup of MySQL on system startup. The Startup Item starts the server by invoking mysql.server.
e.Use the Solaris/OpenSolaris service management framework (SMF) system to initiate and control MySQL startup.

After initializing db ,Now we are proceding to add service in System V-style run directories

Execution steps:-
a.Copy server script and add in system V-style run directories
cmd:-
cp /mysql_admin/mysql_binaries/mysqlbinaries-5.6.16/opt/mysql/server-5.6/support-files/mysql.server /etc/init.d/mysql_amit_service
b.Optional if there is any old services which we need to remove :-
cmd:-update-rc.d -f old_mysql_service_name remove
c. Adding system startup for /etc/init.d/mysql_amit_service ...
cmd:-update-rc.d mysql_amit_service defaults

#~~#Step 9:- update socket in mysql_config
cmd:-
mysql_config_editor set --socket=/mysql_data/my_amit.sock

#~~#Step 10:
ln -s /mysql_admin/configuration_file/my.cnf /etc/my.cnf

#~~#Step 11: Now you can start mysql with
/etc/init.d/mysql_amit_service start
or
/etc/init.d/mysql_amit_service start

Note: We can Shutdown with
/etc/init.d/mysql_amit_service stop
or
/etc/init.d/mysql_amit_service stop



MySQL Standard Source Installation on Ubuntu

                             MySQL Standard Source Installation on Ubuntu

              Created By : Amit Gera DBA(MySQL & ORACLE)

Goal:MySQL Standard based source installation on Ubuntu 64 bit 

Partition Requirement:-
/mysql_admin:-Will contains mysql binaries and confirguration file
/mysql_backup:-Will contains mysql backup [i.e dumpfile or export files or import files]
/mysql_data :- Will contains data files
/mysql_home:-Will contains profiles , sql scripts etc
/mysql_logs:- Will contains log files [i.e trace ,alert , binary , slow query & general audit logfiles]


Note:-Here I have use name amit wrt service name ,As per guidelines we must set it to meaningful name i.e product service name

Prerequisites:-http://myora-dba-notes.blogspot.in/2014/03/mysql-distribution-formats.html

Step 1:-
a.Create group and create user in that group for mysql
cmd:-
groupadd mysql_grp
useradd -r -g mysql_grp mysql_user
b.Create directory mention in below configuration file of step 5
cmd:-
mkdir -p /mysql_data/ibtablespace/
mkdir -p /mysql_data/datadir/
mkdir -p /mysql_data/iblogs
mkdir -p /mysql_logs/slow_logs/
mkdir -p /mysql_logs/error_log
mkdir -p /mysql_logs/replication_log
mkdir -p /mysql_logs/mytmp/
chown mysql_user.mysql_grp -R /mysql_data/
chown mysql_user.mysql_grp -R /mysql_logs/


c.Create configuration & binary directories
mkdir -p /mysql_admin/configuration_file
touch /mysql_admin/configuration_file/my.cnf
mkdir -p /mysql_admin/mysql_binaries


#~~#Step 2:- Add following below listed entries into environment file and load it in envrinoment
step a. cmd:-
echo "PATH="$PATH:/mysql_admin/mysql_binaries/mysqlbinaries-5.6.16/bin"" >> /etc/environment
echo "myadmin="/mysql_admin/"" >> /etc/environment
echo "basedir="/mysql_admin/mysql_binaries/mysqlbinaries-5.6.16"" >> /etc/environment
echo "myconf="/mysql_admin/configuration_file"" >> /etc/environment
echo "MYSQL_HOME="/mysql_home/homedir"" >> /etc/environment
step b. Run the file with cmd:-source /etc/environment

#~~#Step 3:- Install the package with command apt-get install package-name
Before installation please do step 4 i.e for first checking the package status .If pkg are not installed then proceed step 3 else 5
If internet is available then use this cmd:- apt-get install -y cmake build-essential libreadline6-dev libncurses5-dev
Else downloaded the package from other sources and copy to this and Install the package on this with command dpkg -i package-file-name

#~~#Step 4:- Check package is installed or not by command dpkg -l | grep 'keywork to be matched'
a.cmd:- dpkg -l \*libncurses\*
b.cmd:- dpkg -l cmake
Note:- Package information is stored in File Name /var/lib/dpkg/status

#~~#Step 5:-Download mysql source build(option - https://dev.mysql.com/doc/refman/5.6/en/source-configuration-options.html) and Compile & Build MySQL using Cmake
a. Download source
cmds:-
mkdir -p /mysql_admin/mysql_binaries/download
cd /mysql_admin/mysql_binaries/download
b.Extract binaries
cd /mysql_admin/mysql_binaries/download
tar xvfz mysql-5.6.16.tar.gz
c.Check is CmakeCache.txt file exsist or not , if present then delete that file else proceed with step d
cd mysql-5.6.16
rm CmakeCache.txt
d.Complie & Build Mysql
Please read below notes before proceeding so as to be aware of what plugins or engines we need to build:-
The MyISAM, MERGE, MEMORY, and CSV engines are mandatory (always compiled into the server) and need not be installed explicitly.

To compile a storage engine statically into the server, use -DWITH_engine_STORAGE_ENGINE=1. Some permissible engine values are ARCHIVE, BLACKHOLE, EXAMPLE, FEDERATED, INNOBASE (InnoDB), NDB or NDBCLUSTER (NDB), PARTITION (partitioning support), and PERFSCHEMA (Performance Schema). Examples:
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_ARCHIVE_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1

WITH_NDBCLUSTER_STORAGE_ENGINE is supported only when building MySQL Cluster using the MySQL Cluster sources. It cannot be used to enable clustering support in other MySQL source trees or distributions. In MySQL Cluster NDB 7.3 source distributions, it is enabled by default. See Section 17.2.2.3, “Building MySQL Cluster from Source on Linux”, and Section 17.2.3.2, “Compiling and Installing MySQL Cluster from Source on Windows”, for more information.

To exclude a storage engine from the build, use -DWITHOUT_engine_STORAGE_ENGINE=1. Examples:
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITHOUT_FEDERATED_STORAGE_ENGINE=1
-DWITHOUT_PARTITION_STORAGE_ENGINE=1

If neither -DWITH_engine_STORAGE_ENGINE nor -DWITHOUT_engine_STORAGE_ENGINE are specified for a given storage engine, the engine is built as a shared module, or excluded if it cannot be built as a shared module.

Cmd1:- Either follow i) or ii) in this step

Note:Please be aware of this before proceeding this step
-DDEFAULT_CHARSET=charset_name
The server character set. By default, MySQL uses the latin1 (cp1252 West European) character set.
charset_name may be one of binary, armscii8, ascii, big5, cp1250, cp1251, cp1256, cp1257, cp850, cp852, cp866, cp932, dec8, eucjpms, euckr, gb2312, gbk, geostd8, greek, hebrew, hp8, keybcs2, koi8r, koi8u, latin1, latin2, latin5, latin7, macce, macroman, sjis, swe7, tis620, ucs2, ujis, utf8, utf8mb4, utf16, utf16le, utf32. The permissible character sets are listed in the cmake/character_sets.cmake file as the value of CHARSETS_AVAILABLE.
This value can be set at server startup with the --character_set_server option.
-DDEFAULT_COLLATION=collation_name
The server collation. By default, MySQL uses latin1_swedish_ci. Use the SHOW COLLATION statement to determine which collations are available for each character set.
This value can be set at server startup with the --collation_server option.

i). For Compiling with latin which is default use below cmd :-
Note:If we follow this step I.e utf8 character set & collation then In Step 6,we do not need to perform step 6.e
cmake \
-DCMAKE_INSTALL_PREFIX=/mysql_admin/mysql_binaries/mysqlbinaries-5.6.16 \
-DWITH_INNOBASE_STORAGE_ENGINE=1\
-DMYSQL_UNIX_ADDR=/mysql_data/my_amit.sock \
-DSYSCONFDIR=/mysql_admin/configuration_file \
.

or

cmake \
-DCMAKE_INSTALL_PREFIX=/mysql_admin/mysql_binaries/mysqlbinaries-5.6.16 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DMYSQL_UNIX_ADDR=/mysql_data/my_amit.sock \
-DSYSCONFDIR=/mysql_admin/configuration_file \
.

or

ii).For Compiling with utf8 use below cmd parameters setting in above cmd :-
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
Note:If we follow this step I.e utf8 character set & collation then In Step 6,we have to perform step 6.c

Cmd2:-make
Cmd3:-make install


d.Note:- This step is required If CMake fails, you might need to reconfigure by running it again with different options. If you do reconfigure, take note of the following:
If CMake is run after it has previously been run, it may use information that was gathered during its previous invocation. This information is stored in CMakeCache.txt. When CMake starts up, it looks for that file and reads its contents if it exists, on the assumption that the information is still correct. That assumption is invalid when you reconfigure.
Each time you run CMake, you must run make again to recompile. However, you may want to remove old object files from previous builds first because they were compiled using different configuration options.
To prevent old object files or configuration information from being used, run these commands on OS before re-running CMake:
cmds:-cd mysql-5.6.16
make clean

rm CmakeCache.txt

#~~#Step 6: Create configuration file
a. vi /mysql_admin/configuration_file/my.cnf
b. write below listed information into configuration file and save
[client]
port = 3310
socket = /mysql_data/my_amit.sock
# The MySQL server
[mysqld]
user=mysql_user
port = 3310
socket = /mysql_data/my_amit.sock
basedir=/mysql_admin/mysql_binaries/mysqlbinaries-5.6.16
pid-file =/mysql_data/my_amit.pid
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
tmpdir=/mysql_logs/mytmp/
datadir=/mysql_data/datadir/
innodb_data_home_dir = /mysql_data/ibtablespace/
innodb_mirrored_log_groups=1
innodb_log_group_home_dir = /mysql_data/iblogs
innodb_log_files_in_group=3
innodb_log_file_size=256M
log-bin = /mysql_logs/replication_log/amit-replication
log-error = /mysql_logs/error_log/amit-mysqllog.err
slow_query_log=on
long_query_time=1
slow_query_log_file=/mysql_logs/slow_logs/amit-master-slow.log
default-storage-engine = innodb
innodb_file_per_table

c. Note:- This step is only for if source installation in step 5 is build with -DDEFAULT_CHARSET=utf8 &
-DDEFAULT_COLLATION=utf8_general_ci
Add following lines in configuration files
cmd:-
echo "character-set-server = utf8" >> /mysql_admin/configuration_file/my.cnf
echo "collation-server = utf8_general_ci" >> /mysql_admin/configuration_file/my.cnf
echo "character-set-client-handshake = false" >> /mysql_admin/configuration_file/my.cnf

#~~#Step 7:- Now we have to initializes the MySQL data directory and creates the system tables that it should contains.
cmd:-
/mysql_admin/mysql_binaries/mysqlbinaries-5.6.16/scripts/mysql_install_db --user=mysql_user --basedir=/mysql_admin/mysql_binaries/mysqlbinaries-5.6.16 --defaults-file=/mysql_admin/configuration_file/my.cnf

Note: option user=mysql_user is used in order to initialized db with ownership mysql user

There are some third party tool which can help us in creation of configuration files with respect to inputs we mentioned there.
URL for Percona Configuration Wizard for MySQL is :-https://tools.percona.com/ or https://tools.percona.com/wizard


#~~#Step8: There are many ways by which we can start mysql and here we invoke mysql.server script method that use System V-style run directories (that is, /etc/init.d and run-level specific directories),
##Generally, you start the mysqld server in one of these ways:
a.Invoke mysqld directly. This works on any platform.
b.Invoke mysqld_safe, which tries to determine the proper options for mysqld and then runs it with those options. This script is used on Unix and Unix-like systems.
c.Invoke mysql.server. This script is used primarily at system startup and shutdown on systems that use System V-style run directories (that is, /etc/init.d and run-level specific directories), where it usually is installed under the name mysql. The mysql.server script starts the server by invoking mysqld_safe.
d.On Mac OS X, install a separate MySQL Startup Item package to enable the automatic startup of MySQL on system startup. The Startup Item starts the server by invoking mysql.server.
e.Use the Solaris/OpenSolaris service management framework (SMF) system to initiate and control MySQL startup.

After initializing db ,Now we are proceding to add service in System V-style run directories

Execution steps:-
a.Copy server script and add in system V-style run directories
cmd:-
cp /mysql_admin/mysql_binaries/mysqlbinaries-5.6.16/support-files/mysql.server /etc/init.d/mysql_amit_service

b.Optional if there is any old services which we need to remove :-
Removing any system startup links for old mysql service if any /etc/init.d/mysql ...
cmd:-update-rc.d -f old_mysql_service_name remove

c. Adding system startup for /etc/init.d/mysql_amit_service ...
cmd:-update-rc.d mysql_amit_service defaults

#~~#Step 9: Now you can start mysql with
/etc/init.d/mysql_amit_service start
or
/etc/init.d/mysql_amit_service start

Note: We can Shutdown with
/etc/init.d/mysql_amit_service stop
or
/etc/init.d/mysql_amit_service stop