如何在多租户架构中快速完成MySQL环境搭建 多租户数据库环境搭建与用户隔离配置

不是必须但强烈推荐用 separate schemas；shared schema 因 mysql 缺乏行级权限、易越权、难审计备份而持续反噬；应为每个租户创建独立 schema 并严格授予权限。

MySQL 多租户环境必须用 separate schemas 吗？

不是必须，但强烈推荐。shared schema（单库多表+tenant_id 字段）看似省事，实际在权限控制、备份恢复、数据迁移、审计合规上会持续反噬。MySQL 原生不支持行级权限隔离，GRANT 最小粒度是表或 schema，靠应用层加 tenant_id 过滤属于“伪隔离”，一旦 SQL 漏写 WHERE 条件就直接越权。

实操建议：

• 每个租户对应一个独立 schema（即 database），命名统一加前缀如 tenant_abc123
• 禁用 CREATE DATABASE 权限给租户用户，由运维脚本或平台统一创建，避免命名冲突或资源失控
• 不要复用 root 或高权限账号连接业务应用，每个租户只授予其 own schema 的 select, INSERT, UPDATE, delete, EXECUTE

如何用 SQL 脚本批量创建租户 schema 和用户？

手动 CREATE DATABASE + CREATE USER + GRANT 容易出错且不可追溯。应封装为幂等脚本，支持重复执行不报错。

关键点：

• 用 CREATE DATABASE if NOT EXISTS 避免重复建库失败
• CREATE USER IF NOT EXISTS（MySQL 8.0+）或先查 mysql.user 表再创建（5.7）
• GRANT 后必须跟 FLUSH PRIVILEGES（仅 5.7 及更早；8.0+ 在 CREATE USER / GRANT 后自动生效）
• 密码需用 IDENTIFIED WITH mysql_native_password BY 'xxx' 显式指定认证插件，避免 8.0 默认的 caching_sha2_password 导致旧驱动连不上

示例片段（MySQL 8.0）：

CREATE DATABASE IF NOT EXISTS tenant_xyz; CREATE USER IF NOT EXISTS 'app_tenant_xyz'@'%' IDENTIFIED WITH mysql_native_password BY 's3cure_p4ss'; GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON tenant_xyz.* TO 'app_tenant_xyz'@'%';

租户连接时如何自动路由到对应 schema？

应用不能靠写死 USE tenant_abc 或拼接 tenant_abc.table_name —— 这会让 ORM、连接池、SQL 审计全乱套。正确做法是：连接串中不指定 database，由应用在建立连接后立刻执行 USE tenant_xxx，并确保该连接生命周期内只服务该租户。

注意事项：

• 连接池（如 HikariCP）必须关闭 auto-commit 外的自动 schema 切换，否则连接复用会导致跨租户污染
• 若用 ShardingSphere 或 vitess 等中间件，需配置 defaultSchema 或 logic-db 映射，但底层仍是 per-tenant schema
• 禁止在存储过程中跨 schema 查询（如 SELECT * FROM tenant_a.users JOIN tenant_b.orders），这违背租户隔离原则

备份与权限审计容易被忽略的三个细节

多租户环境下，dba 很容易只关注“能不能连上”，却漏掉权限漂移和备份盲区。

必须检查：

• SHOW GRANTS for 'app_tenant_foo'@'%' 输出是否只含 tenant_foo.*，有无意外继承了 mysql 库或 performance_schema 权限
• 物理备份（xtrabackup）是否包含所有租户 schema，--databases 参数若写死列表，新增租户会被跳过
• 逻辑备份（mysqldump）若用 --all-databases，会导出 mysql 系统库，恢复时可能覆盖权限表 —— 应改用 --databases tenant_* 模式匹配

租户数量一过百，手动维护 schema 列表和权限就不可持续，这部分必须交由配置中心或 IaC 工具驱动。