mysql重置root密码，并设置可远程访问

linux系统：

mysqld_safe --skip-grant-tables &

mysql -u root

use mysql

UPDATE user SET host = '%' where USER = 'root';

UPDATE user SET authentication_string=password('root') where USER = 'root';

flush privileges

service mysqld restart

window 系统

mysql -u root -p

use mysql;

select host,user from user;

update user set host = ’%’ where user = ’root’;

Grant all on *.* to 'root'@'%' identified by 'root用户的密码' with grant option;

flush privileges;

windows系统创建用户、授权

drop database mybatisdb;

drop user 'hr';

create user 'hr'@'%' identified by '';

create database mybatisdb;

grant all privileges on mybatisdb.* to 'hr'@'%' identified by '';

flush privileges;

ps:Navicat远程不能连接时，删除连接，重建试试。

Mac

MYSQL 1251错误解决：

Create user 'hr2'@'%' identified by '';

Grant all privileges on *.* to 'hr2'@'%' with grant option;

Alter user 'hr2'@'%' identified by '' password expire never;

Alter user 'hr2'@'%' identified with mysql_native_password by '';
Flush privileges;

MYSQL 1227错误解决：

ERROR  (): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation

原因：查看完MySQL8..16官方文档后得知，SYSTEM_USER是mysql新权限，并且在root用户下创建新用户xx时会授予SYSTEM_USER权限，而自己却没有SYSTEM_USER权限。

# xx用户下操作

mysql> grant system_user on *.* to root;

Query OK,  rows affected (0.06 sec)

# root用户下的操作

mysql> revoke all privileges on *.* from xiaokang;

Query OK,  rows affected (0.02 sec)