MySQL-3 DCL 权限管理 + 日志管理实战教程（DBA 必备）

引言

📌 本人博客原文链接：Breeze 在MySQL数据库运维中，DCL（数据控制语言）和日志管理是DBA日常工作的核心内容。掌握这些知识点不仅能保障数据库的安全性，还能提高数据库的性能和可靠性。本文将基于前两章的内容，深度解析DCL的核心知识点，包括权限管理和日志管理两大部分。

一、DCL与权限管理

1. 权限级别深度解析

MySQL的权限系统非常精细，分为四个级别，每个级别都有其特定的应用场景：

1.1 Global level（全局级别）

• 作用范围：适用于所有库、所有表
• 权限存储：存储在mysql.user表中
• 应用场景：用于管理需要全局权限的用户，如数据库管理员
• 示例权限：SUPER、CREATE USER、RELOAD等管理权限

1.2 Database level（数据库级别）

• 作用范围：适用于某个数据库中的所有表
• 权限存储：存储在mysql.db表中
• 应用场景：用于管理需要访问特定数据库的用户，如应用开发人员
• 示例权限：CREATE、ALTER、DROP等数据库操作权限

1.3 Table level（表级别）

• 作用范围：适用于库中的某个表
• 权限存储：存储在mysql.tables_priv表中
• 应用场景：用于管理需要访问特定表的用户，如报表生成人员
• 示例权限：SELECT、INSERT、UPDATE、DELETE等表操作权限

1.4 Column level（列级别）

• 作用范围：适用于表中的某个字段
• 权限存储：存储在mysql.columns_priv表中
• 应用场景：用于管理需要访问特定列的用户，如财务数据查看人员
• 示例权限：对特定列的SELECT、UPDATE权限

这种分级权限管理机制使得DBA可以根据实际需求，为不同用户设置精确的权限范围，提高数据库的安全性。

2. MySQL用户管理详细操作

2.1 创建用户

创建用户的基本语法如下：

CREATE USER user1@'localhost' IDENTIFIED BY 'Qwe@123456';

参数详解：

• CREATE：创建关键字
• USER：用户关键字
• user1：用户名称（自定义）
• @：分隔符
• 'localhost'：允许登录的主机
• IDENTIFIED BY：身份认证关键字
• 'QianFeng@123456'：用户密码

验证方法：

— 查看所有用户
SELECT * FROM mysql.user\\G;

— 只查看用户名和主机
SELECT user, host FROM mysql.user;

注意事项：

• 用户名和主机名组合必须唯一
• 密码应符合安全要求，包含大小写字母、数字和特殊字符
• 主机名可以使用通配符，如%表示所有主机

2.2 删除用户

删除用户的语法如下：

DROP USER 'user1'@'localhost';

操作示例：

— 查看用户是否存在
SELECT user, host FROM mysql.user WHERE user = 'user1';

— 删除用户
DROP USER 'user1'@'localhost';

— 验证用户是否删除
SELECT user, host FROM mysql.user WHERE user = 'user1';

2.3 修改用户密码

root修改自己密码：

方法一：使用mysqladmin命令

mysqladmin -uroot -p'旧密码' password 'new_password'

参数说明：

• -u：指定用户名
• -p：指定旧密码
• password：修改密码命令
• 'new_password'：新密码

方法二：使用SET PASSWORD语句

— 修改当前用户密码
SET PASSWORD=password('new_password');

— 刷新权限
FLUSH PRIVILEGES;

方法三：直接更新mysql.user表

— 更新密码
UPDATE mysql.user SET authentication_string=password('new_password') WHERE user='root' AND host='localhost';

— 刷新权限
FLUSH PRIVILEGES;

丢失root用户密码的解决方案：

前言：当root用户，忘记了密码。可以使用破解的方式来登录系统，修改密码。

原理：使系统在启动时，不加载密码文件。

vim /etc/my.cnf

[mysqld]
skip-grant-tables

systemctl restart mysqld
mysql -uroot

— 更新密码
UPDATE mysql.user SET authentication_string=password('new_password') WHERE user='root' AND host='localhost';

— 刷新权限
FLUSH PRIVILEGES;

vim /etc/my.cnf
[mysqld]
# skip-grant-tables

systemctl restart mysqld

2.4 登录MySQL

登录MySQL的基本语法(扩展)：

mysql -P 3306 -u root -p‘password’ mysql -e 'show tables'

参数详解：

• -h：指定主机名（默认为localhost）
• -P：MySQL服务器端口（默认3306）
• -u：指定用户名（默认root）
• -p：指定登录密码（默认为空密码）
• mysql：指定登录的数据库
• -e：接SQL语句

示例：

3. MySQL权限原理深入剖析

权限授予的基本语法：

grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified by '密码' with option参数];

3.1 权限列表详解

• ALL：所有权限（不包括授权权限）
• SELECT：查询权限
• INSERT：插入权限
• UPDATE：更新权限
• DELETE：删除权限
• CREATE：创建权限
• ALTER：修改权限
• DROP：删除权限
• GRANT OPTION：授权权限
• SUPER：超级权限
• RELOAD：重载权限
• SHUTDOWN：关闭服务权限
• PROCESS：查看进程权限
• FILE：文件操作权限

3.2 数据库.表名详解

• *.*：所有库下的所有表
• web.*：web库下的所有表
• web.stu_info：web库下的stu_info表
• 列级别权限：GRANT SELECT (id), INSERT (name,age) ON mydb.mytb to 'user8'@'localhost' identified by 'user8@123';

3.3 客户端主机详解

• %：所有主机
• 192.168.2.%：192.168.2.0网段的所有主机
• 192.168.2.168：指定主机
• localhost：本地主机
• 127.0.0.1：本地回环地址

3.4 with_option参数详解

• GRANT OPTION：授权选项，允许用户将自己的权限授予其他用户
• MAX_QUERIES_PER_HOUR：每小时最大查询次数
• MAX_UPDATES_PER_HOUR：每小时最大更新次数
• MAX_CONNECTIONS_PER_HOUR：每小时最大连接次数
• MAX_USER_CONNECTIONS：用户最大连接数

4. MySQL权限示例详细操作

4.1 赋予权限

授权目标：授予admin3对bbs库所有的表，具有所有权限（不包含授权）

操作步骤：

— 创建用户
CREATE USER admin3@'%' IDENTIFIED BY 'Abc@1234';

— 验证用户创建成功
SELECT user, host FROM mysql.user WHERE user = 'admin3';

— 授权
GRANT ALL ON bbs.* TO admin3@'%' IDENTIFIED BY 'Abc@1234';

— 验证授权成功
SHOW GRANTS FOR admin3@'%'\\G;

# 登录测试账户
[root@localhost ~]# mysql -uadmin3 -p'Abc@1234'

# 创建bbs数据库
mysql> create database bbs;

# 尝试创建其他数据库（应该失败）
mysql> create database bba;
ERROR 1044 (42000): Access denied for user 'admin3'@'%' to database 'bba'

# 进入bbs库
mysql> USE bbs;

# 创建表
mysql> CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));

# 插入数据
mysql> INSERT INTO users (name) VALUES ('test');

# 查询数据
mysql> SELECT * FROM users;

# 更新数据
mysql> UPDATE users SET name='test2' WHERE id=1;

# 删除数据
mysql> DELETE FROM users WHERE id=1;

4.2 回收权限

查看权限：

• 查看自己的权限：

  SHOW GRANTS\\G

• 查看别人的权限：

  SHOW GRANTS FOR admin3@'%'\\G

回收权限：

语法：

REVOKE 权限列表 ON 数据库名 FROM 用户名@'客户端主机'

示例：

删除用户：

— 删除用户
DROP USER admin3@'%';

— 验证用户删除
SELECT user, host FROM mysql.user WHERE user = 'admin3';

版本差异：

• MySQL 5.6之前：需要先回收权限，再删除用户
• MySQL 5.7之后：可以直接删除用户，会自动回收权限

二、日志管理深度解析

1. 日志分类及作用

MySQL的日志主要分为以下几类，每类日志都有其特定的作用：

2. Error Log（错误日志）

2.1 配置错误日志

编辑配置文件：

vim /etc/my.cnf

添加配置：

[mysqld]
log-error=/var/log/mysqld.log

参数说明：

• log-error：指定错误日志文件路径

2.2 查看错误日志

查看错误日志内容：

tail -n 50 /var/log/mysqld.log

常见错误类型：

• 启动失败：端口被占用、配置文件错误
• 连接失败：权限问题、网络问题
• 运行错误：SQL语法错误、表损坏

错误日志分析：

• 查找关键字：ERROR、WARNING、Note
• 关注时间戳，确定错误发生时间
• 结合上下文分析错误原因

3. Binary Log（二进制日志）

3.1 开启二进制日志

编辑配置文件：

vim /etc/my.cnf

添加配置：

[mysqld]
log_bin=/var/lib/mysql/mysql-bin
server-id=2

参数说明：

• log_bin：指定二进制日志文件路径，不加路径则默认在数据目录
• server-id：服务器唯一标识，在复制环境中必须设置

重启MySQL服务：

# systemctl restart mysqld

3.2 查看二进制日志

查看二进制日志文件列表：

# ls -l /var/lib/mysql/*bin*

查看二进制日志内容：

# mysqlbinlog -v /var/lib/mysql/mysql-bin.000001

参数说明：

• -v：显示详细内容
• -s：简化输出
• –start-datetime：指定开始时间
• –stop-datetime：指定结束时间
• –start-position：指定开始位置
• –stop-position：指定结束位置

3.3 二进制日志的作用

3.4 二进制日志管理

查看当前二进制日志文件：

SHOW MASTER STATUS;

刷新二进制日志：

FLUSH LOGS;

删除二进制日志：

— 删除指定日期之前的日志
PURGE BINARY LOGS BEFORE '2026-01-01 00:00:00';

— 删除指定文件之前的日志
PURGE BINARY LOGS TO 'mysql-bin.000005';

— 保留指定天数的日志
SET GLOBAL expire_logs_days = 7;

4. Slow Query Log（慢查询日志）

4.1 开启慢查询日志

编辑配置文件：

vim /etc/my.cnf

添加配置：

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/slow-query.log
long_query_time=1
log_queries_not_using_indexes=1

参数说明：

• slow_query_log：开启慢查询日志
• slow_query_log_file：指定慢查询日志文件路径
• long_query_time：慢查询阈值，单位秒
• log_queries_not_using_indexes：记录未使用索引的查询

重启MySQL服务：

systemctl restart mysqld

4.2 查看慢查询日志

查看慢查询日志文件：

ls -l /var/lib/mysql/*slow*

查看慢查询日志内容：

tail -n 50 /var/lib/mysql/slow-query.log

4.3 分析慢查询日志

使用mysqldumpslow工具：

mysqldumpslow -s t /var/lib/mysql/slow-query.log

参数说明：

• -s：排序方式，t=按时间，l=按锁时间，r=按返回记录数
• -t：显示前N条
• -g：正则表达式过滤

示例：

# 查看执行时间最长的10条慢查询
mysqldumpslow -s t -t 10 /var/lib/mysql/slow-query.log

# 查看包含特定表的慢查询
mysqldumpslow -s t -t 10 -g 'users' /var/lib/mysql/slow-query.log

4.4 模拟慢查询

使用BENCHMARK函数：

— 执行5亿次2*3运算，模拟慢查询
SELECT BENCHMARK(500000000,2*3);

使用SLEEP函数：

— 休眠5秒，模拟慢查询
SELECT SLEEP(5);

三、实践案例深度解析

案例1：为应用创建专用数据库用户

需求：为Web应用创建一个专用用户，只允许访问指定数据库，具有增删改查权限，且只能从指定网段登录。

步骤：

案例2：配置慢查询日志优化性能

需求：配置慢查询日志，捕获执行时间超过1秒的SQL语句，分析并优化慢查询。

步骤：

案例3：使用二进制日志恢复数据

需求：误删除了表数据，需要使用二进制日志恢复。

步骤：后续文章讲解

四、最佳实践与性能优化

权限管理最佳实践

日志管理最佳实践

性能优化建议

五、常见问题与解决方案

权限管理常见问题

日志管理常见问题

六、总结

本文深度解析了MySQL DBA运维实战中DCL的核心知识点，包括：

随着MySQL的不断发展，其权限管理和日志管理功能也在不断完善。未来，MySQL可能会：

我们需要不断学习和掌握这些新功能，以适应数据库技术的发展，为企业提供更安全、更高效、更可靠的数据库服务。

DCL的核心知识点是MySQL DBA必备的技能，包括权限管理和日志管理两大部分。在实际运维工作中，要灵活运用这些知识，结合具体场景进行配置和管理，才能确保MySQL数据库的安全、稳定和高效运行。

📌 本人博客原文链接：Breeze