Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL Tutorial: Philosophical Reflections on the unauthenticated user     - Swift string common method (Programming)

- CentOS yum install LAMP (Server)

- Linux group account deletion, and other related operations (Linux)

- JBPM6 Installation and Configuration Tutorial (Linux)

- Open container cluster management system architecture and components introduced Kubernetes (Server)

- Linux server disk expansion and Oracle tablespace file migration operations (Database)

- Debugging with GDB tool Go (Programming)

- CentOS 6.5 installation and configuration Cobbler (Server)

- Oracle 11g maintenance partitions - Adding Partitions (Database)

- DupeGuru- find and remove duplicate files (Linux)

- Oracle Data File Management (Database)

- Git Experience Sharing - Using a remote repository (Linux)

- Ubuntu 14.04 set auto sleep time (Linux)

- Fedora 21 setting boot script (Linux)

- Several reasons MySQL garbled (Database)

- The Samba service does not have permission to access (Server)

- Linux Beginner Guide: Installing packages on Ubuntu and Fedora (Linux)

- Linux System Getting Started Learning: Disable Ubuntu Apport internal error reporting procedures (Linux)

- and localhost difference (Server)

- Android Application Development: an argument between Activity (Programming)

  MySQL Tutorial: Philosophical Reflections on the unauthenticated user
  Add Date : 2017-08-31      
  First, the phenomenon

In a production environment we occasionally encounter such a connection "unauthenticated user", accompanied by the database server level load, higher sys cpu, or thread running abnormal.

+ ----- + ---------------------- + -------------------- + --------- + ------ + ------ + ------- + ----------------- - +
| Id | User | Host | db | Command | Time | State | Info |
+ ----- + ---------------------- + -------------------- + --------- + ------ + ------ + ------- + ----------------- - +
| 235 | unauthenticated user | | NULL | Connect | NULL | login | NULL |
| 236 | unauthenticated user | | NULL | Connect | NULL | login | NULL |
| 237 | user | localhost | NULL | Query | 0 | NULL | show processlist |
+ ----- + ---------------------- + -------------------- + --------- + ------ + ------ + ------- + ----------------- - +
Second, the analysis

Similar to the campus guard see the famous question when a stranger enters submitted, you will enter the philosophical mode: TA who come to do??? From the perspective of IT technology to answer this philosophical question

TA is the Who?

Official description: unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet been done.

Means: MySQL has a connecting thread processing client, but the client is not verified by the user to display the "unauthenticated user" when the show processlist.

Come from?

Before answering this question, we first understand the client and MySQL establishment (within the socket is not in scope) TCP connection process, the general client establishes a connection with the MySQL four steps:

1. The client sends packets to the MySQL server, ready to establish a connection. If the instance of MySQL server is not running the corresponding port will directly return an error:

ERROR 2003 (HY000): Can not connect to MySQL server on '[host]' (111)

2. MySQL server to the client in response to the basic information database server ip, port, mysqld version, the thread id, the client's host, port, and so on, but this time the connection has been established yet to complete the authorization,

"When a new client connects to mysqld, mysqld spawns a new thread to handle the request This thread first checks whether the host name is in the host name cache If not, the thread attempts to resolve the host name..:

The thread takes the IP address and resolves it to a host name (using gethostbyaddr ()). It then takes that host name and resolves it back to the IP address (using gethostbyname ()) and compares to ensure it is the original IP address . "

The actual connection process mysql assign a new thread to handle the connection request from the client. Check whether the client hostname in the cache, if not resolve the hostname to resolve. First reverse analysis for the client IP ---> client hostname, and then make the client hostname ---> positive resolve client IP . If the results meet, verify that legitimate users allowed to log in. If you do not meet the definition of "unauthenticated user".

3. The client sends a username / password / access to the database server dbname to MySQ. If for some reason the client within the specified time connect_timeout not send packages or send an error packet, the database server disconnect the connection.

4. Verify that the server and verify the results returned to the client. If the validation does not pass normally returns:

ERROR 1045 (28000): Access denied for user 'user' @ 'host' (using password: [YES / NO])

ok, so far, we can know TA from the client and MySQL server establishing a second phase of the connection process.

What to do?

Obviously ah ready access to the database to retrieve data from or write data.

How to avoid such a three non-officers it? From the cause of the problem analysis, we introduce the solution is as follows:

a In the /etc/my.cnf [mysqld] add the skip-name-resolve parameters, close the mysql anti dns query, mysql using IP or authorized%

b in the / etc / hosts to add the host name and IP correspondence xxxx

However in our production environment has been configured skip-name-resolve, still a large number of unauthenticated user information indicates that the MySQL server is not enabled for client connection requests confirmation voucher, that MySQL can not confirm these connections use the database account information, within wait_timeout of time MySQL has been waiting for these connections is completed.

For example, I executed on a machine

MySQL is executed on the target machine is displayed as unauthenticated user show processlist

Therefore, this phenomenon is not necessarily the database itself, the following are likely to produce this phenomenon

1. If a large number of database application security problems detected, such a large number of unauthorized connections appear.

2. The application services appeared excessive pressure led to a number thread abort exception database connection.

3. Apps abnormal, leading to a large number of abnormal database connection.

4.MySQL client connection version of the problem, is not compatible authentication protocols, with particular attention to old-password authentication.

5. The database server thread is queued, it can increase the back_log, increasing the ability to handle MySQL connection requests.

The first three from the application server to view a large number of unauthenticated user when the load on the system or application state, the latter two from the database server level to check the status of the system.
- CentOS7 Kubernetes used on container management (Server)
- The most concise explanation of JavaScript closures (Programming)
- Python3 multi-thread download codes (Programming)
- Oracle 11g RAC manually playing GI PSU patch ( (Database)
- Subquery Oracle study notes (Database)
- How to install Zephyr Test Management Tools on CentOS 7.x (Server)
- Ubuntu and Derivative Edition users install LMMS 0.4.15 (Linux)
- SSH automatic disconnection problem solving (Linux)
- Java multi-threaded in a three way (inheritance, implementation, anonymous inner classes) (Programming)
- Linux 101 hack book reading notes (Linux)
- Thrift 0.9.3 compiler installation under Ubuntu (Linux)
- Java 8 stream parsed into SQL (Programming)
- Linux system Iptables Firewall User Manual (Linux)
- Bash common several configuration files (Linux)
- Installation Experience open source car Automotive Grade Linux system (Linux)
- CentOS 6.6 command-line automatic completion (Linux)
- SecureCRT session buffer size settings (Linux)
- MySQL use benchmarking tool sysbench (Database)
- CentOS 6 Install Linux kernel source (Linux)
- Java collections series (Programming)
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.