创建用户

5.7+版本中不能使用 insert into mysql.user 的方式创建用户

  1. CREATE USER 'test'@'localhost'  IDENTIFIED BY '1234'; #本地登录 
  2. CREATE USER 'test'@'%'  IDENTIFIED BY '1234';         #远程登录

授权

  1. # 创建数据库
  2. create database testDB; 
  3. create database testDB default charset utf8 collate utf8_general_ci;
  5. # 全部授权
  6. grant all privileges on testDB.* to "test"@"%" identified by '1234'; 
  8. # MySQL8.0+
  9. grant all privileges on testDB.* to "test"@"%" with grant option; 
  11. #刷新系统权限表
  12. flush privileges;     
  14. # 部分授权
  15. grant select,update on testDB.* to "test"@"localhost" identified by '1234';

删除用户

  1. DELETE FROM mysql.user WHERE User='test' and Host='localhost'; 
  2. flush privileges; 
  3. drop database testDB;
  5. drop user 'test'@'%'; 
  6. drop user 'test'@'localhost';

修改密码

5.7+版本中不再有 Password 字段，取而代之的是 authentication_string 

  1. update mysql.user set authentication_string=password(“新密码”) where User=”test” and Host=”localhost”;