第03章 用户和权限管理
前言
2022/7/28 18:15
暑假学习ing
推荐
【MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！-哔哩哔哩】
第03章 用户和权限管理
1. 用户管理
MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和 修改用户的密码等管理权限;普通用户只拥有被授予的各种权限。
MySQL提供了许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、删 除用户、密码管理和权限管理等内容。
MySQL数据库的安全性需要通过账户管理来保证。
1.1登录MySQL服务器
启动MySQL服务后，可以通过mysql命令来登录MySQL服务器，命令如下:
mysql -h hostname|hostIP -P port -u username -p DatabaseName -e "SQL语句"
mysql -h localhost -P 3306 -p dbtest1 -e "select * from emp1;"
查看用户
use mysql;
select * from user \G;
select host,user from user;
1.2 创建用户
在MySQL数据库中，官方推荐使用CREATE USER语句创建新用户。MySQL8版本移除了PASSWORD加密方法，因此不再推荐使用INSERT语句直接操作MySQL中的user表来增加用户。
使用CREATE USER语句来创建新用户时，必须拥有CREATE USER权限。每添加一个用户，CREATEUSER语句会在 MySQL.user表中添加一条新记录，但是新创建的账户没有任何权限。如果添加的账户已经存在，CREATEUSER语 句就会返回一个错误。
CREATE USER语句的基本语法形式如下:
CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名[IDENTIFIED BY '密码']];
- 用户名参数表示新建用户的账户，由用户(User)和主机名(Host)构成;
- “[]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用IDENTIFIED BY指定明文密码值。
- CREATE USER语句可以同时创建多个用户。
举例:
create user 'zhang3' identified by 'abc123';
再次创建，会报错，已有用户。
create user 'zhang3'@'localhost' identified by 'abc123';
可以看出：user的主键是user和localhost的联合主键
测试登录：
默认的权限有限
只有登录权限。
再举例：
1.3 修改用户
修改用户名：
UPDATE mysql.user SET USER='wang5' WHERE USER='li4' and host='%';
# 一定进行的操作
FLUSH PRIVILEGES;
1.4 删除用户
方式1：使用DROP方式删除（推荐）
可以同时删除好多。
默认删除host=%
drop user 'wang5';
方式2：使用DELETE方式删除
delete from user where user ='zhang3' and host = '%';
直接删除user表中的记录，可能会有残留，不推荐
1.5 设置当前用户密码
适用于root用户修改自己的密码，以及普通用户登录后修改自己的密码。
root用户拥有很高的权限，因此必须保证root用户的密码安全。root用户可以通过多种方式来修改密码，使用 ALTER USER修改用户密码是MySQL官方推荐的方式。此外，也可以通过SET语句修改密码。由于MySQL8中已移除了PASSWORD()函数，因此不再使用UPDATE语句直接操作用户表修改密码。
旧的写法如下:
#修改当前用户的密码:(MySQL5.7测试有效)
SET PASSWORD = PASSWORD('123456')I
这里介绍推荐的写法:
1.使用ALTER USER命令来修改当前用户密码
用户可以使用ALTER命令来修改自身密码，如下语句代表修改当前登录用户的密码。基本语法如下:
ALTER USER USER() IDENTIFIED BY 'new_password';
练习:下面使用ALTER命令来修改root用户的密码，将密码改为“Hello_1234”。命令如下:
ALTER USER USER() IDENTIFIED BY 'Hello_1234';
2.使用SET语句来修改当前用户密码
使用root用户登录MySQL后，可以使用SET语句来修改密码，具体SQL语句如下：
SET PASSWORD='new_password';
该语句会自动将密码加密后在赋给当前用户。
练习:下面使用ALTER命令来修改root用户的密码，将密码改为“Hello_1234”。SET语句具体如下:
SET PASSWORD= 'Hello_1234';
说明：加密
mysql> use mysql;
Database changed
mysql> desc user;
select host,user,authentication_string from user;
1.6 修改其他用户密码
root用户不仅可以修改自己的密码，还可以修改其它普通用户的密码。root用户登录MySQL服务器后，可以通过 ALTER语句和SET语句来修改普通用户的密码。由于PASSWORD()函数已移除，因此使用UPDATE直接操作用户表的方式已不再使用。
1.使用ALTER语句来修改普通用户的密码
可以使用ALTERUSER语句来修改普通用户的密码。基本语法形式如下:
ALTER USER user [IDENTIFIED BY‘新密码’] [,uger[IDENTIFIED BY’新密码’.;
其中，user参数表示新用户的账户，由用户名和主机名构成;“IDENTIFIED BY”关键字用来设置密。 练习:下面使用ALTER语句来修改kangshifu用户的密码，将密码改为“HelloWorld_123”
ALTER USER 'kangshifu'@'localhost' IDENTIFIED BY 'Helloworld_123';
alter user 'zhang3'@'%' identified by 'hello';
2.使用SET命令来修改普通用户的密码
使用root用户登录到MySQL服务器后，可以使用SET语句来修改普通用户的密码。SET语句的代码如下:
SET PASSWORD FOR 'username'@'hostname'='new_password';
其中，username参数是普通用户的用户名;hostname参数是普通用户的主机名;new_password是新密码。
练习:下面使用SET语句来修改kangshifu用户的密码，将密码改成“HelloWorld_123”。
SET PASSWORD for 'kangshifu'@'localhost'='HelloWorld_123';
3.使用UPDATE语句修改普通用户的密码(不推荐）
略
1.7 MySQL8密码管理（了解）
1.密码过期策略
格式：
ALTER USER user PASSWORD EXPIRE;
手动设置指定时间获取方式1：全局设置
- 方式①：使用SQL语句更改该变量的值并持久化
SET PRESIST default_password_lifetime=180;#建立全局策略，设置密码每个180天过期
- 方式②：使用配置文件my.cnf中进行维护
[mysqld]
default_password_lifetime=180 #建立全局策略，设置密码每个180天过期
手动设置指定时间获取方式2：单独设置
每个账号既可延用全局密码过期策略，也可单独设置策略。在CREATE USER和ALTER USER语句上加入 PASSWORD EXPIRE选项可实现单独设置策略。下面是一些语句示例。
#设置kangshifu账号密码每90天过期:
CREATETUSER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
#设置密码永不过期:
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
#适用全局密码过期策略:
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
2.密码重用策略
MySQL限制使用已用过的密码。重用限制策略基于密码更改的数量和使用的时间。重用策略可以是全局的，也可以为每个账号设置单独的策略。
-
账号的历史密码包含过去该账号所使用的密码。MySQL基于以下规则来限制密码重用:
- 如果账号的密码限制
基于密码更改的数量，那么新密码不能从最近限制的密码数量中选择。例如，如果密 码更改的最小值为3，那么新密码不能与最近3个密码中任何一个相同。 - 如果账号密码限制
基于时间，那么新密码不能从规定时间内选择。例如，如果密码重用周期为60天，那么 新密码不能从最近60天内使用的密码中选择。
- 如果账号的密码限制
-
MySQL使用password_history和password_reuse_interval系统变量设置密码重用策略。
password_history:规定密码重用的数量password_reuse_interval:规定密码重用的周期
-
这两个值可在服务器的配置文件中进行维护，也可在运行期间使用SQL语句更改该变量的值并持久化。
-
手动设置密码重用方式1:全局
- 方式①:使用SQL
SET PERSIST password_history=6;#设置不能选择最近使用过的6个密码
SET PERSIST password_reuse_interval=365;#设置不能选择最近一年使用过的密码
* 方式②：my.cnf配置文件
[mysqld]
password_history=6
password_reuse_interval=365
- 手动设置密码重用方式2:单独设置
每个账号可以延用全局密码重用策略，也可单独设置策略。这两个选项可以单独使用，也可以结合在一起使用。
下面是一些语句示例。
#不能使用最近5个密吗:
CREATE USER 'Kangshifu'@'localhost' PASSWORD HISTORY 5;
ALTER USER 'kangshafu'@'localhost' PASSWORD HISTORY 5;
#不能使用最近365天内的密码:
CREATE USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'kangshrfu'@'localhost' PASSWORD REUSE INTERVAL 365DAY;
#既不能使用最近5个密码，也不能使用365天内的密码
CREATE USER 'kangshifu'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;
2. 权限管理
关于MySQL的权限简单的理解就是MySQL允许你做你权力以内的事情，不可以越界。比如只允许你执行SELECT操 作，那么你就不能执行UPDATE操作。只允许你从某台机器上连接MySQL，那么你就不能从除那台机器以外的其他机器连接MySQL。
2.1权限列表
MySQL到底都有哪些权限呢?
mysql> show privileges;
GRANT和REVOKE语句中可以使用的权限如下:
mysql> show privileges;
+----------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+----------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
| Create | Databases,Tables,Indexes | To create new databases and tables |
| Create routine | Databases | To use CREATE FUNCTION/PROCEDURE |
| Create role | Server Admin | To create new roles |
| Create temporary tables | Databases | To use CREATE TEMPORARY TABLE |
| Create view | Tables | To create new views |
| Create user | Server Admin | To create new users |
| Delete | Tables | To delete existing rows |
| Drop | Databases,Tables | To drop databases, tables, and views |
| Drop role | Server Admin | To drop roles |
| Event | Server Admin | To create, alter, drop and execute events |
| Execute | Functions,Procedures | To execute stored routines |
| File | File access on server | To read and write files on the server |
| Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess |
| Index | Tables | To create or drop indexes |
| Insert | Tables | To insert data into tables |
| Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) |
| Process | Server Admin | To view the plain text of currently executing queries |
| Proxy | Server Admin | To make proxy user possible |
| References | Databases,Tables | To have references on tables |
| Reload | Server Admin | To reload or refresh tables, logs and privileges |
| Replication client | Server Admin | To ask where the slave or master servers are |
| Replication slave | Server Admin | To read binary log events from the master |
| Select | Tables | To retrieve rows from table |
| Show databases | Server Admin | To see all databases with SHOW DATABASES |
| Show view | Tables | To see views with SHOW CREATE VIEW |
| Shutdown | Server Admin | To shut down the server |
| Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
| Trigger | Tables | To use triggers |
| Create tablespace | Server Admin | To create/alter/drop tablespaces |
| Update | Tables | To update existing rows |
| Usage | Server Admin | No privileges - allow connect only |
| XA_RECOVER_ADMIN | Server Admin | |
| SHOW_ROUTINE | Server Admin | |
| SET_USER_ID | Server Admin | |
| RESOURCE_GROUP_USER | Server Admin | |
| APPLICATION_PASSWORD_ADMIN | Server Admin | |
| SYSTEM_VARIABLES_ADMIN | Server Admin | |
| AUDIT_ADMIN | Server Admin | |
| SERVICE_CONNECTION_ADMIN | Server Admin | |
| CLONE_ADMIN | Server Admin | |
| PERSIST_RO_VARIABLES_ADMIN | Server Admin | |
| FLUSH_USER_RESOURCES | Server Admin | |
| BINLOG_ADMIN | Server Admin | |
| ROLE_ADMIN | Server Admin | |
| SESSION_VARIABLES_ADMIN | Server Admin | |
| BINLOG_ENCRYPTION_ADMIN | Server Admin | |
| FLUSH_STATUS | Server Admin | |
| SYSTEM_USER | Server Admin | |
| ENCRYPTION_KEY_ADMIN | Server Admin | |
| REPLICATION_SLAVE_ADMIN | Server Admin | |
| GROUP_REPLICATION_ADMIN | Server Admin | |
| BACKUP_ADMIN | Server Admin | |
| RESOURCE_GROUP_ADMIN | Server Admin | |
| FLUSH_OPTIMIZER_COSTS | Server Admin | |
| TABLE_ENCRYPTION_ADMIN | Server Admin | |
| FLUSH_TABLES | Server Admin | |
| CONNECTION_ADMIN | Server Admin | |
| INNODB_REDO_LOG_ENABLE | Server Admin | |
| INNODB_REDO_LOG_ARCHIVE | Server Admin | |
| REPLICATION_APPLIER | Server Admin | |
+----------------------------+---------------------------------------+-------------------------------------------------------+
62 rows in set (0.00 sec)
mysql>
(1)CREATE和DROP权限，可以创建新的数据库和表，或删除(移掉)已有的数据库和表。如果将MySQL数据 库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。
(2) SELECT、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表上实施操作。
(3)SELECT权限只有在它们真正从一个表中检索行时才被用到。
(4)INDEX权限允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在 CREATE TABLE语句中包括索引定义。
(5) ALTER权限可以使用ALTER TABLE来更改表的结构和重新命名表。
(6) CREATE ROUTINE权限用来创建保存的程序(函数和程序)，ALTER ROUTINE权限用来更改和删除保存的程 序，EXECUTE权限用来执行保存的程序。
(7)GRANT权限允许授权给其他用户，可用于数据库、表和保存的程序。
(8) FILE权限使用户可以使用LOAD DATA INFILE和SELEC…INTO OUTFILE语句读或写服务器上的文件，任何被 授予FILE权限的用户都能读或写MySQL服务器上的任何文件(说明用户可以读任何数据库目录下的文件，因为服 务器可以访问这些文件)。
Mysql的权限如何分布：
2.2授予权限的原则
权限控制主要是出于安全因素，因此需要遵循以下几个经验原则︰
1、只授予能满足需要的最小权限，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
2、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
3、为每个用户设置满足密码复杂度的密码。
4、定期清理不需要的用户，回收权限或者删除用户。
2.3 授予权限
给用户授权的方式有2种，分别是通过把角色赋予用户给用户授权和直接给用户授权。用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全隐患。
授权命令:
GRANT 权限1,权限2...权限n ON 数据库名称.表名称 TO 用户名@用户地址〔IDENTIFIED BY '密码口令'];
- 该权限如果发现没有该用户，则会直接新建一个用户。
现在zhang3的权限
root修改zhang3的权限
grant select,update on dbtest1.* to 'zhang3'@'localhost';
zhang3的权限
root 插入数据
zhang3查询
zhang3修改
zhang3尝试删除，失败，不具备权限
root授予删除权限给zhang3
权限叠加
退出，重新登录zhang3
再次查询
特殊用法
mysql> create user 'li4' identified by 'abc123';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all privileges on *.* to 'li4'@'%';
Query OK, 0 rows affected (0.01 sec)
登录li4
查看li4的权限，好多啊
mysql> show grants;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for li4@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 `li4`@`%` |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_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,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,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 `li4`@`%` |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)
那他和root的权限有什么不一样？
root的权限：
mysql> show grants;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
root和li4的权限只有一点不一样WITH GRANT OPTION ，不能给其他用户赋予权限
- 如果需要赋予包括GRANT的权限，添加参数“
WITH GRANTOPTION”这个选项即可，表示该用户可以将自己 拥有的权限授权给别人。经常有人在创建操作用户的时候不指定WITH GRANTOPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。 - 可以使用GRANT重复给用户添加权限，
权限叠加，比如你先给用户添加 一个SELECT权限，然后又给用户添加一个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限。
我们在开发应用的时候，经常会遇到一种需求，就是要根据用户的不同，对数据进行横向和纵向的分组。
- 所谓横向的分组，就是指用户可以接触到的数据的范围，比如可以看到哪些表的数据;
- 所谓纵向的分组，就是指用户对接触到的数据能访问到什么程度，比如能看、能改，甚至是删除。
2.4 查看权限
- 查看当前用户的权限
SHOW GRANTS;
#或
SHOW GRANTS FOR CURRENT_USER;
#或
SHOW GRANTS FOR CURRENT_USER();
- 查看某用户的全局权限
SHOW GRANTS FOR 'user'@'主机地址';
2.5 收回权限
收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。 MySQL中使用REVOKE语句取消用户的某些权限。使用REVOKE收回权限之后， 用户账户的记录将从db、host、 tables_pri和columns_priv表中删除，但是用户账户记录仍然在user表中保存(删除user表中的账户记录使用DROP USER语句)。
注意:在将用户账户从user表删除之前，应该收回相应用户的所有权限。
- 收回权限命令
REVOKE 权限1,权限2..权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
- 举例
mysql> revoke select on dbtest1.* from 'zhang3'@'localhost';
Query OK, 0 rows affected (0.01 sec)
# root 没有权限SYSTEM_USER
mysql> revoke all privileges on *.* from 'li4'@'%';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
# 赋权
mysql> grant SYSTEM_USER on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> revoke all privileges on *.* from 'li4'@'%';
Query OK, 0 rows affected (0.01 sec)
- 注意：
须用户重新登录后生效
总结
有一些程序员喜欢使用Root超级用户来访问数据库，完全把权限控制放在应用层面实现。这样当然也是可以的。但建议大家，尽量使用数据库自己的角色和用户机制来控制访问权限，不要轻易用Root账号。因为Root账号密码放在代码里面不安全，一旦泄露，数据库就会完全失去保护。
而且，MySQL的权限控制功能十分完善，应该尽量利用，可以提高效率，而且安全可靠。
3.权限表
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中。MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表。除此之外，还有tables_priv表、columns_priv表和procs_priv表等。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存 。
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> show tables;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
35 rows in set (0.00 sec)
mysql>
3.1 user表
用户表
mysql> desc user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.02 sec)
主键 Host & User
权限 _pri
安全 ssl_
最大次数 max_
密码相关 Password_
3.2 db表
具体数据库操作的权限
mysql> desc db;
+-----------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
主键 Host & Db & User
权限_priv
3.3 tables_priv表和columns_priv表
tables_priv表
mysql> desc tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Grantor | varchar(288) | NO | MUL | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Table_priv | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO | | | |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.01 sec)
主键 Host & Db & User & Table_name
columns_priv表
mysql> desc columns_priv;
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Table_name | char(64) | NO | PRI | | |
| Column_name | char(64) | NO | PRI | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| Column_priv | set('Select','Insert','Update','References') | NO | | | |
+-------------+----------------------------------------------+------+-----+-------------------+-----------------------------------------------+
7 rows in set (0.01 sec)
主键 5个联合，粒度越小
3.4 procs_priv表
mysql> desc procs_priv;
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
| Host | char(255) | NO | PRI | | |
| Db | char(64) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Routine_name | char(64) | NO | PRI | | |
| Routine_type | enum('FUNCTION','PROCEDURE') | NO | PRI | NULL | |
| Grantor | varchar(288) | NO | MUL | | |
| Proc_priv | set('Execute','Alter Routine','Grant') | NO | | | |
| Timestamp | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+----------------------------------------+------+-----+-------------------+-----------------------------------------------+
8 rows in set (0.01 sec)
4. 访问控制（了解）
正常情况下，并不希望每个用户都可以执行所有的数据库操作r当MySQL允许一个用户执行各种操作时，它将首 先核实该用户向MySQL服务器发送的连接请求，然后确认用户的操作请求是否被允许。这个过程称为MySQL中的 访问控制过程。MySQL的访问控制分为两个阶段:连接核实阶段和请求核实阶段。
4.1 连接核实阶段
当用户试图连接MySQL服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确定接受或 者拒绝连接。即客户端用户会在连接请求中提供用户名、主机地址、用户密码，MySQL服务器接收到用户请求 后，会使用user表中的host、 user和
authentication_string这3个字段匹配客户端提供信息。
服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的密码时才接受连接。如果连接核实没有通过，服务器就完全拒绝访问否则，服务器接受连接，然后进入阶段2等待用户请求。
4.2 请求核实阶段
一旦建立了连接，服务器就进入了访问控制的阶段2，也就是请求核实阶段。对此连接上进来的每个请求，服务 器检查该请求要执行什么操作、是否有足够的权限来执行它，这正是需要授权表中的权限列发挥作用的地方。这 些权限可以来自user、db、table_priv和column_priv表。
确认权限时，MySQL首先检查user表，如果指定的权限没有在user表中被授予，那么MySQL就会继续检查db表，
db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表
中的数据;如果在该层级没有找到限定的权限，则MySQL继续检查tables_priv表以及columns_priv表，如果
所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将返回错误信息，用户请求的操作不能执行，
操作失败。请求核实的过程如图所示。
提示:
MySQL通过向下层级的顺序(从user表到columns_priv表)检查权限表，但并不是所有的权限都要执行该过程。例如，一个用户登录到MySQL服务器之后只执行对MySQL的管理操作，此时只涉及管理权限，因此MySQL 只检查user表。另外，如果请求的权限操作不被允许，MySQL也不会继续检查下一层级的表。
5. 角色管理
5.1角色的理解
角色是在MySQL8.0中引入的新功能。在MySQL中，角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色，同时也被授予角色包含的权限。对角色进行操作需要较高的权限。并且像用户账户一样，角色可以 拥有授予和撤消的权限。
引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。
5.2 创建角色
在实际应用中，为了安全性，需要给用户授予权限。当用户数量较多时，为了避免单独给每一个用户授予多个权限，可以先将权限集合放入角色中，再赋予用户相应的角色。
创建角色使用CREATE ROLE语句，语法如下
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]..
角色名称的命名规则和用户名类似。如果host_name省略，默认为%，role_name不可省略，不可为空。
练习:我们现在需要创建一个经理的角色，就可以用下面的代码:
CREATE ROLE 'manager'@'localhost';
这里创建了一个角色，角色名称是“manager”，角色可以登录的主机是"localhost”，意思是只能从数据库服务器运 行的这台计算机登录这个账号。你也可以不写主机名，直接创建角色“manager”:
CREATE ROLE 'manager';
如果不写主机名，MySQL默认是通配符“%”，意思是这个账号可以从任何一台主机上登录数据库。 同样道理，如果我们要创建库管的角色，就可以用下面的代码:
CREATE ROLE 'stocker';
通过如下的指令，一次性创建3个角色:
CREATE ROLE 'app_develeper','app_read','app_write';
测试
mysql> create role 'manager'@'%';
Query OK, 0 rows affected (0.03 sec)
mysql> create role 'boss'@'%';
Query OK, 0 rows affected (0.01 sec)
5.3 给角色赋予权限
创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。给角色授权的语法结构是:
GRANT privileges ON table_name To 'role_name'[@'host_name'];
上述语句中privileges代表权限的名称，多个权限以逗号隔开。可使用SHOW语句查询权限名称，权限列表，
SHOW PRIVILEGES\G;
测试
mysql> grant select,update on dbtest1.* to 'manager';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to 'boss' @'%';
Query OK, 0 rows affected (0.00 sec)
5.4 查看角色的权限
赋予角色权限后，我们可以通过SHOW GRANT语句，来查看权限是否创建成功了：
测试
mysql> show grants for 'manager'@'%';
+------------------------------------------------------+
| Grants for manager@% |
+------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%` |
| GRANT SELECT, UPDATE ON `dbtest1`.* TO `manager`@`%` |
+------------------------------------------------------+
2 rows in set (0.01 sec)
mysql> show grants for 'boss';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for boss@%
····
5.5 回收角色的权限
角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句。
修改了角色的权限，会影响拥有该角色的账户的权限。 撤销角色权限的SQL语法如下:
REVOKE privileges ON tablename FROM 'rolename';
测试
mysql> revoke update on dbtest1.* from 'manager';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'manager'@'%';
+----------------------------------------------+
| Grants for manager@% |
+----------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%` |
| GRANT SELECT ON `dbtest1`.* TO `manager`@`%` |
+----------------------------------------------+
2 rows in set (0.00 sec)
mysql>
5.6删除角色
当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色。删除 角色的操作很简单，你只要掌握语法结构就行了。
DROP ROLE role[,role2]..
注意，如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限。
练习:执行如下SQL删除角色school_read。
DROP ROLE 'school_read';
测试
mysql> create role 'admin';
Query OK, 0 rows affected (0.00 sec)
mysql> drop role 'admin';
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'admin';
ERROR 1141 (42000): There is no such grant defined for user 'admin' on host '%'
mysql>
5.7给用户赋予角色
角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。给用户添加角色可使用GRANT语句，语法形式如下：
先查看有哪些用户。
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 |
+-----------+------------------+
| % | boss |
| % | li4 |
| % | manager |
| % | root |
| % | zhang3 |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | zhang3 |
+-----------+------------------+
9 rows in set (0.00 sec)
mysql>
创建一个用户，用于测试
mysql> create user 'wang5'@'%' identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)
登录
[root@centos7-mysql-1 ~]# mysql -uwang5 -pabc123
mysql> show grants;
+-----------------------------------+
| Grants for wang5@% |
+-----------------------------------+
| GRANT USAGE ON *.* TO `wang5`@`%` |
+-----------------------------------+
1 row in set (0.00 sec)
mysql>
测试：给wang5赋予角色
mysql> grant 'manager'@'%' to 'wang5'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'wang5';
+------------------------------------+
| Grants for wang5@% |
+------------------------------------+
| GRANT USAGE ON *.* TO `wang5`@`%` |
| GRANT `manager`@`%` TO `wang5`@`%` |
+------------------------------------+
2 rows in set (0.00 sec)
mysql>
wang5中查看权限
mysql> show grants;
+------------------------------------+
| Grants for wang5@% |
+------------------------------------+
| GRANT USAGE ON *.* TO `wang5`@`%` |
| GRANT `manager`@`%` TO `wang5`@`%` |
+------------------------------------+
2 rows in set (0.00 sec)
mysql>
即使退出重登或flush 权限
查询当前角色，如果角色未激活，结果显示NONE。
SELECT CURRENT_ROLE();
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
所以需要激活权限
注意：角色默认是不激活的，所有必须要手动激活
5.8 激活角色
激活角色有两种方式：
方式1：使用set default role命令激活角色
举例：
SET DEFAULT ROLE TO 'kangshifu'@'localhost';
测试
mysql> SET DEFAULT role 'manager'@'%' TO 'wang5'@'%';
Query OK, 0 rows affected (0.00 sec)
退出重新登录
mysql> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `manager`@`%` |
+----------------+
1 row in set (0.00 sec)
注意：用户需要退出重新登录，才能看到赋予的角色。
方式2：使用activate_all_roles_on_login设置为ON
- 默认情况：
mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF |
+-----------------------------+-------+
1 row in set (0.01 sec)
mysql>
- 设置：
SET GLOBAL activate_all_roles_on_login=ON;
这条SQL语句的意思是，对所有角色永久激活。运行这条语句后，用户才真正拥有赋予角色的所有权限。
查看当前会话已激活的角色：
SELECT CURRENT_ROLE();
5.9 撤销用户的角色
撤销用户的角色的SQL语法如下：
REVOKE role FROM user;
测试
wang5自己不能回收权限
mysql> revoke 'manager'@'%' from 'wang5'@'%';
ERROR 1227 (42000): Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation
mysql>
root回收权限
mysql> revoke 'manager'@'%' from 'wang5'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql>
需要退出重登
5.10 设置强制的角色（mandatory role）
强制角色是给每个创建账户的默认角色，不需要手动设置。强制角色无法被REVOKE或者DROP。
方式1:服务启动前设置
[mysqld]
mandatory_roles='role1.role2@localhost,r3@%.atguigu.com'
方式2:运行时设置
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';#系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com';#系统重启后失效
5.11小结
MySQL主要管理角色的语句如下：
6. 配置文件的使用
6.1 配置文件的格式
6.1配置文件格式
与在命令行中指定启动选项不同的是，配置文件中的启动选项被划分为若干个组，每个组有一个组名，用中括号 []扩起来，像这样:
[root@centos7-mysql-1 ~]# vim /etc/my.cnf
[server]
(具体的启动选项...)
[mysqld]
(具体的启动选项...)
[mysqld_safe]
(具体的启动选项...)
[client]
(具体的启动选项...)
[mysql]
(具体的启动选项...)
[mysqladmin]
(具体的启动选项...)
像这个配置文件里就定义了许多个组，组名分别是server、 mysqld、mysqld_safe、client、mysql、
mysqladmin。每个组下边可以定义若干个启动选项，我们以[server]组为例来看一下填写启动选项的形式 (其他组中启动选项的形式是一样的)
[server]
option1 #这是dption1，该选项不需要选项值
option2=value2 #这是option2，该选项需要选项值
在配置文件中指定启动选项的语法类似于命令行语法，但是配置文件中指定的启动选项不允许--前缀，并且
每行只指定一个选项，而且=周围可以有空白字符(命令行中选项名、=、选项值之间不允许有空白字符)。另
外，在配置文件中，我们可以使用#来添加注释，从#出现直到行尾的内容都属于注释内容，读取配置文件时会
忽略这些注释内容。
6.2 启动命令与选项组
[root@centos7-mysql-1 ~]# cd /usr/bin
[root@centos7-mysql-1 bin]# ls
[root@centos7-mysql-1 bin]# cd /usr/sbin
[root@centos7-mysql-1 sbin]# ls
配置文件中不同的选项组是给不同的启动命令使用的。不过有两个选项组比较特别:
- [serverl组下边的启动选项将作用于所有的服务 器程序。
- [client]组下边的启动选项将作用于所有的客户端程序。
下面是启动命令能读取的选项组都有哪些:
比如，在/etc/mysql/my.cnf这个配置文件中添加一些内容:
[server]
skip-networking
default-storage-engine=MyISAM
然后直接用mysqld启动服务器程序:
mysqld
虽然在命令行没有添加启动选项，但是在程序启动的时候，就会默认的到我们上边提到的配置文件路径下查找配置文件，其中就包括/etc/my.cnf。又由于mysqld命令可以读取[server]选项组的内容，所以skip-networking和default-storage-engine=MyISAM这两个选项是生效的。你可以把这些启动选项放在 [client]组里再试试用mysqld启动服务器程序，就不生效。
6.3特定MySQL版本的专用选项组
我们可以在选项组的名称后加上特定的MySQL版本号，比如对于[mysqld]选项组来说，我们可以定义一个 [mysqld-5.7]的选项组，它的含义和[mysqld]一样，只不过只有版本号为5.7的mysqld程序才能使用这个选项组中的选项。
6.4同一个配置文件中多个组的优先级
我们说同一个命令可以访问配置文件中的多个组，比如mysqld可以访问[mysqld]、[server]组，如果在同一个配置文件中，比如~/.my.cnf，在这些组里出现了同样的配置项，比如这样:
[server]
default-storage-engine=InnoDB
[mysqld]
default-storage-engine=MyISAM
那么，将以最后一个出现的组中的启动选项为准，比方说例子中default-storage-engine既出现在[mysqld]组也出现在[server]组，因为[mysqld]组在[server]组后边，就以[mysqld]组中的配置项为准。
6.5命令行和配置文件中启动选项的区别
在命令行上指定的绝大部分启动选项都可以放到配置文件中，但是有一些选项是专门为命令行设计的，比方说 defaults-extra-file、 defaults-file这样的选项本身就是为了指定配置文件路径的，再放在配置文件中使用就没啥意义了。
如果同一个启动选项既出现在命令行中，又出现在配置文件中，那么以命令行中的启动选项为准!比如我们在配置文件中写了:
[server]
default-storage-engine=InnoDB
而我们的启动命令是:
mysql.server start --default-storage-engine=MyISAM
那最后default-storage-engine的值就是MyISAM。
7. 系统变量（复习）
7.1系统变量简介
变量由系统定义，不是用户定义，属于服务器层面。启动MySQL服务，生成MySQL服务实例期间，MySQL将为 MySQL服务器内存中的系统变量赋值，这些系统变量定义了当前MySQL服务实例的属性、特征。这些系统变量的值要么是编译MySQL时参数的默认值，要么是配置文件(例如my.ini等)中的参数值。大家可以通过网址
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html查看MySQL文档的系统变量。
系统变量分为全局系统变量(需要添加global关键字)以及会话系统变量(需要添加session关键字)，有时也把全局系统变量简称为全局变量，有时也把会话系统变量称为local变量。如果不写，默认会话级别。静态变量(在MySQL服务实例运行期间它们的值不能使用set动态修改)属于特殊的全局系统变量。
每一个MySQL客户机成功连接MySQL服务器后，都会产生与之对应的会话。会话期间，MySQL服务实例会在 MySQL服务器内存中生成与该会话对应的会话系统变量，这些会话系统变量的初始值是全局系统变量值的复制。 如下图:
7.2 查看系统变量
- 查看所有或部分系统变量
#查看所有全局变量
SHOW GLOBAL VARIABLES:
#查看所有会话变量
SHOW SESSION VARIABLES;
#或
SHOW VARIABLES;
#查看满足条件的部分系统变量。
SHOW GLOBAL VARIABLES LIKE'%标识符%';
#查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%标识符%';
举例:
SHOW GLOBAL VARIABLES LIKE 'admin_%’;
- 查看指定系统变量
作为MvSQL编码规范，MvSQL中的系统变量以两个“@”开头，其中“@@global”仅用王标记全局系统变
量，“@@session”仅用于标记会话系统变量。“@@”首先标记会话系统变量，如果会话系统变量不存在，则标记全局系统变量。
#查看指定的系统变量的值
SELECT @dglobal.变量名;
#查看指定的会话变量的值
SELECT @@session.变量名;
#或者
SELECT @@变量名
7.3 设置系统变量
有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务实例的属性、特征。具体方法;
7.3.1通过启动选项设置
大部分的系统变量都可以通过启动服务器时传送启动选项的方式来进行设置。如何填写启动选项我们总结一下， 主要是两种方式:
- 通过命令行添加启动选项
比方说我们在启动服务器程序时用这个命令:
mysqld --default-storage-engine=MyISAM --max-connections=10
- 通过配置文件添加启动选项
我们可以这样填写配置文件:
[server]
default-storage-engine=MyISAM max-connections=10
当使用上边两种方式中的任意一种启动服务器程序后，我们再来查看一下系统变量的值:
mysql> SHOW VARIABLES LIKE 'default_storage_engine';
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.05 sec)
可以看到default_storage_engine和max_connections这两个系统变量的值已经被修改了。有一点需要注意 的是，对于启动选项来说，如果启动选项名由多个单词组成，各个单词之间用短划线-或者下划线_连接起来都 可以，但是它对应的系统变量的单词之间必须使用下划线_连接起来。
7.3.2 服务器出现运行过程中设置
对于大部分系统变量来说，它们的值可以在服务器程序运行过程中进行动态修改而无需停止并重启服务器。但是，系统变量有作用范围之分。
设置不同作用范围的系统变量
多个客户端程序可以同时连接到一个服务器程序。对于同一个系统变量，我们有时想让不同的客户端有不同的值。比方说客户端A，他想让当前客户端对应的默认存储引擎为InnoDB，所以他可以把系统变量default_storage_engine的值设置为InnoDB;客户端B，他想让当前客户端对应的默认存储引擎为MyISAM，所以他可以把系统变量default_storage_engine的值设置为MyISAM。这样两个客户端拥有不同的默认存储引擎，使用时互不影响，十分方便。但是这样各个客户端都私有一份系统变量会产生这么两个问题:
- 有一些系统变量并不是针对单个客户端的，比如允许同时连接到服务器的客户端数量
max_connections，查 询缓存的大小query_cache_size，这些公有的系统变量让某个客户端私有显然不合适。 - 一个新连接到服务器的客户端对应的系统变量的值该怎么设置?
为了解决这两个问题，MySQL提出了系统变量作用范围的概念，具体来说作用范围分为这两种:
GLOBAL:全局变量，影响服务器的整体操作。SESSION:会话变量，影响某个客户端连接的操作。(注:SESSION有个别名叫LOCAL)
在服务器启动时，会将每个全局变量初始化为其默认值(可以通过命令行或选项文件中指定的选项更改这些默认 值)。然后服务器还为每个连接的客户端维护一组会话变量，客户端的会话变量在连接时使用相应全局变量的当前值初始化。
以default_storage_engine举例，在服务器启动时会初始化一个名为default_storage_engine，作用范围为GLOBAL的系统变量。之后每当有一个客户端连接到该服务器时，服务器都会单独为该客户端分配一个名为 default_storage_engine，作用范围为SESSION的系统变量，该作用范围为SESSION的系统变量值按照当前作用范围为GLOBAL的同名系统变量值进行初始化。
很显然，通过启动选项设置的系统变量的作用范围都是GLOBAL的，也就是对所有客户端都有效的，因为在系统 启动的时候还没有客户端程序连接进来呢。了解了系统变量的GLOBAL和SESSION作用范围之后，我们再看一下在服务器程序运行期间通过客户端程序设置系统变量的语法:
SET [GLOBAL|SESSION]系统变量名=值;
或者写成这样也行:
SET [@@(GLOBAL| SESSION).]var_name = XXX;
比如，服务器运行过程中把作用范围为GLOBAL的系统变量default_storage_engine的值修改为MyISAM，也就是想让之后新连接到服务器的客户端都用MyISAM作为默认的存储引擎，那我们可以选择下边两条语句中的任意一条来进行设置:
#方式一:
Tl
SET GLOBAL default_storage_engine = MyISAM;
#方式二:
SET @@GL0BAL.default_storage_engine = MyISAM;
如果只想对本客户端生效，也可以选择下边三条语句中的任意一条来进行设置:
#方式一:
SET SESSION default_storage_engine = MyISAM;
#方式二:
sET @@SESSION.default_storage_engine = MyISAM;
#方式三:
SET default_storage_engine = MyISAM;
从上边的方式三可以看出，如果在设置系统变量的语句中省略了作用范围，默认的作用范围就是SESSION。也就是说SET 系统变量名 = 值和SET SESSION 系统变量名 = 值是等价的。
查看不同作用范围的系统变量
既然系统变量有作用范围之分，那我们的SHOW VARIABLES语句查看的是什么作用范围的系统变量呢?
答:默认查看的是SESSION作用范围的系统变量。
我们也可以在查看系统变量的语句上加上要查看哪个作用范围的系统变量:
SHOW [GLOBALI SESSION] VARIABLES [LIKE 匹配的模式];
下边我们演示一下完整的设置并查看系统变量的过程:
mysql> SHOW SESSION VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)
mysql> SET SESSION default_storage_engine = MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SESSION VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)
可以看到，最初default_storage_engine的系统变量无论是在GLOBAL作用范围上还是在SESSION作用范围 上的值都是InnoDB，我们在SESSION作用范围把它的值设置为MyISAM之后，可以看到GLOBAL作用范围的值并没有改变。
小贴士:如果某个客户端改变了某个系统变量在GLOBAL作用范围的值，并不会影响该系统变量在当前已经连接的客户端作用范围为SESSION的值，只会影响后续连入的客户端在作用范围为SESSION的值。
注意事项
并不是所有系统变量都具有GLOBAL和SESSION的作用范围。
- 有一些系统变量只具有
GLOBAL作用范围，比方说max_connections，表示服务器程序支持同时最多有多少 个客户端程序进行连接。 - 有一些系统变量只具有
SESSION作用范围，比如insert_id，表示在对某个包含AUTO_INCREMENT列的表进行插入时，该列初始的值。 - 有一些系统变量的值既具有
GLOBAL作用范围，也具有SESSION作用范围，比如我们前边用到的default_storage_engine，而且其实大部分的系统变量都是这样的 - 有些系统变量是只读的，并不能设置值。
比方说version，表示当前MySQL的版本，我们客户端是不能设置它的值的，只能在SHOW VARIABLES语句里查看。
2022/7/30 11:17
最后
p105~p109
Markdown 41609 字数 1571 行数
HTML 36828 字数 897 段落
216
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
