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.)


Tuesday, August 19, 2014

MariaDB Audit Plugin for MySQL

                                                      MariaDB Audit Plugin for MySQL
MariaDB Audit Plugin for MySQL:- The library is included in a tarball (server_audit-VERSION.tar.gz ) as 32-bit and 64-bit version for Linux for both, debug and non-debug versions of MariaDB and MySQL. I am using server_audit-1.1.7.tar.gz here.

Step1: Download MariaDB Audit Plugin for MySQL & untar the compressed tarball.
After untar we will get package of 32-bit and 64-bit version for Linux for both, debug and non-debug versions of MySQL.

Step2: Copy file that's meet the requirement and place at MySQL plugin dir mentioned in MySQL variable:-SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
Note:Make sure for assign required ownership & privileges

Step 3: load plugin in MySQL:-
#~a~#The plugin can be loaded from the command-line as a start-up parameter, or it can be set in the configuration file (i.e.,
my.cnf or my.ini). Below is an excerpt from a configuration file, showing the relavent line to load this plugin. To use
this option from the command-line at start-up, just add a double-dash (e.g., pluginload).

[mysqld]
...
pluginload=server_audit=server_audit.so
...

#~b~#Another way to install this plug-in is to execute the INSTALL PLUGIN statement from within MySQL. You would need to
use an administrative account which has INSERT privilege for the mysql.plugin table. To do this, you would execute the
following within the mysql client or an equivalent client:
INSTALL PLUGIN server_audit SONAME 'server_audit.so';

Note:
--The variables that will be used by the plugin (see the Configuration section) will be unknown to the
server until the plugin has been loaded the first time. The database server will not start successfully if
these variables are set in the configuration file before the audit plugin has been loaded at least once
before.

--The UNINSTALL PLUGIN statement may be used to uninstall a plugin. For the auditing plugin, you might want to disable
this possibility. To do this, you could add the following line to the configuration file after the plugin is loaded once:
[mysqld]
...
pluginload=server_audit=server_audit.so
server_audit=FORCE_PLUS_PERMANENT
...

--Once you've added the option above to the server's configuration file and restarted the server, if someone tries then to
uninstall the audit plugin, an error message will be returned. Below is an example of this with the error message:
UNINSTALL PLUGIN server_audit;
ERROR 1702 (HY000):Plugin 'server_audit' is force_plus_permanent and can not be unloaded

Database Migration [ Oracle to MySQL ]

                            Database Migration [ Oracle to MySQL ]

#~~#Action Plan for Database Migration with best practices:
1.Prepare to Migrate
2.Test the Migration Process
3.Test the Migrated Test Database
4.Prepare and Preserve the Source Database
5.Migrate the Production Database
6.Tune and Adjust the New Production Database

#~~#Database Migration:

-1-Schema Migration Action Plan from Oracle to MySQL:
Migrate oracle schema into MySQL either manually or you can also use software for schema http://www.convert-in.com/ora2sql.htm but here if using third party utility then please make sure it  migrated into optimal state.This one is the most important step in migration and should be properly verified in order to avoid any unnecessary service outage in future.
There are also some third party tools that can help in complete migration,Here we have shared plan for doing manual migration freely without any cost.
Tools are :
i)  Oracle-to-MySQL
link:- http://dbconvert.com/convert-oracle-to-mysql-pro.php
ii) SQLways - Oracle to MySQL data migration
link:- http://www.ispirer.com/products/oracle-to-mysql-migration
any many more third party utitiles are there.
Note:There is one blog that I have found later after migration but i would like to list it here for your help its link is http://tkurek.blogspot.in/2013/04/migrate-oracle-to-mysql.html

Imp.Note:Will soon upload datatype & functioanlity comparision for Oracle and MySQL.

-2-Procedure/Function/Triggers Migration Action Plan from Oracle to MySQL:
It includes cnversion of follwing action items
i).Syntax conversion.
ii).Conversion for Oracle built-in functionality into MySQL built-in functionality if available else create user defined funtionality for the same behaviour.
iii).User Defined Method Conversion.

-3-Data Migration Action Plan from Oracle to MySQL:
a). Take dump from oracle using below listed format into dumpfiles
SET TRIMSPOOL ON
SET PAGESIZE  0
SET ECHO OFF
SET FEEDBACK  OFF
SET HEADING   OFF
Spool /path/filename.csv
query(i.e select Col1||','||','||Col2||','||Col3 from tablename; Note:Please make sure the output format of column in query should be as per MySQL format)
quit

b). Restore dumpfiles into MySQL using load command or mysql import utilities for dumpfile that we get from step 2.

Friday, August 1, 2014

SQLPLUS Parameters


SET TERM      OFF
-- TERM = ON will display on terminal screen (OFF = show in LOG only)

SET ECHO      ON
-- ECHO = ON will Display the command on screen (+ spool)
-- ECHO = OFF will Display the command on screen but not in spool files.
-- Interactive commands are always echoed to screen/spool.

SET TRIMOUT   ON
-- TRIMOUT = ON will remove trailing spaces from output

SET TRIMSPOOL ON
-- TRIMSPOOL = ON will remove trailing spaces from spooled output

SET HEADING   OFF
-- HEADING = OFF will hide column headings

SET FEEDBACK  OFF
-- FEEDBACK = ON will count rows returned

SET PAUSE     OFF
-- PAUSE = ON .. press return at end of each page

SET PAGESIZE  0  
-- PAGESIZE = height 54 is 11 inches (0 will supress all headings and page brks)

SET LINESIZE  80
-- LINESIZE = width of page (80 is typical)

SET VERIFY    OFF
-- VERIFY = ON will show before and after substitution variables

-- Start spooling to a log file
SPOOL C:\TEMP\MY_LOG_FILE.LOG
--
-- The rest of the SQL commands go here
--
SELECT * FROM TABLE_NAME;
SPOOL OFF