Access denied for user ' xxx ' @ ' localhost.localdomain ' (using Password:yes)

Recently in the deployment project, because before the development of Windows, deployment is under Linux, eat a lot of cross-platform loss, such as today only found that MySQL under the table name is strictly case-sensitive. This is another pit, when Tomcat connects to MySQL, error access denied for user ' xxx ' @ ' localhost.localdomain ' (using Password:yes).

Have encountered similar problems before, but under Windows, and the error message is a little different, is Access denied for user ' xxx ' @ ' localhost ' (using Password:yes)), The result is that the cache is not clear, the actual user name and password used to connect to the database and the configuration file inconsistencies, the reason for troubleshooting only need to connect the database with the user name and password to print once, it is not table. However, this is not the cause of this, the user name and password are not error.

Troubleshooting process:

First log in to MySQL with the root user, and then execute the following statement to query all MySQL users, observe the results, which will help you understand the following content:

SELECT DISTINCT CONCAT (' User: ', user, ' @ ', host, '; ') as query from Mysql.user;

You will see that even the same user name, there will be different @host suffix, this refers to the same user on a different host login, @host refers to the host

Here the error is that access denied for user ' xxx ' @ ' Span style= "FONT-FAMILY:SIMSUN; font-size:14px ">localhost.localdomain " (using Password:yes) instead of access denied for user ' xxx ' @ ' localhost " (using Password:yes)), there is a big difference between a localdomain. This involves the difference between localhost and 127.0.0.1 (Localhost.localdomain).

when we log in to MySQL data directly at the command line as "mysql-u Username-p password", we do not use TCP/IP connection, MySQL will consider the connection from localhost, so the actual login is [email protected] . However, in Linux, when using Tomcat to connect to the database (I do not do this under Windows, do not know why), using TCP/IP, the host is determined to come from 127.0.0.1, then the actual login is [email protected], Then you use [email protected] password to verify [email protected] Of course can only be determined to reject the connection.

Workaround:

1. Create a new user and grant them permission to access the specified database. Here I specify the host for Localhost.localdomain, which means allow user named Snow User, with password 123 in Connect to the database on the Localhost.localdomain host; Note This specifies that the host Localhost.localdomain is critical.

GRANT USAGE on * * to ' snow ' @ ' localhost.localdomain ' identified by ' 123 ' with GRANT OPTION;

2. Bit the new user gives all permissions to the operational database Exampledb.

Grant all privileges the exampledb.* to [e-mail protected] identified by ' 123 ';

3. Update the Permissions table

Flush privileges;

Then change the user name of the connection in the code to snow, and change the password to 123. , problem solved, successfully connected on MySQL.

Of course, you can also not change the code in the user name and password, just the above 1th, 2 steps of the user name and password to replace with your user name and password.

Access denied for user ' xxx ' @ ' localhost.localdomain ' (using Password:yes)