无法检测到mysql数据库连接_无法使用MySQLConnection连接到MySQL数据库

I'm using the MySqlConnection class to connect to a local MySQL database:

SourceHost = ConfigurationManager.AppSettings["sourceHost"];

SourceDatabase = ConfigurationManager.AppSettings["sourceDatabase"];

SourceUsername = ConfigurationManager.AppSettings["sourceUsername"];

SourcePassword = ConfigurationManager.AppSettings["sourcePassword"];

SourceString = String.Format("Server={0};Database={1};UID={2};Password={3};", SourceHost, SourceDatabase, SourceUsername, SourcePassword);

var sqlSourceConnection = new MySqlConnection(SourceString);

sqlSourceConnection.Open();

These are the values in the App.config file

The error I get is saying: Access denied for user myUser@myHost.myDomain using password:YES, but by using the myUser and myPSW credentials I can login from the command prompt.

What's wrong?

EDIT: It worked when I changed the sourceHost to

Talk1:

Are you running the code on and from the same host you're trying on the command line?

Talk2:

Check the port. MySQL usually defines the port as being 3306, but if you've changed it, or is using another distribution (say, MariaDB), the credentials will crash. Other solution: Check if your user has the permission to read your database.

Talk3:

yes, I am running it from the command line

Talk4:

I checked the database and it is using the port 3306. The user I am trying to login is root.

Talk5:

It started working when I replaced the machine name with localhost. I think it was trying to use some form of domain authentication instead of the SQL login.

Solutions1

User myUser is only authorized from localhost.

You need to authorize myUser for your hostname.

GRANT ALL PRIVILEGES ON *.* to 'myUser'@'%' WITH GRANT OPTION;

or

GRANT ON *.* TO 'myUser'@'myHost.myDomain' IDENTIFIED BY 'password' ;

or you can also do

GRANT ON *.* TO 'myUser'@'%.myDomain' IDENTIFIED BY 'password' ;

Solutions2

Standard

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Specifying TCP port

Server=myServerAddress;Port=1234;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

The port 3306 is the default MySql port.The value is ignored if Unix socket is used.

Solutions3

When you try SHOW GRANTS you should see line like this:

mysql> SHOW GRANTS;

+-----------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'myUSer'@'localhost' WITH GRANT OPTION |

+-----------------------------------------------------------------------+

Note the localhost, so this happens with the connection:

When you try to connect to localhost system automatically uses 127.0.0.1 as connection source address.

But when you connect to 10.0.0.1 it uses 10.0.0.1 as your source address.

C:\>ping localhost -S 127.0.0.1

Pinging localhost [127.0.0.1] from 127.0.0.1 with 32 bytes of data:

Reply from 127.0.0.1: bytes=32 time<1ms TTL=128

C:\>ping localhost -S 10.0.0.1

Pinging localhost [127.0.0.1] from 10.0.0.1 with 32 bytes of data:

PING: transmit failed. General failure.

C:\>ping 10.0.0.1 -S 10.0.0.1

Pinging 10.0.0.1 from 10.0.0.1 with 32 bytes of data:

Reply from 10.0.0.1: bytes=32 time<1ms TTL=128

C:\>ping 10.0.0.1 -S 127.0.0.1

Pinging 10.0.0.1 from 127.0.0.1 with 32 bytes of data:

PING: transmit failed. General failure.

So it works with localhost, because localhost is an allowed source host, and it doesn't with your_host because 10.0.0.1 is not an allowed host.

Just grant privileges for your user to 10.0.0.1 and it should work (maybe you'll have to change firewall settings), or allow all connection from the outside (using % as host).