Adding a MySQL Remote User / Accessing MySQL Database Remotely [Simple Solution]

Here is a background – I have two desktops and a laptop that I generally use for development purposes. One of the desktop is my database server. However, when I tried to access the database from either my development laptop or a development desktop, I could not remotely connect to the mysql database that was running just a feet away under my desk within the same network.

Then a little research revealed that the mysql needs to approve a remote usage. There were tons of solutions online, not as close to this one.

All I basically had to do was three basic steps

  1. Add the remote user with it’s ip address in the mysql database (mysql.user)
  2. Grant permissions for this new user.
  3. Then flush the privileges.

My tip is create a unique user id that is not used locally or anonymously (‘user’@’localhost’ or ‘user’@’%’). Although the localhost one should not be a problem, the anonymous one (%) did create a problem by trumping my other ip addresses.

Here is how you can create user id – I suggest you do this from the database server, although it is possible to do it if you already have a remote access established.

NOTE: These commands are run either mysql prompt or your GUI CLIENT

CREATE USER 'someuser'@'192.168.0.77' IDENTIFIED BY 'somepassword';

This will add a user on mysql.user table.

The step to grant permission is:

GRANT ALL ON *.* TO ‘someuser’@‘192.168.0.77’; 

This is granting all permissions, but you can choose your specific ones.

Finally flush the privileges.

FLUSH PRIVILEGES;

In rare cases, you may need to restart the mysql or OS.

Inserting Values From Text File To MySQL

This tutorial shows how you can insert some data from a text file to a MySQL database. You can also extend this to work with other databases by changing the driver.

package com.kushal.utilities;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class InsertTextFromFileToMySql {

public static Connection getConnection() throws Exception {
	String driver = "com.mysql.jdbc.Driver";
	String url = "jdbc:mysql://localhost/sanjaal_com";
	String username = "root";
	String password = "";

	Class.forName(driver);
	Connection conn = DriverManager.getConnection(url,
                                  username, password);
	return conn;
}

public static void main(String[] args) throws Exception {
	String textValue = "";
	int textLength = 0;
	String category = "funny";
	String lineRead = null;
	PreparedStatement pstmt = null;

	String fileNameToInsertTextFrom = "C:/temp/smsCollection002.txt";
	FileReader fileReader = new FileReader(new File(
			fileNameToInsertTextFrom));

	Connection conn = null;
	try {
		conn = getConnection();
		conn.setAutoCommit(false);

		BufferedReader reader = new BufferedReader(fileReader);
		pstmt = conn.prepareStatement(
		"insert into smsTable(sms_text, sms_length, sms_category)"+
		"values (?, ?, ?)");

		while ((lineRead = reader.readLine()) != null) {
			try {
				textValue = lineRead.trim();
				textLength = textValue.length();

				if (textLength > 0) {

					pstmt.setString(1, textValue);
					pstmt.setInt(2, textLength);
					pstmt.setString(3, category);
					pstmt.executeUpdate();
					conn.commit();
				}

			} catch (NullPointerException npe) {
				// do nothing proceed to another line
			}
			lineRead = null;
		}

	} catch (Exception e) {
		System.err.println("Error: " + e.getMessage());
		e.printStackTrace();
	} finally {
		pstmt.close();
		conn.close();
	}
	}
}

SQL Example To Create Table On MySql DB

This is a very basic script that creates a table in the database. This has been tested to work with mysql, but similar format can be applied to other databases such as MS SQL Server, Oracle, IBM DB2 etc.

Here is the database schema that we want to create:

The code to create this table is:

CREATE TABLE KP_BANK_PYMT_INFO(
	INFO_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
	BANK_NAME VARCHAR(100),
	CURRENT_BAL DECIMAL(5,2),
	PYMT_DUE_DT TIMESTAMP DEFAULT NOW()
	LAST_PYMT_AMT DECIMAL(5,2).
	LAST_PAID_DT TIMESTAMP,
	APR DECIMAL (3,2),
	REMARKS VARCHAR (100)
)