mysql 8.x 开启远程访问和修改root密码、

当前安装是基于mysql 8.0.25 直接安装msi的

https://dev.mysql.com/downloads/windows/installer/8.0.html

 

windows环境中 在安装mysql或者启动不了mysql服务时，切记

右击此电脑–>管理–>本地用户和组–>组–>双击Administrators–>添加–>高级
把NETWORK SERVICE添加到Administrators组

 

 

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>

 

 

 

实在搞不懂，mysql这种垃圾软件为什么还有这么多人用，8.x之后的命令也有很多变化

每一次在windows中安装mysql就没有不出问题，不是权限就是安装问题，狗屎！！！

同样安装使用mssql和mysql十几年了，个人对mysql一点好感都没有，

好吧对金钱低头！！！不得不使用！！！，狗屎粑粑mysql