Home > MySQL database and Ubuntu Server > mySQL Remote Access in my LAN

mySQL Remote Access in my LAN

Example of Shared Local Network

My steps taken to allow remote access to mySQL server running on Shared Local Network. For example, allowing your java application to connect to mysql server requires permission for remote access.

Sample Connection String for mySQL Server Database Connection:

  try
  {
    String myConnString = "jdbc:mysql://192.168.1.8:3306" +
                          "/myDatabaseName?user=myUserName" +
                          &password=myPassword";

    Class.forName("com.mysql.jdbc.Driver");
    connection = DriverManager.getConnection(myConnString);

    //Print to console a message for testing.
    System.out.println(DatabaseConnection.class.getName() +
                       " > Opened connection successfully.");
  }
  catch (ClassNotFoundException | SQLException ex)
  {
    System.out.println(DatabaseConnection.class.getName() +
                       " > FAILED DB CONNECTION.");
    //Log Error
    Logger.getLogger(DatabaseConnection.class.getName()).log(
                     Level.SEVERE, null, ex);
  }

Example error message when the user doesnt have permission to connect to the specified database.

java.sql.SQLException: Access denied for user ‘myUserName’@’192.168.1.7′ (using password: YES)

To allow for remote database access:

1. Log into mySQL server, enter password at prompt.

mysql -u root -p

2. Create new user to connect to mySQL Server. Note the single quotes around both username and ip address. The at symbol is not enclosed in quotes. The IP Address should be the ip address of the computer where the user will connect. The reserved keyword “identified by” sets the password for the user.

create user ‘myUserName’@’192.168.1.7′ identified by ‘myPassword’;

In a local network, you might use the wildcard % in the ip address to allow for any computers within the range to connect.

create user ‘myUserName’@’192.168.1.%’ identified by ‘myPassword’;

3. Grant permissions to manage database. Note the IP Range wild card used here to apply to all. Now user can perform the basic database operations.

GRANT SELECT, INSERT, UPDATE, DELETE ON myDatabaseName.* TO ‘myUserName’@’192.168.1.%’;

Reference ebook: MySQL Enterprise Solutions, by Pachev, Alexander Sasha, 2003, ISBN: 0-471-26922-0 , Pages: 85, 86

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: