Showing posts with label Database. Show all posts
Showing posts with label Database. Show all posts

Friday, February 17, 2017

Replication Set in MongoDB – HowTo

Following write up describe how one can set up Replica Set in MongoDB (3x version). It is tested on CenOS-6x version. The same setup should work in any *nix based system. 

The IPs cited here are for example only.

A.  Participating Servers


1.   primary-ins   ========    172.16.20.3
2.   secondary-ins ========    172.16.20.4
3.   arbiter       ========     172.16.20.5

# cat /etc/centos-release
CentOS release 6.8 (Final)
    
     [On Openstack cloud platform]

B.   Host resolution (on all three servers)

     /etc/hosts
    
     172.16.20.3          primary-ins    
     172.16.20.4          secondary-ins  
     172.16.20.5          arbiter  

C.        Installed packages (On all three)

    
mongodb-org-shell-3.4.2-1.el6.x86_64
     mongodb-org-tools-3.4.2-1.el6.x86_64
     mongodb-org-server-3.4.2-1.el6.x86_64
     mongodb-org-mongos-3.4.2-1.el6.x86_64
     mongodb-org-3.4.2-1.el6.x86_64 
    

D.  Firewall considerations

* MongoDB listen on all interfaces instead of Loopback on all
  three hosts.
       [tcp 0  0 0.0.0.0:27017 0.0.0.0:*  LISTEN  22913/mongod]
    

     1. IPTABLES:

        Allowed entire subnet in Iptables INPUT chain for port 27017         (in all three hosts)
          
         # iptables -I INPUT -p tcp -s 172.16.20.0/24 --dport 27017 -j ACCEPT
         # service iptables save
         iptables: Saving firewall rules to /etc/sysconfig/iptables:[  OK  ]
         # service iptables restart
         iptables: Setting chains to policy ACCEPT: filter          [  OK  ]
         iptables: Flushing firewall rules:                         [  OK  ]
         iptables: Unloading modules:                               [  OK  ]
         iptables: Applying firewall rules:                         [  OK  ]
          
         # iptables -L | grep 27017
         ACCEPT     tcp  --  172.16.20.0/24   anywhere   tcp dpt:27017
              2.         SeLinux
          
           SeLinux changed from Enforcing to Permissive:
           # getenforce
            Enforcing
             
           #setenforce permissive

E.  Replication Setup (all three) /etc/mongod.conf

     replication:

       ## Replica Set name, consistent across all the systems
         replSetName: myRepSet

       ## Operation Log file size
         oplogSizeMB: 64

F.  Restart Mongo service (on all three, any order)

# service mongod restart 

G.  Initiate replica set

Connect to any Mongo Server and run rs.initiate() to initiate the new replica set (preferably connect to the one that would act as Primary):
    
# mongo
      
       > rs.initiate()
       {
"info2" : "no configuration specified. Using a default configuration for the set",
                     "me" : "primary-ins:27017",
                     "ok" : 1
       }
       myRepSet:OTHER>
    
    
     Note:
Once the initialization is done, the prompt would change to;
     myRepSet:PRIMARY>
    

H.  Add replica set members


Now, while on the Primary Server's prompt, add the other members (Secondary and the Arbiter) as follows;

     i.     Add seondary:
              myRepSet:PRIMARY> rs.add("secondary-ins:27017")
              { "ok" : 1 }

       ii.    Add arbiter
              myRepSet:PRIMARY> rs.addArb("arbiter:27017")
              { "ok" : 1 }

I.  Connect to other two Mongo servers and check the prompt(s):

     myRepSet:SECONDARY>
     myRepSet:ARBITER>

J.  Allow read from secondary members

Execute rs.slaveOk(). This is to allow you to read from secondary members (including the Arbiter);
    
myRepSet:SECONDARY> rs.slaveOk()
myRepSet:ARBITER> rs.slaveOk()

K.  Test

    Create a DB and insert a document to a collection on the primary
and check if it that has been replicated to the secondary;
    
     i.   Create DB and insert document on Primary Mongo server;
          
        myRepSet:PRIMARY> use test_replica
        switched to db test_replica
             
        myRepSet:PRIMARY> db.movie.insert({"name":"Jungle Book"})
        WriteResult({ "nInserted" : 1 })

     ii.  On the secondary Mongo server;
          
           myRepSet:SECONDARY> show dbs
           admin         0.000GB
           local         0.000GB
           test_replica  0.000GB
             
           myRepSet:SECONDARY> use test_replica
           switched to db test_replica
           myRepSet:SECONDARY> show collections
           movie
             
           myRepSet:SECONDARY> db.movie.find({})
           { "_id" : ObjectId("58963c37bede8f63243f5e52"), "name" : "Jungle Book" }
          
    
     iii. On Arbiter (Remember Arbiter does not take part in Data
replication, its only job is to elect member during a Failover)
          
myRepSet:ARBITER> show dbs
          admin  0.000GB
          local  0.000GB
               

L.   Check the configuration


myRepSet:PRIMARY> rs.conf()
{
        "_id" : "myRepSet",
        "version" : 3,
        "protocolVersion" : NumberLong(1),
        "members" : [
                {
                        "_id" : 0,
                        "host" : "primary-ins:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 1,
                        "host" : "secondary-ins:27017",
                        "arbiterOnly" : false,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                },
                {
                        "_id" : 2,
                        "host" : "arbiter:27017",
                        "arbiterOnly" : true,
                        "buildIndexes" : true,
                        "hidden" : false,
                        "priority" : 1,
                        "tags" : {

                        },
                        "slaveDelay" : NumberLong(0),
                        "votes" : 1
                }
        ],
        "settings" : {
                "chainingAllowed" : true,
                "heartbeatIntervalMillis" : 2000,
                "heartbeatTimeoutSecs" : 10,
                "electionTimeoutMillis" : 10000,
                "catchUpTimeoutMillis" : 2000,
                "getLastErrorModes" : {

                },
                "getLastErrorDefaults" : {
                        "w" : 1,
                        "wtimeout" : 0
                },
                "replicaSetId" : ObjectId("589632c875b52533db9c7540")
        }

}

Thursday, March 19, 2015

MySQL Cluster Setup on CentOS-6x

MySQL Cluster is a technology providing shared-nothing clustering and auto-sharding for the MySQL database management system. It is designed to provide high availability and high throughput with low latency, while allowing for near linear scalability.   
--- From Wikipedia


Note(s):
1. Tested on Centos-6.6 (64 bit) openstack instances.
2. Iptables is ON (specific iptables rules are given below).
3. SeLinux is Permissive (setenforce 0)
4. SeLinux Bollean values turned on for MySQL user;
# setsebool -PV allow_user_mysql_connect on
# setsebool -PV mysql_connect_any on


Architecture:
I. Management Node  
IP: 172.16.20.21

II. Node - 1   (Data and SQL Node)
IP: 172.16.20.22  

III.        Node - 2   (Data and SQL Node)
IP: 172.16.20.23


A. Downloads:
For all nodes download the following package;
http://dev.mysql.com/get/Downloads/MySQL-Cluster-7.4/MySQL-Cluster-gpl-7.4.4-1.el6.x86_64.rpm-bundle.tar .


B. Installation on Management Node (IP: 172.16.20.21):

1. Install the needed packages;
yum install perl libaio -y

2. Remove the following mysql library as it would conflict with cluster packages;
yum remove mysql-libs

3. Extract the compressed package and install it;
i. tar -xvf MySQL-Cluster-gpl-7.4.4-1.el6.x86_64.rpm-bundle.tar
ii. rpm -ivh MySQL-Cluster-server-gpl-7.4.4-1.el6.x86_64.rpm

4. Take a note of the initial root password of MySql
You will find that password in '/root/.mysql_secret'.

C. Installation on Data Node (IP: 172.16.20.22, 172.16.20.23):
1. Install the needed packages;
yum install perl libaio -y

2. Remove the following mysql library as it would conflict with cluster packages;
yum remove mysql-libs

3. Extract the compressed package and install it;
i. tar -xvf MySQL-Cluster-gpl-7.4.4-1.el6.x86_64.rpm-bundle.tar
ii. rpm -ivh MySQL-Cluster-server-gpl-7.4.4-1.el6.x86_64.rpm

4. Take a note of the initial root password of MySql:
You will find that password in '/root/.mysql_secret'.

5. Install the addtional MySQL client package on SQL Nodes:
rpm -ivh MySQL-Cluster-client-gpl-7.4.4-1.el6.x86_64.rpm


D. Installation on SQL NOdes (IP: 172.16.20.22, 172.16.20.23):

1. Install the needed packages;
yum install perl libaio -y

2. Remove the following mysql library as it would conflict with cluster packages;
yum remove mysql-libs

3. Extract the compressed package and install it;
i. tar -xvf MySQL-Cluster-gpl-7.4.4-1.el6.x86_64.rpm-bundle.tar
ii. rpm -ivh MySQL-Cluster-server-gpl-7.4.4-1.el6.x86_64.rpm

4. Take a note of the initial root password of MySql:
You will find that password in '/root/.mysql_secret'.

5. Install the addtional MySQL client package on SQL Nodes:
rpm -ivh MySQL-Cluster-client-gpl-7.4.4-1.el6.x86_64.rpm


E. Configurations:
A. Data and SQL Nodes;

# mkdir -p /usr/local/mysql/data
# chown -R mysql:mysql /usr/local/mysql/data

vi /etc/my.cnf
[mysqld]
# Options for mysqld process:
ndbcluster                      # run NDB storage engine

[mysql_cluster]
# Options for MySQL Cluster processes:
ndb-connectstring=172.16.20.21  # location of management server


B. Management node:
# mkdir /var/lib/mysql-cluster
# chown -R mysql:mysql /var/lib/mysql-cluster
# cd /var/lib/mysql-cluster

vi config.ini

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=2    # Number of replicas
DataMemory=80M    # How much memory to allocate for data storage
IndexMemory=18M   # How much memory to allocate for index storage
 # For DataMemory and IndexMemory, we have used the
 # default values. Since the "world" database takes up
 # only about 500KB, this should be more than enough for
 # this example Cluster setup.

ServerPort=50501  # This is to allocate a fixed port through which one node is connected to the other node within the cluster.
 # By default, this port is allocated dynamically in such a way as to ensure that no two nodes on the same host
 # computer receive the same port number.
 # To open specific ports in a firewall to permit communication between data nodes and API nodes (including SQL
 # nodes), you can set this parameter to the number of the desired port in an [ndbd] section or (if you need to do
 # this for multiple data nodes) the [ndbd default] section of the config.ini file, and then open the port having
 # that number for incoming connections from SQL nodes, API nodes, or both.

[tcp default]
# TCP/IP options:
#portnumber=1186   # This the default; however, you can use any
 # port that is free for all the hosts in the cluster
 # Note: It is recommended that you do not specify the port
 # number at all and simply allow the default value to be used
 # instead

[ndb_mgmd]
# Management process options:
hostname=172.16.20.21           # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster  # Directory for MGM node log files

[ndbd]
# Options for data node "A":
hostname=172.16.20.22           # Hostname or IP address
datadir=/usr/local/mysql/data   # Directory for this data node's data files

[ndbd]
# Options for data node "B":
hostname=172.16.20.23           # Hostname or IP address
datadir=/usr/local/mysql/data   # Directory for this data node's data files

[mysqld]
# SQL node options:
hostname=172.16.20.22           # Hostname or IP address
# (additional mysqld connections can be
# specified for this node for various
# purposes such as running ndb_restore)
[mysqld]
# SQL node options:
hostname=172.16.20.23           # Hostname or IP address
# (additional mysqld connections can be
# specified for this node for various
# purposes such as running ndb_restore)



F. IPtables rules;
(Here, I have allowed connection from any source on the destination ports as I have another set of                 firewall on top of the cloud and thus port are not exposed to the outside world, but in an ideal
                condition, one needs to specify the connection source)

I. On Management Node (INPUT chain):

# iptables -I INPUT -i eth0 -p tcp --dport 1186 -j ACCEPT
# iptables -I INPUT -i eth0 -p tcp --dport 3306 -j ACCEPT
# iptables -I INPUT -i eth0 -p tcp --dport 50501 -j ACCEPT
# service iptables save
# service iptables restart

II. On Data and SQL Nodes (INPUT chain):
# iptables -I INPUT -i eth0 -p tcp --dport 3306 -j ACCEPT

G. Start the services (in a sequence);

1. Management Node:
# ndb_mgmd -f /var/lib/mysql-cluster/config.ini

2. Data Node:
# ndbd

3. MySQL
# service mysql start

On the SQL Node,
Change the default mysql root password as;
# mysqladmin -u root -p'oldpassword' password newpass
(Check the old password in '/root/.mysql_secret')

H. Connect the MySQL Cluster Management Console and check the status (if everything        
         works fine then you should see similar to the following);

ndb_mgm> show
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @172.16.20.22  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)
id=3    @172.16.20.23  (mysql-5.6.23 ndb-7.4.4, Nodegroup: 0, *)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @172.16.20.21  (mysql-5.6.23 ndb-7.4.4)

[mysqld(API)]   2 node(s)
id=4    @172.16.20.22  (mysql-5.6.23 ndb-7.4.4)
id=5    @172.16.20.23  (mysql-5.6.23 ndb-7.4.4)

ndb_mgm>


I. Log files:
1. Management Node:
/var/lib/mysql-cluster

2. Data/SQL Nodes:
/usr/local/mysql/data/

Thursday, February 27, 2014

MySQL, How to reset lost root password

Did you forget the root password of MySQL ? Here is how get it back.

The following steps tells you how to reset the root password in MySQL (this is specific to Linux environment, I understand the same process would also work in Windows)


1. Stop MySQL service:
# service mysqld stop
Stopping mysqld:                                           [  OK  ]


2. Start Mysql server with "--skip-grant-tables" option (user privileges table). You may optionally provide "--skip-networking" which would prohibit anyone from connecting the server from remote place;


Please note that, one need to start the server using "mysqld_safe" command. You need to send the process in the background by either pressing ctrl+z and send it to the background using "bg" or '&' at the end of the command;

# mysqld_safe --skip-grant-tables --skip-networking
140227 18:39:40 mysqld_safe Logging to '/var/log/mysqld.log'.
140227 18:39:40 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
^Z
[1]+  Stopped                 mysqld_safe --skip-grant-tables --skip-networking

3. Login to Mysql without password (this will not prompt you for password);

[root@Fedora-14 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

4. Update the password for "root" user with a new password;

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("root") where user="root";
Query OK, 0 rows affected (0.01 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> quit
Bye

5. Stop the MySQL server;

[root@Fedora-14 ~]# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]

6. Start back the MySQL server;

# service mysqld start
Starting mysqld:                                           [  OK  ]

7. Login to the server with root user and the new password;

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

Saturday, April 11, 2009

Database replication an effective means of Backup !

What is Database replication?
Database replication is one of most effective means of Backup. This is more effective when you need to handle a huge volume of data in a big organization. It is an online process means the master database is backed up instantaneously as and when there are any changes to the master database. The process of Replication may work in chained fashion. Replication protects against hardware failures on one of the replicated databases but not against user stupidity or maliciousness! If a user deletes a number of records, this process will then be replicated onto the other replicated servers, making replication useless as a reliable means of backup.

How to achieve this? ( I described about MySQL Database only)
Here both the servers are of same versions i.e. version: 4.1.15

The Master server is located in the location
/home/bijit/database/mysql

To start the services:

mysqld_multi start 3
mysqld_multi start 4

To log into mysql:

/home/bijit/database/mysql/bin/mysql -u root -proot --port=3307 --socket=/home/bijit/database/mysql/config/mysql.sock

/home/bijit/database2/mysql/bin/mysql -u root -p --port=3308 --socket=/home/bijit/database2/mysql/var/mysql.sock

The slave server is located in the path
/home/bijit/database2/mysql
Steps:
On the master do the following:
1.
use database mysql;
GRANT REPLICATION SLAVE ON *.* TO replicator@localhost IDENTIFIED BY 'replicator';
FLUSH PRIVILEGES;
2.
Make a copy of tables and data i.e. the entire database of the Master server. In this case copy the data directory located in /home/bijit/database/mysql/var
3.
In the my.cnf of Master server, add the following entries;

log-bin
server-id=1
4. Login to maser mysql server, and note the bin-log file and its position as
mysql> show master status;
+----------------------+----------+--------------+------------------+
File Position Binlog_Do_DB Binlog_Ignore_DB
+----------------------+----------+--------------+------------------+
localhost-bin.000002 79
+----------------------+----------+--------------+------------------+
1 row in set (0.05 sec)

On the slave do the following:

1.
In the file my.cnf, add the following entries (under the slave servers entry),
master-host = localhost
master-user = replicator
master-password = replicator
master-port = 3307
server-id = 2

2. Start the slave server, create a user called replicator as created in the master;
mysql> use mysql;
mysql> grant all privileges on *.* to replicator@localhost identified by 'replicator';
mysql>flush privileges;
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='localhost',
MASTER_USER='root',
MASTER_PASSWORD='root',
MASTER_PORT=3307,
MASTER_LOG_FILE='localhost-bin.000002'
MASTER_LOG_POS=79
2.
Copy the data taken from the master onto the slave.

3. Login to slave server as mysql -u root -proot [This is now same as master]
Start the slave server as
mysql> start slave;
mysql> LOAD DATA FROM MASTER;

Now check both master and slave and you will see both servers are in sync.
MySql replication has begun !!!

Monday, June 23, 2008

Installing ORACLE 9i on RHEL5

Note: I have installed it successfully on "Red Hat Enterprise Linux Server release 5 (Tikanga)" 32 bit. Please understand; it involves tweaking of Kernel parameters, so take care while you do that.

Steps:
1. Create oracle User AccountLogin as root and create te user oracle which belongs to dba group.$ su - root
# groupadd dba
# useradd -g dba oracle
2. Setting System parameters:Edit the /etc/sysctl.conf with an editor like vi and add following lines:kernel.shmall = 2097152
kernel.shmmax = 536870912
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=262144
net.core.wmem_default=262144
net.core.rmem_max=262144
net.core.wmem_max=262144
Note: You need to execute "sysctl -p" to apply the above settings.

3. Setting Oracle EnviromentEdit the /home/oracle/.bash_profile file and add following lines:ORACLE_BASE=/opt/oracle
ORACLE_HOME=$ORACLE_BASE/920
ORACLE_SID=orcl
LD_LIBRARY_PATH=$ORACLE_HOME/lib
ORACLE_OEM_JAVARUNTIME=/opt/jre1.3.1_15
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH ORACLE_OEM_JAVARUNTIME PATH

Save the .bash_profile and execute following commands to load the new environment:
# cd /home/oracle.

# .bash_profile
4. Create base directory for OracleLogin as root and create base directory for Oracle ($ORACLE_BASE).
$ su - root

# cd /opt
# mkdir oracle
# chown oracle:dba oracle
5. Download and install required .rpm packagessome additional packages may be required for successful installation . To check whether required packages are installed on your operating system use following command:

rpm -q gcc glibc-headers glibc-kernheaders glibc-devel compat-libstdc++ cpp compat-gcc
6. Download the Java Runtime Enviroment (I used j2re-1_3_1_19-linux-i586.bin) from the
Sun website. Login as root, give the file execute permission and then execute it. When the JRE is exracted move the same under the directory "/usr/local" directory.# chmod +x j2re-1_3_1_19-linux-i586.bin

# ./ j2re-1_3_1_19-linux-i586.bin
# mv jre1.3.1_19 /usr/local/
7. Download the Oracle 9i (9.2.0.4) software from
Oracle website. Extract the files using following command:


gunzip ship_9204_linux_disk1.cpio.gz

gunzip ship_9204_linux_disk2.cpio.gz
gunzip ship_9204_linux_disk3.cpio.gz
cpio -idmv "<" ship_9204_linux_disk1.cpio
cpio -idmv "<" ship_9204_linux_disk2.cpio
cpio -idmv "< "ship_9204_linux_disk3.cpio
mv Disk* /home/oracle/
chown –R oracle:dba Disk*

When all archives were extracted you've got three directories Disk1, Disk2 and Disk3.

Edit the Disk1/install/linux/oraparam.ini and modify JRE_LOCATION variable and set path to our JRE installation from Step 6. JRE_LOCATION=/usr/local/jre1.3.1_19 4.

Start the Oracle software installation process. To start the installation process; execute the following commands:

$ cd Disk1
$ ./runInstaller

***************************************************
Errors with solutions:
1. When network configuration assistant and database configuration assistant has failed during startup then do following steps:
Solution:
$ cd /opt/oracle/920
$ rm JRE $ ln -s /usr/local/jre1.3.1_19 JRE
$ su - root
# cd /opt/oracle/920/JRE/bin # ln -s java jre
# cd i386/native_threads
# ln -s java jre
2. If you encounter the following error:
ORACLE_HOME/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory Solution: Create a symbolic link
$ su - root
# ln -s /usr/lib/libgdbm.so.2.0.0 /usr/lib/libdb.so.2
3. If you encounter something like below at the very beginning of installation:
error while loading shared libraries: libstdc++-libc6.1-1.so.2: cannot open shared object file: No such file or directory
Solution:
$ su - root
# cd /usr/lib
# ln -s libstdc++-3-libc6.2-2-2.10.0.so libstdc++-libc6.1-1.so.2
Now, run the installer again:
$ ./runInstaller
****************************************************
Note: If you encounter any more errors, please post it here