MySQL创建数据库和创建用户以及授权_mysql创建数据库和用户-优快云博客

本文链接：https://blog.youkuaiyun.com/zhangxitab/article/details/80534605

本文介绍如何使用MySQL数据库管理系统来管理用户账号，包括查看用户信息、创建数据库和用户、分配及撤销用户权限等操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.通过mysql数据库的user表查看用户相关信息

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user from user;
+-----------+------+-------------------------------------------+
| host      | user |
+-----------+------+-------------------------------------------+
| localhost | root |
| localhost |      |
| test      |      |
+-----------+------+-------------------------------------------+

2.创建数据库

mysql> create database [databasename] default character set utf8 collate utf8_general_ci;

3.创建用户

mysql> create user 'userName'@'%' identified by 'passwr';

user表中host列的值的意义

% 匹配所有主机

localhost localhost不会被解析成IP地址，直接通过UNIXsocket连接

127.0.0.1 会通过TCP/IP协议连接，并且只能在本机访问；

::1 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1

4.对userName用户给予针对指定数据库进行增删改查的权限

mysql> grant select,insert,update,delete,create on [databasename].* to 'userName';
flush privileges;

注意：修改完权限以后一定要刷新服务，或者重启服务，刷新服务用：FLUSH PRIVILEGES

5.通过show grants命令查看权限，若想要在原来的基础上增加权限则继续执行grant

mysql> grant drop on [databasename].* to 'userName';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'userName';
+----------------------------------------------------------------------------------------------------+
| Grants for 'userName'@% |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'userName'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `[databasename]`.* TO 'userName'@'%' |

+----------------------------------------------------------------------------------------------------+

6.通过revoke命令移除用户的相关权限

mysql> revoke drop on [databasename].* from 'userName';

mysql> show grants for 'userName';
+----------------------------------------------------------------------------------------------------+
| Grants for 'userName'@% |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'userName'@'%' IDENTIFIED BY PASSWORD '*****************************************' |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `[databasename]`.* TO 'userName'@'%' |
+----------------------------------------------------------------------------------------------------+