MySQL（管理员常用命令）

最新推荐文章于 2025-02-10 10:55:58 发布

缘友一世

最新推荐文章于 2025-02-10 10:55:58 发布

阅读量4.3k

点赞数 4

分类专栏：数据库基础篇文章标签：笔记基础知识 mysql 数据库管理员操作命令

本文链接：https://blog.csdn.net/yang2330648064/article/details/123793380

版权

数据库基础篇专栏收录该内容

48 篇文章

订阅专栏

文章目录

✨一创建用户
✨二授权
- ✨ 授权原则说明
✨二查看用户权限
✨ 四设置和更改用户密码
✨ 五撤销用户权限
✨ 六删除用户
✨[拓展(点击跳转)](https://blog.csdn.net/vkingnew/article/details/81742262)

attention：MySQL8.0中无法在创建用户时，直接授予权限。
必须先创建用户，再给与权限

✨一创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

说明：username：创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
‘%’ ：哪台主机上可以登录mysql，%是通配符指的是任意IP，也可以指定具体的IP，或者localhost代表本机才可以登录。
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器例子：

CREATE USER 'niuben'@'localhost' IDENTIFIED BY '123456';

CREATE USER 'niuben'@'192.168.1.101_' IDENDIFIED BY '123456';
 
CREATE USER 'niuben'@'%' IDENTIFIED BY '123456';
 
CREATE USER 'niuben'@'%' IDENTIFIED BY '';
 
CREATE USER 'niuben'@'%';

例如：

mysql> create user 'u1'@'%' identified by 'dir99';
Query OK, 0 rows affected (0.01 sec)

✨二授权

GRANT privileges ON databasename.tablename TO 'username'@'host' [with grant option];

grant命令说明：
• 💦priveleges (权限列表)，可以是all，表⽰所有权限，也可以是select、update等权限，多个权限之间⽤逗号分开。
• 💦ON ⽤来指定权限针对哪些库和表，格式为数据库.表名，点号前⾯⽤来指定数据库名，点号后⾯⽤来指定表名，. 表⽰所有数据库所有表。
• 💦TO 表⽰将权限赋予某个⽤户, 格式为username@host，@前⾯为⽤户名，@后⾯接限制的主机，可以是IP、IP段、域名以及%，%表⽰任何地⽅。
• 💦WITH GRANT OPTION 这个选项表⽰该⽤户可以将⾃⼰拥有的权限授权给别⼈。注意：经常有⼈在创建操作⽤户的时候不指定WITH GRANT OPTION选项导致后来该⽤户不能使⽤GRANT命令创建⽤户或者给其它⽤户授权。备注：可以使⽤GRANT重复给⽤户添加权限，权限叠加，⽐如你先给⽤户添加⼀个select权限，然后又给⽤户添加⼀个insert权限，那么该⽤户就同时拥有了select和insert权限。

GRANT SELECT, INSERT ON test.user TO 'u2'@'%';
 
GRANT ALL ON *.* TO 'u3'@'%';
 
GRANT ALL ON mysc0530.* TO 'u4'@'%';

例如：
不规范的语法：

mysql> grant privileges on mysc0530.* to u1;
ERROR 3619 (HY000): Illegal privilege level specified for PRIVILEGES

规范的语法：

mysql> grant all privileges on mysc0530.* to u1;
Query OK, 0 rows affected (0.01 sec)

注意：
用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

mysql> grant all privileges on mysc0530.* to u1 with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql> create user 'u3'@'%' identified by 'dir99';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on mysc0530.* to 'u3'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql>

✨ 授权原则说明

• 只授予能满⾜需要的最⼩权限，防⽌⽤户⼲坏事，⽐如⽤户只是需要查询，那就只给
select权限就可以了，不要给⽤户赋予update、insert或者delete权限
• 创建⽤户的时候限制⽤户的登录主机，⼀般是限制成指定IP或者内⽹IP段
• 初始化数据库的时候删除没有密码的⽤户，安装完数据库的时候会⾃动创建⼀些⽤户，这些⽤户默认没有密码
• 为每个⽤户设置满⾜密码复杂度的密码
• 定期清理不需要的⽤户，回收权限或者删除⽤户

✨二查看用户权限

show grants for 'root'@'localhost';

显示权限：

mysql> show grants for u1;
+--------------------------------------------------+
| Grants for u1@%                                  |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%`                   |
| GRANT ALL PRIVILEGES ON `mysc0530`.* TO `u1`@`%` |
+--------------------------------------------------+
2 rows in set (0.00 sec)

show grants;
查看当前⽤户的权限

SHOW GRANTS;

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION

GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION

GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION

✨ 四设置和更改用户密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

方法一：直接更新

alter user '用户名'@'主机范围' identified by '新密码';

测试如下：

mysql> revoke all privileges on mysc0530.* from 'u1'@'%';
Query OK, 0 rows affected (0.01 sec)

方法二：通过修改mysql.user表修改密码
mysql8.0中没有password加密函数，可以用sha\sha1来进行密码的加密

use mysql;
update user set authentication_string = sha1('321') where user = 
'test1' and host = '%';
flush privileges;

经测试，没毛病：

mysql> UPDATE user SET authentication_string=sha1("dir999") WHERE user='u1';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

✨ 五撤销用户权限

REVOKE privileges ON databasename.tablename FROM 'username'@'host';
说明：privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL。
如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.*

例如：

mysql> alter user 'u1'@'%' identified by 'dir999';
Query OK, 0 rows affected (0.01 sec)

✨ 六删除用户

方法一：

drop user if exists username;

drop的⽅式删除⽤户之后，⽤户下次登录就会起效。
方法二：

delete from user where user='⽤户名' and host='主机';
flush privileges;

注意通过表的⽅式删除的，需要调⽤flush privileges;刷新权限信息（权限启动的时候在内存中保存着，通过表的⽅式修改之后需要刷新⼀下）。

测试如下：

mysql> delete from user where user='u3';
Query OK, 1 row affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

✨拓展(点击跳转)

【以下内容转载于他人】

MySQL的加密和解密函数个人分为两类：

1.只支持正向加密不支持反向解密的函数：
MD5()、SHA1()|SHA()、SHA2()
针对此类加密的算法MySQL不提供解密，但是我们可以通过网站
http://www.cmd5.com/ 反向解密。
改网站支持MD5、SHA、SHA256、SHA512等解密。

2.支持加密和解密的函数：
COMPRESS(),UNCOMPRESS()：普通的加密和解密算法。
ENCODE(),DECODE():普通的加密和解密算法
DES_ENCRYPT(),DES_DECRYPT()：支持DES算法的加密和解密。
AES_DECRYPT(),AES_ENCRYPT()：支持AES算法的加密和解密。
ASYMMETRIC_ENCRYPT(),ASYMMETRIC_DECRYPT()：签名加密和解密。
STATEMENT_DIGEST(),STATEMENT_DIGEST_TEXT():针对语句进行hash算法和反向解析。

3.第三类：
3.1
ASYMMETRIC_DERIVE()
ASYMMETRIC_SIGN()
ASYMMETRIC_VERIFY()
CREATE_ASYMMETRIC_PRIV_KEY() 创建私钥
CREATE_ASYMMETRIC_PUB_KEY()  创建公钥


3.2
CREATE_DH_PARAMETERS() 生成DH秘钥
CREATE_DIGEST()        字符串digest
PASSWORD()           返回加密的密码，此函数在MySQL8.0被移除。
RANDOM_BYTES()       生成随机向量值。
UNCOMPRESSED_LENGTH()  返回加密前的字符串长度。
VALIDATE_PASSWORD_STRENGTH() 密码强度的验证值。

上述加密算法中的 MD5(), SHA(), SHA1(), SHA2(),STATEMENT_DIGEST(), STATEMENT_DIGEST_TEXT().返回的是ASCII字符，字符由参数character_set_connection和collation_connection决定。当设置为binary时则不受影响。
```sql
mysql> show variables like '%connection';
+---------------------------+--------------------+
| Variable_name             | Value              |
+---------------------------+--------------------+
| character_set_connection  | utf8mb4            |
| collation_connection      | utf8mb4_unicode_ci |
| default_master_connection |                    |
+---------------------------+--------------------+
3 rows in set (0.001 sec)

示例:
1.MD5加密

mysql> select md5('123456') MD5;
+----------------------------------+
| MD5                              |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)

MD5返回的字符串是32bit的，若要存储的话可以使用char(32).
若要将这类信息转换为16进制的复杂格式，可以使用UNHEX()函数和binary(16)存储。

mysql> create table wuhan.t_md5 as select unhex(md5('123456')) hex_md5;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
 
mysql> select * from wuhan.t_md5;
+------------------+
| hex_md5          |
+------------------+
| 9IV       |
+------------------+
1 row in set (0.00 sec)
 
mysql> desc wuhan.t_md5;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| hex_md5 | varbinary(64) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
1 row in set (0.00 sec)
 
 
2.SHA 加密：SHA等同于SHA1，SHA加密算法比MD5更加安全。
mysql> select sha('123456');
+------------------------------------------+
| sha('123456')                            |
+------------------------------------------+
| 7c4a8d09ca3762af61e59520943dc26494f8941b |
+------------------------------------------+

3.SHA2加密：

SHA2(str, hash_length)
hash_length支持的值224, 256, 384, 512, or 0。0等同于256.
mysql> select sha2('123456',0) A,sha2('123456',256) B\G   
*************************** 1. row ***************************
A: 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
B: 8d969eef6ecad3c29a3a629280e686cf0c3f5d5a86aff3ca12020c923adc6c92
1 row in set (0.01 sec)

预计国内很多中小创业公司，和一些IT公司可能使用的加密算法就是上面的几种。
本人在网上收集到的一些密码，可以看到大部分的国人使用的密码都很简单，很容易猜测和密码碰撞到的。

Password_encrypt                  peoples  plain_text  
--------------------------------  -------  ------------
e10adc3949ba59abbe56e057f20f883e     1735  123456      
c33367701511b4f6020ec61ded352059       67  654321      
21218cca77804d2ba1922c33e0151105       54  888888      
670b14728ad9902aecba32e22fa4f6bd       49  000000      
96e79218965eb72c92a549dd5a330112       30  111111      
f379eaf3c831b04de153469d1bec345e       22  666666      
25d55ad283aa400af464c76d713c07ad       20  12345678    
25f9e794323b453885f5181f1b624d0b       19  123456789   
e35cf7b66449df565f93c607d5a81d09       16  456789      
fcea920f7412b5da7be0cf42b8c93759       14  1234567 
排名前十的密码，占比密码取样的25%.