Thursday, May 30, 2013

HOW ACCESS MYSQL SERVER FROM REMOTE SYSTEM ?

ACCESS MYSQL SERVER FROM REMOTE SYSTEM

Here I illustrates

1. How to create a new root user

2. Enable server for remote access

1. CREATE A NEW ROOT USER AND ASSIGN PREVILEGES

Enter in to the mysql console as 'root' user and type as shown below,

mysql> CREATE USER 'userName'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'userName'@'%'

    ->     WITH GRANT OPTION;

Yes, now you created a new root user with all previleges. For ensure this type.

mysql> show GRANTS FOR 'userName'@'%';
+------------------------------------------------------------------------------------------------------------------------------------+
| Grants for userName@%                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'userName'@'%' IDENTIFIED BY PASSWORD '*1C8B9ABB8*********************' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------+

2. ENABLE SERVER FOR REMOTE ACCESS

Once connected you need to edit the MySQL server configuration file my.cnf using a text editor such as vi.

If you are using Debian Linux file is located at /etc/mysql/my.cnf location
If you are using Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location
If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf

Edit /etc/my.cnf, run:
# vi /etc/my.cnf

Once file opened, locate line that read as follows

[mysqld]

Make sure line skip-networking is commented (or remove line) and add following line

bind-address=YOUR-SERVER-IP

For example, if your MySQL server IP is 65.55.55.2 then entire block should be look like as follows:

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/English
bind-address    = 65.55.55.2
# skip-networking
....
..
....

Where,

bind-address : IP address to bind to.
skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.

Save and Close the file

If you are using Debian / Ubuntu Linux, type the following command to restart the mysql server:
# /etc/init.d/mysql restart
If you are using RHEL / CentOS / Fedora / Scientific Linux, type the following command to restart the mysql server:
# /etc/init.d/mysqld restart
If you are using FreeBSD, type the following command to restart the mysql server:

# /usr/local/etc/rc.d/mysql-server stop
# /usr/local/etc/rc.d/mysql-server start

OR
# /usr/local/etc/rc.d/mysql-server restart

Grant access to remote IP address

Connect to mysql server:
$ mysql -u root -p mysql

Grant access to a new database

If you want to add a new database called foo for user bar and remote IP 202.54.10.20 then you need to type the following commands at mysql> prompt:

mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';

How Do I Grant Access To An Existing Database?

Let us assume that you are always making connection from remote IP called 202.54.10.20 for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database, enter:

mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';

Allow Access to everyone with user name and password

mysql> CREATE USER 'public'@'%' IDENTIFIED BY 'public123';

mysql> GRANT ALL PRIVILEGES ON *.* TO 'public'@'%' WITH GRANT OPTION;

Logout of MySQL

Type exit command to logout mysql:mysql> exit

Open port 3306

You need to open TCP port 3306 using iptables or BSD pf firewall.

A sample iptables rule to open Linux iptables firewall

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your web server located at 10.5.1.3:

/sbin/iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your lan subnet 192.168.1.0/24:

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPT

Finally save all rules (RHEL / CentOS specific command):
# service iptables save

A sample FreeBSD / OpenBSD pf rule ( /etc/pf.conf)

pass in on $ext_if proto tcp from any to any port 3306

OR allow only access from your web server located at 10.5.1.3:

pass in on $ext_if proto tcp from 10.5.1.3 to any port 3306  flags S/SA synproxy state

Test it

From your remote system or your desktop type the following command:
$ mysql -u webadmin –h 65.55.55.2 –p
Where,

-u webadmin: webadmin is MySQL username
-h IP or hostname: 65.55.55.2 is MySQL server IP address or hostname (FQDN)
-p : Prompt for password

You can also use the telnet or nc command to connect to port 3306 for testing purpose:
$ echo X | telnet -e X 65.55.55.2 3306
OR
$ nc -z -w1 65.55.55.2 3306
Sample outputs:

Connection to 65.55.55.2 3306 port [tcp/mysql] succeeded!

Tuesday, May 28, 2013

Spring MVC & Eclipse with example

Spring MVC & Eclipse

Here we go......

Step	Description
1	Create a Dynamic webproject 'HelloApp' in Eclipse(New-> Project -> Web -> Dynamic Web Project) If your Eclipse not have this option, install web module from `http://download.eclipse.org/webtools/updates or` http://download.eclipse.org/webtools/repository/EclipseVersion (Eg: http://download.eclipse.org/webtools/repository/helios)

2	Download http://ge.tt/api/1/files/9fwBGvh/0/blob?download extract the files and Drag and drop libraries into the folder WebContent/WEB-INF/lib.
3	Create a Java class HelloController under the controller package.
4	Create Spring configuration files web.xml and HelloWeb-servlet.xml under theWebContent/WEB-INF folder.
5	Create a sub-folder with a name jsp under the WebContent/WEB-INF folder. Create a view file hello.jsp under this sub-folder.
6	The final step is to create the content of all the source and configuration files and export the application as explained below.

Following is the content of Spring Web configuration file HelloWeb-servlet.xml

 <beans xmlns="http://www.springframework.org/schema/beans"  
   xmlns:context="http://www.springframework.org/schema/context"  
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
   xsi:schemaLocation="  
   http://www.springframework.org/schema/beans     
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd  
   http://www.springframework.org/schema/context   
   http://www.springframework.org/schema/context/spring-context-3.0.xsd">  
   <context:component-scan base-package="controller" />  
   <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">  
    <property name="prefix" value="/WEB-INF/jsp/" />  
    <property name="suffix" value=".jsp" />  
   </bean>  
 </beans>

Following is the content of another Spring Web configuration file web.xml

 <web-app id="WebApp_ID" version="2.4"  
   xmlns="http://java.sun.com/xml/ns/j2ee"   
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
   xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee   
   http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">  
   <display-name>Spring MVC Application</display-name>  
   <servlet>  
    <servlet-name>HelloWeb</servlet-name>  
    <servlet-class>  
      org.springframework.web.servlet.DispatcherServlet  
    </servlet-class>  
    <load-on-startup>1</load-on-startup>  
   </servlet>  
   <servlet-mapping>  
    <servlet-name>HelloWeb</servlet-name>  
    <url-pattern>/</url-pattern>  
   </servlet-mapping>  
 </web-app>

Here is the content of FirstController.java file:

 package controller;  
 import org.springframework.stereotype.Controller;  
 import org.springframework.ui.Model;  
 import org.springframework.ui.ModelMap;  
 import org.springframework.web.bind.annotation.RequestMapping;  
 import org.springframework.web.bind.annotation.RequestMethod;  
 @Controller  
 @RequestMapping("/")  
 public class FirstController {  
 @RequestMapping(method = RequestMethod.GET)  
 public String home(ModelMap model){  
      model.addAttribute("message","Welcome to the world of MVC");  
      return "first";  
 }  
 }

Here is the content of SecondController.java file:

 package controller;  
 import org.springframework.stereotype.Controller;  
 import org.springframework.ui.Model;  
 import org.springframework.ui.ModelMap;  
 import org.springframework.web.bind.annotation.RequestMapping;  
 import org.springframework.web.bind.annotation.RequestMethod;  
 @Controller  
 @RequestMapping("/second")  
 public class SecondController {  
 @RequestMapping(method = RequestMethod.GET)  
 public String home(ModelMap model){  
      model.addAttribute("data"," Ya, its works....!");  
      return "second";  
 }  
 }

Following is the content of Spring view file first.jsp

 <%@ page contentType="text/html; charset=UTF-8" %>  
 <html>  
 <head>  
 <title>Hello World</title>  
 </head>  
 <body>  
   <h2>${message}</h2>  
 </body>

<a href="second">Click to go to the next page</a>


 </html>

Following is the content of Spring view file second.jsp

 <%@ page contentType="text/html; charset=UTF-8" %>  
 <html>  
 <head>  
 <title>Hello World</title>  
 </head>  
 <body>  
   <h2>${data}</h2>  
 </body>  
 </html>

If you need to link files(like java script file,css, image, video, audio etc.) not from internet, then we must do the following steps.

1. Change the code of HelloWeb-servlet.xml as follows


 <?xml version="1.0" encoding="UTF-8"?>  
 <beans xmlns="http://www.springframework.org/schema/beans"  
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"  
  xmlns:mvc="http://www.springframework.org/schema/mvc"  
  xsi:schemaLocation="http://www.springframework.org/schema/beans  
  http://www.springframework.org/schema/beans/spring-beans-2.5.xsd  
  http://www.springframework.org/schema/mvc  
  http://www.springframework.org/schema/mvc/spring-mvc-3.0.xsd  
  http://www.springframework.org/schema/context  
  http://www.springframework.org/schema/context/spring-context-3.0.xsd">  
   <!-- not strictly necessary for this example, but still useful, see http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/mvc.html#mvc-ann-controller for more information -->  
  <context:component-scan base-package="controller" />  
   <!-- the mvc resources tag does the magic -->  
  <mvc:resources mapping="/resources/**" location="/resources/" />  
   <!-- also add the following beans to get rid of some exceptions -->  
  <bean   class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter" />  
  <bean  
 class="org.springframework.web.servlet.mvc.annotation.DefaultAnnotationHandlerMapping">  
  </bean>  
   <!-- JSTL resolver -->  
  <bean id="viewResolver"  
  class="org.springframework.web.servlet.view.InternalResourceViewResolver">  
  <property name="viewClass"  
   value="org.springframework.web.servlet.view.JstlView" />  
  <property name="prefix" value="/WEB-INF/jsp/" />  
  <property name="suffix" value=".jsp" />  
  </bean>  
 </beans>

2. Add a new folder 'resources' in 'WebContent'

3. Drag and drop files those you need to include in webpage. We can create sub folders also

4. Include the reference path of the resource as follows.

If you have an image file, 'myImage.jpeg' in 'resources/images' folder.

The link path will be, '/ProjectName/resources/images/myImage.jpeg'

So now add an image in 'WebContent/resources/images' folder and change the second.jsp file content as follows

  <%@ page contentType="text/html; charset=UTF-8" %>   
  <html>   
  <head>   
  <title>Hello World</title>   
  </head>   
  <body>   
   <h2>${data}</h2>   
 <img src="/HelloApp/resources/images/myImage.jpeg">  
  </body>   
  </html>