Problems after setting the root user of mysql to be accessible from the external network

Question

Modify the root user to be able to log in when not local

mysql> use mysql;
Database changed
mysql> select host,user from user;
+-----------+------+
| host      | user |
+-----------+------+
| 127.0.0.1 | root |
| ::1       | root |
| localhost | root |
+-----------+------+
3 rows in set (0.00 sec)

mysql> update user set host='%' where user='root';
ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit;
Bye

After completing the above steps, I encountered a problem:
When I am local, mysql -uroot and press Enter, after pressing Enter, I can log in directly

But I can’t log in via password:

C:\wamp\mysql\bin>mysql -uroot -p
Enter password: *********
ERROR 1045 (28000): Access denied for user 'root'@'localhost' 
(using password: YES)

C:\wamp\mysql\bin>

why is that?

Answer 1

localhost is not in %
mysql -uroot -h127.0.0.1 -p
and
mysql -uroot -p
are equivalent to logging in with 2 users

update user set host='%' where user='root';
And if you change it like this, it is equivalent to modifying the above three records, so the system prompts you "ERROR 1062 (23000): Duplicate entry '%-root' ' for key 'PRIMARY' is wrong because the Host and User fields are composite primary keys and are not unique.

So, LZ’s execution of this statement is actually unsuccessful. So there is no need to look further from this step.