前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql的用户基本操作

Mysql的用户基本操作

作者头像
用户1173509
发布2018-03-28 12:51:03
7760
发布2018-03-28 12:51:03
举报
文章被收录于专栏:CaiRui

创建用户:

代码语言:javascript
复制
mysql> create user 'cai'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;                                                            
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| cai           | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| wordpress     | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)
代码语言:javascript
复制
[root@cairui ~]# mysql -ucai -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 275
Server version: 5.7.21-debug Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

授权:

命令:GRANT privileges ON databasename.tablename TO 'username'@'host'

代码语言:javascript
复制
mysql> grant select,delete,create,insert,update on aa.* to 'cai'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for cai@localhost;
+-----------------------------------------------------------------------------+
| Grants for cai@localhost                                                    |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cai'@'localhost'                                     |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `aa`.* TO 'cai'@'localhost' |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

撤销用户权限:

命令:REVOKE privilege ON databasename.tablename FROM 'username'@'host';

代码语言:javascript
复制
mysql> show grants for cai@localhost;
+-----------------------------------------------------------------------------+
| Grants for cai@localhost                                                    |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cai'@'localhost'                                     |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `aa`.* TO 'cai'@'localhost' |
+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> revoke insert on aa.* from 'cai'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for cai@localhost;
+---------------------------------------------------------------------+
| Grants for cai@localhost                                            |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'cai'@'localhost'                             |
| GRANT SELECT, UPDATE, DELETE, CREATE ON `aa`.* TO 'cai'@'localhost' |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec) 

设置与修改用户密码:

  方法一:

代码语言:javascript
复制
[root@cairui ~]# mysqladmin -ucai -p'123456' password '123'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.
[root@cairui ~]# mysql -ucai -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 279
Server version: 5.7.21-debug Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

  方法二:

代码语言:javascript
复制
mysql> update mysql.user set authentication_string=password('cai') where user='cai' ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
mysql> flush privileges;
[root@cairui ~]# mysql -ucai -pcai;
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 284
Server version: 5.7.21-debug Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

   方法三:

代码语言:javascript
复制
mysql> set password=password('root');
Query OK, 0 rows affected, 1 warning (0.00 sec)
[root@cairui ~]# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 288
Server version: 5.7.21-debug Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

忘记密码情况下的登录:

代码语言:javascript
复制
[root@cairui mysql]# bin/mysqld_safe --skip-grant-tables & 
#登录时不需要密码,进入可修改密码

删除用户:

代码语言:javascript
复制
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| cai           | localhost |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| wordpress     | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)

mysql> drop user 'cai'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
| wordpress     | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 创建用户:
  • 授权:
  • 撤销用户权限:
  • 设置与修改用户密码:
    •   方法一:
      •   方法二:
        •    方法三:
        • 忘记密码情况下的登录:
        • 删除用户:
        相关产品与服务
        云数据库 SQL Server
        腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
        领券
        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档