Monday, 31 March 2014

Enable Network Services in Oracle Database 11g

3.3.7.1 Granting Connect Privileges

The following example demonstrates how to grant connect privileges to any host for the APEX_040000 database user. This example assumes you connected to the database where Oracle Application Express is installed as SYS specifying the SYSDBA role.
DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  -- Look for the ACL currently assigned to '*' and give APEX_040000
  -- the "connect" privilege if APEX_040000 does not have the privilege yet.

  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = '*' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- Before checking the privilege, ensure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --
  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
    FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
         EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000', 
     'connect') IS NULL THEN 
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
     'APEX_040000', TRUE, 'connect'); 
  END IF;

EXCEPTION
  -- When no ACL has been assigned to '*'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('power_users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_040000', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('power_users.xml','*');
END; 
 
 
 
The following example demonstrates how to provide less privileged access to local network resources. This example would enable indexing the Oracle Application Express Online Help and could possibly enable email and PDF printing if those servers were also on the local host.


DECLARE
  ACL_PATH  VARCHAR2(4000);
  ACL_ID    RAW(16);
BEGIN
  -- Look for the ACL currently assigned to 'localhost' and give APEX_040000
  -- the "connect" privilege if APEX_040000 does not have the privilege yet.
  SELECT ACL INTO ACL_PATH FROM DBA_NETWORK_ACLS
   WHERE HOST = 'localhost' AND LOWER_PORT IS NULL AND UPPER_PORT IS NULL;

  -- Before checking the privilege, ensure that the ACL is valid
  -- (for example, does not contain stale references to dropped users).
  -- If it does, the following exception will be raised:
  --
  -- ORA-44416: Invalid ACL: Unresolved principal 'APEX_040000'
  -- ORA-06512: at "XDB.DBMS_XDBZ", line ...
  --

  SELECT SYS_OP_R2O(extractValue(P.RES, '/Resource/XMLRef')) INTO ACL_ID
    FROM XDB.XDB$ACL A, PATH_VIEW P
   WHERE extractValue(P.RES, '/Resource/XMLRef') = REF(A) AND
         EQUALS_PATH(P.RES, ACL_PATH) = 1;

  DBMS_XDBZ.ValidateACL(ACL_ID);
   IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(ACL_PATH, 'APEX_040000', 
     'connect') IS NULL THEN 
      DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL_PATH, 
     'APEX_040000', TRUE, 'connect'); 
  END IF;

EXCEPTION
  -- When no ACL has been assigned to 'localhost'.
  WHEN NO_DATA_FOUND THEN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('local-access-users.xml',
    'ACL that lets power users to connect to everywhere',
    'APEX_040000', TRUE, 'connect');
  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('local-access-users.xml','localhost');
END;


 http://docs.oracle.com/cd/E17556_01/doc/install.40/e15513/otn_install.htm#BABBHCID

Tuesday, 25 March 2014

The Official Red Hat Linux Reference Guide

 Copyright © 2001 by Red Hat, Inc.
Red Hat, Inc.
2600 Meridian Parkway
Durham, NC 27713
USA
Phone: +1 919 547 0012
Phone: 888 733 4281
Fax: +1 919 547 0024
PO Box 13588
Research Triangle Park, NC 27709
USA


Friday, 7 March 2014

Routing Protocol Selection Guide - IGRP, EIGRP, OSPF, IS-IS, BGP

Routing Protocol Selection Guide - IGRP, EIGRP, OSPF, IS-IS, BGP

VERSION 8 

Overview

The purpose of routing protocols is to learn of available routes that exist on the enterprise network, build routing tables and make routing decisions. Some of the most common routing protocols include IGRP, EIGRP, OSPF, IS-IS and BGP. There are two primary routing protocol types although many different routing protocols defined with those two types. Link state and distance vector protocols comprise the primary types. Distance vector protocols advertise their routing table to all directly connected neighbors at regular frequent intervals using a lot of bandwidth and are slow to converge. When a route becomes unavailable, all router tables must be updated with that new information. The problem is with each router having to advertise that new information to its neighbors, it takes a long time for all routers to have a current accurate view of the network. Distance vector protocols use fixed length subnet masks which aren't scalable. Link state protocols advertise routing updates only when they occur which uses bandwidth more effectively. Routers don't advertise the routing table which makes convergence faster. The routing protocol will flood the network with link state advertisements to all neighbor routers per area in an attempt to converge the network with new route information. The incremental change is all that is advertised to all routers as a multicast LSA update. They use variable length subnet masks, which are scalable and use addressing more efficiently.


Interior Gateway Routing Protocol (IGRP)

Interior Gateway Routing Protocol is a distance vector routing protocol developed by Cisco systems for routing multiple protocols across small and medium sized Cisco networks. It is proprietary which requires that you use Cisco routers. This contrasts with IP RIP and IPX RIP, which are designed for multi-vendor networks. IGRP will route IP, IPX, Decnet and AppleTalk which makes it very versatile for clients running many different protocols. It is somewhat more scalable than RIP since it supports a hop count of 100, only advertises every 90 seconds and uses a composite of five different metrics to select a best path destination. Note that since IGRP advertises less frequently, it uses less bandwidth than RIP but converges much slower since it is 90 seconds before IGRP routers are aware of network topology changes. IGRP does recognize assignment of different autonomous systems and automatically summarizes at network class boundaries. As well there is the option to load balance traffic across equal or unequal metric cost paths.

Characteristics
  • Distance Vector
  • Routes IP, IPX, Decnet, Appletalk
  • Routing Table Advertisements Every 90 Seconds
  • Metric: Bandwidth, Delay, Reliability, Load, MTU Size
  • Hop Count: 100
  • Fixed Length Subnet Masks
  • Summarization on Network Class Address
  • Load Balancing Across 6 Equal or Unequal Cost Paths ( IOS 11.0 )
  • Update Timer: 90 seconds
  • Invalid Timer: 270 seconds
  • Holddown Timer: 280 seconds
  • Metric Calculation = destination path minimum BW * delay (usec)
  • Split Horizon



Enhanced Interior Gateway Routing Protocol (EIGRP)

Enhanced Interior Gateway Routing Protocol is a hybrid routing protocol developed by Cisco systems for routing many protocols across an enterprise Cisco network. It has characteristics of both distance vector routing protocols and link state routing protocols. It is proprietary which requires that you use Cisco routers. EIGRP will route the same protocols that IGRP routes (IP, IPX, Decnet and Appletalk) and use the same composite metrics as IGRP to select a best path destination. As well there is the option to load balance traffic across equal or unequal metric cost paths. Summarization is automatic at a network class address however it can be configured to summarize at subnet boundaries as well. Redistribution between IGRP and EIGRP is automatic as well. There is support for a hop count of 255 and variable length subnet masks.

Convergence

Convergence with EIGRP is faster since it uses an algorithm called dual update algorithm or DUAL, which is run when a router detects that a particular route is unavailable. The router queries its neighbors looking for a feasible successor. That is defined as a neighbor with a least cost route to a particular destination that doesn't cause any routing loops. EIGRP will update its routing table with the new route and the associated metric. Route changes are advertised only to affected routers when changes occur. That utilizes bandwidth more efficiently than distance vector routing protocols.

Autonomous Systems

EIGRP does recognize assignment of different autonomous systems which are processes running under the same administrative routing domain. Assigning different autonomous system numbers isn't for defining a backbone such as with OSPF. With IGRP and EIGRP it is used to change route redistribution, filtering and summarization points.

Characteristics
  • Advanced Distance Vector
  • Routes IP, IPX, Decnet, Appletalk
  • Routing Advertisements: Partial When Route Changes Occur
  • Metrics: Bandwidth, Delay, Reliability, Load, MTU Size
  • Hop Count: 255
  • Variable Length Subnet Masks
  • Summarization on Network Class Address or Subnet Boundary
  • Load Balancing Across 6 Equal or Unequal Cost Paths (IOS 11.0)
  • Hello Timer: 5 seconds on Ethernet / 60 seconds on Non-Broadcast
  • Holddown Timer: 15 seconds on Ethernet / 180 seconds on Non-Broadcast
  • Metric Calculation = destination path minimum BW * delay (msec) * 256
  • Split Horizon
  • LSA Multicast Address: 224.0.0.10



Open Shortest Path First (OSPF)

Open Shortest Path First is a true link state protocol developed as an open standard for routing IP across large multi-vendor networks. A link state protocol will send link state advertisements to all connected neighbors of the same area to communicate route information. Each OSPF enabled router, when started, will send hello packets to all directly connected OSPF routers. The hello packets contain information such as router timers, router ID and subnet mask. If the routers agree on the information they become OSPF neighbors. Once routers become neighbors they establish adjacencies by exchanging link state databases. Routers on point-to-point and point-to-multipoint links (as specified with the OSPF interface type setting) automatically establish adjacencies. Routers with OSPF interfaces configured as broadcast (Ethernet) and NBMA (Frame Relay) will use a designated router that establishes those adjacencies.

Areas

OSPF uses a hierarchy with assigned areas that connect to a core backbone of routers. Each area is defined by one or more routers that have established adjacencies. OSPF has defined backbone area 0, stub areas, not-so-stubby areas and totally stubby areas. Area 0 is built with a group of routers connected at a designated office or by WAN links across several offices. It is preferable to have all area 0 routers connected with a full mesh using an Ethernet segment at a core office. This provides for high performance and prevents partitioning of the area should a router connection fail. Area 0 is a transit area for all traffic from attached areas. Any inter-area traffic must route through area 0 first. Stub areas use a default route injected from the ABR to forward traffic destined for any external routes (LSA 5,7) to the area border router. Inter-area (LSA 3,4) and intra-area (LSA 1,2) routing is as usual. Totally Stubby areas are a Cisco specification that uses a default route injected from the ABR for all Inter-area and external routes. The Totally Stubby area doesn't advertise or receive external or Inter-area LSA's. The Not-So-Stubby area ABR is a transit area that will import external routes with type 7 LSA and flood them to other areas as type 5 LSA. External routes aren't received at that area type. Inter-area and intra-area routing is as usual. OSPF defines internal routers, backbone routers, area border routers (ABR) and autonomous system boundary routers (ASBR). Internal routers are specific to one area. Area border routers have interfaces that are assigned to more than one area such as area 0 and area 10. An autonomous system boundary router has interfaces assigned to OSPF and a different routing protocol such as EIGRP or BGP. A virtual link is utilized when an area doesn't have a direct connection to area 0. A virtual link is established between an area border router for an area that isn't connected to area 0, and an area border router for an area that is connected to area 0. Area design involves considering geographical location of offices and traffic flows across the enterprise. It is important to be able to summarize addresses for many offices per area and minimize broadcast traffic.

Convergence

Fast convergence is accomplished with the SPF (Dijkstra) algorithm which determines a shortest path from source to destination. The routing table is built from running SPF which determines all routes from neighbor routers. Since each OSPF router has a copy of the topology database and routing table for its particular area, any route changes are detected faster than with distance vector protocols and alternate routes are determined.

Designated Router

Broadcast networks such as Ethernet and Non-Broadcast Multi Access networks such as Frame Relay have a designated router (DR) and a backup designated router (BDR) that are elected. Designated routers establish adjacencies with all routers on that network segment. This is to reduce broadcasts from all routers sending regular hello packets to its neighbors. The DR sends multicast packets to all routers that it has established adjacencies with. If the DR fails, it is the BDR that sends multicasts to specific routers. Each router is assigned a router ID, which is the highest assigned IP address on a working interface. OSPF uses the router ID (RID) for all routing processes.

Characteristics
  • Link State
  • Routes IP
  • Routing Advertisements: Partial When Route Changes Occur
  • Metric: Composite Cost of each router to Destination (100,000,000/interface speed)
  • Hop Count: None (Limited by Network)
  • Variable Length Subnet Masks
  • Summarization on Network Class Address or Subnet Boundary
  • Load Balancing Across 4 Equal Cost Paths
  • Router Types: Internal, Backbone, ABR, ASBR
  • Area Types: Backbone, Stubby, Not-So-Stubby, Totally Stubby
  • LSA Types: Intra-Area (1,2) Inter-Area (3,4), External (5,7)
  • Hello Timer Interval: (10 seconds for Ethernet / 30 seconds for Non-Broadcast)
  • Dead Timer Interval: 40 seconds for Ethernet / 120 seconds for Non-Broadcast)
  • LSA Multicast Address: 224.0.0.5 and 224.0.0.6 (DR/BDR) Don't Filter!
  • Interface Types: Point to Point, Broadcast, Non-Broadcast, Point to Multipoint, Loopback



Integrated IS-IS

Integrated Intermediate System - Intermediate System routing protocol is a link state protocol similar to OSPF that is used with large enterprise and ISP customers. An intermediate system is a router and IS-IS is the routing protocol that routes packets between intermediate systems. IS-IS utilizes a link state database and runs the SPF Dijkstra algorithm to select shortest paths routes. Neighbor routers on point to point and point to multipoint links establish adjacencies by sending hello packets and exchanging link state databases. IS-IS routers on broadcast and NBMA networks select a designated router that establishes adjacencies with all neighbor routers on that network. The designated router and each neighbor router will establish an adjacency with all neighbor routers by multicasting link state advertisements to the network itself. That is different from OSPF, which establishes adjacencies between the DR and each neighbor router only. IS-IS uses a hierarchical area structure with level 1 and level 2 router types. Level 1 routers are similar to OSPF intra-area routers, which have no direct connections outside of its area. Level 2 routers comprise the backbone area which connects different areas similar to OSPF area 0. With IS-IS a router can be an L1/L2 router which is like an OSPF area border router (ABR) which has connections with its area and the backbone area. The difference with IS-IS is that the links between routers comprise the area borders and not the router. Each IS-IS router must have an assigned address that is unique for that routing domain. An address format is used which is comprised of an area ID and a system ID. The area ID is the assigned area number and the system ID is a MAC address from one of the router interfaces. There is support for variable length subnet masks, which is standard with all link state protocols. Note that IS-IS assigns the routing process to an interface instead of a network.

Characteristics
  • Link State
  • Routes IP, CLNS
  • Routing Advertisements: Partial When Routing Changes Occur
  • Metric: Variable Cost (default cost 10 assigned to each interface)
  • Hop Count: None (limited by network)
  • Variable Length Subnet Masks
  • Summarization on Network Class Address or Subnet Boundary
  • Load Balancing Across 6 Equal Cost Paths
  • Hello Timer Interval: 10 seconds
  • Dead Timer Interval: 30 seconds
  • Area Types: Hierarchical Topology similar to OSPF
  • Router Types: Level 1 and Level 2
  • LSP Types: Internal L1 and L2, External L2
  • Designated Router Election, No BDR



Border Gateway Protocol (BGP)

Border Gateway Protocol is an exterior gateway protocol, which is different from the interior gateway protocols discussed so far. The distinction is important since the term autonomous system is used somewhat differently with protocols such as EIGRP than it is with BGP. Exterior gateway protocols such as BGP route between autonomous systems, which are assigned a particular AS number. AS numbers can be assigned to an office with one or several BGP routers. The BGP routing table is comprised of destination IP addresses, an associated AS-Path to reach that destination and a next hop router address. The AS-Path is a collection of AS numbers that represent each office involved with routing packets. Contrast that with EIGRP, which uses autonomous systems as well. The difference is their autonomous systems refer to a logical grouping of routers within the same administrative system. An EIGRP network can configure many autonomous systems. They are all managed by the company for defining route summarization, redistribution and filtering. BGP is utilized a lot by Internet Service Providers (ISP) and large enterprise companies that have dual homed internet connections with single or dual routers homed to the same or different Internet Service Providers. BGP will route packets across an ISP network, which is a separate routing domain that is managed by them. The ISP has its own assigned AS number, which is assigned by InterNIC. New customers can either request an AS assignment for their office from the ISP or InterNIC. A unique AS number assignment is required for customers when they connect using BGP. There are 10 defined attributes that have a particular order or sequence, which BGP utilizes as metrics to determine the best path to a destination. Companies with only one circuit connection to an ISP will implement a default route at their router, which forwards any packets that are destined for an external network. BGP routers will redistribute routing information (peering) with all IGP routers on the network (EIGRP, RIP, OSPF etc) which involve exchange of full routing tables. Once that is finished, incremental updates are sent with topology changes. The BGP default keepalive timer is 60 seconds while the holddown timer is 180 seconds. Each BGP router can be configured to filter routing broadcasts with route maps instead of sending/receiving the entire internet routing table.

Characteristics
  • Path Vector
  • Routes IP
  • Routing Advertisements: Partial When Route Changes Occur
  • Metrics: Weight, Local Preference, Local Originated, As Path, Origin Type, MED
  • Hop Count: 255
  • Variable Length Subnet Masks
  • Summarization on Network Class Address or Subnet Boundary
  • Load Balancing Across 6 Equal Cost Paths
  • Keepalive Timer: 60 seconds
  • Holddown Timer: 180 seconds
  • Designated Router: Route Reflector

BGP Routing Table Components
  • Destination IP Address / Subnet Mask
  • AS-Path
  • Next Hop IP Address


Copyright © 2013 Shaun L. Hummel All Rights Reserved

https://supportforums.cisco.com/docs/DOC-30205

Saturday, 19 October 2013

How to Install MySQL Database Using Yum groupinstall on CentOS

In this article, let us review how to install MySQL on CentOS using yum. Instead of searching and installing mysql and related packages one-by-one, it is better to install MySQL using yum groups.

If you are interested in installing the full LAMP stack, refer to our earlier article on how to install/upgrade LAMP using yum.

1. Identify the Group name of MySQL Packages

yum grouplist displays all package groups that are available in the repository. As shown below, mysql package group is called “MySQL Database”.
# yum grouplist | grep -i mysql
   MySQL Database

2. What is bundled in the “MySQL Database” group?

yum groupinfo displays all the packages that are bundled in a group. This displays the mandatory, default and optional packages that are available in that particular group.
As shown below, “MySQL Database” group contains 1 mandatory package, 6 default packages, and 5 optional packages.
# yum groupinfo "MySQL Database"
Group: MySQL Database
 Description: This package group contains packages useful for use with MySQL.
 Mandatory Packages:
   mysql
 Default Packages:
   MySQL-python
   libdbi-dbd-mysql
   mysql-connector-odbc
   mysql-server
   perl-DBD-MySQL
   unixODBC
 Optional Packages:
   mod_auth_mysql
   mysql-bench
   mysql-devel
   php-mysql
   qt-MySQL

3. Install the “MySQL Database” group using yum groupinstall

yum groupinstall will install the “MySQL Database” group of packages as shown below.
# yum groupinstall "MySQL Database"

Resolving Dependencies
Dependencies Resolved

Transaction Summary
=========================
Install     12 Package(s)
Update       0 Package(s)
Remove       0 Package(s)         

Installed:
  MySQL-python.i386 0:1.2.1-1 libdbi-dbd-mysql.i386 0:0.8.1a-1.2.2
  mysql.i386 0:5.0.77-4.el5_4.2  mysql-connector-odbc.i386 0:3.51.26r1127-1.el5
  mysql-server.i386 0:5.0.77-4.el5_4.2  perl-DBD-MySQL.i386 0:3.0007-2.el5
  unixODBC.i386 0:2.2.11-7.1    

Dependency Installed:
  libdbi.i386 0:0.8.1-2.1 libdbi-drivers.i386 0:0.8.1a-1.2.2
  libtool-ltdl.i386 0:1.5.22-7.el5_4
  mx.i386 0:2.0.6-2.2.2 perl-DBI.i386 0:1.52-2.el5  

Complete!
Note: If you are having some issues during the installation, verify the full mysql install log to see what you are missing.

4. Verify MySQL Installation

Execute rpm -qa, to confirm that the mysql related packages are installed.
# rpm -qa | grep -i mysql
MySQL-python-1.2.1-1
mysql-5.0.77-4.el5_4.2
mysql-connector-odbc-3.51.26r1127-1.el5
mysql-server-5.0.77-4.el5_4.2
libdbi-dbd-mysql-0.8.1a-1.2.2
perl-DBD-MySQL-3.0007-2.el5
Check the /etc/passwd and /etc/group to make sure it has created a mysql username and group.
# grep mysql /etc/passwd
mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/bash

# grep mysql /etc/group
mysql:x:27:

5. MySQL Post installation – Execute mysql_install_db

mysql_install_db program will setup the necessary grant tables. The mysql_install_db program gets executed as part of the rpm installation. But, it doesn’t hurt to execute the mysql_install_db program again to make sure the grant tables are setup properly.
# /usr/bin/mysql_install_db --user=mysql
Installing MySQL system tables...OK
Filling help tables...OK
.....
The latest information about MySQL is available on the web at http://www.mysql.com

6. Start MySQL Server

# service mysqld status
mysqld is stopped

# service mysqld start
Starting MySQL:                                            [  OK  ]

7. Verify that the MySQL server is up and running.

# /usr/bin/mysqladmin version
/usr/bin/mysqladmin  Ver 8.41 Distrib 5.0.77, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  5.0.77
Protocol version 10
Connection  Localhost via UNIX socket
UNIX socket  /var/lib/mysql/mysql.sock
Uptime:   39 sec

Threads: 1  Questions: 2  Slow queries: 0  Opens: 12  Flush tables: 1
Open tables: 6  Queries per second avg: 0.051
# /usr/bin/mysqlshow
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+

# /usr/bin/mysqlshow mysql
Database: mysql
+---------------------------+
|          Tables           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
Stop and start the mysql server again to make sure they are no issues.
# service mysqld stop
Stopping MySQL:                                            [  OK  ]

# service mysqld start
Starting MySQL:                                            [  OK  ]

8. Change the MySQL root account password

Change the MySQL root account password to something secure.
# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.0.77 Source distribution

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

mysql> select host, user from mysql.user;
+-----------+------+
| host      | user |
+-----------+------+
| 127.0.0.1 | root |
| localhost |      |
| localhost | root |
+-----------+------+
5 rows in set (0.00 sec)

mysql> set password for 'root'@'localhost' = PASSWORD('DoNotTell$AnyBody');
Query OK, 0 rows affected (0.00 sec)

mysql> set password for 'root'@'127.0.0.1' = PASSWORD('DoNotTell$AnyBody');
Query OK, 0 rows affected (0.00 sec)
Make sure you are able to login to MySQL using the new password as shown below.
# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

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

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

mysql>
 

 
 
Free counters!

Installing & Configuring MySQL Server

 http://centoshelp.org/servers/database/installing-configuring-mysql-server/

Installing & Configuring MySQL Server

This Howto will show you how to install MySQL 5.x, start the service, make sure the server starts on reboot, login via terminal, change the root database admin password, change the name of the root user, add a new user with specific privileges to a specific database, add a new DBA, add a new database, remove all anonymous logins, remove all non-root users, added file security steps, disable remote access (via port 3306), purge the scrollback history, and finally the installation of the gui tool mysql-administrator.

Applicable to Centos Versions:

  • Centos 5.x
  • Centos 6.x

Requirements

  1. Login to a terminal as root using one of these options: (su –login | su -l | or: su -)
  2. Yum and rpm must also be installed and functional (something is seriously wrong if they aren’t)

Doing the Work

  1. Install mysql mysql-server:
  2. # yum install mysql mysql-server
    Loading "priorities" plugin
    Loading "changelog" plugin
    Loading "fastestmirror" plugin
    Loading "allowdowngrade" plugin
    Loading "kernel-module" plugin
    Loading "fedorakmod" plugin
    Loading "installonlyn" plugin
    Loading "protectbase" plugin
    Setting up Install Process
    Setting up repositories
    livna                     100% |=========================| 1.1 kB    00:00
    updates                   100% |=========================| 1.2 kB    00:00
    core                      100% |=========================| 1.1 kB    00:00
    extras                    100% |=========================| 1.1 kB    00:00
    Loading mirror speeds from cached hostfile
    Reading repository metadata in from local files
    primary.xml.gz            100% |=========================| 1.8 MB    00:06     
    extras    : ################################################## 5594/5594
    0 packages excluded due to repository priority protections
    0 packages excluded due to repository protections
    Parsing package install arguments
    Resolving Dependencies
    --> Populating transaction set with selected packages. Please wait.
    ---> Downloading header for mysql to pack into transaction set.
    mysql-5.0.27-1.fc6.i386.r 100% |=========================|  36 kB    00:00
    ---> Package mysql.i386 0:5.0.27-1.fc6 set to be updated
    ---> Downloading header for mysql-server to pack into transaction set.
    mysql-server-5.0.27-1.fc6 100% |=========================|  33 kB    00:00
    ---> Package mysql-server.x86_64 0:5.0.27-1.fc6 set to be updated
    ---> Downloading header for mysql to pack into transaction set.
    mysql-5.0.27-1.fc6.x86_64 100% |=========================|  36 kB    00:00
    ---> Package mysql.x86_64 0:5.0.27-1.fc6 set to be updated
    --> Running transaction check
    --> Processing Dependency: perl-DBI for package: mysql-server
    --> Processing Dependency: perl(DBI) for package: mysql
    --> Processing Dependency: perl(DBI) for package: mysql-server
    --> Processing Dependency: perl-DBD-MySQL for package: mysql-server
    --> Restarting Dependency Resolution with new changes.
    --> Populating transaction set with selected packages. Please wait.
    ---> Downloading header for perl-DBI to pack into transaction set.
    perl-DBI-1.52-1.fc6.x86_6 100% |=========================|  16 kB    00:00
    ---> Package perl-DBI.x86_64 0:1.52-1.fc6 set to be updated
    ---> Downloading header for perl-DBD-MySQL to pack into transaction set.
    perl-DBD-MySQL-3.0007-1.f 100% |=========================| 8.5 kB    00:00
    ---> Package perl-DBD-MySQL.x86_64 0:3.0007-1.fc6 set to be updated
    --> Running transaction check
    
    Dependencies Resolved
    =============================================================================
    Package                 Arch       Version          Repository        Size
    =============================================================================
    Installing:
     mysql                   i386       5.0.27-1.fc6     updates           3.3 M
     mysql                   x86_64     5.0.27-1.fc6     updates           3.3 M
     mysql-server            x86_64     5.0.27-1.fc6     updates            10 M
    
    Installing for dependencies:
     perl-DBD-MySQL          x86_64     3.0007-1.fc6     core              147 k
     perl-DBI                x86_64     1.52-1.fc6       core              605 k
    
    Transaction Summary
    =============================================================================
    Install      5 Package(s)         
    Update       0 Package(s)         
    Remove       0 Package(s)         
    
    Total download size: 18 M
    Is this ok [y/N]:
  3. Start MySQL server daemon (mysqld):
  4. # chkconfig --level 2345 mysqld on; service mysqld start
    Initializing MySQL database:  Installing all prepared tables
    Fill help tables
    
    To start mysqld at boot time you have to copy support-files/mysql.server
    to the right place for your system
    
    PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
    To do so, start the server, then issue the following commands:
    /usr/bin/mysqladmin -u root password 'new-password'
    /usr/bin/mysqladmin -u root -h angstrom password 'new-password'
    See the manual for more instructions.
    
    You can start the MySQL daemon with:
    cd /usr ; /usr/bin/mysqld_safe &
    
    You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory:
    cd sql-bench ; perl run-all-tests
    
    Please report any problems with the /usr/bin/mysqlbug script!
    
    The latest information about MySQL is available on the web at
    
    http://www.mysql.com
    
    Support MySQL by buying support/licenses at http://shop.mysql.com
                                                               [  OK  ]
    Starting MySQL:                                            [  OK  ]
  5. Login as root database admin to MySQL server:
  6. # mysql -u root
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2 to server version: 5.0.27
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql>
  7. Delete ALL users who are not root:
  8. mysql> delete from mysql.user where not (host="localhost" and user="root");
    Query OK, 5 rows affected (0.15 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>
  9. Change root database admin password: (note: once this step is complete you’ll need to login with: mysql -p -u root)
  10. mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mypass');
    
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>
  11. Change root username to something less guessable for higher security.
  12. mysql> update mysql.user set user="mydbadmin" 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>
  13. Remove anonymous access to the database(s):
  14. mysql> DELETE FROM mysql.user WHERE User = '';
    Query OK, 2 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>
  15. Add a new user with database admin privs for all databases:
  16. mysql> GRANT ALL PRIVILEGES ON *.* TO 'warren'@'localhost' IDENTIFIED BY 'mypass' WITH GRANT OPTION;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql>
  17. Add a new user with database admin privs for a specific database, in this case the database is called “bugzilla”: (note: The ‘bugzilla’ database must first be added, see below.)
  18. mysql> GRANT ALL PRIVILEGES ON bugzilla.* TO 'warren'@'localhost' IDENTIFIED BY 'mypass';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    mysql> 
    
    Alternatively, you can give someone access to only certain privileges by substituting "ALL PRIVILEGES"
    with any combination of the following (commas included):
    SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES
  19. Add a MySQL database:
  20. mysql> create database bugzilla;
    Query OK, 1 row affected (0.15 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> quit
    Bye
  21. Installing mysql-administrator:
  22. The MySql Administrator packages for Centos 5.x can be found here:
    
    MySQL Administrator Packages:
    http://people.centos.org/hughesjr/mysql-gui-tools/i386/
    
    Possible Dependencies:
    http://centos.karan.org/el5/extras/testing/i386/RPMS/
    
    To install these packages download the desired tools into a directory on your desktop or a directory
    on the server, cd into the directory and issue this command: rpm -ivh *.rpm
    
    Make sure that the rpms you want to install are the only files in the directory.
  23. Improving local file security (after saving and exiting remember to: service mysqld restart for changes to take effect):
  24. The next change is to disable the use of LOAD DATA LOCAL INFILE command, which will help to 
    prevent against unauthorized reading from local files. This matters especially when new SQL
    Injection vulnerabilities in PHP applications are found.
    
    For that purpose, the following parameter should be added in the [mysqld] section in:
    /etc/my.cnf
    
    set-variable=local-infile=0
  25. Disabling remote access to the MySQL server (after saving and exiting remember to: service mysqld restart for changes to take effect).
  26. This change applies to the 3306/tcp port, on which MySQL listens by default. Because,
    according to the initial assumptions, the database will be used only by locally installed PHP
    applications, we can freely disable listening on that port. This will limit possibilities of
    attacking the MySQL database by direct TCP/IP connections from other hosts. Local communication
    will be still possible throw the mysql.sock socket. In order to disable listening on the
    mentioned port, the following parameter should be added to the [mysqld] section of /etc/my.cnf:
    
    skip-networking
    
    If, for some reason, remote access to the database is still required (e.g. to perform remote
    data backup), the SSH protocol can be used as follows:
    
    (modify to your needs)
    backuphost$ ssh mysqlserver /usr/local/mysql/bin/mysqldump -A > backup

Troubleshooting

How to test

  1. Make sure mysql and mysql server are indeed installed and that they are the correct versions:
  2. # rpm -qa | grep mysql && chkconfig --list | grep mysql
    mysql-5.0.27-1.fc6
    mysql-5.0.27-1.fc6
    mysql-gui-common-1.1.10-3.fc6
    mysql-server-5.0.27-1.fc6
    mysql-administrator-1.1.10-3.fc6
    mysqld          0:off   1:off   2:off   3:off   4:off   5:off   6:off
  3. Starting mysqld on boot:
  4. # chkconfig --level 2345 mysqld on && service mysqld restart && chkconfig --list | grep mysqld
    Stopping MySQL:                                            [  OK  ]
    Starting MySQL:                                            [  OK  ]
    mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off
    
  5. Clear MySQL scrollback history (so sensitive data such as passwords cannot be seen by others with access):
  6. ]# cat /dev/null > ~/.mysql_history
  7. Show all users in the MySQL Server database:
  8. mysql> select * from mysql.user;
    8 rows in set (0.00 sec)
  9. Delete a user from the MySQL Server database:
  10. mysql> delete from mysql.user where host = "dev.mydomain.com";Query OK, 2 rows affected (0.00 sec)
  11. Delete a null user (user without a username) from the MySQL Server database:
  12. mysql> delete from mysql.user where user = ' ';
    Query OK, 1 rows affected (0.00 sec)

Common problems and fixes

Problem: User has not properly logged in with roots environment.
Fix: (switch to root with one of the following methods):
su –login
su -l
su -

More Information

Disclaimer

We test this stuff on our own machines, really we do. But you may run into problems, if you do, come to #centoshelp on irc.freenode.net
This has been tested on Centos 5.x and 6.x

Added Reading

Friday, 18 October 2013

Howto Install MySQL on Linux

Reference link 2 :
http://www.thegeekstuff.com/2008/07/howto-install-mysql-on-linux/

Most of the Linux distro comes with MySQL.  If you want use MySQL, my recommendation is that you download the latest version of MySQL and install it yourself. Later you can upgrade it to the latest version when it becomes available. In this article, I will explain how to install the latest free community edition of MySQL on Linux platform.

1. Download the latest stable relase of MySQL

Download mySQL from mysql.com .  Please download the community edition of MySQL for your appropriate Linux platform. I downloaded the “Red Hat Enterprise Linux 5 RPM (x86)”. Make sure to download MySQL Server, Client and “Headers and libraries” from the download page.

  • MySQL-client-community-5.1.25-0.rhel5.i386.rpm
  • MySQL-server-community-5.1.25-0.rhel5.i386.rpm
  • MySQL-devel-community-5.1.25-0.rhel5.i386.rpm

2. Remove the existing default MySQL that came with the Linux distro

Do not perform this on an system where the MySQL database is getting used by some application.
[local-host]# rpm -qa | grep -i mysql
mysql-5.0.22-2.1.0.1
mysqlclient10-3.23.58-4.RHEL4.1

[local-host]# rpm -e mysql --nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
[local-host]# rpm -e mysqlclient10

3. Install the downloaded MySQL package

Install the MySQL Server and Client packages as shown below.
[local-host]# rpm -ivh MySQL-server-community-5.1.25-0.rhel5.i386.rpm MySQL-client-community-5.1.25-0.rhel5.i386.rpm
Preparing...                ########################################### [100%]
1:MySQL-client-community ########################################### [ 50%]
2:MySQL-server-community ########################################### [100%]
This will also display the following output and start the MySQL daemon automatically.
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h medica2 password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.
See the manual for more instructions.
Please report any problems with the /usr/bin/mysqlbug script!
The latest information about MySQL is available at http://www.mysql.com/
Support MySQL by buying support/licenses from http://shop.mysql.com/

Starting MySQL.[  OK  ]
Giving mysqld 2 seconds to start
Install the “Header and Libraries” that are part of the MySQL-devel packags
[local-host]# rpm -ivh MySQL-devel-community-5.1.25-0.rhel5.i386.rpm Preparing...                ########################################### [100%] 1:MySQL-devel-community  ########################################### [100%]
Note: When I was compiling PHP with MySQL option from source on the Linux system, it failed with the following error. Installing the MySQL-devel-community package fixed this problem in installing PHP from source.
configure: error: Cannot find MySQL header files under yes.
Note that the MySQL client library is not bundled anymore!

4.  Perform post-install security activities on MySQL.

At a bare minimum you should set a password for the root user as shown below:
[local-user]# /usr/bin/mysqladmin -u root password 'My2Secure$Password'
The best option is to run the mysql_secure_installation script that will take care of all the typical security related items on the MySQL as shown below. On a high level this does the following items:
  • Change the root password
  • Remove the anonymous user
  • Disallow root login from remote machines
  • Remove the default sample test database
[local-host]# /usr/bin/mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
You already have a root password set, so you can safely answer 'n'.
Change the root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] Y
... Success!
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] Y
... Success!
By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] Y
... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!

5.  Verify the MySQL installation:

You can check the MySQL installed version by performing mysql -V as shown below:
[local-host]# mysql -V
mysql  Ver 14.14 Distrib 5.1.25-rc, for redhat-linux-gnu (i686) using readline 5.1
Connect to the MySQL database using the root user and make sure the connection is successfull.
[local-host]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.1.25-rc-community MySQL Community Server (GPL)

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

mysql>
Follows the steps below to stop and start MySQL

[local-host]# service mysql status
MySQL running (12588)                                      [  OK  ]
[local-host]# service mysql stop
Shutting down MySQL.                                       [  OK  ]
[local-host]# service mysql start
Starting MySQL.                                            [  OK  ]

Install MySQL for Database Server.

Reference link 1 :
http://www.server-world.info/en/note?os=CentOS_6&p=mysql

[root@www ~]#
yum -y install mysql-server
[root@www ~]#
/etc/rc.d/init.d/mysqld start

Initializing MySQL database:  Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h www.server.world password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

Starting mysqld:     [  OK  ]

[root@www ~]#
chkconfig mysqld on

[root@www ~]#
mysql -u root
# connect to MySQL

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.52 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

# show user info

mysql>
select user,host,password from mysql.user;

# set root password

mysql>
set password for root@localhost=password('password');

Query OK, 0 rows affected (0.00 sec)
# set root password

mysql>
set password for root@'127.0.0.1'=password('password');

Query OK, 0 rows affected (0.00 sec)
# set root password

mysql>
set password for root@'www.server.world'=password('password');

Query OK, 0 rows affected (0.00 sec)
# delete anonymous user

mysql>
delete from mysql.user where user='';

Query OK, 2 rows affected (0.00 sec)
mysql>
select user,host,password from mysql.user;

mysql>
exit
# quit

Bye
[root@www ~]#
mysql -u root -p
# connect with root

Enter password:
# MySQL root password

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.52 Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
exit

Bye