For beginners, mysql users often encounter mysql connection failures. Special post to collect the symptoms and causes of such problems.
First, throw a brick.
Summarized as follows:
Fault description: Unable to connect to mysql
========================================================== ============================================
ERROR Message 1: ERROR 1045 (28000): Access denied for user 'usera '@ 'localhost' (using password: YES) ERROR message 2: ERROR 1045 (28000 ): access denied for user 'usera '@ 'localhost' (using password: NO). First, we will analyze the two types of error messages in the following scenarios: Description: when using mysql connection commands or tools to connect to a remote database, the above two types of error messages may appear. The following describes how to connect to a remote database using commands. When using the mysql connection command, if the-p parameter is included and the password is specified, or the-p parameter is not included, a string is input when you enter the password in the next step, "Error Message 1" is returned. If the-p parameter is not included or the-p parameter is included, but the password is not entered in the next step, "Error Message 2" is returned, as shown below:
C: \ Documents ents and Settings \ Administrator> mysql-uroot-h 192.168.8.88-proot // With the-p parameter, and specify the password
ERROR 1045 (28000): Access denied for user 'root' @ '192. 168.8.123 '(using password: YES)
C: \ Documents ents and Settings \ Administrator> mysql-uroot-h 192.168.8.88-p // enter the password in the next step with the-p Parameter
Enter password: // string Input
ERROR 1045 (28000): Access denied for user 'root' @ '192. 168.8.123 '(using password: YES)
C: \ Documents ents and Settings \ Administrator> mysql-uroot-h 192.168.8.88 // without the-p Parameter
ERROR 1045 (28000): Access denied for user 'root' @ '192. 168.8.123 '(using password: NO)
C: \ Documents ents and Settings \ Administrator> mysql-uroot-h 192.168.8.88-p // enter the password in the next step with the-p Parameter
Enter password: // No string Input
ERROR 1045 (28000): Access denied for user 'root' @ '192. 168.8.123 '(using password: NO)
The above comparison can be concluded that, when using the mysql Command for logon, if there is a password input behavior and a character is entered, after an error is verified, the returned error prompt is in, for (using password :?) Medium? If NO password is entered or NO password is entered, NO is returned.
In addition to the above experiment comparison, we also performed the following login comparison operations, recorded the error message types they returned, and verified the above summary:
1. Use an existing user without entering the password
ERROR 1045 (28000): Access denied for user 'root' @ '192. 168.8.123 '(using password: NO)
2. Use a user that does not exist and do not enter a password
ERROR 1045 (28000): Access denied for user 'root' @ '192. 168.8.123 '(using password: NO)
3. Use an existing user and enter the correct password
ERROR 1045 (28000): Access denied for user 'root' @ '192. 168.8.123 '(using password: YES)
4. The existing user is used, but the entered password is incorrect.
ERROR 1045 (28000): Access denied for user 'root' @ '192. 168.8.123 '(using password: YES)
5. Use a non-existent user, but enter a password in the database
ERROR 1045 (28000): Access denied for user 'root' @ '192. 168.8.123 '(using password: YES)
6. Use a user that does not exist and enter a password that does not exist in the Database
ERROR 1045 (28000): Access denied for user 'root' @ '192. 168.8.123 '(using password: YES)
Summary: When an ERROR 1045 (28000): Access denied for user 'root' @ '192. 168.8.123 'is returned, (using password :?) Medium? Of
Whether the keyword is YES or NO, the key is not whether the user exists or the password is correct. The result depends on whether the user has entered the password string during logon. If not, after the MySQL database verifies the password, if this type of information is returned due to an error, it should be (using password: NO). If the user has a string for the password, the returned value is
(Using password: YES ).
The following describes the ERROR 1045 (28000): Access denied for user 'usera '@ 'localhost:
Cause 1: the user account for remote client access is not created
Check:
After logging on as administrator ROOT, show grants for 'user' @ 'IP'; or select user from mysql. user; to check whether the user account exists.
Mysql> show grants for 'jtsec '@ '192. 168.8.123 ';
ERROR 1141 (42000): There is no such grant defined for user 'jtss' on host' 192. 168.8.123'
Mysql>
ERROR 1141 (42000): There is no such grant defined for user 'jtsec 'on host' 192. 168.8.123 'indicates that there is no jtsec user or no jtsec user is authorized to remotely access 192.168.8.123. Mysql> select user, host from mysql. user;
+ ------- + --------------- +
| User | host |
+ ------- + --------------- +
| Root | localhost |
+ ------- + --------------- +
1 rows in set (0.00 sec)
Mysql>
There is only one user record count. It is root and there is no jtsec related record, indicating that there is no jtsec account in the database.
Process: create a user account.
Mysql> grant all privileges on *. * to 'jtsec '@ '192. 168.8.123 'identified by 'jtsec' with grant option;
Query OK, 0 rows affected (0.00 sec)
Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Mysql> show grants for 'jtsec '@ '192. 168.8.123 ';
+ Certificate ------------ +
| Grants for jtsec@192.168.8.123 |
+ Certificate ------------ +
| Grant all privileges on *. * TO 'jtss' @ '192. 168.8.123 'identified by password' * 0B4AB716B6BE11F89101577836F3016D8EEAA217' with grant option |
+ Certificate ------------ +
1 row in set (0.00 sec)
Mysql> select user, host from mysql. user;
+ ------- + --------------- +
| User | host |
+ ------- + --------------- +
| Jtsec | 192.168.8.157 |
| Root | localhost |
+ ------- + --------------- +
2 rows in set (0.00 sec)
Mysql>
Cause 2: the user account exists, but the client IP address is not authorized for remote access.
Check: After logging on as administrator ROOT, show grants for 'user' @ 'IP'; mysql> show grants for 'root' @ '192. 168.8.123 ';
ERROR 1141 (42000): There is no such grant defined for user 'root' on host' 192. 168.8.123'
Mysql>
ERROR 1141 (42000): There is no such grant defined for user 'root' on host' 192. 168.8.123 'indicates that there is no root user (this is ruled out because it is a MySQL Super User), or the root user is not authorized for remote access at 192.168.8.123. Let's take a look at the comparison. for the root user's local access permission, we can find out: mysql> show grants for 'root' @ 'localhost ';
+ Certificate --------- +
| Grants for root @ localhost |
+ Certificate --------- +
| Grant all privileges on *. * TO 'root' @ 'localhost' identified by password' * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B 'with grant option |
+ Certificate --------- +
1 row in set (0.00 sec) or directly query the mysql user table select user, host from mysql. the permission information of each user is recorded. mysql> select user, host from mysql. user;
+ ------- + --------------- +
| User | host |
+ ------- + --------------- +
| Root | localhost |
+ ------- + --------------- +
1 rows in set (0.00 sec)
Mysql>
If there is only one record with the user value as root and the host value is localhost, it means that the root user can only access the database locally.
Processing: Remote Access Authorization for the root user can be authorized to the specified Client IP address, or can be authorized to access all IP addresses (the host value is % ).
Authorize all IP addresses to use the User root and password root to remotely access the database.
Mysql> grant all privileges on *. * TO 'root' @ '%' identified by 'root' with grant option;
Query OK, 0 rows affected (0.00 sec)
Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Query authorization again
Mysql> show grants for 'root' @ '% ';
+ Region-+
| Grants for root @ % |
+ Region-+
| Grant all privileges on *. * TO 'root' @ '%' identified by password' * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B 'with grant option |
+ Region-+
1 row in set (0.00 sec) re-query the Authorization Table Record mysql> select user, host, password from mysql. user;
+ ------- + --------------- + ------------------------------------------- +
| User | host | password |
+ ------- + --------------- + ------------------------------------------- +
| Root | localhost | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| Root | % | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+ ------- + --------------- + ------------------------------------------- +
2 rows in set (0.00 sec) mysql>
Cause 3: The password for authorized access to the user account is incorrect. Check: After you log on as administrator ROOT, select user, host, password from mysql. user; mysql> select user, host, password from mysql. user;
+ ------- + --------------- + ------------------------------------------- +
| User | host | password |
+ ------- + --------------- + ------------------------------------------- +
| Root | localhost | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| Root | % | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | jtsec | 192.168.8.123 |
| Jtsec | 192.168.8.123 | * 0B4AB716B6BE11F89101577836F3016D8EEAA217 |
+ ------- + --------------- + ------------------------------------------- +
4 rows in set (0.00 sec) mysql>
According to the query results, the root account's local access password is the same as the remote access password. While the jtse account is on the IP address 192.168.8.123, there are two passwords for remote database access, one of which is empty (the third record ). That is to say, on the client whose IP address is 192.168.8.123, the account jtsec can be used to remotely access the database. Two Valid passwords can be selected: one is that no password is entered (the password is blank ), the other is * 0B4AB716B6BE11F89101577836F3016D8EEAA217 (encrypted). All other input values are incorrect.
Handling: use the correct password for access. ========================================================== ========================================================== === Error message: ERROR 2003 (HY000): Can't connect to MySQL server on '100. 168.8.88 '(10065) cause: Check for firewall blocking on the MySQL server: Check iptables rules in Linux to see if the default communication port 3306 of the MySQL database is not allowed: add the corresponding release rules and add the code in/etc/sysconfig/iptables:
-A RH-Firewall-1-INPUT-m state -- state NEW-m tcp-p tcp -- dport 3306-j ACCEPT
Or try to turn off the Firewall
Chkconfig ip6tables off
Chkconfig iptables off
Problem description: C: \ Documents ents and Settings \ Administrator> mysql-uroot-h 192.168.8.88-proot
ERROR 2003 (HY000): Can't connect to MySQL server on '100. 168.8.88 '( 10065) C: \ Documents and Settings \ Administrator >====================================== ========================================================== ============
ERROR message: ERROR 2003 (HY000): Can't connect to MySQL server on 'hostxxxxx' (10061)
Cause: mysqld Database Service is not started.
Check: in the windows Task Manager or in unix/linux, check out ps-aux | grep mysql. Confirm that the service has been started.
Processing: Start the mysqld service
========================================================== ========================================================== ===
ERROR message: ERROR 1130: Host xx. xx is not allowed to connect to this MySQL server
Cause: the mysql server does not grant the remote connection permission to the client.
Check: On the mysql server, check whether the host corresponding to the user table in the mysql database contains the IP address of the client machine (% no restriction on IP addresses allow remote connection ).
Processing: Modify the user table in the mysql database: update user set host = '%' where user = 'xxx'; flush privileges;
========================================================== ========================================================== ===
ERROR message: ERROR 1045 (28000): Access denied for user 'usera '@ 'localhost' (using password: NO)
Cause: the user account is not created, and the logon command does not directly contain the password item during Remote Logon: After the Administrator logs on as ROOT, show grants for 'usera '@ 'localhost '; or select user from mysql. user; check whether the user account exists.
Process: create a user account.
========================================================== ========================================================== ===
ERROR message: ERROR 2003 (HY000): Can't connect to MySQL server on 'hostxxxxx' (10061)
Cause: the mysqld client and server port are inconsistent.
Check: the service port is set in my. ini. In this case, especially when the customer is inconsistent with the remote server port, it is easy to see problems.
Processing: Start the mysqld service
========================================================== ========================================================== ===
ERROR message: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql. sock' (111)
Cause: mysql. sock of mysqld is not in the corresponding position.
Processing: 1. First check/etc/rc. d/init. d/mysql status to see if it is enabled
2. Change the permission chown-R mysql: msyql/var/lib/mysql
3. Modify/etc/my. cnf (Note: Stop the database first)
[Mysqld]
Datadir =/usr/local/mysql/data
Socket =/var/lib/mysql. sock
[Mysql. server]
User = mysql
Basedir =/usr/local/mysql
[Client]
Socker =/var/lib/mysql. sock
4. Start the database
========================================================== ========================================================== ===
Common check steps.
1. PING hostname or PING 189. xx to confirm that the server IP layer communication is correct. If PING general rules continue (2), PING general rules seek help from network experts.
2. TELNET hostname 3306 to confirm that the TCP layer communication on the server is correct. (Your port number may not be 3306). If the general rule persists, check whether mysqld is running and the firewall shields the port.
3. check user permissions, show grants... or select user, host, password from mysql. user;
========================================================== ========================================================== ===
Fault description: you cannot log on to mysql-u root-p locally with the original password, but you can log on without a password. You must log on with the original password remotely.
ERROR message: ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: YES)
Cause: unknown. I do not know if mysql has a new password control policy.
Check: the mysql-u root password is empty. No questions are asked when you log on. If you use navica mysql remotely without a password, the password is correct.
Processing: Temporarily remove the local Login Password
Version environment: mysql Ver 14.14 Distrib 5.1.56, for debian-linux-gnu (istrib) using readline 6.2 Problem Analysis (lioncode): Start --> MySQL database user information is stored in mysql. the user table contains information such as the user name, password, and authorization scope (IP addresses that can be authorized to log on, and types of database operations that can be performed, it is necessary to check the related record items of the root user in the table. The query results for the above phenomena may be as follows: if the data in the table is as follows before the problem occurs: mysql> select user, host, password from mysql. user;
+ ------- + --------------- + ------------------------------------------- +
| User | host | password |
+ ------- + --------------- + ------------------------------------------- +
| Root | localhost | * Others | root | % | * Others | + ------- + --------------- + ------------------------------------------- +
2 rows in set (0.00 sec) mysql>
After a problem occurs, the data in the table may be like this: mysql> select user, host, password from mysql. user;
+ ------- + --------------- + ------------------------------------------- +
| User | host | password |
+ ------- + --------------- + ------------------------------------------- +
| Root | localhost |
| Root | % | * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | + ------- + --------------- + --------------------------------- +
2 rows in set (0.00 sec) mysql>
The first data explains why you cannot log on locally with the original password, but you can log on successfully without a password. Originally, the password for local logon is the same as the password for remote logon. Both are * 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B (encrypted). However, for some reason, the password for local logon is cleared, therefore, you cannot log on with the original password. The second piece of data explains why the original password must be used to log on remotely. The root user's local login password setting and root user's remote login password setting are two different data, so the local login password is changed, does not affect the remote login password. <-- End
========================================================== ========================================================== =
Fault description: Unable to connect to mysql windows
ERROR message: ERROR 2003 (HY000): Can't connect to MySQL server on 'hostxxxxx' (10061)
Cause: when a large number of tcp ports and only a small number of short-lived ports are enabled at the same time, mysql may run the wrong port when they are in the time_wait period.
Check: in windows, 5000 temporary ports are enabled by default for calling, and their life is only 120 s, which means 120 s of delay will occur when the port is disabled.
Processing: reduces unnecessary tcp ports.
Modify registry
========================================================== ========================================================== =
I don't know if you have the same situation?
Lost connectiion to MySQL server during query.
According to the method described on the internet, I still haven't solved the problem. Have you ever met the landlord?
========================================================== ========================================================== =
Lost connection to MySQL server at 'reading initial communication packet ', system error: 0
What's the problem ??
Author: lioncode
CMP: JTSEC-RD
Date:
Project: jtsec_one-way_dbsync
Version: v1.0.0
