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

linux系统：

ps aux |grep mysql

kill -9 所有相关进程

mysqld_safe --skip-grant-tables &

mysql -u root

use mysql

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

UPDATE user SET password=PASSWORD("123456") where USER = 'root';

select user, host from user;

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;

drop database mybatisdb;
drop user 'hr';
create user 'hr'@'%' identified by '123456';
create database mybatisdb;
grant all privileges on mybatisdb.* to 'hr'@'%' identified by '123456';
flush privileges;

 

 

Mac

MYSQL 1251错误解决：
Create user 'hr2'@'%' identified by '123456';
Grant all privileges on *.* to 'hr2'@'%' with grant option;
Alter user 'hr2'@'%' identified by '123456' password expire never;
Alter user 'hr2'@'%' identified with mysql_native_password by '123456';
Flush privileges;

MYSQL 1227错误解决：
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
原因：查看完MySQL8.0.16官方文档后得知，SYSTEM_USER是mysql新权限，并且在root用户下创建新用户xx时会授予SYSTEM_USER权限，而自己却没有SYSTEM_USER权限。

# xx用户下操作
mysql> grant system_user on *.* to root;
Query OK, 0 rows affected (0.06 sec)


# root用户下的操作
mysql> revoke all privileges on *.* from xiaokang;
Query OK, 0 rows affected (0.02 sec)