Remote mysql database connection in nodejs

Question

I wanna connect with another server database. for example the code is in abc server and i want database of another server which is xyz server. I have done it. but getting error as

Error: ER_BAD_DB_ERROR: Unknown database 'cp'

where i do have a database as CP

the server code to interact is

var db_config={
  "domain" : "http://****/",
  "hostname" : "****",
  "port"     :  3306,
  "user" : "*****",
  "password" : "****",
  "database" : "cp"
}

    var connection=mysql.createConnection(db_config);
    connection.connect(function(err,s){
      if (err){
        console.log("Error"+err);
      }
      else{
        console.log("db connected");
      }
    });

when i remove the database and the credentials are correct i could connect with server. if i use information_schema as db name also the db is getting connected properly. but when i use the other database i am still facing the error as

Error: ER_BAD_DB_ERROR: Unknown database 'cp'

I have made setup for remote access in the particular server and checked in php code, its working fine over there.

Kindly help in this hectic task.

Did you add credentials on the remote db for your user and ip-address? It sounds like you have the credentials to connect to the mysql server but not to access the cp database. — grim_i_am
– grim_i_am, Commented Aug 30, 2017 at 12:08
Make sure that mysql user 'root' have remote connection permission. — Nashir Uddin
– Nashir Uddin, Commented Aug 30, 2017 at 12:14
@grim Yes, i have access to the mysql server but not the database. have added the credentials on the remote db. — Faizal Shaikh
– Faizal Shaikh, Commented Aug 31, 2017 at 6:09
@NashirUddin I haven't provided for the root user. instead i gave permission for the specific user created. So are you sure it will work when we give permission to root. I will try and let you know. — Faizal Shaikh
– Faizal Shaikh, Commented Aug 31, 2017 at 6:11
@NashirUddin On giving permission to 'root' user and using the credentials i am getting access denied error >Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'root'@'localhost' (using password: YES) but i can able to connect the database through php code — Faizal Shaikh
– Faizal Shaikh, Commented Aug 31, 2017 at 7:31

grim_i_am · Accepted Answer · 2017-09-01 13:14:21Z

1

I believe that your grant statement was specified incorrectly (If it is exactly as you show it in your comment above).

It should read:

GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

However, these are very general privileges. I would suggest restricting it to a specific database:

GRANT INSERT, UPDATE, SELECT, DELETE  ON <database>.* TO 'USERNAME'@'%';

After your set up your database, normal users shouldn't need to create or modify tables or grant access to other users.

Also, as you can see, I do not specify the password in the grant command. This should be specified in the CREATE USER command as under certain circumstances, GRANT may be logged in server logs or on the client side history files, which means that cleartext passwords may be read by anyone having read access to that information.

edited Sep 1, 2017 at 13:14

answered Sep 1, 2017 at 12:50

grim_i_am

3,9535 gold badges22 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Remote mysql database connection in nodejs

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related