zheng的个人博客

Mysql权限管理

作者：

zhengadmin

在

用户权限介绍

  • 权限
INSERT,SELECT, 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
  • 每次设定只能有一个属主，没有属组或其他用户的概念
grant     all privileges    on     *.*    to   user01@''192.168.226.%''  identified by    ''123'';
                权限               作用对象          归属               密码

作用对象分解

  • . [当前MySQL实例中所有库下的所有表]
  • wordpress.* [当前MySQL实例中wordpress库中所有表（单库级别）]
  • wordpress.user [当前MySQL实例中wordpress库中的user表（单表级别）]

企业中权限的设定

  • 开发人员说：给我开一个用户
  • 沟通
    • 你需要对哪些库、表进行操作
    • 你从哪里连接过来
    • 用户名有没有要求
    • 密码要求
    • 发邮件
  • 一般给开发创建用户权限
grant select,update,delete,insert on *.* to 'user01'@'192.168.226.%' identified by '123456';

实验思考问题

#创建wordpress数据库
create database wordpress;
#使用wordpress库
use wordpress;
#创建t1、t2表
create table t1 (id int);
create table t2 (id int);
#创建blog库
create database blog;
#使用blog库
use blog;
#创建t1表
create table tb1 (id int);

授权

1、grant select on *.* to wordpress@'192.168.226.%' identified by '123';
2、grant insert,delete,update on wordpress.* to wordpress@'192.168.226.%' identified by '123';
3、grant all on wordpress.t1 to wordpress@'192.168.226.%' identified by '123';

模拟企业真实场景下，DBA（数据库管理员）为开发人员创建专属数据库、建表，并精细化分配权限。

第一阶段：初始化环境与建库建表（准备“空房子”）

在给开发人员授权之前，DBA 需要先把基础设施搭好。

  1. 创建数据库
    • create database wordpress;
    • create database blog;
    • 作用： 在 MySQL 实例中创建两个独立的业务库（模拟企业里不同的项目）。这就好比建了两栋独立的办公楼。
  2. 切换并建表
    • use wordpress;
    • create table t1 (id int);
    • create table t2 (id int);
    • use blog;
    • create table tbl (id int);
    • 作用： 进入具体的库（办公楼），并在里面建好具体的表（房间）。

第二阶段：核心授权操作（给开发人员发“门禁卡”）

这是你实验的重点。企业里开发人员来了，DBA 不能直接给 root 账号，必须遵循最小权限原则。你通过 grant 语句逐步开放权限。

代码 1： grant select on *.* to wordpress@'192.168.226.%' identified by '123';

  • 作用拆解：

    • grant select：赋予查询权限。
    • on *.*：作用于所有数据库的所有表（全局级别）。
    • to wordpress@'192.168.226.%'：创建/指定一个名为 wordpress 的用户，且限制只能从 192.168.226.x这个网段连接（通过IP限制来源，比允许任何IP安全）。
    • identified by '123'：设置该用户的密码为 123。

  • 业务含义： 这一步给了开发人员查看全局数据的权限，通常用于排查问题时查阅基础数据，但还没有修改数据的权利。

代码 2： grant insert,delete,update on wordpress.* to wordpress@'192.168.226.%' identified by '123';

  • 作用拆解：
    • insert,delete,update：赋予增、删、改权限（CRUD 中的 CUD）。
    • on wordpress.*：缩小范围，只针对 wordpress 这个库下的所有表。
  • 业务含义： 开发人员开始在自己的业务库（wordpress）里写代码了，他需要能修改自己业务库的数据。注意，他依然没有权限修改 blog 库的数据。

代码 3： grant all on wordpress.t1 to wordpress@'192.168.226.%' identified by '123';

  • 作用拆解：
    • grant all：赋予所有权限（包括建表、删表、增删改查等）。
    • on wordpress.t1：精确到表级别，只对 wordpress 库里的 t1 这张表生效。
  • 业务含义： 这就是最小权限原则的体现。可能 t1 表是一个非常核心的配置表，开发人员需要最高权限去维护它，但同一个库里的 t2 表（可能涉及敏感数据）他依然只能用代码 2 里给的基础权限。

代码 4：刷新权限 flush privileges;

  • 作用： 虽然用 grant 语句授权通常会自动刷新内存，但在企业操作规范中，执行完一整套权限变更后，执行 flush privileges; 是一个好习惯，确保权限立即生效。

第三阶段：验证测试（以开发人员身份登录）

授权做完了，必须验证是否生效。终端截图展示了这个验证过程：

  1. 尝试登录： mysql -uwordpress -p123（不带IP或带 -h localhost）

    • 结果： ERROR 1045 (28000): Access denied
    • 原因： 因为我们之前授权时，写的是 @'192.168.226.%'。当你直接本机登录（默认走localhost）时，MySQL 认为你不在授权的网段内，所以拒绝。

  2. 按授权规则登录： mysql -uwordpress -p123 -h 192.168.226.10

    • 结果： 成功进入 MySQL 命令行。
    • 原因： -h 指定了 192.168.226.10 这个 IP，完全匹配了之前的网段授权。

  3. 权限验证：

    • 进去后执行 use wordpress; 成功切换。
    • 执行 show tables; 成功看到了 t1 和 t2（因为有 select 权限）。
    • 执行 select * from t1; 成功读取数据（因为没有写入测试，所以显示 Empty set，但不报权限错误，说明查询权限正常）。

    image-20261011225042032

image-20261011230950981

权限级别（全局、库级、表级）和具体操作权限

在企业级 MySQL权限管理中，这三条命令演示了基于角色的访问控制（RBAC）和最小权限原则的落地过程。

第一条：全局只读权限（Global Read-Only）

grant select on *.* to wordpress@'192.168.226.%' identified by '123';
  • 权限范围：*.* 代表全局级别（Global Level），即当前 MySQL 实例中的所有数据库和所有表。
  • 赋予的操作：select。仅允许执行 SELECT 查询语句。
  • 安全策略（Host 限制）：@'192.168.226.%' 限制了该账号的登录来源 IP网段，非该网段的客户端连接会被直接拒绝。
  • DBA 视角的业务意义：为开发人员提供全局只读权限。这通常用于排查跨库的数据依赖问题，或进行全局数据抽取，但==严格禁止其对生产环境做任何数据变更（DML）或结构变更（DDL）==。

第二条：库级读写权限（Database-Level Read/Write）

grant insert,delete,update on wordpress.* to wordpress@'192.168.226.%' identified by '123';
  • 权限范围：wordpress.* 代表库级别（Database Level），权限被精确收敛到 wordpress 这一个数据库下的所有表。
  • 赋予的操作：insert（插入）、delete（删除）、update（更新）。结合第一条命令，该用户在 wordpress 库中具备了完整的 DML（数据操纵语言） 权限（CRUD）。
  • DBA视角的业务意义：满足开发人员对自身业务库进行日常数据维护的需求。同时，由于权限范围限制在 wordpress.*，该用户依然无法修改其他业务库（如 blog 库）的数据，实现了==业务隔离==。

第三条：表级全权控制（Table-Level Full Control）

grant all on wordpress.t1 to wordpress@'192.168.226.%' identified by '123';
  • 权限范围：wordpress.t1 代表表级别（Table Level），权限被进一步收敛到 wordpress 库中的 t1 这一张表。
  • 赋予的操作：all。代表该表上的所有可用权限，包括但不限于 DROP（删表）、ALTER（修改表结构）、CREATE（建索引）等 DDL（数据定义语言） 权限，以及所有 DML权限。
  • DBA视角的业务意义：应对特殊业务场景。例如，t1 可能是开发人员需要全权负责维护的临时表、日志表或核心配置表。==赋予 all 权限允许他们自主重建表结构或清空表数据==，而同一库中的其他表（如 t2）依然受限于第二条命令的普通读写权限。

综合权限模型分析

这三条命令构成了一个漏斗式的权限收敛模型：

  1. 全局层（*.*）：授予基础的查询权限，保证跨库查阅能力。
  2. 数据库层（wordpress.*）：授予业务所需的读写权限，满足日常开发需求。
  3. 数据表层（wordpress.t1）：授予特定表的完全控制权限，应对特殊维护需求。

重点： MySQL的权限是叠加（Union）生效的。该用户最终的权限是这三条命令权限的并集。这种分层授权既保证了开发人员有足够的权限完成工作，又严格限制了误操作或恶意操作波及的范围。

客户端连接测试以及权限验证的过程

image-20261011231551543

这一整套操作，就是标准的DBA 用户授权后的功能验证（Functional Testing），确保交给开发人员的账号是符合预期且安全的。

DBA 权限分配与验证 流程图

text

[阶段一：DBA 上帝视角] 
       │
       ▼
  mysql -uroot -p123456
       │
       ├─ 角色：数据库超级管理员（DBA）
       ├─ 权限：最高权限（建库、删库、建表、创建用户、分配权限等）
       └─ 现实意义：绝对保密，仅限核心 DBA 团队掌握，绝不外借给开发人员。
       │
       ▼
[阶段二：DBA 派发权限] 
       │
       ▼
  grant ... to 'wordpress'@'192.168.226.%' identified by '123';
       │
       ├─ 角色：DBA（执行者） <--> 开发人员（申请者）
       ├─ 操作：DBA 用 root 登录，执行 grant 命令
       │    ├─ 网络层授权：放行 @'192.168.226.%'（限制来源 IP 网段）
       │    └─ 数据层授权：分配特定库表的读写权限
       └─ 原则：最小权限原则（只给工作必需的权限，绝不轻易 grant all）
       │
       ▼
[阶段三：开发人员日常连接] 
       │
       ▼
  Navicat (输入主机 IP、账号 wordpress、密码 123)
       │
       ├─ 角色：开发人员
       ├─ 操作：使用图形化工具 Navicat 连接数据库
       ├─ 权限验证（连接成功即证明）：
       │    ├─ DBA 分配的网络权限（IP限制）生效了
       │    └─ DBA 分配的库表权限生效了
       └─ 后续限制（实验结论验证）：
            ├─ 尝试删掉 wordpress 库里的 t1 表 → 会被拒绝（无 DROP 权限）
            └─ 尝试修改 blog 库里的数据 → 会被拒绝（无跨库写权限）

逻辑架构视角（权限收敛漏斗）

如果你从系统架构的角度来看，这三步其实是一个权限逐级收敛、安全性逐级提高的过程：

text

===================== 权限边界 =====================
      【最高层】 root 账号 (localhost)
        └─> 拥有全部 DDL / DML / 授权 / 管理权限
                 │
                 │ (DBA 执行 grant 命令，向下派发)
                 ▼
      【中间层】 wordpress 账号 (192.168.226.% 网段)
        ├─> 全局层 (*.*) : 只有 SELECT (全局只读)
        ├─> 数据库层 (wordpress.*) : 拥有 INSERT, DELETE, UPDATE (库级读写)
        └─> 数据表层 (wordpress.t1) : 拥有 ALL (表级全权)
                 │
                 │ (开发人员用 Navicat 连接)
                 ▼
      【最底层】 开发人员实际可操作范围
        └─> 只能严格按照 DBA 分配的库表权限执行操作，多一步都做不了

核心总结（一句话牢记）

DBA用 root 手握全局，通过 grant 像做手术一样精细划分权限，最后开发人员只能用 wordpress 账号在划定的圈子里干活。 这就是企业级数据库安全管理的标准范式。

image-20261012000836323

image-20261012003911387

案件复盘：1142 报错背后的真相

线索一：操作者是谁？—— 身份决定权限

  • 表面现象：Navicat左侧连接名为 wordpress连接和权限测试，报错 INSERT command denied。
  • 底层本质：你这次没有用 root 越权操作，而是老老实实按规范使用了 wordpress 普通账号。MySQL 服务器精准识别了你的身份，并执行了权限判定。
  • 最终结论：系统“认号不认人”，你是普通用户，就只享有普通用户的待遇。

线索二：数据从哪来？—— 能看 ≠ 能改

  • 表面现象：表格里还能看到 10 和 111111，但当你想插入 888 时却被拒绝。
  • 底层本质：
    • 那两条数据是你（DBA）之前用 root 账号在 Linux 终端强行插入的。
    • 现在你能看见它们，是因为你拥有 grant select on *.* 的全局只读权限。
    • 但你现在改不动，是因为你没有 blog 库的写入权限。
  • 最终结论：允许跨库查数据辅助开发，严禁跨库篡改数据保证安全。

线索三：锅该谁背？—— 工具只是传话筒

  • 表面现象：你曾怀疑“是不是 Navicat 默认给了我权限？”
  • 底层本质：Navicat 只是将你的鼠标操作（点击保存）翻译成 INSERT SQL语句发送给服务器。服务器核对权限后拒绝，Navicat只是把错误原封不动地弹出来给你看。
  • 最终结论：Navicat只是传话筒，MySQL 才是铁面无私的判官。绝对不存在“工具绕过权限”一说。

MySQL连接管理

不建议使用的方法：

vim /etc/my.cnf
[client]
user = root
password = 123456

  • MySQL

    • -u:指定用户
    • -p:指定密码
    • -h:指定主机
    • -P:指定端口
    • -S:指定sock
    • -e:指定SQL

  • 第三方连接工具

    • sqlyog
    • navicat

1. 常见的几种登录方式

  • 方式一：明文密码（极度不推荐）
    • 命令：mysql -uroot -p123456
    • 特点：把密码直接写在命令里。虽然敲起来快，但极其危险。同一台服务器上的其他用户，只要执行 ps aux | grep mysql，就能直接看到你的密码明文。在生产环境绝对禁止。
  • 方式二：交互式输入密码（最推荐 ✅）
    • 命令：mysql -uroot -p
    • 特点：敲完回车后，系统会提示 Enter password:，此时你输入密码，屏幕不会显示任何字符（不显示星号，也不显示数字）。这种方式既安全（密码不会留在历史命令或进程里），又通用（不需要依赖任何配置文件）。
  • 方式三：依赖配置文件（图1里的写法，不推荐 ❌）
    • 命令：直接敲 mysql
    • 特点：这就是你刚才在 /etc/my.cnf 的 [client] 下面配置了 user 和 password 之后的效果。
    • 缺点：极度不安全。任何能登上这台服务器的人，只要敲 mysql 就能以 root 身份进去。生产环境严禁这么干。
  • 方式四：使用 mysql_config_editor（企业级安全用法）
    • 命令：先执行 mysql_config_editor set --login-path=local --host=localhost --user=root --password，然后以后登录只需敲 mysql --login-path=local。
    • 特点：这是 MySQL官方提供的加密登录方式。密码会被加密存储在一个 .mylogin.cnf 文件里，比明文写在配置文件里安全得多。企业里用得多。

image-20261012013226239

image-20261012013258341

企业级 MySQL 用户权限管理方案与实战命令

最小权限原则（Least Privilege）和职责分离（Separation of Duties）。

第一部分：创建受限超级管理员与权限回收

这部分演示了 DBA如何创建一个“受约束的超级管理员”，以及如何回收不再需要的权限。

1. 创建带资源限制的超级管理员

sql

CREATE USER 'super_admin'@'localhost' IDENTIFIED BY 'StrongAdminP@ssw0rd';
GRANT USAGE ON *.* TO 'super_admin'@'localhost'
  WITH
    MAX_QUERIES_PER_HOUR 0
    MAX_UPDATES_PER_HOUR 0
    MAX_CONNECTIONS_PER_HOUR 0
    MAX_USER_CONNECTIONS 10;
  • CREATE USER ...：先创建一个账号，密码设置得比较强（符合安全规范）。
  • GRANT USAGE ...：这里非常巧妙。USAGE 本身代表“没有任何实际权限”，仅仅只允许该用户登录数据库。
  • WITH ...：这是 MySQL的资源限制子句。
    • MAX_QUERIES_PER_HOUR 0：每小时查询次数无限制（0代表无限制）。
    • MAX_UPDATES_PER_HOUR 0：每小时更新操作无限制。
    • MAX_CONNECTIONS_PER_HOUR 0：每小时连接次数无限制。
    • MAX_USER_CONNECTIONS 10：这是核心。限制该账号最多只能同时保持 10 个活跃连接。这可以防止因为程序 Bug 或恶意攻击导致数据库连接池被瞬间耗尽。

2. 授予管理员权限

sql

GRANT ALL PRIVILEGES ON *.* TO 'super_admin'@'localhost' WITH GRANT OPTION;
  • 赋予了所有库和表的全部权限（ALL PRIVILEGES）。
  • WITH GRANT OPTION：这个子句非常关键，它允许该用户把自己拥有的权限再授权给其他用户。这才是真正意义上的“管理员”权限。
  • 注意：由于前面设置了资源限制，这个超级管理员的连接数依然受限于 10，所以它不是“无限膨胀”的。

3. 回收权限

sql

REVOKE SELECT ON *.* FROM root@'192.168.226.%';
  • 这里演示了权限回收。假设之前因为某个特殊原因，给 192.168.226.% 网段的 root 用户开了全局查询权限，现在任务完成了，出于安全考虑，必须用 REVOKE 收回。
  • 如果是验证权限，可以使用 SHOW GRANTS FOR root@'192.168.226.%'; 来确认回收是否成功。

第二部分：不同角色的精细化权限分配方案

这部分是整个文档的精华，它针对企业里常见的 6 种角色，给出了详细的权限设计方案。

1. 开发人员 (Developer)

  • 核心需求：日常写代码、调试，需要操作开发/测试库，通常不接触生产库。

  • 基础权限：SELECT, INSERT, UPDATE, DELETE（针对开发库业务表）。

  • 结构权限：CREATE TABLE, ALTER TABLE, CREATE INDEX（允许他们建表和修改表结构，方便开发）。

  • 限制（红线）：禁止 DROP（防止误删表）、禁止 TRUNCATE（防止清空表）、禁止 GRANT（防止开发人员私自授权，造成数据泄露）。

  • 授权命令示例：

    sql

    GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE 
ON dev_db.* TO 'developer'@'192.168.1.%';

2. 测试人员 (Tester)

  • 核心需求：执行测试用例，写入测试数据，检查功能。
  • 权限：只有数据操作权限（SELECT, INSERT, UPDATE, DELETE）。
  • 限制：禁止结构操作（不能改表结构、建表、删表）、禁止权限管理（不能 GRANT）。这是为了防止测试人员破坏测试环境的数据结构，导致测试数据失效。

3. 数据分析/报表人员 (Data Analyst)

  • 核心需求：提取数据，制作报表。

  • 权限：只读（SELECT）。通常只针对特定的表或视图。

  • 安全亮点：通过视图隐藏敏感列。

    sql

    CREATE VIEW safe_user AS SELECT id, name, register_date FROM user_info;
GRANT SELECT ON safe_user TO 'analyst'@'%';

    把身份证、手机号等敏感信息隐藏起来，只授予视图的查询权限。这样分析人员看不到敏感数据，但依然能完成分析工作。

4. 运维、DevOps(Operations)

  • 核心需求：监控数据库状态、备份、维护。
  • 权限：BACKUP DATABASE（备份）、SHUTDOWN（关库）、PROCESS（查看进程）。
  • 限制：禁止 INSERT/UPDATE/DELETE 业务数据（防止运维误改数据）、禁止 DROP（防止破坏结构）。

5. 业务系统账号 (App Account)

  • 核心需求：应用程序连接数据库，执行代码逻辑。
  • 权限：根据业务需要，仅授予 SELECT/INSERT/UPDATE/DELETE（针对具体的业务表）。
  • 限制：禁止 CREATE/ALTER/DROP。非常重要！因为程序 Bug 或 SQL 注入一旦发生，如果账号有DDL 权限，整个表结构可能瞬间被毁。如果只有 DML 权限，最多被删几行数据，可以通过备份快速恢复。也禁止 GRANT，防止权限扩散。
  • 特殊：如果程序需要用临时表，可以单独授予 CREATE TEMPORARY TABLES 权限。

6. DBA 自身 (DBA)

  • 最高权限：ALL PRIVILEGES（只用于紧急情况和核心管理）。
  • 审计权限：SELECT 审计日志表，ALTER 审计规则。
  • 最佳实践：日常操作不建议使用 root 或高权限账号。强烈建议DBA 也通过堡垒机（Bastion Host，一种跳板机）来操作，这样所有操作都会被日志记录，防止内部人员作恶。

总结：权限分配的通用原则

文档最后总结的这三点，是所有运维人员和安全人员必须遵守的铁律：

  1. 环境隔离：生产、测试、开发环境必须用不同的账号体系，严格隔离，生产库的权限要给到最小。
  2. 动态调整：员工离职、转岗，必须立即回收权限（定期审计非常重要，比如每季度审一次）。
  3. 日志追溯：所有的权限变更（GRANT/REVOKE），以及敏感的 DELETE 操作，都必须要有审计日志，做到“有迹可循”。

Mysql用户管理

MySQL用户与主机域核心知识

username@’主机域’ 主机域:可以理解为是Mysql登录的白名单 主机域格式：

192.168.226.10
192.168.226.1%
192.168.226.1 192.168.226.10-19 192.168.226.100-199
192.168.226.%
192.168.%.%
%
`localhost`
192.168.226.0/255.255.255.0

一、 用户定义格式与作用

MySQL 用户的核心定义格式是：username@'主机域'（Host）。

  • 主机域的作用：可以理解为 MySQL登录的白名单。它决定了特定的账号只能从哪些IP 地址或网段连接到数据库，这是 MySQL安全机制的重要一环。

二、 主机域（Host）的常见书写格式

在实际配置中，主机域可以使用具体的 IP、通配符（%）或网段形式，以应对不同的访问需求。结合当前环境（本机 IP 为 192.168.226.10），常见的格式如下：

  • 192.168.226.10：精确匹配单个 IP。只允许这一台机器（即本机）连接到数据库。
  • 192.168.226.%：C 类网段匹配。允许 192.168.226.1 到 192.168.226.254 这个网段内的所有机器连接。
  • 192.168.%.%：B 类网段匹配。允许 192.168 开头的所有网段（范围极大）。
  • 192.168.226.1%：前缀模糊匹配。匹配以 192.168.226.1 开头的所有 IP 地址（例如 192.168.226.10、192.168.226.100 等，不够精确，需慎用）。
  • %：全匹配。允许从任何IP地址连接（生产环境极度危险，慎用）。
  • localhost：本地 Socket 连接。仅允许从本机服务器发起连接，不经过 TCP/IP 网络协议（这是默认 root 用户的常用配置）。
  • 192.168.226.0/255.255.255.0：子网掩码形式。精确指定 192.168.226.0 整个网段（与 192.168.226.% 效果类似）。

三、 实战用户表的操作与解析

1. 切换系统库

  • 执行命令：use mysql;
  • 作用：切换到 MySQL 自带的系统数据库。MySQL所有的==用户账号、密码、权限信息==都存放在这个库中的 user 表里。

2. 查询所有列（重点疑问）

  • 执行命令：select * from user;
  • 为什么会有那么多列输出？ ==因为 select * 表示查询该表的所有字段== 。MySQL将用户的每一个具体权限（如增、删、改、查、建表、授权等）都设计成了独立的列字段。
    • 基本认证列：Host（主机域）、User（用户名）、Password（加密后的密码哈希值）。
    • 权限控制列：Select_priv、Insert_priv、Update_priv、Grant_priv 等等。这些列的值通常是 Y 或 N，代表该用户是否具备全局级别的某项权限。
    • 其他属性列：plugin（密码加密插件，截图显示为 mysql_native_password）、password_expired（密码是否过期）等。
    • 注意：当把这张表的所有列全部在一行中打印出来时（如您截图中的长列表），终端会显示得非常长，同时很多列（如 N 或 0）是因为该用户为普通用户或受限用户，没有开启这些全局权限。
    • image-20261012202746450

3. 优化查询（查看核心信息）

  • 执行命令：select host,user from user;

  • 执行结果解析（结合您的环境）： 这条命令==只提取最关键的两列==，输出会变得非常简洁清晰。在当前环境下，执行后会列出如下几行核心数据：

    • 127.0.0.1 | root
    • 192.168.226.% | wordpress
    • ::1 | root
    • localhost | root
    • localhost.localdomain | root
    • localhost | (空)
    • localhost.localdomain | (空)
    • 分析：这代表当前服务器默认配置了 root 账号用于本地访问（localhost 及几个本机回环IP 别名），并为名为 wordpress 的网站程序配置了专用的连接账号（允许从 192.168.226 网段连接）。最后两行空用户名通常代表初始自带的无密码匿名用户，出于安全考虑通常建议将其删除。

    image-20261012203822899

image-20261012203622207

用mysqladmin管理工具设置了root用户的密码

mysqladmin -uroot password '123456' 通过用户名和密码来登录数据库 mysql -uroot -p123456

1. 为什么它必须要在服务端（Linux终端）运行？

mysqladmin 是一个独立的可执行程序（通常存放在 MySQL的 bin 目录下，比如 /usr/bin/mysqladmin）。 它的运行机制是：

  • 接收你输入的参数（比如 -uroot -p123456）。
  • 通过网络或本地 Socket 连接去连上 MySQL 服务端。
  • 向服务端发送一条“修改密码”的指令。
  • 服务端执行成功后，mysqladmin 程序就退出了。

因为它是一个独立的程序，所以必须要在操作系统的命令行（也就是您截图里的 [root@localhost ~]#）下运行。一旦您输入 exit 退出了 mysql> 交互环境，就回到了操作系统层面，这时候再敲 mysqladmin，系统才认识它。

2. 为什么在 mysql> 里面输入 mysqladmin 会报错？

因为 mysql> 是一个SQL 解析器。您在里面输入的所有东西，都会被当作SQL 语句来解析。

  • 您输入 SELECT * FROM user;，它认识，去执行。
  • 您输入 mysqladmin ...，它心里想：“这什么玩意儿？SQL 语句哪有以 mysqladmin 开头的？”于是直接甩出 ERROR 1064（语法错误）。

3. mysqladmin 和 SQL 命令改密码的区别

截图里展示了两种改密码的方式，它们其实是殊途同归，只是入口不同：

  • 方法一（服务端外部工具，截图里做的）： 在 Linux 终端：mysqladmin -uroot -p123456 password '654321' 特点： 不需要进入MySQL，直接在操作系统层面调用工具去连接并修改密码。
  • 方法二（客户端内部SQL语句）： 先登录进 mysql>，然后执行： ALTER USER 'root'@'localhost' IDENTIFIED BY '654321'; 特点： 利用 SQL语句本身来修改权限表里的密码字段。

image-20261012205721569

忘记 root 密码后的强制重置流程

它的核心思路是：让 MySQL 启动时暂时“忘记”检查密码（跳过权限表验证），从而让我们能免密登录进去，修改完密码后，再恢复正常启动。

# 如果mysql的密码忘记了，如何解决？
[root@server1 ~]# /etc/init.d/mysqld stop
[root@server1 ~]# mysqld_safe --skip-grant-tables --skip-networking 
[root@server1 ~]# mysql
mysql> use mysql
mysql> update user set password=PASSWORD('123456') where user='root' and host='localhost';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@server1 ~]# pkill mysql
[root@server1 ~]# systemctl start mysqld.service
[root@server1 ~]# mysql -uroot -p123456

第一阶段：停止当前服务，进入“无密码”模式

1. [root@server1 ~]# /etc/init.d/mysqld stop

  • 作用：停止正在运行的 MySQL服务。
  • 原因：你要修改底层的认证方式，必须先让数据库停下来。

2. [root@server1 ~]# mysqld_safe --skip-grant-tables --skip-networking &

  • 作用：这是整个流程的灵魂步骤。使用 mysqld_safe 工具（一个守护进程启动脚本）来启动MySQL。
  • 关键参数：
    • --skip-grant-tables：跳过权限表验证。==启动时，MySQL不会去加载 mysql.user 表里的密码和权限信息==。这意味着，任何人都可以免密登录，且拥有最高权限。
    • --skip-networking：跳过网络连接。防止其他机器通过网络连上这个“不设防”的数据库，只允许本机（localhost）通过 Socket 连接，保证重置过程的安全性。
  • 注：截图里的命令末尾虽然没有加 &（让程序在后台运行），但在实际操作中，敲下这行命令后，终端通常会卡住（因为它在运行），此时需要再开一个终端窗口来执行后面的步骤。

第二阶段：免密登录并修改密码

3. [root@server1 ~]# mysql

  • 作用：由于上一步已经跳过了权限验证，此时输入 mysql 命令，不需要 -u 和 -p 参数，直接就能进入 MySQL 交互界面。

4. mysql> use mysql

  • 作用：切换到 mysql 系统库，准备修改 user 表。

5. mysql> update user set password=PASSWORD('123456') where user='root' and host='localhost';

  • 作用：这是核心的密码修改语句。
    • 定位目标：找到 user 表中，用户名为 root 且主机为 localhost 的那一行记录。
    • 赋值操作：将 password 字段的值，修改为 PASSWORD('123456')。PASSWORD() 是一个内置函数，用于生成加密后的哈希值。
  • 结果提示解读：
    • Query OK, 0 rows affected：看起来好像没改（0行受影响），但在老版本 MySQL（如5.6/5.7之前）里，如果设置的新密码和旧密码一样，MySQL 可能认为不需要更新，所以显示 0。这并不代表密码没生效。
    • Rows matched: 1 Changed: 0：说明找到了一条符合条件的数据（匹配了1行），但可能因为密码没有变化，所以没有实质性的修改（Changed 0）。
    • 如果你的目标是设置一个全新的密码，这里就会显示 Changed: 1。

6. mysql> flush privileges;

  • 作用：刷新权限表。
  • 原因：这是一个极其重要的习惯。在我们直接通过 update 修改完系统表后，必须通知MySQL服务器重新加载权限表，这样新的密码才会立即生效。

7. mysql> quit

  • 作用：退出当前的 MySQL客户端环境。

第三阶段：恢复正常状态并验证

8. [root@server1 ~]# pkill mysql

  • 作用：强制杀掉所有与MySQL相关的进程。
  • 原因：刚刚启动的那个 MySQL是在“无权限验证”的畸形状态下运行的，不能一直这样开着。必须把它彻底停掉，才能正常启动。

9. [root@server1 ~]# systemctl start mysqld.service

  • 作用：使用系统服务管理工具（systemctl），正常启动 MySQL。
  • 结果：此时MySQL会带着完整的权限表加载，正常启动，不再跳过验证。

10. [root@server1 ~]# mysql -uroot -p123456

  • 作用：使用刚刚重置的新密码 123456 尝试登录数据库。
  • 如果成功，说明整个重置流程圆满完成。

/etc/init.d/mysqld为啥因为PID停止失败

1. 真正的数据库进程（/application/mysql/bin/mysqld）

  • 定位：这是 MySQL的核心二进制可执行文件。
  • 启动方式：执行该命令时，它启动数据库引擎，分配内存，监听 3306 端口，准备处理 SQL请求。
  • 与配置文件的关系：它启动时依赖 --defaults-file=/etc/my.cnf 参数。该参数指定了配置文件路径，mysqld 会读取并严格按照该文件内的参数（如数据目录、端口、缓冲区大小等）运行。
  • 特点：它不认识 start、stop 等操作指令。如果在终端直接执行，它会在前台运行并占用终端。

2. 服务管理脚本（/etc/init.d/mysqld）

  • 定位：这是一个 Shell 脚本，用于封装对 MySQL进程的启停管理。
  • 工作方式：它接受 start、stop、restart、status 等参数，并执行相应的逻辑。
  • 执行 stop 的逻辑：脚本会去指定路径（如 /var/run 或数据目录）查找 mysqld.pid 文件。读取里面的进程号（PID），然后调用 kill 命令终止该进程。如果找不到PID文件，脚本就会报错 PID file could not be found!。
  • 与配置文件的关系：脚本内部通常也会引用 /etc/my.cnf 配置文件，但它的目的是为了获取PID 文件路径等管理相关的信息，而不是像 mysqld 那样加载所有数据库运行参数。

3. 两者在本次操作中的关系（为什么会报错，以及如何处理）

  • 正常情况下，脚本（/etc/init.d/mysqld）负责管理核心进程（mysqld）。脚本启动 mysqld 时，会把进程号写入 PID文件，以便后续通过 stop 命令读取并停止它。
  • 当前报错 PID file could not be found! 的原因：脚本无法找到 PID 文件，因此它不知道要终止哪个进程。
  • 报错并不意味着 mysqld 进程没有运行。通过 ps -ef | grep mysql 可以看到，mysqld 进程（PID 817）实际上正在运行。
  • 因此，当前的操作逻辑是：由于管理脚本无法通过PID文件正常停止该进程，我们手动获取其 PID（817），直接使用 kill -9 817 命令终止它。停止后，再通过 mysqld_safe --skip-grant-tables 等参数手动启动，以进行后续的密码重置等操作。

一句话总结： mysqld 是实际运行的服务进程，依赖 /etc/my.cnf 启动；/etc/init.d/mysqld 是管理该进程的脚本，依赖 PID 文件执行停止操作。PID 文件丢失导致脚本停止失败，所以需手动通过 PID强制终止进程。

image-20261012212710720

image-20261012221408351

image-20261012221817075

MySQL 中 修改密码的三种核心途径

第一部分：修改密码的三种方式详解

这三种方式其实殊途同归，最终目的都是修改底层 mysql.user 表里的密码字段，但适用场景和生效机制不同。

# 1.直接修改，修改完直接生效
mysql> set password=PASSWORD('123456');
# 2.修改数据表user中的password字段，修改完需要flush privileges
mysql> update user set password=PASSWORD('321') where user='root' and host='localhost';
# 3.在授权的时候可以在最后通过identified by来指定密码，修改完需要flush lrivileges
mysql> grant all privileges on *.* to user01@'192.168.226.%' identified by '123456';

1. 直接修改（SET PASSWORD）

  • 语法：SET PASSWORD = PASSWORD('123456');
  • 机制：这是 MySQL提供的专用 SQL 语句。它专门用来修改当前登录用户的密码。
  • 生效：修改完立刻生效，不需要 flush privileges。因为 MySQL内部会直接更新内存中的权限缓存。
  • 适用场景：用户已经登录，且拥有修改自己密码的权限。
  • 避坑：在 MySQL8.0 中，不再使用 PASSWORD() 函数，直接写 SET PASSWORD = '123456'; 或者 ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';。

image-20261012224853003

image-20261012224945986

2. 修改数据表（UPDATE）

  • 语法：UPDATE user SET password=PASSWORD('321') WHERE user='root' AND host='localhost';

  • 机制：这是一种“硬核”修改方式。绕过 SQL语法层，直接去修改 mysql 系统库中的 user 表。

  • 生效：修改完必须执行 flush privileges;。因为直接修改表，MySQL的内存缓存（比如 information_schema）不会自动更新，必须手动刷新。

  • 适用场景：

    • 忘记密码，处于 --skip-grant-tables 免密模式时（此时 SET PASSWORD 等命令会报错，只能通过 UPDATE 表来修改）。
    • 批量修改多个用户或特定主机域的用户密码。

    image-20261012225908358

image-20261012225934926

3. 授权时指定（GRANT ... IDENTIFIED BY）

  • 语法：GRANT ALL PRIVILEGES ON *.* TO username@'192.168.226.%' IDENTIFIED BY '123456';
  • 机制：这是 MySQL5.7 及以前特有的语法。它把“创建用户”、“赋权”和“设置密码”三个动作合为一体。
  • 生效：修改完必须执行 flush privileges;。
  • 适用场景：新建用户并分配权限时，顺手设置好初始密码。
  • 避坑（非常重要！）：在 MySQL 8.0 中，GRANT 语句不能再用来创建用户或设置密码了。必须先 CREATE USER，再 GRANT。所以这个语法在 8.0 里已经废弃。

image-20261012234430220

image-20261012234303804

MySQL 三层启动调用链

核心启动链：

/etc/init.d/mysqld` ——(调用)——> `mysqld_safe` ——(拉起并监控)——> `mysqld

第一层：/etc/init.d/mysqld —— 标准化接口（面向操作系统）

1. 它是什么？ 这是一个 Shell 脚本（在 CentOS 6及更早版本中是标准写法，CentOS 7+ 逐渐被 systemctl 取代）。它是 Linux 系统管理服务的“标准入口”。

2. 它的职责（为什么需要它？）：

  • 适配系统规范：Linux 管理服务有一套统一的标准（比如用 service mysqld start 或 systemctl start mysqld）。这个脚本的作用就是把系统的标准命令翻译成 MySQL能听懂的话。
  • 环境准备：在真正拉起数据库之前，它会做很多繁琐的准备工作：
    • 读取配置文件（/etc/my.cnf）。
    • 检查数据目录的权限（确保 mysql 用户有权限读写 /var/lib/mysql）。
    • 检查 PID文件是否残留、端口是否被占用。
  • 结果反馈：当底层启动完毕后，它会向系统返回 OK 或 FAILED，让你能在屏幕上看到成功或失败的状态。

总结：它是面向操作系统的接口层，负责处理所有的系统对接和前置检查工作。

第二层：mysqld_safe —— 中间层守护（安全与监控）

1. 它是什么？ 它是一个 守护进程（Daemon），本质上也是一个 Shell 脚本。它存在于第一层和核心数据库之间，是一个中间层。

2. 它的职责（为什么需要它？）：

  • 安全保障：它负责在启动核心数据库前，把一些不安全的因素排除掉。
  • 故障恢复（最核心作用）：mysqld 这个核心进程有时候会因为内存溢出、Bug 或异常 SQL而崩溃。如果 mysqld 直接挂在系统上，服务就彻底死了。 而 mysqld_safe 会一直监控 mysqld 进程，如果发现它意外退出了，mysqld_safe 会立刻把它重新启动，保证数据库的高可用性。
  • 日志记录：负责记录启动过程中的错误日志（如 error.log），方便排查问题。
  • 参数传递：像我们之前模拟忘记密码时用的 --skip-grant-tables 和 --skip-networking，通常就是通过 mysqld_safe 把这个参数传递给 mysqld 的。

总结：它是中间层的守护进程，负责监控核心进程并提供故障恢复机制。

第三层：mysqld —— 核心功能实现（数据库本体）

1. 它是什么？ 这是 MySQL 真正的核心服务进程（二进制可执行文件，通常在 /usr/sbin/mysqld 或 /application/mysql/bin/mysqld）。它是实际执行数据库功能的核心程序。

2. 它的职责（为什么需要它？）：

  • 处理SQL 语句：解析客户端发来的 SELECT、UPDATE 等语句。
  • 管理存储引擎：操作 InnoDB 或 MyISAM引擎去读写磁盘上的数据文件。
  • 管理连接：监听 3306 端口，处理用户连接和权限验证。
  • 维护内存结构：管理 Buffer Pool、日志缓冲区等。

3. 它的特点： 它极度专注，只做数据库该做的事。它不管防火墙，不管系统服务规范，不管崩溃后怎么重启（那是前两层的事）。它一旦启动，就会一直运行，直到被手动停止或崩溃。

总结：它是核心功能实现层，专注于数据库服务和数据处理本身。

为什么这种“分层设计”如此重要？（架构分析）

如果把这三层合并成一个大程序，会带来什么问题？

  1. 不兼容：不同 Linux 发行版（Ubuntu、CentOS）管理服务的方式不同，合并会导致兼容性极差。
  2. 不稳定：核心数据库一旦崩溃，整个服务就全挂了，没有中间层去恢复它。
  3. 难维护：日志、权限检查、启动参数传递都和核心业务代码搅在一起，出错了很难排查。

三层分层的好处：

  • 各司其职（单一职责）：系统层管环境，守护层管生存，核心层管业务。

  • 高可用：mysqld_safe 的存在，大大提高了数据库的健壮性。

  • 可移植：只要针对不同的 Linux 系统写不同的 /etc/init.d/mysqld 脚本，底层的 mysqld_safe 和 mysqld 几乎不需要改动。

补充：现代 MySQL 的变化（CentOS7+ 和 MySQL 8.0）

现在（CentOS 7 及以上），/etc/init.d/mysqld 已经被 systemd 取代（即 systemctl start mysqld）。 但是，分层的核心思想没有变。

  • 现在的 systemd 会直接接管 mysqld，不再经过 mysqld_safe（因为 systemd本身就带有 Restart=on-failure 自动重启功能，替代了 mysqld_safe 的部分职责）。
  • 但在一些手动编译安装的 MySQL5.6/5.7 环境中，这三层调用链依然经典且常见。

一句话总结：系统接口 -> 守护进程 -> 核心服务，层层递进，构建了稳定可靠的 MySQL 服务体系。

1. 从文件属性看：它是一个具有执行权限的文件

Linux 系统允许这个文件作为程序被运行

2. 从文件内容看：它是一个 Shell 脚本

看截图里的 cat /etc/init.d/mysqld 输出：

  • 第一行：#!/bin/sh 这是最关键的标志。这行叫做 Shebang。它告诉 Linux 内核：“这个文件是个脚本，请用 /bin/sh（也就是 Shell）来解释并执行它”。所以，它本质上就是一个 Shell 脚本。
  • 后续的注释： 大段的 # 开头的内容，都是在描述这个脚本的历史、用途（启动/停止 MySQL守护进程）、以及在老版本 Linux（如 RedHat）中的系统运行级别配置。这些是给管理员看的说明文档。

3. 脚本内部通常包含什么？

​ 这个 mysqld 脚本的完整逻辑通常是这样的：

  • 它接收系统传来的参数（如 start、stop、restart、status）。
  • 它检查环境（检查配置、检查权限）。
  • 最核心的：它调用真正的程序。当接收到 start 参数时，它会去执行 /application/mysql/bin/mysqld_safe 或直接执行 mysqld。

image-20261013000448562

用户指令

1. 查看系统用户表

命令： mysql> select user,host from mysql.user;

  • 作用：查询 mysql 系统库中的 user 表，但只提取 user（用户名）和 host（允许登录的主机域）这两列。

2. 查看当前登录的用户

命令： mysql> select user();

  • 作用：查看当前这个连接是以哪个用户的身份登录的。
  • 结果解读（1 row in set）： 输出 root@localhost。意思就是：您现在正以 root 用户的身份，从 localhost（本机）登录着MySQL。

3. 查看当前数据库（成功但为空）

命令： mysql> select database();

  • 作用：查看当前处于哪个数据库中。
  • 结果解读（1 row in set）： 输出 NULL。意思是：您现在虽然登录了MySQL，但并没有选中任何数据库（刚登录进来默认就是没有选中任何库）。

5. 切换数据库

命令： mysql> use mysql;

  • 作用：切换到名为 mysql 的系统数据库。
  • 提示信息：
    • Reading table information for completion of table and column names：这是 MySQL 在读取表信息，为了给您提供自动补全提示（方便您敲表名、列名），不影响操作。
    • Database changed：明确告诉您，已经成功切换到 mysql 库了。

6. 再次查看当前数据库（验证切换成功）

命令： mysql> select database();

  • 作用：再次确认当前所在的数据库。
  • 结果解读（1 row in set）： 输出 mysql。这就证明您刚才的 use mysql; 生效了，您现在位于 mysql 系统库中。

image-20261013005134094

MySQL多实例配置

多实例

  • 多套后台进程+线程+内存结构
  • 多个配置文件
    • 多端口
    • 多socket文件
    • 多个日志文件
    • 多个server_id
  • 多套数据

实战配置

#创建数据目录
mkdir -p /data/330{7..9}
#创建配置文件
touch /data/330{7..9}/my.cnf
touch /data/330{7..9}/mysql.log
#编辑3307配置文件
vim /data/3307/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
log-bin=/data/3307/mysql-bin
server_id=7
port=3307
[client]
socket=/data/3307/mysql.sock
#编辑3308配置文件
vim /data/3308/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
log-bin=/data/3308/mysql-bin
server_id=8
port=3308
[client]
socket=/data/3308/mysql.sock
#编辑3309配置文件
vim /data/3309/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
log-bin=/data/3309/mysql-bin
server_id=9
port=3309
[client]
socket=/data/3309/mysql.sock
#修改目录权限
chown -R mysql.mysql /data/330*

#初始化3307数据,每条命令需要间隔若干秒，否则失败
/application/mysql/scripts/mysql_install_db 
--user=mysql 
--defaults-file=/data/3307/my.cnf 
--basedir=/application/mysql --datadir=/data/3307/data
#初始化3308数据,每条命令需要间隔若干秒，否则失败
/application/mysql/scripts/mysql_install_db 
--user=mysql 
--defaults-file=/data/3308/my.cnf 
--basedir=/application/mysql --datadir=/data/3308/data
#初始化3309数据,每条命令需要间隔若干秒，否则失败
/application/mysql/scripts/mysql_install_db 
--user=mysql 
--defaults-file=/data/3309/my.cnf 
--basedir=/application/mysql --datadir=/data/3309/data

#启动多实例
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &
# 进入单独的mysql实例
mysql -S /data/3307/mysql.sock -uroot
#设置每个实例的密码
mysqladmin -S /data/3307/mysql.sock -uroot password '123456'
#查看server_id
mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"  -p123456
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"  -p123456
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"  -p123456
# 关闭实例，如果设置了密码登录就需要密码了 用参数-p
mysqladmin -S /data/3307/mysql.sock -uroot  -p123456 shutdown
mysqladmin -S /data/3308/mysql.sock -uroot  -p123456 shutdown
mysqladmin -S /data/3309/mysql.sock -uroot  -p123456 shutdown

准备工作

1. 编辑配置文件（核心部分） 使用 vim /data/3307/my.cnf 分别修改三个配置文件。这里面的参数是确保多实例互不干扰的关键：

  • basedir=/application/mysql：MySQL 的安装目录（三个实例共用同一个程序文件，节省空间）。
  • datadir=/data/3307/data：数据目录。这是每个实例独立的，数据绝不能混在一起。
  • socket=/data/3307/mysql.sock：套接字文件。本地连接（比如 mysql -uroot）默认走 socket，如果不区分开，连接会串门。
  • log_error=/data/3307/mysql.log：错误日志路径，独立存放方便排错。
  • log-bin=/data/3307/mysql-bin：开启二进制日志（用于主从复制或数据恢复），前缀独立。
  • server_id=7：服务器唯一 ID。在做主从复制时，每个节点的 ID 必须不同。
  • port=3307：监听端口。必须不同（3307/3308/3309）。
  • [client] socket=...：这个很重要。它告诉本机的MySQL 客户端，连接这个实例时应该去哪个 socket 文件找它。

image-20261013215222286

2. 修改目录权限

bash

chown -R mysql.mysql /data/330*
  • 解释：将刚才创建的目录和文件的所有者改为 mysql 用户。这一步极其重要！ 因为后续启动 MySQL 时是用 mysql 用户的身份运行的。如果权限不对，MySQL 无法写入数据或日志，直接启动失败。

3. 初始化数据

bash

/application/mysql/scripts/mysql_install_db 
--user=mysql 
--defaults-file=/data/3307/my.cnf 
--basedir=/application/mysql --datadir=/data/3307/data
  • 解释：这是数据库奠基仪式。
    • 作用：它会去读取你写的 my.cnf，然后在 datadir 目录下生成 MySQL的基础系统库（比如 mysql 库、performance_schema 库等）。没有这一步，启动数据库会报错说找不到系统表。
    • 教程特意标注：#初始化3307数据，每条命令需要间隔若干秒，否则失败。
    • 为什么必须间隔？ 当你连续敲这三条命令时，系统资源（CPU、磁盘 IO）可能还没释放完，或者是前一个临时进程的锁还没释放。强行连续执行会导致后一个卡死或报错。必须等前一个 mysql_install_db 命令完全执行完（回到命令提示符）之后，再执行下一个。
    • 你的错误复盘：上一张图，是在 3307 的 mysqld 已经启动（占用端口和资源）的情况下，又去执行 mysql_install_db，这违背了“先初始化，再启动”的铁律，所以卡死了。

4.启动多实例

bash

mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &
mysqld_safe --defaults-file=/data/3309/my.cnf &
  • 解释：初始化完成后，终于可以启动了。
  • 使用 mysqld_safe 启动（这是官方推荐的启动方式，它会监控 mysqld 进程，如果异常挂掉会自动重启）。
  • 加上 & 让它后台运行，否则你的终端会被第一个启动的实例卡住。

5. 登录并设置密码

bash

mysql -S /data/3307/mysql.sock -uroot
  • 解释：初始化后，root 用户默认没有密码。
  • 注意这里的 -S /data/3307/mysql.sock。因为你有三个实例，你直接敲 mysql -uroot，系统不知道该连哪个。必须通过指定 -S (socket) 参数，明确告诉客户端你要连 3307 这个实例。

bash

mysqladmin -S /data/3307/mysql.sock -uroot password '123456'
  • 解释：给 3307 实例的 root 用户设置密码为 123456。

image-20261013220557546

6. 验证实例（查看 server_id）

bash

mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'" -p123456
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'" -p123456
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'" -p123456
  • 解释：分别连接三个实例，查看它们各自的 server_id。
  • 如果 3307 返回 7，3308 返回 8，3309 返回 9，说明你的多实例部署完全成功，它们各自独立工作了。

image-20261013221151781

正确的梳理流程应是：

1. 创建目录和配置文件。
2. 赋予 mysql 用户权限。
3. 依次、逐个执行 `mysql_install_db` 初始化（务必等前一个结束）。
4. 分别使用 `mysqld_safe` 启动服务。
5. 通过 `-S` 指定 socket 进行单独管理。

mysqladmin

  • “强制回应 (Ping)”服务器
  • 关闭服务器
  • 创建和删除数据库
  • 显示服务器和版本信息
  • 显示或重置服务器状态变量
  • 设置口令
  • 重新刷新授权表
  • 刷新日志文件和高速缓存
  • 启动和停止复制
[root@localhost ~]# mysqladmin -uroot -p123456 create hellodb
[root@localhost ~]# mysqladmin -uroot -p123456 drop hellodb
[root@localhost ~]# mysqladmin -uroot -p123456 ping 检查服务端状态的
[root@localhost ~]# mysqladmin -uroot -p123456 status 服务器运行状态
[root@localhost ~]# mysqladmin -uroot -p123456 status 服务器状态 --sleep 2 --count 10 每两秒钟显示
一次服务器实时状态一共显示10次
[root@localhost ~]# mysqladmin -uroot -p123456 extended-status 显示状态变量
[root@localhost ~]# mysqladmin -uroot -p123456 variables 显示服务器变量
[root@localhost ~]# mysqladmin -uroot -p123456 flush-privileges 数据库重读授权表，等同于reload
[root@localhost ~]# mysqladmin -uroot -p123456 flush-tables 关闭所有已经打开的表
[root@localhost ~]# mysqladmin -uroot -p123456 flush-threds 重置线程池缓存
[root@localhost ~]# mysqladmin -uroot -p123456 flush-status 重置大多数服务器状态变量
[root@localhost ~]# mysqladmin -uroot -p123456 flush-logs 日志滚动。主要实现二进制和中继日志滚动
[root@localhost ~]# mysqladmin -uroot -p123456 flush-hosts 清楚主机内部信息
[root@localhost ~]# mysqladmin -uroot -p123456 kill 杀死线程
[root@localhost ~]# mysqladmin -uroot -p123456 refresh 相当于同时执行flush-hosts flush-logs
[root@localhost ~]# mysqladmin -uroot -p123456 shutdown 关闭服务器进程
[root@localhost ~]# mysqladmin -uroot -p123456 version 服务器版本以及当前状态信息
[root@localhost ~]# mysqladmin -uroot -p123456 start-slave 启动复制，启动从服务器复制线程
[root@localhost ~]# mysqladmin -uroot -p123456 stop-slave 关闭复制线程

mysqldump

  • 备份数据库和表的内容
# 备份所有数据库
mysqldump -uroot -p123456  --all-databases > /backup/mysqldump/all.db
                           -A > /backup/mysqldump/all.db 
# 备份指定数据库
mysqldump -uroot -p123456 wordpress > /backup/mysqldump/wordpress.db
# 备份指定数据库指定表(多个表以空格间隔)
mysqldump -uroot -p123456 wordpress t1 > /backup/mysqldump/t1.db
# 备份指定数据库排除某些表
mysqldump -uroot -p123456  test --ignore-table=test.t1 --ignore-table=test.t2 > /backup/mysqldump/test2.db
  • 还原的方法
# 注：在导入备份数据库前，db_name如果没有，是需要创建的； 而且与db_name.db中数据库名是一样的才可以导入
mysqladmin -uroot -p123456 create db_name 
mysql -uroot -p123456 db_name < /backup/mysqldump/db_name.db
# source也可以还原数据库
mysql > use db_name
mysql > source /backup/mysqldump/db_name.db

第一部分：备份数据库和表的内容

核心工具是 mysqldump（MySQL 自带的逻辑备份工具）。它的基本公式是： mysqldump [连接参数] [要备份的对象] > [导出的文件路径] (注：> 是 Linux 的重定向符号，意思是不在屏幕上打印，而是把结果写入右边的文件里)。

1. 备份所有数据库

mysqldump -uroot -p123456 --all-databases > /backup/mysqldump/all.db
                           -A > /backup/mysqldump/all.db
  • --all-databases：关键参数，意思是把当前 MySQL 服务器里所有的数据库（包括系统库 mysql、information_schema 等）全部导出。
  • > /backup/mysqldump/all.db：把导出的 SQL 语句保存到 all.db 这个文件中。
  • 适用场景：服务器整体迁移、全量冷备份。

2. 备份指定数据库

mysqldump -uroot -p123456 wordpress > /backup/mysqldump/wordpress.db
  • wordpress：写在连接参数后面，表示只备份名为 wordpress 的这个数据库.
  • 适用场景：只关心某一个业务库，不想备份其他无关的库。

3. 备份指定数据库指定表(多个表以空格间隔) 

mysqldump -uroot -p123456 wordpress t1 > /backup/mysqldump/t1.db
  • 这里的语法是：mysqldump [连接参数] [数据库名] [表1] [表2] ...
  • wordpress：这是数据库名。（注意：这里的 wordpress 是数据库名，不是用户名，指的是MySQL自带的系统数据库）。
  • t1 和 t2：这是 wordpress 库里的两张表的名字。它们之间用空格隔开。
  • 作用：只备份 wordpress 库里的 t1 表和 t2 表，其他表不管。
  • 适用场景：误删了某几张核心表，只需要针对这几张表做单独备份。

4. 备份指定数据库排除某些表

bash

mysqldump -uroot -p123456 wordpress --ignore-table=wordpress.t1 --ignore-table=wordpress.t2 > /backup/mysqldump/wordpress_t.db
  • wordpress：备份 wordpress 数据库。
  • --ignore-table=wordpress.t1：关键参数，意思是“忽略（不备份）wordpress 库里的 t1 表”。
  • --ignore-table=wordpress.t2：同上，忽略 t2 表。
  • 注意语法：写 --ignore-table 时，必须带上数据库名.表名（如 wordpress.t1）。
  • 适用场景：数据库里有一张表特别大（比如几千万条日志数据），每次备份都很慢，就可以用这个命令把它排除掉，只备份其他重要的表。

第二部分：还原的方法

还原的本质就是把你之前备份出来的 .db（其实里面就是一堆SQL语句）重新执行一遍。图里给出了两种主流方式。

方式一：在 Linux 终端直接导入（不进入 MySQL 交互界面）

步骤 1：先创建目标数据库（如果不存在）

bash

mysqladmin -uroot -p123456 create db_name
  • mysqladmin：这是MySQL 的管理工具。
  • create db_name：创建一个名为 db_name 的空数据库。
  • 重点：备份文件里通常只包含“建表”和“插入数据”的语句，不包含“建库”语句（除非你全库备份时带了 --databases 参数）。所以恢复前，你得先准备好一个空的“房子”。

步骤 2：导入数据

bash

mysql -uroot -p123456 db_name < /backup/mysqldump/db_name.db
  • 这里的 < 是输入重定向符号。意思是把右边的 .db 文件内容，作为输入喂给左边的 mysql 命令。
  • db_name：指定要导入到哪个数据库里。
  • 注意：这是一种“盲导”，如果过程中有报错，终端可能一闪而过，你很难察觉。

方式二：进入MySQL 交互界面导入

步骤 1：登录 MySQL 并选中数据库

sql

mysql -uroot -p123456   # 先登录进 MySQL
mysql> use db_name;     # 选中你的目标数据库

步骤 2：使用 source 命令导入

sql

mysql> source /backup/mysqldump/db_name.db
  • source 是MySQL 客户端自带的一个命令。
  • 优点：当文件很大时，用 source 导入，如果有 SQL语法错误或者主键冲突，终端会明显地把报错信息打印出来，方便你排错。

image-20261014005150210

image-20261014005236115

  1. 备份指定库（test.db）：导出文件里通常没有 CREATE DATABASE test; 和 USE test;。它仅仅是导出了一堆表和数据的 SQL语句。

image-20261014005545238

  1. 备份全库（all.db）：导出文件里包含 CREATE DATABASE mysql; 和 USE mysql; 等语句。

image-20261014005822926

==总结==：

最稳妥的方式就是用同名数据库进行恢复。

更多文章