Hello Guy’s,

Every System Admin and technical person who is working or doing any kind of High Availability stuff, He really needs bench marking to check and verify system health under development, here I am very beginner in this kind of system benchmarking to know how server/CPU/memory and network reacts when doing this and given load (purposely) on system to use or run the service or process. I will explain how I have done this in the part -2 😉

Memory Util

 

CPU Util

 

-Ravi

(1) Comment    Read More   

Introduction ~

I was planning to write article on Multi Master MySQL replication since long time; Finally started now!. Please refer the the article on “How to configure MySQL replication with one Master” URL ~ http://www.indiangnu.org/2007/mysql-replication-one-master-multiple-slave/

* Let me inform you all that Multi Master replication in MySQL is purely based on following two variables. It has nothing to do with replication technology used in MySQL replication….

mysql> show variables  like  ‘%increment_%’;

+—————————————+——-+
| Variable_name                                   | Value |
+—————————————+——-+
| auto_increment_increment    |  1    |
| auto_increment_offset               |  1    |
+—————————————+—–+
2 rows in set (0.00 sec)

mysql>

** Requirements ~

a) Master Hosts (2 master in my case) ~

master-1 => 10.66.66.194
master-2 => 10.66.90.135
b) Replication Slave (1 slave) ~
slave => 10.66.75.137

c) MySQL server (with replication support)

** Let us understand how it works ?


* Master-1 Server =>

Set following variables…

mysql> set auto_increment_increment=5;
mysql> set auto_increment_offset=1;

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 2     |
| auto_increment_offset    | 1     |
+————————–+——-+
2 rows in set (0.00 sec)

mysql>

** Create Table ~

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );

** Add Record now ~

mysql> INSERT INTO class VALUES (”,’Arun Bagul’);
mysql> INSERT INTO class VALUES (”,’Ravi Bhure’);
mysql> INSERT INTO class VALUES (”,’Karthik Appigita’);
mysql> INSERT INTO class VALUES (”,’Ameya Pandit’);

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      1 | Arun Bagul       |
|      3 | Ravi Bhure       |
|      5 | Karthik Appigita |
|      7 | Ameya Pandit     |
+——–+——————+
4 rows in set (0.00 sec)

mysql>

* Master-2 Server =>

Set following variables…

mysql> set auto_increment_increment=2;
mysql> set auto_increment_offset=2;

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 2     |
| auto_increment_offset    | 2     |
+————————–+——-+
2 rows in set (0.00 sec)

mysql>

** Create Table ~

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );

** Add Record now ~

mysql> INSERT INTO class VALUES (”,’Nilkanth Parab’);
mysql> INSERT INTO class VALUES (”,’Nishit Shah’);
mysql> INSERT INTO class VALUES (”,’Ram Krishna’);
mysql> INSERT INTO class VALUES (”,’Suhail Thakur’);

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      2 | Nilkanth Parab   |
|      4 | Nishit Shah      |
|      6 | Ram Krishna      |
|      8 | Suhail Thakur    |
+——–+——————+
4 rows in set (0.00 sec)

mysql>

** What is the importance of “auto_increment_increment” and “auto_increment_offset” ~

mysql> desc class;
+——–+————-+——+—–+———+—————-+
| Field  | Type        | Null | Key | Default | Extra          |
+——–+————-+——+—–+———+—————-+
| rollno | int(5)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30) | YES  |     | NULL    |                |
+——–+————-+——+—–+———+—————-+

auto_increment_offset => This is BASE value for column with “auto_increment” attribute (please refer the above example)
auto_increment_increment => This is the increment value for column with “auto_increment” attribute

** If you combine the both tables (master-1 and master-2) the final table will look like this ~

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      1 | Arun Bagul       |
|      2 | Nilkanth Parab   |
|      3 | Ravi Bhure       |
|      4 | Nishit Shah      |
|      5 | Karthik Appigita |
|      6 | Ram Krishna      |
|      7 | Ameya Pandit     |
|      8 | Suhail Thakur    |
+——–+——————+
8 rows in set (0.00 sec)

mysql>

** This is how Multi master replication works….

auto_increment_offset=Nth master server
auto_increment_increment=M

Where –
N => nth number of master server (on master-1 keep it 1 and on master-2 keep it 2 and so on..)
M => Total number of Master Server (2 in our case but better to keep this value high so that we can add new master server easily)

log-slave-updates => Slave server does not log to its own binary log any updates that are received from a Master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log.

** Make sure that MySQL is running and up on all master servers and slave server-

How to setup Multi Master MySQL replication ? –

Step 1] Create Database/Tables on  Master  & Slave Servers –

You can create DB on all master & slave server or  create on one server and export that DB on  rest of all servers…

Master-1 => Create DB and Table

mysql> create database student;

mysql> use student;

mysql> create table class ( rollno INT(5) NOT NULL PRIMARY KEY AUTO_INCREMENT , name VARCHAR(30) );

mysql> show tables;
+——————-+
| Tables_in_student |
+——————-+
| class             |
+——————-+
1 row in set (0.00 sec)

mysql> desc class;
+——–+————-+——+—–+———+—————-+
| Field  | Type        | Null | Key | Default         |      Extra          |
+——–+————-+——+—–+———+—————-+
| rollno | int(5)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(30) | YES  |     | NULL    |                |
+——–+————-+——+—–+———+—————-+
2 rows in set (0.00 sec)

mysql> SELECT * FROM class;
Empty set (0.00 sec)
mysql>

* Now take dump of “student” DB and export it on all master and Slave server…

[root@master-1~]# mysqldump -u root -p  -d student > /home/arunsb/student.sql

* SCP the dump file on master-2 and slave server ~
[root@master-1~]# scp /home/arunsb/student.sql arunsb@10.66.90.135:/tmp/student.sql
[root@master-1~]# scp /home/arunsb/student.sql arunsb@10.66.75.137:/tmp/student.sql

Login on master-2 and slave ~

mysql> create database student;

[root@master-2~]# mysql -u root -p student < /tmp/student.sql
Enter password:
[root@master-2~]#

[root@master-2~]# mysql -u root -p
Enter password:

mysql> use student

mysql> SELECT * FROM class;
Empty set (0.00 sec)
mysql>

** Please repeat the same steps on Slave server as well…

Step 2] Update “my.cnf” config file on master-1,master-2 and slave server –

[root@master-1~]# cat /etc/my.cnf

###########################
##MySQL replication setting

#Master setting(1)
server-id = 1
log-bin = /var/log/mysql/binary/mysql-bin.log
binlog-do-db = student
binlog-ignore-db = mysql
log = /var/log/mysql/mysql.log
auto_increment_offset=1
auto_increment_increment=5
log-slave-updates

##slave setting
master-port=3306
master-host=10.66.90.135
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student
###########################

[root@master-1~]#

[root@master-2~]# cat /etc/mysql/my.cnf

###########################
##MySQL replication setting

#Master setting(2)
server-id = 2
log-bin = /var/log/mysql/binary/mysql-bin.log
binlog-do-db=student
binlog-ignore-db = mysql
log = /var/log/mysql/mysql.log
auto_increment_offset=2
auto_increment_increment=5
log-slave-updates

##slave setting
master-port=3306
master-host=10.66.66.194
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student
###########################

[root@master-2~]#

* please create directory for binary log and set permission…

[root@master-1~]# mkdir -p /var/log/mysql/binary/
[root@master-1~]# chown mysql:adm  /var/log/mysql/ /var/log/mysql/binary/

[root@master-2~]# mkdir -p /var/log/mysql/binary/
[root@master-2~]# chown mysql:adm  /var/log/mysql/ /var/log/mysql/binary/

** MySQL Replication Slave ~

[root@slave~]# cat  /etc/my.cnf

[mysqld]

########################################
##slave setting
server-id=4
master-port=3306
master-host=10.66.90.135
master-user=replication
master-password=mypwd
master-connect-retry=60
replicate-do-db=student
########################################

[root@slave~]#


Step 3] Give Replication permission on both masters ~


** Master (1 & 2) ~

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replication’@’10.66.%.%’ IDENTIFIED BY ‘mypwd’;
Query OK, 0 rows affected (0.00 sec)

mysql>

Step 4] Restart MySQL on both master as well as replication slave server ~

** Please verify setting on master-1 and master-2 server…

* Master-1

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 5     |
| auto_increment_offset    | 1     |
+————————–+——-+
2 rows in set (0.00 sec)

* Master-2

mysql> show variables like ‘%increment_%’;
+————————–+——-+
| Variable_name            | Value |
+————————–+——-+
| auto_increment_increment | 5     |
| auto_increment_offset    | 2     |
+————————–+——-+
2 rows in set (0.00 sec)

** Please verify ‘master’ & ‘slave’ status on both masters(1 & 2) and slave –

mysql> show master status;
mysql> show slave status;

** Multi Master replication is started…

Step 5] Add few records on Master-1 & Master-2 server at same time ~

Add records on both master server at same time and check master and replication slave status as shown above….

** Add following on master-1

mysql> INSERT INTO class VALUES (”,’Arun Bagul’);
mysql> INSERT INTO class VALUES (”,’Ravi Bhure’);
mysql> INSERT INTO class VALUES (”,’Karthik Appigita’);
mysql> INSERT INTO class VALUES (”,’Ameya Pandit’);

** Add following on master-2

mysql> INSERT INTO class VALUES (”,’Nilkanth Parab’);
mysql> INSERT INTO class VALUES (”,’Nishit Shah’);
mysql> INSERT INTO class VALUES (”,’Ram Krishna’);
mysql> INSERT INTO class VALUES (”,’Suhail Thakur’);

** Please verify the numbers of records on both masters and slave….

mysql> SELECT * FROM class;
+——–+——————+
| rollno | name             |
+——–+——————+
|      1 | Arun Bagul       |
|      2 | Nilkanth Parab   |
|      6 | Ravi Bhure       |
|     11 | Karthik Appigita |
|     16 | Ameya Pandit     |
|     17 | Nishit Shah      |
|     22 | Ram Krishna      |
|     27 | Suhail Thakur    |
+——–+——————+
8 rows in set (0.00 sec)

mysql>

* So we all learned to configure multi-master MySQL replication. Enjoy!!

Regards,
Arun Bagul

(2) Comments    Read More   
Oct
21
Posted on 21-10-2009
Filed Under (Database & DBA) by Arun Bagul

What is ETL ?

Extract, Transform, and Load (ETL) is procedure to

a) Extracting data from outside sources
b) Transforming the data as ther operational requirements
c) Loading it into the end target (database or data warehouse)

1) Talend Open Studio ~ http://www.talend.com

Talend Open Studio operates as a code generator allowing data transformation scripts and underlying programs to be generated either in Perl or in Java.
Its GUI is made of a metadata repository and a graphical designer. The jobs are designed using graphical components, for transformation, connectivity or other operations. The threads created can be executed from within the studio or as standalone scripts.

2) CloverETL ~     http://www.cloveretl.com/

CloverETL is a Java-based data integration framework used to transform, cleanse, standardize and distribute data to applications, databases or warehouses. Its component-based structure allows easy customization and embeddability.

* CloverETL Designer – the graphical user interface to create and modify data transformations for CloverETL Server and Engine.
* CloverETL Engine – executes the transformations (run-time); can be embedded as a library. Available under LGPL.
* CloverETL Server – full-fledged server application with a rich WEB-based administrative interface, which leverages the existing CloverETL Engine.

3) Pentaho ~ http://www.pentaho.com/

The Pentaho BI Project is Open Source application software for enterprise reporting, analysis, dashboard, data mining, workflow and ETL capabilities for Business Intelligence (BI) needs.

4) Apatar ~ http://www.apatar.com/

Apatar is an open source ETL (Extract-Transform-Load) and mashup data integration software application. Other open source data integration projects are Clover.ETL, Pentaho Project, Talend Open Studio or Enhydra Octopus.

5) http://www.azinta.com/
Regards,

Arun

(0) Comments    Read More   
May
10
Posted on 10-05-2009
Filed Under (Database & DBA, MySQL Database, Oracle Database) by Arun Bagul

Introduction –

Partitioning is available in Oracle since 8i (few feature or type) version but in MySQL it is available after 5.1 version. Partitioning allows you to distribute portions of table across a file system (physically at different location). User can define the partitioning of table so that some portion of table can stored at different physical location….

Table is structure which has columns and rows. Partitioning can be applied to rows or volumes.

* Horizontal partitioning ~ Storing different rows of table to different physical location is called as horizontal partitioning of table.
* Vertical partitioning ~ Storing different columns of table to different physical location is called as vertical partitioning of table.

MySQL supports only support “Horizontal partitioning” !!

** Advantages of using partitions ~

a) Smaller and more manageable pieces of data
b) Faster access of data
c) Import / export at the partition Level
d) Each partitions is independent of other
e) Easy to use
f) Reduced recovery time
g) Failure impact is less

1] How to check whether MySQL installed on your system supports ‘partitioning’ ?

* Check MySQL version as…

root@laptop:~# mysqladmin version -u root -p

* Check ‘partition’ plugin is installed or not… ?

mysql> SHOW PLUGINS;
+————+———-+—————-+———+———+
| Name | Status | Type | Library | License |
+————+———-+—————-+———+———+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL | **

…..

+————+———-+—————-+———+———+
10 rows in set (0.00 sec)

mysql>

* Check above output. If you don’t see ‘partition’ entry in above query. Then MySQL version does not support partitioning. If it entry exist then check “have_partitioning” system variable is set to ‘YES’ or not….

mysql> SHOW VARIABLES LIKE ‘%partition%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| have_partitioning | YES |
+——————-+——-+
1 row in set (0.00 sec)
Partitioning
mysql>

2] Which storage engines (table type) supports partitioning?

MySQL partitioning engine runs in a separate layer and can interact with any of storage engine. So we can use most of the storage engine as backend/table type to store data. Note we must use same storage engine for all partitions of table.

3] Types of Partitions ~

There are 4 types of partitioning in MySQL or Oracle

a) Range Partitioning
b) List Partitioning
c) Hash Partitioning
d) Key Partitioning

NOTE ~ In any type of partitioning – partitions are always numbered automatically and in sequence starting with 0. When a new row is inserted into a partitioned table, these partition numbers that are used in identifying the correct partition. Also partition names are not case-sensitive. Number of partitions for the table, this must be expressed as a positive, non-zero integer literal with no leading zeroes, and may not be an expression.

MAXVALUE ~ It is an integer value that is always greater than the largest possible integer value (least upper limit).

** Let’s create database ‘mydata’ and create table with each partition type –

mysql> create database mydata;
Query OK, 1 row affected (0.33 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mydata |
| mysql |
+——————–+
3 rows in set (0.00 sec)

mysql>

A] Range Partitioning ~ table is partitioned by range of values. Each partition contains row which lies within a given range. The ranges should be
contiguous but not overlapping…

mysql> use mydata;
Database changed

mysql> CREATE TABLE cricket (
-> player_name VARCHAR(30),
-> country_name VARCHAR(30),
-> match_count INT,
-> run_scored INT NOT NULL
-> )
-> PARTITION BY RANGE (run_scored)
-> (
-> PARTITION p0 VALUES LESS THAN (26),
-> PARTITION p1 VALUES LESS THAN (51),
-> PARTITION p2 VALUES LESS THAN (76),
-> PARTITION p3 VALUES LESS THAN (101),
-> PARTITION p4 VALUES LESS THAN MAXVALUE
->
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables from mydata;
+——————+
| Tables_in_mydata |
+——————+
| cricket |
+——————+
1 row in set (0.00 sec)

mysql> desc cricket;
+————–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————–+————-+——+—–+———+——-+
| player_name | varchar(30) | YES | | NULL | |
| country_name | varchar(30) | YES | | NULL | |
| match_count | int(11) | YES | | NULL | |
| run_scored | int(11) | NO | | NULL | |
+————–+————-+——+—–+———+——-+
4 rows in set (0.00 sec)

mysql>

*** If cricketer’s score is…
<= 25 then row will go to p0 partition
<= 50 then row will go to p1 partition and so on ..
If score is more than 100 then row will go to p4 partition

* inserting few records to test behavior…

mysql> SELECT * FROM cricket;
+————-+————–+————-+————+
| player_name | country_name | match_count | run_scored |
+————-+————–+————-+————+
| Rahul | India | 2 | 16 |
| Sachin | India | 2 | 56 |
| Saurav | India | 2 | 80 |
| Sachin | India | 1 | 122 |
| Rahul | India | 1 | 153 |
| Saurav | India | 1 | 183 |
+————-+————–+————-+————+
6 rows in set (0.00 sec)

mysql>

** Let’s us check the physical location of database….

root@laptop:~# ls /var/lib/mysql/mydata/
cricket.frm cricket#P#p0.MYD cricket#P#p1.MYD cricket#P#p2.MYD cricket#P#p3.MYD cricket#P#p4.MYD db.opt
cricket.par cricket#P#p0.MYI cricket#P#p1.MYI cricket#P#p2.MYI cricket#P#p3.MYI cricket#P#p4.MYI
root@laptop:~#

B] LIST Partitioning ~ It is similar to range partitioning. But each partition is defined based on the columns value which is one of value in lists,
rather than in one of a set of contiguous ranges of values…

mysql> CREATE TABLE world (
-> country_name VARCHAR(25) NOT NULL,
-> country_id INT NOT NULL
-> )
-> PARTITION BY LIST (country_id)
-> (
-> PARTITION asia VALUES IN (91,95,35,20),
-> PARTITION europe VALUES IN (10,16,82,84),
-> PARTITION america VALUES IN (12,25),
-> PARTITION australia VALUES IN (60,65)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+——————+
| Tables_in_mydata |
+——————+
| cricket |
| world |
+——————+
2 rows in set (0.00 sec)

mysql>

* inserting few records to test behavior…

mysql> SELECT * FROM world;
+————–+————+
| country_name | country_id |
+————–+————+
| India | 91 |
| Sri Lanka | 95 |
| France | 10 |
| U.K. | 16 |
| Canada | 12 |
| Brazil | 60 |
+————–+————+
6 rows in set (0.00 sec)

mysql>

** What if ‘country_id’ value NOT matching with lists values ?

=> use IGNORE keyword while inserting records… let’s try

mysql> INSERT IGNORE INTO world VALUES (‘South Africa’,50);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM world;
+————–+————+
| country_name | country_id |
+————–+————+
| India | 91 |
| Sri Lanka | 95 |
| France | 10 |
| U.K. | 16 |
| Canada | 12 |
| Brazil | 60 |
+————–+————+
6 rows in set (0.00 sec)

mysql>

C] HASH Partitioning ~ HASH is used primarily to ensure an even distribution of data among a all number of partitions. If you do not include a
PARTITIONS clause while creating table with HASH partitioning, the number of partitions by defaults is one(1). The partition in
which the record is stored is determined by which_partition = MOD(expression, total_no_partition)

mysql> CREATE TABLE africa (
-> country_name VARCHAR(50),
-> population INT,
-> census_date DATE NOT NULL DEFAULT ‘2000-01-01’
-> )
-> PARTITION BY HASH (YEAR(census_date))
-> PARTITIONS 5;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+——————+
| Tables_in_mydata |
+——————+
| africa |
| cricket |
| world |
+——————+
3 rows in set (0.01 sec)

mysql>

mysql> SELECT * FROM africa;
+————–+————+————-+
| country_name | population | census_date |
+————–+————+————-+
| Nigeria | 2278032 | 2008-09-18 |
| Egypt | 21278032 | 2008-05-28 |
| South Africa | 1678032 | 2009-04-20 |
+————–+————+————-+
3 rows in set (0.00 sec)

mysql>

D] KEY Partitioning ~ It is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression.

4] Subpartitioning ~

It is also known as composite partitioning in which partition is divided in to sub partition. Use SUBPARTITION clauses while creating table.

**Few useful command to know more about tables –

mysql> SHOW CREATE TABLE cricket \G
*************************** 1. row ***************************
Table: cricket
Create Table: CREATE TABLE `cricket` (
`player_name` varchar(30) DEFAULT NULL,
`country_name` varchar(30) DEFAULT NULL,
`match_count` int(11) DEFAULT NULL,
`run_scored` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (run_scored)
(PARTITION p0 VALUES LESS THAN (26) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (51) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (76) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (101) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.00 sec)

mysql>

** Below statement will give information about all tables in given DB…

mysql> SHOW TABLE STATUS FROM mydata;

Thank you,
Arun Bagul

(2) Comments    Read More   
Oct
28
Posted on 28-10-2008
Filed Under (Database & DBA, Oracle Database) by Arun Bagul

Introduction –

Oracle Database is widely used commercial Database on Unix/Linux and Windows platform….
This article explain the step by step installation of Oracle 10g Release 2.0 on Ubuntu 8.04111!!
Please refer the Oracle installation guide for System requirement…

1] Hardware requirement –

RAM – minimum 1024 MB of physical memory
Disk space -1.5 GB  to 3.5 GB of disk space, depends on the installation…
Minimum 400 MB of disk space in the /tmp directory

arunsb@arun:~$ free  -m
arunsb@arun:~$ df -h
arunsb@arun:~$ df -h /tmp

2] Software dependency –

Make sure that following packages are installed on your system!!

make-3.8
gcc-4.2
libdb4.2
libdb4.2++-dev
libstdc++6
libstdc++6-4.2-dev
libmotif-dev
libmotif3

root@arun:~# apt-get install  build-essential libc6-dev  libmotif-dev  libmotif3
root@arun:~# apt-get install  xauth libxp6 libxt6 libxtst6 libaio1  glibc-source
root@arun:~# apt-get install  rpm

root@arun:~# ln -s /usr/bin/awk   /bin/awk
root@arun:~# ln -s /usr/bin/basename     /bin/basename
root@arun:~# ln -s /usr/bin/rpm    /bin/rpm

root@arun:~# dpkg -l

3] Operating System Groups and Users for Oracle –

* oinstall – Oracle Inventory group
* dba       – The OS DBA group
* oracle   – Oracle software owner  (in mycase arunsb is oracle user)

root@arun:~# id oracle
id: oracle: No such user
root@arun:~#
root@arun:~# grep oinstall /etc/group
root@arun:~# grep dba  /etc/group

NOTE – “oinstall” should be primary group of oracle user. It is not necessary to use “oracle” as username, you can name but make sure that the “oinstall” is primary group of that user!!

root@arun:~# addgroup oinstall
Adding group ‘oinstall’ (GID 1001) …
Done.
root@arun:~#

root@arun:~# addgroup  dba
Adding group `dba’ (GID 1002) …
Done.
root@arun:~#

root@arun:~# addgroup nobody
Adding group `nobody’ (GID 1000) …
Done.
root@arun:~#

root@arun:~# useradd -g oinstall -G dba   arunsb
root@arun:~#

root@arun:~# id arunsb
uid=1000(arunsb) gid=1001(oinstall) groups=1001(oinstall),1002(dba)
root@arun:~#

*** other setting –

root@arun:~# echo “Red Hat Linux release 3.0 (drupal)” > /etc/redhat-release

root@arun:~# cat /etc/redhat-release
Red Hat Linux release 3.0 (drupal)
root@arun:~#

4] Configure Kernel Parameters –

Add following kernel variable value as shown below in sysctl.conf file, then run command “sysctl -p” to update the setting…
root@arun:~# tail -n 16  /etc/sysctl.conf
###############################
## Adding setting for oracle 10g

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144

##############################
root@arun:~#

* Use below command to set above kernel variables…

root@arun:~# sysctl -p

5] Oracle user profile and envirnoment –

Update /etc/security/limits.conf file as shown below….

root@arun:~# vi /etc/security/limits.conf
root@arun:~# tail  /etc/security/limits.conf
#######################
## Added for arunsb (oracle user)

arunsb soft nproc 2047
arunsb hard nproc 16384
arunsb soft nofile 1024
arunsb hard nofile 65536

#######################
#
root@arun:~#

Add following setting in /etc/pam.d/login file
root@arun:~# vi /etc/pam.d/login

###################################
# oracle user –
session    required   pam_limits.so
###################################

** set envirnoment variables in user profile file “/home/arunsb/.bashrc”  as shown below

arunsb@arun:~$ tail  /home/arunsb/.bashrc
###########################
# Setting for Oracle 10g
export DISPLAY=”:0.0″
export ORACLE_OWNER=arunsb
export ORACLE_BASE=/u01/app
export ORACLE_HOME=/u01/app/product/10.2.0/db_1
export ORACLE_HOSTNAME=arun.mylaptop.com
PATH=$PATH:/u01/app/product/10.2.0/db_1/bin

##########################
#
arunsb@arun:~$

6] Oracle inventory and installation setting –

you need to create oralce Inventory director where Oracle stores all information about oralce software installed on your system. Also use same direcory
for oracle installation make sure to set proper path in  user profile file ie .barshrc

root@arun:~# mkdir -p  /u01/app/oraInventory
root@arun:~# ls -ld /u01/app/oraInventory
drwxr-xr-x 2 root root 4096 2008-11-23 22:57 /u01/app/oraInventory
root@arun:~# chown arunsb:oinstall -R /u01/app/oraInventory
root@arun:~# ls -ld /u01/app/oraInventory
drwxr-xr-x 2 arunsb oinstall 4096 2008-11-23 22:57 /u01/app/oraInventory
root@arun:~#

arunsb@arun:~$ mkdir -p /u01/app/product/10.2.0/db_1

7] It’s  time to install Oracle –

arunsb@arun:/root/oracle-setup/oracle_server-10g$ ./runInstaller
Starting Oracle Universal Installer…

In between installation you need to run following shell scripts as root user…..

root@arun:~# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory to 770.
Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete
root@arun:~# /u01/app/product/10.2.0/db_1/root.sh
Running Oracle10 root.sh script…

The following environment variables are set as:
ORACLE_OWNER= arunsb
ORACLE_HOME=  /u01/app/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …
Creating /etc/oratab file…
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
root@arun:~#

8] Errors during installation –

NOTE – Installation failed many times with following ERRORs … please don’t forget to create soft link for awk , basename , rpm

Exception Name: MakefileException
Exception String: Error in invoking target ‘ntcontab.o’ of makefile

Exception Name: MakefileException
Exception String: Error in invoking target ‘utilities ctx_on’ of makefile

=================================================
The following J2EE Applications have been deployed and are accessible at the URLs listed below.

iSQL*Plus URL:
http://arun.mylaptop.com:5560/isqlplus
iSQL*Plus DBA URL:
http://arun.mylaptop.com:5560/isqlplus/dba
Oracle Enterprise manager URL
http://arun.mylaptop.com:5560/isqlplus/dba
=================================================

Thank you,
Arun Bagul

(1) Comment    Read More   

TIMESTAMP DEFAULT :- in this when new row get inserted or existing row get updated that time current timestamp get inserted.

CURRENT_TIMESTAMP :- in this when row get inserted that time current timestamp get inserted.no change on update.

ON UPDATE CURRENT_TIMESTAMP :- when row get affected that time current timestamp get inserted.

Regards

Prasanna Shejwal

(2) Comments    Read More   
Jan
23
Posted on 23-01-2008
Filed Under (Database & DBA) by Arun Bagul

Introduction – Few days back somebody ask me about “How to swap data in two columns?”. Before that I have never thought about such situation…

1] Login to MySQL Server and create Datatabase –

root@IndianGNU:/home/arun.bagul# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 137 to server version: 5.0.21-Debian_3ubuntu1-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> show databases;
+———————————-+
| Database |
+———————————-+
| information_schema |
| mysql |
+———————————-+
2 rows in set (0.04 sec)

mysql>
mysql> create database arunbagul;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+———————————-+
| Database |
+———————————-+
| information_schema |
| mysql |

| arunbagul |
+———————————+
3 rows in set (0.04 sec)

2 ] Now create Table in ‘arunbagul’ database –

mysql> use arunbagul;
Database changed

mysql> create table student ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY , name varchar(30), surname varchar(30) );
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+———————————–+
| Tables_in_arunbagul |
+———————————–+
| student |
+———————————–+
1 row in set (0.00 sec)

mysql>

mysql> desc student;
+———+————-+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+—————-+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(30) | YES | | NULL | |
| surname | varchar(30) | YES | | NULL | |
+———+————-+——+—–+———+—————-+
3 rows in set (0.01 sec)

mysql>

3 ] Now insert values in ‘student’ table –

mysql> INSERT INTO student VALUES (”,’Arun’ , ‘Bagul’);
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> INSERT INTO student VALUES (”,’Yogesh’ , ‘Nikam’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO student VALUES (”,’Nishit’ , ‘Shah’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO student VALUES (”,’Binish’ , ‘Philip’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> INSERT INTO student VALUES (”,’Deven’ , ‘Jadhav’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from student;
+—-+——–+—————–+
| id | name | surname |
+—-+———-+—————+
| 1 | Arun | Bagul |
| 2 | Yogesh | Nikam |
| 3 | Nishit | Shah |
| 4 | Binish | Philip |
| 5 | Deven | Jadhav |
+—-+——–+———+
5 rows in set (0.00 sec)

mysql>

4 ] Swap data in two cloumns in ‘student’ table –

mysql> UPDATE student as tab1, student as tab2 set tab1.name = tab1.surname, tab1.surname=tab2.name WHERE tab1.id = tab2.id AND tab2.id = tab1.id;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> select * from student;

+—-+———-+—————+
| id | name | surname |
+—-+———-+—————+
| 1 | Bagul | Arun |
| 2 | Nikam | Yogesh |
| 3 | Shah | Nishit |
| 4 | Philip | Binish |
| 5 | Jadhav | Deven |
+—-+———-+————–+
5 rows in set (0.00 sec)

mysql>

Thank you,

Arun Bagul , Prasnna and Devendra

(2) Comments    Read More   
Nov
29
Posted on 29-11-2007
Filed Under (Database & DBA) by Arun Bagul

 when  you loss  mysql  root  pasword. then  stop  mysql  server.. then  follow  below  steps.

 * Method -1

1]  Start  MySQL without  loading  grant access…

root@indiangnu:~# mysqld –skip-grant-tables &
[1] 10197
root@indiangnu:~# 071129  9:41:39  InnoDB: Started; log sequence number 0 43655
071129  9:41:39 [Note] mysqld: ready for connections.
Version: ‘5.0.22-Debian_0ubuntu6.06.3-log’  socket: ‘/var/run/mysqld/mysqld.sock’  port: 3306  Debian Etch distribution
2] Start MySQL client

root@indiangnu:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-Debian_0ubuntu6.06.3-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use  mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> UPDATE user SET Password=PASSWORD(‘password’) WHERE User=’root’;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye
root@indiangnu:~#

* Then  kill current  MySQL process service… and  start  normal  MySQL  daemon

* Method -2  

1]  Start  MySQL  in safe mode without  loading  grant access…

root@indiangnu:~# mysqld_safe –skip-grant-tables &
[1] 10216
root@indiangnu:~# Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[10277]: started

2] Start MySQL client
root@indiangnu:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22-Debian_0ubuntu6.06.3-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> UPDATE user SET Password=PASSWORD(‘password’) WHERE User=’root’;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
root@indiangnu:~#

Thank  you,

Arun Bagul

(2) Comments    Read More   
Aug
03
Posted on 03-08-2007
Filed Under (Database & DBA) by Arun Bagul

What is GreenSQL?

GreenSQL is an Open Source database firewall used to protect databases from SQL injection attacks. GreenSQL works in a proxy mode and has built in support for MySQL. The logic is based on evaluation of SQL commands using a risk scoring matrix as well as blocking known db administrative commands (DROP, CREATE, etc). GreenSQL is distributed under the GPL license.

How it works?

The GreenSQL database firewall provides database protection using a number of methods:

  • Blocking administrative and sensitive SQL commands.
  • Calculating risk of each query and blocking queries with high risk.

1) Blocking specific SQL commands

GreenSQL uses a pattern matching engine to find commands that are considered “illegal“. For example GreenSQL blocks database administrative commands. The GreenSQL administrator can decide which commands to block using the GreenSQL web management console. The administrator can also approve a blocked query by adding them to a whitelist.
Risk Calculation

For each query GreenSQL calculates its risk. After the risk is calculated GreenSQL can block the query or just create a warning message. There are a number of factors that are taken into account when calculating risk. For example:

* Access to sensitive tables increases risk query (users, accounts, credit information)
* Comments inside SQL commands increases query risk
* Usage of an empty password string
* Found ‘or’ token inside query
* Found SQL expression that always return true
* Comparison of constant values

How commands are blocked?

When GreenSQL determines that a query should be blocked it will generate an empty result set to send back to the front end so that the application can continue gracefully.
How Whitelist works?

Each time GreenSQL considers a SQL query as a security risk – it is blocked. You can alter this behavior for a specific query by explicitly allowing its pattern.
What is a GreenSQL Listener?

A GreenSQL Listener object is the heart of the GreenSQL Databae Firewall. A Listener is a proxy object used to connect queries from the frontend to a specific backend server. Before passing the query to the backend it is checked by the Listener to determine if it is malicious and if so how it should be handled (block, alert, pass).


GreenSQL

Thank you,
Arun Bagul

(0) Comments    Read More   
Jul
19
Posted on 19-07-2007
Filed Under (Database & DBA) by Arun Bagul

Introduction – The SQL so far is part of the standard that all SQL implementations. Now let’s about the most useful, but most difficult areas of SQL – built-in SQL functions.

Types of functions –

Like most computer languages, SQL includes the capability to perform functions on data. However, each database vendor has implemented their own set of functions. Not only does each vendor choose which functions they will support, different vendors implement the same functionality using different syntax! This basically means that as soon as you open the door to functions, your database code stops being portable. For traditional database applications, that is less of an issue, but for web-based applications using tools like PHP, it means that the SQL functions you embedded in your web application will break when you move from MySQL to Oracle or to SQL-Server. To make matters worse, SQL functions often provide significant performance gains for all sorts of database manipulation, particularly in the case of web applications.

So what’s a we should to do? My advice is to use functions anywhere you can produce a measurable performance gain and document thoroughly what database the SQL was intended to work with. As we’ll talk about in a future column, moving any function-oriented code to a database stored procedure and essentially “black-boxing” it is an excellent approach — this lets the DBA rewrite the stored procedures for optimal performance — but some databases do not support stored procedures, so this is not a perfect solution. There are a number of functions that are available in a wide range of databases, so you can usually get the functionality you need as long as the functions are not too exotic.

There are four basic types of functions –

1) Numeric functions – for statistical, trigonometric, and other mathematical operations,
2) Text functions – for formatting and manipulating text values,
3) Time/date functions – used to parse date values as well as manipulate the date itself, and
4) System functions – for returning database-specific information (such as a username).

How do you know what functions your database supports? Unfortunately, the only reliable way is to check the documentation. As a general rule of thumb, the more a database costs (or the more widely it is used for open source databases), the more functions it supports. Functions open up a huge range of additional functionality for SQL developers, but at the cost of portability between database platforms. However, functions provide improved functionality and performance for data-intensive applications.

How to use functions?

Functions in SQL are typically used in WHERE clauses, though they generally can be used anywhere you’d use a field name or value. One of the easiest ways to learn functions is to see a few in action. We’ll start with a SELECT statement to find names longer than 12 characters…

Step(1) Create Table – student –

mysql> use SQL_function;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> CREATE TABLE student ( name varchar(40) );

Query OK, 0 rows affected (0.00 sec)
mysql>

mysql> show tables;
+————————+
| Tables_in_SQL_function |
+————————+
| student |
+————————+
1 row in set (0.00 sec)

mysql> desc student;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| name | varchar(40) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
1 row in set (0.00 sec)

Step(2) INSERT Few records in student table –

mysql> INSERT INTO student VALUES(‘Arun Bagul’);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO student VALUES(‘Sri madhanvan’);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO student VALUES(‘Hari madhanvan’);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT name FROM student;
+—————-+
| name |
+—————-+
| Arun Bagul |
| Sri madhanvan |
| Hari madhanvan |
+—————-+
3 rows in set (0.00 sec)

mysql>

Step(3) Use functions in WHERE clause –

mysql> SELECT name FROM student WHERE length(name) > 12 ;
+—————-+
| name |
+—————-+
| Sri madhanvan |
| Hari madhanvan |
+—————-+
2 rows in set (0.00 sec)

mysql>

Thank you,
Arun Bagul

(0) Comments    Read More   

www.flickr.com
arunbagul's photos More of arunbagul's photos
Get Adobe Flash player
-->