Mysql高级篇（上）

Mysql架构介绍(一)

1、Linux环境下的MySQL的安装与使用

参考博文 Linux环境下的MySQL的安装与使用

2、MySQL请求到响应字符集变化（了解）

3、MySQL8 的主要目录结构

类别	目录
数据库文件目录	/var/lib/mysql/
命令目录	/usr/bin/ 和 /usr/sbin
配置文件目录	/usr/share/mysql-8.0/（命令及配置文件） 和 /etc/下的my.cnf文件

4、数据库和文件系统关系

（1）默认数据库

SHOW DATABASES;

（2）数据库在文件系统中的表示

参考站内博文 Linux环境下MySQL的数据目录

5、用户与权限管理

（1）用户管理

创建

创建后，用户host默认为 % (任意IP)
CREATE USER '用户名' IDENTIFIED BY '密码';


创建指定host的用户
CREATE USER '用户名'@'IP地址' IDENTIFIED BY '密码';

疑问点

Linux环境下我登录MySQL，图片中是我用root用户登录MySQL的信息，上面user表中的用户名和对应的host值，其中有两个叫summer的用户； 随后我退出root用户，然后用summer用户登录MySQL，如果所示最后一张图片是用summer用户登录的，也登录成功了，但是我不知道是哪个summer用户，怎么确定是哪个summer用户啊？

修改(表字段的修改)

UPDATE 表名 SET 字段名=字段值 WHERE 条件;


刷新权限（必须要刷新，否则不生效）

FLUSH PRIVILEGES;

删除

DROP 表名 '用户名'@'host值';

用户密码修改与管理

修改当前登录用户

1、使用 ALTER USER 命令修改当前用户密码（推荐）

在命令行中修改（当前用户已经登录）USER()是函数
ALTER USER USER() IDENTIFIED BY '新密码';


在远程工具(如：navicat)修改
ALTER USER '用户名'@'host值' IDENTIFIED BY '新密码';

2、使用 SET 命令修改当前用户密码

在命令行中修改（当前用户已经登录）
SET PASSWORD='新密码';


在远程工具(如：navicat)修改
SET PASSWORD FOR '用户名'@'host值' = '新密码';

root 修改其他用户

和上述远程工具一样

ALTER USER命令修改

• ALTER USER '用户名'@'host值' IDENTIFIED BY '新密码';
  
  
  

SET 命令修改

• SET PASSWORD FOR '用户名'@'host值' = '新密码';

密码管理参考 腾讯云开发者社区文章

（2）权限管理

权限列表

SHOW PRIVILEGES;

授予权限原则

• 只授予满足需要的最小权限
• 创建用户的时候限制用户的登录主机，一般是限制成指定IP或内网IP段
• 为每个用户设置满足密码复杂度的密码
• 定期清理废弃用户，回收权限

授予权限方式

1. 角色赋予用户给用户授权
2. 直接给用户授权

授予权限命令

GRANT 权限1,权限2,...权限n ON 数据库名称.表名 TO 用户名@用户地址 [IDENTIFIED BY '密码口令'];

查看权限

查看当前用户的权限
SHOW GRANTS;

查看某用户的全局权限
SHOW GRANTS FOR '用户名'@'用户地址';

收回权限

收回权限就是取消已经赋予用户的某些权限，使用 revoke语句;
如果要删除某个用户的张虎，那么在删除之前，安全起见，在删除之前，一定要先收回该用户的所有权限

注意：收回权限需用户重新登录后才能生效

收回权限命令

REVOKE 权限1,权限2,...权限n ON 数据库名.表名 FROM 用户名@用户地址;

（3）权限表

MySQL 8.0 的权限表包括 mysql.user、mysql.db、mysql.tables_priv、mysql.columns_priv 和 mysql.procs_priv。这些表存储了用户账号、数据库、表和列级别的权限信息

• 查看【用户账号】权限
  SELECT * FROM mysql.user;
  
  
• 查看【数据库级别】权限
  SELECT * FROM mysql.db;
  
  
• 查看【表级别】权限
  SELECT * FROM mysql.tables_priv;
  
  
• 查看【列级别】权限
  SELECT * FROM mysql.columns_priv;
  
  
• 查看【存储过程和函数】权限
  SELECT * FROM mysql.procs_priv;

（4）访问控制（了解）

在Linux下，MySQL 8.0 的访问控制包括两个重要的阶段：连接核实阶段和请求核实阶段。这两个阶段确保MySQL能够在用户连接和执行操作时进行权限的验证，从而保证数据库的安全性和控制权。

1. 连接核实阶段

2. 请求核实阶段

（5）角色管理

在MySQL 8.0 中，角色管理变得更加灵活和强大。角色可以看作是一组权限的集合，用户可以被授予一个或多个角色，从而简化了权限管理

1. 创建角色

-- host_name 可以省略，省略后默认为 % ；role_name不可省略
-- 可以一次性创建多个角色，中间用 逗号 隔开
CREATE ROLE 'role_name'@'host_name';

2. 授予权限给角色

-- host_name 可以省略，省略后默认为 % ；role_name不可省略
-- PRIVILEGES 代表权限的名称，多个权限用逗号隔开
-- SHOW PRIVILEGES;  查看权限
GRANT PRIVILEGES ON 数据库名.表名 TO 'role_name'@'host_name';


-- 例如：授予developer角色对数据库mydb的所有表的SELECT和INSERT权限
GRANT SELECT, INSERT ON mydb.* TO 'developer';

3. 查看用户的角色和权限

-- 查看用户被分配了哪些角色,host_name 可以省略，省略后默认为 % 
SHOW GRANTS FOR 'user_name'[@'host_name'];


-- 例如：查看用户alice的角色和权限：
SHOW GRANTS FOR 'alice';

4. 收回角色权限

-- 如果需要撤销用户的角色，可以使用REVOKE命令：
REVOKE 'role_name' FROM 'user_name';

-- 收回角色的权限
-- PRIVILEGES 代表权限的名称，多个权限用逗号隔开
-- SHOW PRIVILEGES;  查看权限
REVOKE PRIVILEGES  ON 数据库名.表名  FROM 'role_name'@'host_name';





-- 例如：撤销用户alice的developer角色：
REVOKE 'developer' FROM 'alice';

--- 收回 developer角色对数据库mydb的所有表的SELECT和INSERT权限
REVOKE SELECT, INSERT ON mydb.* FROM  'developer';

5. 删除角色

-- 如果不再需要某个角色，可以将其删除：
DROP ROLE 'role_name';

6. 将角色分配给用户

-- 将角色分配给用户可以让用户继承该角色的所有权限。
GRANT 'role_name' TO 'user_name';


-- 例如，将developer角色分配给用户alice：
GRANT 'developer' TO 'alice';

7. 激活角色

分配了角色后，用户需要激活该角色才能使用它的权限。用户可以通过以下命令来激活某个角色

-- 分配了角色后，用户需要激活该角色才能使用它的权限。用户可以通过以下命令来激活某个角色
SET ROLE 'role_name'[@'host_name' TO 'user_name'@'host_name'];

--如果想要激活所有已分配的角色，可以使用：
SET ROLE ALL;

8. 默认角色

-- 用户可以设置一个或多个默认角色，这些角色将在用户登录时自动激活：
SET DEFAULT ROLE 'role_name' FOR 'user_name';


-- 例如，设置developer为用户alice的默认角色：
SET DEFAULT ROLE 'developer' FOR 'alice';

6、配置文件的使用

在 MySQL 8.0 中，my.cnf 文件是用于配置 MySQL 服务器及其相关工具行为的关键文件。不同的部分标签（如[mysqld]、[server]、[mysql]等）对应不同的 MySQL 组件或客户端工具的配置。

-- 使用示例
[mysqld]
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
max_connections = 200
innodb_buffer_pool_size = 1G

[mysql]
user = root
host = localhost
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld_safe]
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
nice = 0
open-files-limit = 8192

[client]
port = 3306
host = localhost
user = root
socket = /var/lib/mysql/mysql.sock

[mysqladmin]
user = root
password = your_password
host = localhost

总结：
在 MySQL 8.0 中，my.cnf 文件通过不同的部分标签（如 [mysqld]、[client]、[mysql] 等）来配置 MySQL 服务器和各个工具的行为。理解和正确配置这些部分，可以有效地管理 MySQL 的各种功能和工具，使其更好地适应您的需求。