查看当前mysql中的所有用户
mysql> select user, host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| healthchecker | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
如果有很多垃圾的账户,可以执行以下类似的删除操作进行账户清理。
mysql> delete from mysql.user where user not in ('mysql.sys', 'mysql.session', 'mysql.infoschema', 'root', 'healthchecker') or host not in ('localhost');
Query OK, 0 rows affected (0.00 sec)
给localhost的root用户重设密码
mysql> set password for 'root'@'localhost' = 'helloworld';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
创建管理用户, 并为这个管理用户授予任意地址访问的所有权限(包括with grant option)
mysql> create user 'cara'@'%' identified by 'admin';
Query OK, 0 rows affected (0.02 sec)
mysql> grant all on *.* to 'cara'@'%' with grant option;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
该指令创建了cara用户,设置其密码为admin, 并允许cara从任意ip登陆,并且给予cara任意权限。
尝试登陆并查看权限
bash-4.4# mysql -ucara -padmin;