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

This article mainly explains the "how to reset mysql root password and set mysql remote login permissions", the content of the article is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "how to reset mysql root password and set mysql remote login permissions" bar!

The root password is forgotten. Reset the root password of mysql: t

Modify the configuration file my.cnf of mysql

1. Add a sentence to the paragraph of [mysqld]: skip-grant-tables

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip-name-resolve

Skip-grant-tables

Save and exit vi.

(or execute mysqld_safe-- skip-grant-tables &)

two。 Restart mysqld

# / etc/init.d/mysqld restart

Stopping MySQL: [OK]

Starting MySQL: [OK]

3. Log in and change the root password of MySQL

# / usr/bin/mysql

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

Your MySQL connection id is 3 to server version: 3.23.56

Type 'help;' or'\ h' for help. Type'\ c'to clear the buffer.

Mysql > use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > update user set authentication_string = password ('root'), password_expired =' root', password_last_changed = now () where user = 'root'

Query OK, 0 rows affected (0.00 sec)

Rows matched: 2 Changed: 0 Warnings: 0

Mysql > flush privileges

Query OK, 0 rows affected (0.01 sec)

Mysql > quit

Bye

4. Change the login settings of MySQL back

# vi / etc/my.cnf

Delete the skip-grant-tables just added in the paragraph of [mysqld]

Save and exit vi.

5. Restart mysqld

# / etc/init.d/mysqld restart

Stopping MySQL: [OK]

Starting MySQL: [OK]

II.

Stop the mysql service

/ etc/init.d/mysqld stop

Sudo mysqld_safe-skip-grant-table&

Mysql

Use mysql

Update user set password = password ('yourpasswd') where user =' root'

Flush privileges

Restart the mysql service and log in with the new password

/ etc/init.d/mysqld restart

Allow remote connection to mysql

This error occurs when connecting to MySQL through navicat

ERROR 1130: Host *. * is not allowed to connect to this MySQL server

Indicates that the connected user account does not have the permission to connect remotely and can only log in on the local machine (localhost).

You need to change the host entry in the user table in the MySQL database

Rename localhost%

I.

Log in to MySQL, first use MySQL

An error occurred when update in the way provided by others.

MySQL > update user set host='%' where user = 'root'

ERROR 1062 (23000): Duplicate entry'%-root' for key 'PRIMARY'

Then check the host information of the following database as follows:

MySQL > select host from user where user = 'root'

+-- +

| | host |

+-- +

| |% |

| | 127.0.0.1 | |

| | localhost.localdomain |

+-- +

3 rows in set (0.00 sec)

Host already has the% value, so run the command directly:

MySQL > flush privileges

II.

Mysql > grant all privileges on *. * to 'root'@'%' with grant option

Query OK, 0 rows affected (0.02 sec)

Mysql > flush privileges

Query OK, 0 rows affected (0.00 sec)

Mysql > selectuser.host from user

+-+ +

| | user | host |

+-+ +

| | root |% | |

| | root | 127.0.0.1 | |

| | repl_user | 192.168.1.52 | |

+-+ +

3 rows in set (0.00 sec)

Allow specified IP to access mysql

Mysql > grant all privileges on *. * to 'root'@116.224.126.250 identified by' 123456'

QueryOK, 0 rows affected (0.00 sec)

Thank you for reading, the above is "how to reset mysql's root password and set mysql remote login permissions" content, after the study of this article, I believe you on how to reset mysql's root password and set mysql remote login permissions this problem has a deeper understanding, the specific use of the situation also needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.