Microsoft Windows [版本 10.0.14393]
(c) 2016 Microsoft Corporation。保留所有权利。
C:\Users\Administrator>mysql -u root -P 3916 -p 指定端口账号登录
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.25 MySQL Community Server - GPL
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be 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 User,Password,Host from user;
ERROR 1054 (42S22): Unknown column ‘Password‘ in ‘field list‘
mysql> select User,Host from user; //查看用户数据
+------------------+-----------+
| User | Host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
mysql> grant all privileges on *.* to ‘root‘@‘%‘ identified by ‘root‘ with grant option;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘identified by ‘root‘ with grant option‘ at line 1
mysql> update user set host = ‘%‘ where user = ‘root‘;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> grant all privileges on *.* to root@‘%‘ identified by "Sz321654"; //8.x以前版本写法
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘identified by "Sz321654"‘ at line 1
mysql> update user set host=‘%‘ where user=‘root‘; //先修改user表中的root的host字段数据
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> grant all on *.* to ‘root‘@‘%‘; //给root用户权限
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> flush privileges;//刷新权限缓存
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)
mysql> ALTER USER ‘root‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘root‘;//修改为mysql_native_password访问方式 因为在native访问的加密方式不一样
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,plugin from mysql.user;//查看修改之后
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)
mysql> alter user ‘root‘@‘%‘ identified with mysql_native_password by ‘你的密码‘;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host | user | plugin |
+-----------+------------------+-----------------------+
| % | root | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session | caching_sha2_password |
| localhost | mysql.sys | caching_sha2_password |
+-----------+------------------+-----------------------+
4 rows in set (0.00 sec)
mysql>