1. 查看mysql用户
select User,Host,authentication_string from mysql.user;
+------+--------------+-----------------------+
| User | Host | authentication_string |
+------+--------------+-----------------------+
| root | localhost | |
| root | b7d0cf7b6e2f | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | NULL |
| | b7d0cf7b6e2f | NULL |
+------+--------------+-----------------------+
可以先查看user表中的字段,select自己想要的字段信息,一般有user,host,password(有些版本是authentication_string)
2. 增加用户
create user ‘新用户名‘@‘localhost‘ identified by ‘密码‘;
# 允许所有ip连接
create user ‘新用户名‘@‘%‘ identified by ‘密码‘;
3. 给新用户赋予权限
# 基本格式如下
grant all privileges on 数据库名.表名 to ‘新用户名‘@‘指定ip‘ identified by ‘新用户密码‘ ;
# 允许访问所有数据库下的所有表
grant all privileges on *.* to ‘新用户名‘@‘指定ip‘ identified by ‘新用户密码‘ ;
# 指定数据库下的指定表
grant all privileges on test.test to ‘新用户名‘@‘指定ip‘ identified by ‘新用户密码‘ ;
4. 删除用户
DROP USER username@localhost;
5. 修改用户权限
#设置用户拥有所有权限也就是管理员
grant all privileges on *.* to ‘用户名‘@‘指定ip‘ identified by ‘用户密码‘ WITH GRANT OPTION;
#拥有查询权限
grant select on *.* to ‘用户名‘@‘指定ip‘ identified by ‘用户密码‘ WITH GRANT OPTION;
#其它操作权限说明,select查询 insert插入 delete删除 update修改
#设置用户拥有查询插入的权限
grant select,insert on *.* to ‘用户名‘@‘指定ip‘ identified by ‘用户密码‘ WITH GRANT OPTION;
#取消用户查询的查询权限
REVOKE select ON what FROM ‘用户名‘;
6. 修改后刷新权限
flush privileges;
7. mysql启动命令及连接
# 启动mysql
mysqld
或
mysqld --initialize-insecure
service mysql start
# 连接mysql
mysql -uroot -p