开发者空间里玩转MySQL之安全与备份

1 概述

1.1 案例介绍

MySQL安全指通过权限管理、访问控制、数据加密等措施保护数据库免受攻击或泄露。MySQL备份是通过全量/增量备份策略（如mysqldump）定期保存数据副本，确保故障时快速恢复。

数据库安全与备份是保障数据完整性和业务连续性的关键机制。MySQL 通过完善的安全控制和灵活的备份策略，确保企业数据资产受到保护，是构建高可靠、高可用应用的基础保障。

1.2 适用对象

• 个人开发者
• 高校学生

1.3 案例时间

本案例总时长预计40分钟。

1.4 案例流程

{{{width="60%" height="auto"}}}

说明：

1. 领取空间开发桌面；
2. 在空间开发桌面终端进入MySQL；
3. 进行数据库的开发者空间进行MySQL之安全与备份的操作。

1.5 资源总览

本案例预计花费总计0元。

资源名称	规格	单价（元）	时长（分钟）
开发者空间 - 空间开发桌面	ARM| 4 vCPUs 8GB | Ubuntu	0	40

2 安全管理

MySQL 权限管理系统是一个多层次的访问控制机制，用于管理用户对数据库对象的操作权限。

核心要点：

权限体系结构：

• 验证阶段：验证用户身份（用户名+密码+主机）
• 授权阶段：检查用户对特定对象的操作权限

权限层级：

• 全局权限：作用于所有数据库（存储在mysql.user表）
• 数据库级权限：作用于特定数据库（mysql.db）
• 表级权限：作用于特定表（mysql.tables_priv）
• 列级权限：作用于特定列（mysql.columns_priv）
• 例程权限：作用于存储过程和函数（mysql.procs_priv）

核心操作命令

• 用户管理
• 权限控制
• 权限刷新

常见权限类型

• 数据操作：SELECT, INSERT, UPDATE, DELETE
• 结构操作：CREATE, ALTER, DROP, INDEX
• 管理权限：GRANT OPTION, SUPER, PROCESS
• 特殊权限：ALL PRIVILEGES (所有权限), USAGE (无权限)

2.1 权限表

2.1.1 知识点内容：

MySQL 8+ 使用一系列系统表来存储权限信息，主要包括：

权限表	存储内容	关键字段
mysql.user	用户账户信息 全局权限（适用于所有数据库） 安全配置（密码过期、账户锁定等）	Host, User - 组成用户标识 Select_priv, Insert_priv 等 - 各种全局权限 authentication_string - 加密后的密码（MySQL 8.0+） password_expired, account_locked - 账户状态
mysql.db	数据库级别的权限 指定用户对特定数据库的权限	Host, Db, User - 组成权限标识 各种数据库操作权限字段（与user表类似）
mysql.tables_priv	表级别的权限 包括视图的权限	Host, Db, User, Table_name Table_priv - 表级权限（如Select, Insert等） Column_priv - 列级权限（位掩码）
mysql.columns_priv	列级别的权限 控制用户对表中特定列的访问	Host, Db, User, Table_name, Column_name Column_priv - 具体列权限
mysql.procs_priv	存储过程和函数的权限 控制谁可以执行或修改存储程序	Host, Db, User, Routine_name, Routine_type Proc_priv - 执行/修改权限

2.1.2 其他相关表：

• global_grants (MySQL 8.0+)：存储动态全局权限分配
• default_roles：存储用户的默认角色（MySQL 8.0+角色功能）
• role_edges：存储角色层次结构关系

2.1.3 权限表工作原理：

• 权限检查顺序：

  先检查user表中的全局权限

  然后检查db表的数据库权限

  接着检查tables_priv和columns_priv

  最后检查procs_priv（针对存储过程）

• 权限合并规则：

权限是叠加的，不是覆盖的

用户在某级别获得的权限不会被更高级别的REVOKE撤销

2.2 用户管理

2.2.1 创建用户

基础创建语法：

CREATE USER [IF NOT EXISTS] 'username'@'host_identifier' 
IDENTIFIED [WITH authentication_plugin] BY 'password';

完整语法结构

CCREATE USER [IF NOT EXISTS]  -- 如果用户不存在才创建
    'username'@'host_identifier'  -- 用户名@主机限制
    [IDENTIFIED [WITH authentication_plugin] [BY 'password']]  -- 认证方式
    | [USING 'auth_string']  -- 替代认证字符串
    [DEFAULT ROLE role [, role] ...]  -- 默认角色(MySQL 8.0+)
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]  -- 连接安全要求
    [WITH resource_option [resource_option] ...]  -- 资源限制
    [password_option | lock_option]  -- 密码策略/锁定状态
    [COMMENT 'comment_string']  -- 注释信息
[ATTRIBUTE 'json_object']  -- 自定义属性(8.0.21+)

参数说明：

语法元素	说明	示例
IF NOT EXISTS	仅当用户不存在时创建	CREATE USER IF NOT EXISTS 'user1'@'%'
'user'@'host'	用户标识，host可用%通配	'admin'@'192.168.1.%'
IDENTIFIED BY	设置密码	IDENTIFIED BY 'pass123'
WITH plugin	指定认证插件	WITH caching_sha2_password
DEFAULT ROLE	设置默认角色	DEFAULT ROLE 'read_role'
REQUIRE SSL	强制SSL连接	REQUIRE SSL AND X509
WITH resource	资源限制	WITH MAX_QUERIES_PER_HOUR 100
PASSWORD EXPIRE	密码过期策略	PASSWORD EXPIRE INTERVAL 90 DAY
ACCOUNT LOCK	账户锁定状态	ACCOUNT LOCK
COMMENT	添加注释	COMMENT 'API account'
ATTRIBUTE	自定义属性	ATTRIBUTE '{"dept":"IT"}'

MySQL 8+ 支持的认证插件：

• caching_sha2_password (默认)
• mysql_native_password (传统兼容)
• sha256_password (更安全的SHA-256)

语句示例：

-- 1. 基本用户创建
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'SecurePass123!';
-- 2. 允许远程连接的用户
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'NetworkPass456!';
-- 3. 使用特定认证插件
CREATE USER 'secure_user'@'localhost'
IDENTIFIED WITH caching_sha2_password BY 'StrongPass789!';
-- 4. 带账户选项的用户
CREATE USER 'temp_user'@'192.168.1.%'
IDENTIFIED BY 'TempPass123!'
PASSWORD EXPIRE INTERVAL 90 DAY
ACCOUNT LOCK;
-- 5. 使用SSL/TLS要求的用户
CREATE USER 'secure_admin'@'%'
IDENTIFIED BY 'AdminPass123!'
REQUIRE SSL;

操作结果：

添加结果：

SELECT User, Host FROM mysql.user;

适用场景：

• 为新应用程序创建专用用户
• 为不同服务创建隔离的数据库账户

注意事项：

• 主机部分可以是 IP、主机名或通配符（%表示任意主机）
• MySQL 8+ 默认使用 caching_sha2_password 认证插件

2.2.2 删除用户

语法：

DROP USER 'username'@'host';

语句示例：

DROP USER 'secure_admin'@'%';

操作结果：

注意事项：

• 删除用户不会自动删除该用户创建的对象
• 需要确保没有活动连接使用该账户

2.2.3 修改用户账号

语法：

RENAME USER 'old_user'@'host' TO 'new_user'@'host';

语句示例：

SELECT User, Host FROM mysql.user;

-- 修改
RENAME USER ' temp_user'@'192.168.1.%' TO 'temp_user_change'@'192.168.1.%';

-- 查看修改结果
SELECT User, Host FROM mysql.user;

操作结果：

2.2.4 修改用户口令

语法：

ALTER USER [IF EXISTS]  -- 如果用户存在才修改
    'username'@'host_identifier'  -- 要修改的用户
    [IDENTIFIED [WITH authentication_plugin] [BY 'password']]  -- 修改认证方式
    | [USING 'auth_string']  -- 修改认证字符串
    [DEFAULT ROLE role [, role] ...]  -- 修改默认角色
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]  -- 修改连接安全要求
    [WITH resource_option [resource_option] ...]  -- 修改资源限制
    [password_option | lock_option]  -- 修改密码策略/锁定状态
    [COMMENT 'comment_string']  -- 修改注释
[ATTRIBUTE 'json_object']  -- 修改属性

参数说明：

语法元素	说明	示例
IF EXISTS	仅当用户不存在时创建	ALERT USER IF EXISTS 'user1'@'%'
'user'@'host'	用户标识，host可用%通配	'admin'@'192.168.1.%'
IDENTIFIED BY	设置密码	IDENTIFIED BY 'pass123'
WITH plugin	指定认证插件	WITH caching_sha2_password
DEFAULT ROLE	设置默认角色	DEFAULT ROLE 'read_role'
REQUIRE SSL	强制SSL连接	REQUIRE SSL AND X509
WITH resource	资源限制	WITH MAX_QUERIES_PER_HOUR 100
PASSWORD EXPIRE	密码过期策略	PASSWORD EXPIRE INTERVAL 90 DAY
ACCOUNT LOCK	账户锁定状态	ACCOUNT LOCK
COMMENT	添加注释	COMMENT 'API account'
ATTRIBUTE	自定义属性	ATTRIBUTE '{"dept":"IT"}'

示例：

-- 修改前
SELECT 
    User, 
    Host, 
    plugin,
    authentication_string,
    password_last_changed AS 最后修改时间,
    password_expired AS 是否过期,
    password_lifetime AS 有效期
FROM mysql.user 
WHERE User='remote_user';

-- 修改
ALTER USER 'remote_user'@'%' IDENTIFIED BY '456!NetworkPass';

-- 修改后
SELECT 
    User, 
    Host, 
    plugin,
    authentication_string,
    password_last_changed AS 最后修改时间,
    password_expired AS 是否过期,
    password_lifetime AS 有效期
FROM mysql.user 
WHERE User='remote_user';

注：在MySQL中，出于安全考虑，密码是以加密哈希值存储的，无法直接查看明文密码。

操作结果：

注意事项：

• 权限要求：执行这些操作通常需要CREATE USER和UPDATE权限
• 主机名部分：如果用户限制特定主机访问，必须准确指定主机名部分
• 密码强度：MySQL 8默认启用密码验证插件，简单密码可能被拒绝
• 生效时间：修改后，现有连接可能需要重新认证才会生效
• 最佳实践：修改重要账户前，建议先创建备份账户：

CREATE USER 'backup_admin'@'localhost' IDENTIFIED BY 'backup_password';
GRANT ALL PRIVILEGES ON *.* TO 'backup_admin'@'localhost';

• 查看修改结果：

SELECT * FROM mysql.user WHERE User='用户名';

2.3 权限管理

2.3.1 授予权限

语法：

GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_or_role [, user_or_role] ...
    [WITH GRANT OPTION]
    [AS user [WITH ROLE role]]

常用权限类型：

权限类型	说明
ALL [PRIVILEGES]	所有权限(不包括GRANT OPTION)
ALTER	修改表结构
CREATE	创建数据库/表
DELETE	删除数据
DROP	删除数据库/表
EXECUTE	执行存储过程/函数
INDEX	创建/删除索引
INSERT	插入数据
SELECT	查询数据
UPDATE	更新数据
SHOW VIEW	查看视图定义
GRANT OPTION	允许授予权限给他人
PROCESS	查看服务器进程
RELOAD	执行FLUSH操作

示例：

-- 1. 授予数据库级别权限
GRANT 权限类型 ON 数据库名.* TO '用户名'@'主机名';

-- 2. 授予表级别权限
GRANT 权限类型 ON 数据库名.表名 TO '用户名'@'主机名';

-- 3. 授予存储过程和函数权限
GRANT EXECUTE ON PROCEDURE 数据库名.存储过程名 TO '用户名'@'主机名';
GRANT EXECUTE ON FUNCTION 数据库名.函数名 TO '用户名'@'主机名';

-- 4. 授予全局权限
GRANT 权限类型 ON *.* TO '用户名'@'主机名';

-- 5. 授予角色权限
GRANT 角色名 TO '用户名'@'主机名';

-- 6. 特殊选项
-- WITH GRANT OPTION
-- 允许用户将自己拥有的权限授予其他用户
GRANT SELECT ON mydb.* TO 'manager'@'%' WITH GRANT OPTION;

-- AS 子句(MySQL 8.0+)
-- 以其他用户的权限执行授权
GRANT SELECT ON mydb.* TO 'user1'@'%' AS 'admin'@'%';

-- 权限生效
-- 授予权限后，通常需要执行：
FLUSH PRIVILEGES;

-- 查看已授予的权限
SHOW GRANTS FOR '用户名'@'主机名';

语句示例：

-- 1.应用用户 - 授予本地应用用户对app_database数据库的完整数据操作权限，包括执行存储过程的能力。
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE 
ON app_database.* 
TO 'app_user'@'localhost';

-- 2. 远程用户 - 允许远程用户读取整个分析数据库
GRANT SELECT ON analytics.* 
TO 'remote_user'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

操作结果：

注意事项：

• 遵循最小权限原则，只授予必要的权限
• 避免使用ALL PRIVILEGES和*.*组合
• 对生产环境使用特定主机限制('%'要谨慎使用)
• 使用角色管理权限组，简化权限管理
• 定期审查权限分配情况

2.3.2 权限的撤销

语法：

REVOKE [权限类型] ON [数据库对象] FROM '用户名'@'主机';

常见权限撤销场景

-- 1. 撤销特定权限（保留其他权限）
-- 撤销INSERT权限
REVOKE INSERT ON app_database.* FROM 'app_user'@'localhost';
-- 撤销多个权限
REVOKE INSERT, DELETE ON app_database.* FROM 'app_user'@'localhost';

-- 2. 撤销所有数据库权限（保留USAGE权限）
REVOKE ALL PRIVILEGES ON app_database.* FROM 'app_user'@'localhost';

-- 3. 完全撤销用户所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'app_user'@'localhost';

-- 4. 撤销存储过程/函数权限
REVOKE EXECUTE ON PROCEDURE db_name.procedure_name FROM 'user'@'host';

-- 刷新权限使更改生效：
FLUSH PRIVILEGES;
-- 验证权限是否已撤销：
SHOW GRANTS FOR 'app_user'@'localhost';

特殊权限撤销：

-- 1. 撤销表级权限
REVOKE SELECT ON database_name.table_name FROM 'user'@'host';
-- 2. 撤销列级权限
REVOKE SELECT(column1, column2) ON database_name.table_name FROM 'user'@'host';
-- 3. 撤销代理权限
REVOKE PROXY ON 'from_user'@'from_host' FROM 'to_user'@'to_host';

示例：

-- 1. 查看当前权限
SHOW GRANTS FOR 'remote_user'@'%';

-- 2. 删除 analytics 数据库的 SELECT 权限
REVOKE SELECT ON analytics.* FROM 'remote_user'@'%';

-- 3. 刷新权限
FLUSH PRIVILEGES;

-- 4. 再次验证
SHOW GRANTS FOR 'remote_user'@'%';

操作结果：

注意事项：

• USAGE权限是默认权限，表示"无权限"，无法被撤销
• 如果用户是通过角色获得的权限，需要先撤销角色授权
• 生产环境操作前建议备份用户权限数据
• 权限变更可能影响正在运行的应用程序，建议在维护窗口操作

3 备份与恢复

3.1 备份和恢复概述

知识点内容：

MySQL 备份主要分为：

• 逻辑备份：SQL 语句形式（如 mysqldump）
• 物理备份：直接复制数据文件
• 增量备份：仅备份变更数据

适用场景：

• 逻辑备份适合小型数据库和跨版本迁移
• 物理备份适合大型数据库快速恢复
• 增量备份适合频繁变更的大型数据库

注意事项：

• 制定定期备份策略
• 测试备份恢复流程
• 考虑备份文件的安全存储

3.2 备份数据

3.2.1 表数据导出

表数据导出

语法：

SELECT [列名] FROM 表名
INTO OUTFILE '文件路径'
[FIELDS
    [TERMINATED BY '分隔符']
    [[OPTIONALLY] ENCLOSED BY '包围符']
    [ESCAPED BY '转义符']
]
[LINES
    [TERMINATED BY '行结束符']
]

示例：

-- 导出enrollment表数据
SELECT * FROM university.enrollment
    INTO OUTFILE '/var/lib/mysql-files/enrollment_export.csv'
    FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"'
LINES TERMINATED BY '\n';

操作结果：

使用技巧：

1. 条件导出（特定学期且成绩优秀的选课记录）

SELECT * FROM enrollment
WHERE semester = '2025 Spring' AND grade >= 90.0
INTO OUTFILE '/var/lib/mysql-files/honor_roll_2025spring.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

2. 选择特定列导出（精简版选课记录）

SELECT enrollment_id, student_id, course_id, semester
FROM enrollment
INTO OUTFILE '/var/lib/mysql-files/enrollment_basic.tsv'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n';

3. 自定义格式导出（成绩报告格式）

SELECT 
    enrollment_id,
    student_id,
    course_id,
    semester,
    IFNULL(grade, '未出成绩') AS 成绩,
    DATE_FORMAT(enrollment_date, '%Y年%m月%d日 %H:%i') AS 选课时间
FROM enrollment
INTO OUTFILE '/var/lib/mysql-files/grade_report.csv'
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

4. 导出复杂查询结果（各课程平均成绩统计）

SELECT 
    c.course_id,
    c.course_name,
    COUNT(e.student_id) AS 选课人数,
    AVG(e.grade) AS 平均成绩,
    MAX(e.grade) AS 最高分,
    MIN(e.grade) AS 最低分
FROM enrollment e
JOIN course c ON e.course_id = c.course_id
WHERE e.grade IS NOT NULL
GROUP BY c.course_id, c.course_name
INTO OUTFILE '/var/lib/mysql-files/course_stats.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

5. 分批导出大数据（按学生ID范围）

-- 第一段: ID以A-M开头的学生
SELECT * FROM enrollment
WHERE student_id REGEXP '^[A-M]'
INTO OUTFILE '/var/lib/mysql-files/enrollment_part1.csv';

-- 第二段: ID以N-Z开头的学生
SELECT * FROM enrollment
WHERE student_id REGEXP '^[N-Z]'
INTO OUTFILE '/var/lib/mysql-files/enrollment_part2.csv';

注意事项：

• 实际使用时请先确认secure_file_priv设置：

SHOW VARIABLES LIKE 'secure_file_priv';

• 对于包含中文的数据，建议指定字符集：utf8mb4
• 如果目标文件已存在，导出会失败

3.2.2 使用mysqldump命令备份数据

基础语法：

mysqldump [options] database [tables];

语句示例：

# 导出整个数据库
sudo mysqldump -u root -p university --result-file=/var/lib/mysql-files/university.sql

# 导出特定表
sudo mysqldump -u root -p university enrollment --result-file=/var/lib/mysql-files/enrollment_backup.sql

# 只导出数据（不包含表结构）
sudo mysqldump -u root -p --no-create-info university enrollment --result-file=/var/lib/mysql-files/enrollment_data.sql

# 只导出表结构
sudo mysqldump -u root -p --no-data university enrollment --result-file=/var/lib/mysql-files/enrollment_schema.sql

# 导出为CSV格式
sudo mysqldump -u root -p --tab=/var/lib/mysql-files/ --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\n' university enrollment

# 按条件导出
sudo mysqldump -u root -p --where="semester='2025 Spring'" university enrollment --result-file=/var/lib/mysql-files/2025_Spring_enrollments.sql

操作结果：

查看导出结果：

sudo ls -la /var/lib/mysql-files/

适用场景：

• 定期完整数据库备份
• 迁移数据库到其他服务器
• 创建数据库快照

3.3 恢复数据

为避免前面的存储过程或者触发器造成恢复数据运行异常，可先全部去除存储函数和触发器：

-- 当前数据库所有存储函数；
SHOW PROCEDURE STATUS WHERE Db = DATABASE();
-- 删除语句示例
DROP PROCEDURE IF EXISTS add_student_account;
DROP PROCEDURE IF EXISTS enroll_student;
DROP PROCEDURE IF EXISTS update_student_age;
DROP PROCEDURE IF EXISTS update_student_info;

-- 动态生成删除触发器语句
SELECT CONCAT('DROP TRIGGER IF EXISTS `', TRIGGER_NAME, '`;') 
FROM INFORMATION_SCHEMA.TRIGGERS 
WHERE TRIGGER_SCHEMA = DATABASE();
-- 生成的语句示例
DROP TRIGGER IF EXISTS `check_course_capacity`;
DROP TRIGGER IF EXISTS `update_course_enrollment`;
DROP TRIGGER IF EXISTS `after_enrollment_insert`;

在 MySQL 命令行中恢复数据有几种常用方法：

3.3.1 使用 source 命令恢复 SQL 备份文件

-- 登录 MySQL
mysql -u root -p

-- 选择数据库
USE university;

-- 查看当前数据
select count(*) from enrollment;
-- 清空数据
TRUNCATE TABLE enrollment;

-- 执行恢复（假设文件在 /var/lib/mysql-files/ 目录）
SOURCE /var/lib/mysql-files/enrollment_backup.sql;

-- 查看恢复数据
select count(*) from enrollment;

操作结果：

3.3.2 使用 LOAD DATA INFILE 恢复 CSV 数据

-- 查看当前数据
select count(*) from enrollment;
-- 清空数据
TRUNCATE TABLE enrollment;

-- 执行 CSV 文件导入
LOAD DATA INFILE '/var/lib/mysql-files/enrollment_export.csv'
INTO TABLE enrollment
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- 查看恢复数据
select count(*) from enrollment;

操作结果：

3.3.3 直接执行 SQL 文件（不进入交互模式）

在mysql中先清空表

-- 查看当前数据
select count(*) from enrollment;
-- 清空数据
TRUNCATE TABLE enrollment;
-- 查看删除后数据
select count(*) from enrollment;

新打开一个终端

# 直接在终端执行（不需要进入 MySQL 命令行）
sudo sh -c 'mysql -u root -p university < /var/lib/mysql-files/enrollment_backup.sql'

在mysql中查看恢复后数据

-- 查看恢复后数据
select count(*) from enrollment;