无须死记硬背,直接 copy 就好。


1. 查看目前 mysql 用户

select user,host,password from mysql.user;

2. 修改 root 密码(使用内置函数修改)

set password for root@localhost=password('your new password');

或者

set password for [email protected]=password('your new password');

3. 退出 mysql

exit

4. 重新登录(需要输入密码)

mysql -u root -p

5. 删除匿名用户

  • 查看是否有匿名用户
select user,host from mysql.user;
  • 删除匿名用户(user='', ''表示空串)
delect from mysql.user where user = '';
  • 刷新,使操作生效
flush privileges;

6. 添加 mysql 新用户

insert into mysql.user(Host,User,Password) values("localhost", "yourusername", password("yourpaddword"));
  • 刷新,使操作生效
flush privileges;

7. 创建新的database

CREATE DATABASE `db_test` DEFAULT CHARRACTER SET utf8 COLLATE utf8_general_ci;

8. 给本地用户赋予所有权限

grant all privileges on db_test.* to yourusername@localhost identified by 'yourpassword';

9. 给账号开通外网所有权限

grant all privileges on db_test.* to 'yourusername'@'%' identified by 'yourpassword';
  • 这里需要根据自己所需要的权限给予权限,例如把db_test数据库下所有的表增改查权限(不给删除权限)给到 192.168.199.111 主机,写法如下:
grant select,insert,update  on db_test.* to yourusername@'192.168.199.111' identified by 'yourpassword';

12-09 13:22