6.2.17 Troubleshooting Problems Connecting to MySQL (original) (raw)

6.2.17 Troubleshooting Problems Connecting to MySQL

If you encounter problems when you try to connect to the MySQL server, the following items describe some courses of action you can take to correct the problem.

$> mysql  
ERROR 2003: Can't connect to MySQL server on 'host_name' (111)  
$> mysql  
ERROR 2002: Can't connect to local MySQL server through socket  
'/tmp/mysql.sock' (111)  
$> netstat -ln | grep mysql  
$> mysql -u root  
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

It means a root password has already been assigned during installation and it has to be supplied. SeeSection 2.9.4, “Securing the Initial MySQL Account” on the different ways the password could have been assigned and, in some cases, how to find it. If you need to reset the root password, see instructions in Section B.3.3.2, “How to Reset the Root Password”. After you have found or reset your password, log on again asroot using the--password (or-p) option: 

$> mysql -u root -p  
Enter password:

However, the server is going to let you connect asroot without using a password if you have initialized MySQL using mysqld --initialize-insecure (seeSection 2.9.1, “Initializing the Data Directory” for details). That is a security risk, so you should set a password for theroot account; seeSection 2.9.4, “Securing the Initial MySQL Account” for instructions.

$> mysql  
Client does not support authentication protocol requested  
by server; consider upgrading MySQL client

For information on how to deal with this, seeSection 6.4.1.3, “Migrating Away from Pre-4.1 Password Hashing and the mysql_old_password Plugin”.

$> mysqladmin --no-defaults -u root version

The option files that clients use are listed inSection 4.2.2.2, “Using Option Files”. Environment variables are listed in Section 4.9, “Environment Variables”.

$> mysqladmin -u root -pxxxx ver  
Access denied for user 'root'@'localhost' (using password: YES)

If the preceding error occurs even when you have not specified a password, it means that you have an incorrect password listed in some option file. Try the--no-defaults option as described in the previous item.
For information on changing passwords, seeSection 6.2.10, “Assigning Account Passwords”.
If you have lost or forgotten the root password, see Section B.3.3.2, “How to Reset the Root Password”.

SELECT * FROM user;

The result should include a row with theHost and User columns matching your client's host name and your MySQL user name.

Host ... is not allowed to connect to this MySQL server

You can fix this by setting up an account for the combination of client host name and user name that you are using when trying to connect.
If you do not know the IP address or host name of the machine from which you are connecting, you should put a row with'%' as the Host column value in the user table. After trying to connect from the client machine, use a SELECT USER() query to see how you really did connect. Then change the '%' in theuser table row to the actual host name that shows up in the log. Otherwise, your system is left insecure because it permits connections from any host for the given user name.
On Linux, another reason that this error might occur is that you are using a binary MySQL version that is compiled with a different version of the glibc library than the one you are using. In this case, you should either upgrade your operating system or glibc, or download a source distribution of MySQL version and compile it yourself. A source RPM is normally trivial to compile and install, so this is not a big problem.

$> mysqladmin -u root -pxxxx -h some_hostname ver  
Access denied for user 'root'@'' (using password: YES)

If you try to connect as root and get the following error, it means that you do not have a row in theuser table with a User column value of 'root' and thatmysqld cannot resolve the host name for your client: 

Access denied for user ''@'unknown'

These errors indicate a DNS problem. To fix it, executemysqladmin flush-hosts to reset the internal DNS host cache. See Section 5.1.11.2, “DNS Lookups and the Host Cache”.
Some permanent solutions are: