Shulou(Shulou.com)05/31 Report--

This article shows you how to solve the problem of not logging in locally and remotely on mysql. The content is concise and easy to understand, and it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The problem of not logging in locally on mysql

If you can't log in locally, that is, you forget your password, how to solve it?

First, we start mysql without checking permissions, as follows:

1/usr/local/mysql/bin/mysqld-- skip-grant-tables &

Then, you can log in directly, as follows:

1 / usr/local/mysql/bin/mysql-u root

Finally, just change the password.

1update user set authentication_string=PASSWORD ('dequan') where User='root'

Note: in mysql5.7 and later versions, the authentication_string field is used instead of the Password field.

The problem of not logging in remotely on mysql

For mysql started by checking permissions, when the client logs in to mysql as a user, the mysql server verifies that the user and the ip in which the user resides have permission to operate. Mysql can authorize operations based on user ip, database, table, operation type and other dimensions. The authorization methods include modifying the user table in the mysql library, executing mysql authorization statements, and so on.

1. Authorize the operation based on modifying the user table

If an ip is not logged in, the ip is not authorized in the User table. First, let's look at some fields in the user table.

Host: 127.0.0.1

User: root

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: Y

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

Create_tablespace_priv: Y

Ssl_type:

Ssl_cipher:

X509_issuer:

X509_subject:

Max_questions: 0

Max_updates: 0

Max_connections: 0

Max_user_connections: 0

Plugin: mysql_native_password

Authentication_string: * 89E04A681364F578C8E900403166C192A1E8E2B6

Password_expired: N

Password_last_changed: 2017-08-04 10:27:44

Password_lifetime: NULL

Account_locked: N

Where Host is the hostname or ip that allows login (if%, it represents any ip or host), User login user, authentication_string is the login password, and * _ priv is the permission to respond to the function, such as the read permission for Select_priv, the write permission for Insert_priv, the permission to update Update_priv, and the permission to delete Delete_priv.

Therefore, if you want to allow root to log in on a remote host ip1, you need to change the Host field of the corresponding login user:

Update user set host = 'ip1' where user =' root'

/ / or

Update user set host ='% 'where user =' root'

Finally, just update the permissions.

FLUSH RIVILEGES

2. Perform authorization operation based on mysql authorization statement

Of course, mysql also provides the corresponding authorization statements, as follows:

Grant all privileges on db.table to 'user'@'host' identified by' password 'with grant option

The all privileges representative grants all permissions, but of course we can only grant certain permissions. Finally, you can update the permissions.

FLUSH RIVILEGES

The above content is how to solve the problem of local and remote login in mysql. Have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.