Shulou(Shulou.com)06/01 Report--

Today, I talked with my colleagues about technology. When I talked about some high-end systems and designs in BAT, there will always be some gaps, but there is a deep accumulation of knowledge in a company like that, so it is also a kind of progress and improvement to be able to do what we can and refine it.

If you always feel that your environment is limited, there are various KPI or cost considerations, and it is difficult to do things from the bottom up, these are practical difficulties, and many companies have such problems. In terms of resource constraints, I am particularly troubled. To take an interesting example, if I receive an alarm indicating that the database table space is insufficient, then add a data file, as a result, the space problem at the database level is solved. and will immediately receive an alarm of insufficient system space, encounter this situation, your own experience, the mood must be very complicated.

The case I encountered today is quite special, it is about MySQL login, the database environment is version 5.6.

> select version ()

+-+

| | version () |

+-+

| | 5.6.23-72.1-log |

+-+

1 row in set (0.1 sec) Today my colleague asked me a question to show me the privileges of a database user. After I logged in to the server, everything went well.

# mysql

Logging to file'/ home/mysql/query.log'

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 52625

Server version: 5.6.23-72.1-log Percona Server (GPL), Release 72.1, Revision 0503478 everything is fine, and then I'm going to take a look at the threads connecting to the database.

> show processlist

ERROR 1227 (42000): Access denied; you need (at least one of) the PROCESS privilege (s) for this operation throws this strange error and is banned if you want to check the information in the data dictionary.

> select user,host from mysql.user

ERROR 1142 (42000): SELECT command denied to user''@ 'localhost' for table' user' is so tangled at this time that my root users can no longer log in to MySQL, let alone troubleshooting for colleagues.

With doubt, I checked error log and found nothing unusual about it.

This question how to continue to go down, if you want to make changes, it will not affect the existing test users, even though it is the test environment, restart the service and so on, you still have to fully communicate with the developer students before you can do it. moreover, I am helping to check the environment, and I can't change it casually.

When I was a little anxious about this problem, I thought that I had really left a back door for myself, that is, when I helped them deal with the problem, I set up a user on my server to test the connection to the database. I didn't expect that such an inadvertent move would become the last key to analyze the problem.

Soon, I logged in to this MySQL server from a security-certified central client, and there were more than 100 connections. While sighing his wisdom, he quickly analyzed the problem.

There are about 10 database users in this database, which is roughly like this, which has been modified.

> select user,host from mysql.user

+-- +

| | user | host |

+-- +

| | cloud_test |% | |

| | cloudcs_app |% | |

| | root |% | |

| | cloud_test | 10.127.138.107 |

| | root | 10.127.138.107 |

| | localhost |

| | jeanron | test_user% |

+-+-+ check the information of show process and see that the user is root.

| | 52629 | root | localhost |

+-the permission to view root@localhost is not available.

> show grants for root@'localhost'

ERROR 1141 (42000): There is no such grant defined for user 'root' on host' localhost' Let's stop at this time. In this scenario, is the system mysql command directly connected to root@localhost? Not from the error log, but from the thread information, so we need to take a closer look at what the problem is.

Although the server directly mysql command login, can not check the thread situation, can not check the data dictionary, but the show grants command is OK.

> show grants

+

| | Grants for @ localhost |

+

| GRANT USAGE ON *. * TO'@ 'localhost' can see that the logged-in user is' @ 'localhost', instead of root@'localhost',. There are no root@'localhost' users configured in this environment.

Then if you look at mysql.user again, you will find that the following configuration is quite special. Root uses a wide range of domain names, allowing different IP to access, while another record is the specified IP.

| | root |% | |

| | root | 10.127.138.107 |

| | localhost | what does this mean, that is, the effect of using root@localhost is similar to that of root@'%'. This'@ localhost is currently the default connection method, and it needs to be said that it is preferred under this configuration.

After we initialize a mysql environment, the general content of mysql.user is like this, such as a 5.7 environment.

Mysql > select user,host from mysql.user

+-+ +

| | user | host |

+-+ +

| | mysql.sys | localhost |

| | root | localhost |

+-+ +

2 rows in set (0.00 sec) the default connection method is root@'localhost'

In the above scenario, without the configuration of root@'localhost', the user'@ 'localhost' is preferred.

Why is there such a problem? after communicating with the developer, I analyzed the location and found that the IP of this server had changed before. Later, the development students themselves also made some modifications and configuration, which is now the situation.

How to fix this situation? my idea is to delete anonymous users, the server does not enable passwords, that is, root@'localhost', and client connections use domain name resolution, but root permissions are not open to developers, so we delete root@'%' users.

Delete anonymous user'@ 'localhost'

> drop user'@ localhost

Delete the highest privileged root user and do not provide arbitrary permission access to the outside world.

Drop user root@'%'; modifies the server configuration where the IP has changed to localhost

> set the password to empty with update mysql.user set host='localhost' where user='root' and host='10.127.138.107';, and use flush privileges at last.

In this way, we have achieved our desired effect by logging in using mysql.

> show grants

+--

| | Grants for root@localhost |

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.