I am trying to run some queries on a mysql database on a remote machine. I don't have admin access on this remote machine, so I need to authenticate my credentials on this machine before I can access the database (which also needs my username and password authentication). After reading this post (Read remote file in java which needs username and password) I can see how I would use authentication to access the remote machine and I am familiar with using DriverManager to run queries on a local database; I am just not sure how I can combine the two functionalities. Any suggestions?
-
Generally, mysql database authentication on remote machine is different from authentication on remote machine. It means mysql has its own user name and password and you should be able to connect with remote mysql server using that user name and password. Authentication on remote machine should not be required.Vikas Sachdeva– Vikas Sachdeva2016-12-06 00:54:53 +00:00Commented Dec 6, 2016 at 0:54
-
Oh, that's good to know. So if I get an 'access denied' error when using the mysql authentication with DriverManager.getConnection(...) what could be the cause of the error?user97468– user974682016-12-06 23:12:26 +00:00Commented Dec 6, 2016 at 23:12
Add a comment
|
2 Answers
It looks that the user you are using for connecting with mysql database does not have remote access privileges, so you need to grant remote access to that user.
Execute following queries on mysql server as root user for creating a new user and granting remote access to that user -
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION;
CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Comment below line in /etc/mysql/my.cnf file -
bind-address = 127.0.0.1
And restart mysql server -
sudo service mysql restart
2 Comments
Lord_PedantenStein
You have to bind to the external IP Address to gain remote access.
user97468
Thanks, that did turn out to be the problem! the database wasn't configured for remote access
Use JDBC http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html. How did you run MySQL querys before? In JDBC you access remote machines like the local one (EDIT: in code).
EDIT: The tutorial is a bit rich in information, so here is some code to get you started:
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
private static Connection co;
private Statement st;
public void connectDb(){
try{
String url = "jdbc:mysql://localhost/dataset"; //url to _DATABASE_ create as mysqlroot first!
String user = "theuser";
String password = "thepassword";
Class.forName("com.mysql.jdbc.Driver");
co = DriverManager.getConnection(url, user, password);
st = co.createStatement();
co.setAutoCommit(false);
} catch(Exception e){
e.printStackTrace();
}
}
You need to get the JDBC driver before you can use this (see tutorial)!
This is how you could run a query:
private ResultSet executeQuery(String query){
ResultSet rs = null;
try{
rs = st.executeQuery(query);
return rs;
} catch(Exception e){
System.out.println(query);
e.printStackTrace();
}
return rs;
}
And this is how an Update could be executed, notice the difference between an update and a query!
private void executeUpdate(String query){
try{
st.executeUpdate(query);//data manipulation = executeUpdate
} catch(Exception e){
System.out.println(query);
e.printStackTrace();
}
}
Hope this helps ^^-d
2 Comments
user97468
That's pretty much the code I use to query local databases; However when I try to query a remote database (by changing localhost to the remote server IP) I get an 'access denied for <username>' error.
Lord_PedantenStein
Hi again, you need to know the credentials and the database must be configured so that it can be accessed with these credentials and by remote. So you should connect with an admin. This could help the admin: stackoverflow.com/questions/6239131/… and stackoverflow.com/questions/16287559/… hope this helps ^^
