MySQL5.5.51 Enabling network remote connections

When accessed on other computer hosts, the host IP is not a allowed to connect to this MySQL

The following code is the way to fix the problem:

: \program Files\mysql-5.5. Wuyi\bin>mysql-u root-p******

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 1

Server Version:5.5.51-log MySQL Community Server (GPL)

Copyright (c), +, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> use MySQL;

Database changed

mysql> Select Host,user,password from user;

+-----------+------+-------------------------------------------+

| Host | user | password |

+-----------+------+-------------------------------------------+

| localhost | Root | *6bb4837eb74329105ee4568dda7dc67ed2ca2ad9 |

| 127.0.0.1 |                                           Root | |

| :: 1 |                                           Root | |

|      localhost |                                           | |

+-----------+------+-------------------------------------------+

4 rows in Set (0.00 sec)

mysql> Update user set host= '% ' where user= ' root ';

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

mysql> Update user set host= '% ' where user= ' root ';

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

mysql> Select Host,user from user where user= ' root ';

+-----------+------+

| Host | user |

+-----------+------+

| %        | Root |

| 127.0.0.1 | Root |

| :: 1 | Root |

+-----------+------+

3 Rows in Set (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.02 sec)

Mysql> quit;

Bye

D:\Program files\mysql-5.5.51\bin>net stop MySQL

The MySQL service is stopping.

The MySQL service has stopped successfully.

D:\Program files\mysql-5.5.51\bin>net start MySQL

The MySQL service is starting.

The MySQL service has started successfully.

D:\Program files\mysql-5.5.51\bin>mysql-u root-p

Enter Password: ******

ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using password:y

ES)

D:\Program files\mysql-5.5.51\bin>mysql-u root-p

Enter Password:

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 3

Server Version:5.5.51-log MySQL Community Server (GPL)

Copyright (c), +, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

Mysql> exit;

Bye

D:\Program files\mysql-5.5.51\bin>mysqladmin-uroot password 123456

D:\Program files\mysql-5.5.51\bin>mysql-uroot-p123456

Welcome to the MySQL Monitor. Commands End With; or \g.

Your MySQL Connection ID is 6

Server Version:5.5.51-log MySQL Community Server (GPL)

Copyright (c), +, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its

Affiliates. Other names trademarks of their respective

Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> Grant all privileges on * * to ' root ' @ '% ' identified by ' 123456 ' with GRANT option;

Query OK, 0 rows Affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows Affected (0.00 sec)

Explain the issues that are encountered above:

1. First query whether to open remote access (can let any IP through the root user access)

2. No open, then modify host= '% ', will error: Error 1062 (23000): Duplicateentry '%-root ' for key ' PRIMARY '

3. Ignore the second question and flush privileges; (Refresh MySQL system permissions related table, otherwise error: Access denied)

4. Re-login via command error: Error 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using password:yes), because the password becomes empty, Reset Password: mysqladmin-uroot password 123456

5. This time access or error: Host IP is not allowed to connect to this MySQL. It can be resolved with the following two commands, grant all privileges on * * to ' root ' @ '% ' identified by ' 123456 ' with grant Option;flush privileges;

Reference: https://jingyan.baidu.com/article/60ccbceb05804164cab1978c.html

MySQL5.5.51 Enabling network remote connections