## 1. 环境准备
– 服务器：至少3台服务器，配置推荐：8核16G内存
– 操作系统：CentOS 7.9 / RHEL 7.9
– 网络：确保服务器之间网络畅通
– 存储：每个服务器至少500GB磁盘空间

## 2. 部署TiDB集群
$ tiup cluster deploy fgedu-tidb-cluster v6.5.0 topology.yaml –user root -p

## 3. 配置多租户
$ cat > multi_tenant_config.yaml << EOF global: user: "tidb" ssh_port: 22 deploy_dir: "/tidb/app/tidb-deploy" data_dir: "/tidb/fgdata" server_configs: tidb: performance.max-procs: 8 prepared-plan-cache.enabled: true tikv-client.max-batch-wait-time: 2000000 tikv-client.batch-wait-size: 128 oom-action: "log" resource-control.enabled: true tikv: storage.engine: "raftkv" raftstore.sync-log: false rocksdb.max-open-files: 4096 server.grpc-concurrency: 4 readpool.storage.normal-concurrency: 4 readpool.coprocessor.normal-concurrency: 4 pd: replication.enable-placement-rules: true replication.location-labels: ["zone", "rack" , "host" ] pd_servers: - host: 192.168.1.100 tidb_servers: - host: 192.168.1.100 tikv_servers: - host: 192.168.1.101 - host: 192.168.1.102 - host: 192.168.1.103 monitoring_servers: - host: 192.168.1.100 grafana_servers: - host: 192.168.1.100 alertmanager_servers: - host: 192.168.1.100 EOF ## 4. 启动TiDB集群 $ tiup cluster start fgedu-tidb-cluster ## 5. 创建租户数据库 $ mysql -h 192.168.1.100 -P 4000 -u root -p # 创建租户1数据库 CREATE DATABASE fgedu_tenant1; # 创建租户2数据库 CREATE DATABASE fgedu_tenant2; # 创建租户3数据库 CREATE DATABASE fgedu_tenant3; ## 6. 创建租户用户 # 创建租户1用户 CREATE USER 'fgedu_tenant1' @'%' IDENTIFIED BY 'password' ; GRANT ALL PRIVILEGES ON fgedu_tenant1.* TO 'fgedu_tenant1' @'%'; # 创建租户2用户 CREATE USER 'fgedu_tenant2' @'%' IDENTIFIED BY 'password' ; GRANT ALL PRIVILEGES ON fgedu_tenant2.* TO 'fgedu_tenant2' @'%'; # 创建租户3用户 CREATE USER 'fgedu_tenant3' @'%' IDENTIFIED BY 'password' ; GRANT ALL PRIVILEGES ON fgedu_tenant3.* TO 'fgedu_tenant3' @'%'; ## 7. 配置资源控制 $ tiup ctl tidb -u http://192.168.1.100:10080 config set resource-control.enabled true ## 8. 创建资源组 $ mysql -h 192.168.1.100 -P 4000 -u root -p # 创建租户1资源组 CREATE RESOURCE GROUP fgedu_tenant1_rg WITH ( RU_PER_SEC=1000, PRIORITY=HIGH, BURSTABLE=TRUE ); # 创建租户2资源组 CREATE RESOURCE GROUP fgedu_tenant2_rg WITH ( RU_PER_SEC=500, PRIORITY=MEDIUM, BURSTABLE=TRUE ); # 创建租户3资源组 CREATE RESOURCE GROUP fgedu_tenant3_rg WITH ( RU_PER_SEC=200, PRIORITY=LOW, BURSTABLE=TRUE ); ## 9. 绑定资源组 # 绑定租户1用户到资源组 ALTER USER 'fgedu_tenant1' @'%' RESOURCE GROUP fgedu_tenant1_rg; # 绑定租户2用户到资源组 ALTER USER 'fgedu_tenant2' @'%' RESOURCE GROUP fgedu_tenant2_rg; # 绑定租户3用户到资源组 ALTER USER 'fgedu_tenant3' @'%' RESOURCE GROUP fgedu_tenant3_rg; ## 10. 验证多租户配置 $ mysql -h 192.168.1.100 -P 4000 -u fgedu_tenant1 -p USE fgedu_tenant1; CREATE TABLE fgedu_users (id INT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255)); INSERT INTO fgedu_users VALUES (1, 'user1' , 'user1@fgedu.net.cn' ); SELECT * FROM fgedu_users; $ mysql -h 192.168.1.100 -P 4000 -u fgedu_tenant2 -p USE fgedu_tenant2; CREATE TABLE fgedu_orders (id INT PRIMARY KEY, user_id INT, amount DECIMAL(10,2)); INSERT INTO fgedu_orders VALUES (1, 1, 100.00); SELECT * FROM fgedu_orders; $ mysql -h 192.168.1.100 -P 4000 -u fgedu_tenant3 -p USE fgedu_tenant3; CREATE TABLE fgedu_products (id INT PRIMARY KEY, name VARCHAR(255), price DECIMAL(10,2)); INSERT INTO fgedu_products VALUES (1, 'product1' , 50.00); SELECT * FROM fgedu_products;

## 1. 配置TiDB资源控制
$ cat > tidb_resource_control.toml << EOF [resource-control] enabled=true [resource-control.resource-groups] [fgedu_tenant1_rg] ru-per-sec=1000 priority="high" burstable=true [fgedu_tenant2_rg] ru-per-sec=500 priority="medium" burstable=true [fgedu_tenant3_rg] ru-per-sec=200 priority="low" burstable=true EOF ## 2. 更新TiDB配置 $ tiup cluster edit-config fgedu-tidb-cluster tidb_servers: - host: 192.168.1.100 config: resource-control: enabled: true resource-groups: fgedu_tenant1_rg: ru-per-sec: 1000 priority: "high" burstable: true fgedu_tenant2_rg: ru-per-sec: 500 priority: "medium" burstable: true fgedu_tenant3_rg: ru-per-sec: 200 priority: "low" burstable: true ## 3. 重启TiDB服务 $ tiup cluster restart fgedu-tidb-cluster -R tidb ## 4. 配置监控 $ cat> prometheus_multi_tenant.yml << EOF global: scrape_interval: 15s scrape_configs: - job_name: 'tidb' static_configs: - targets: ['192.168.1.100:9090'] - job_name: 'tikv' static_configs: - targets: ['192.168.1.101:9091', '192.168.1.102:9091' , '192.168.1.103:9091' ] - job_name: 'pd' static_configs: - targets: ['192.168.1.100:9092'] - job_name: 'node' static_configs: - targets: ['192.168.1.100:9100', '192.168.1.101:9100' , '192.168.1.102:9100' , '192.168.1.103:9100' ] EOF ## 5. 配置Grafana仪表板 # 访问 http://192.168.1.100:3000 # 登录（默认用户名：admin，密码：admin） # 导入TiDB多租户仪表板 ## 6. 配置告警 $ cat>
alertmanager_multi_tenant.yml << EOF global: resolve_timeout: 5m route: group_by: ['alertname', 'tenant' ] group_wait: 30s group_interval: 5m repeat_interval: 1h receiver: 'email' receivers: - name: 'email' email_configs: - to: 'admin@fgedu.net.cn' send_resolved: true inhibit_rules: - source_match: severity: 'critical' target_match: severity: 'warning' equal: ['alertname', 'tenant' ] EOF ## 7. 配置多租户管理工具 $ cat> multi_tenant_manager.py << EOF #!/usr/bin/env python3 # multi_tenant_manager.py # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn import pymysql import argparse class MultiTenantManager: def __init__(self, host, port, user, password): self.host=host self.port=port self.user=user self.password=password self.conn=None self.cursor=None def connect(self): self.conn=pymysql.connect( host=self.host, port=self.port, user=self.user, password=self.password, charset='utf8mb4' , cursorclass=pymysql.cursors.DictCursor ) self.cursor=self.conn.cursor() def disconnect(self): if self.cursor: self.cursor.close() if self.conn: self.conn.close() def create_tenant(self, tenant_name, password): try: # 创建数据库 self.cursor.execute(f"CREATE DATABASE IF NOT EXISTS fgedu_{tenant_name}") # 创建用户 self.cursor.execute(f"CREATE USER IF NOT EXISTS 'fgedu_{tenant_name}' @'%' IDENTIFIED BY '{password}'") # 授权 self.cursor.execute(f" GRANT ALL PRIVILEGES ON fgedu_{tenant_name}.* TO 'fgedu_{tenant_name}' @'%'") # 创建资源组 self.cursor.execute(f"CREATE RESOURCE GROUP IF NOT EXISTS fgedu_{tenant_name}_rg WITH (RU_PER_SEC=500, PRIORITY=MEDIUM, BURSTABLE=TRUE)") # 绑定资源组 self.cursor.execute(f"ALTER USER 'fgedu_{tenant_name}' @'%' RESOURCE GROUP fgedu_{tenant_name}_rg") self.conn.commit() print(f"Tenant {tenant_name} created successfully") except Exception as e: print(f"Error creating tenant: {e}") self.conn.rollback() def delete_tenant(self, tenant_name): try: # 删除用户 self.cursor.execute(f"DROP USER IF EXISTS 'fgedu_{tenant_name}' @'%'") # 删除资源组 self.cursor.execute(f"DROP RESOURCE GROUP IF EXISTS fgedu_{tenant_name}_rg") # 删除数据库 self.cursor.execute(f"DROP DATABASE IF EXISTS fgedu_{tenant_name}") self.conn.commit() print(f"Tenant {tenant_name} deleted successfully") except Exception as e: print(f"Error deleting tenant: {e}") self.conn.rollback() def list_tenants(self): try: # 查询数据库 self.cursor.execute("SHOW DATABASES LIKE 'fgedu_%'") databases = self.cursor.fetchall() print(" Tenants:") for db in databases: db_name=db['Database'] tenant_name=db_name.replace('fgedu_', '' ) print(f"- {tenant_name}") except Exception as e: print(f"Error listing tenants: {e}") def update_tenant_resource(self, tenant_name, ru_per_sec): try: # 更新资源组 self.cursor.execute(f"ALTER RESOURCE GROUP fgedu_{tenant_name}_rg SET RU_PER_SEC={ru_per_sec}") self.conn.commit() print(f"Tenant {tenant_name} resource updated successfully") except Exception as e: print(f"Error updating tenant resource: {e}") self.conn.rollback() if __name__=="__main__" : parser=argparse.ArgumentParser(description="Multi-tenant manager for TiDB" ) parser.add_argument('--host', default='192.168.1.100' , help='TiDB host' ) parser.add_argument('--port', type=int, default=4000, help='TiDB port' ) parser.add_argument('--user', default='root' , help='TiDB user' ) parser.add_argument('--password', default='' , help='TiDB password' ) parser.add_argument('action', choices=['create', 'delete' , 'list' , 'update' ], help='Action to perform' ) parser.add_argument('--tenant', help='Tenant name' ) parser.add_argument('--password', dest='tenant_password' , help='Tenant password' ) parser.add_argument('--ru', type=int, help='RU per second' ) args=parser.parse_args() manager=MultiTenantManager(args.host, args.port, args.user, args.password) manager.connect() if args.action=='create' : if not args.tenant or not args.tenant_password: print("Error: --tenant and --password are required for create action") else: manager.create_tenant(args.tenant, args.tenant_password) elif args.action=='delete' : if not args.tenant: print("Error: --tenant is required for delete action") else: manager.delete_tenant(args.tenant) elif args.action=='list' : manager.list_tenants() elif args.action=='update' : if not args.tenant or not args.ru: print("Error: --tenant and --ru are required for update action") else: manager.update_tenant_resource(args.tenant, args.ru) manager.disconnect() EOF ## 8. 使用多租户管理工具 $ chmod +x multi_tenant_manager.py # 创建租户 $ python3 multi_tenant_manager.py create --tenant tenant4 --password password # 列出租户 $ python3 multi_tenant_manager.py list # 更新租户资源 $ python3 multi_tenant_manager.py update --tenant tenant4 --ru 1000 # 删除租户 $ python3 multi_tenant_manager.py delete --tenant tenant4 ## 9. 配置多租户备份 $ cat>
multi_tenant_backup.sh << EOF #!/bin/bash # multi_tenant_backup.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 备份所有租户 for tenant in tenant1 tenant2 tenant3; do echo "Backing up tenant $tenant..." mysqldump -h 192.168.1.100 -P 4000 -u fgedu_$tenant -ppassword fgedu_$tenant> /tidb/backup/fgedu_$tenant_$(date +%Y%m%d).sql
echo “Backup of tenant $tenant completed”
done
EOF

## 10. 执行多租户备份
$ chmod +x multi_tenant_backup.sh
$ ./multi_tenant_backup.sh

## 1. 逻辑隔离配置

### 1.1 数据库隔离
$ mysql -h 192.168.1.100 -P 4000 -u root -p

# 创建租户数据库
CREATE DATABASE fgedu_tenant1;
CREATE DATABASE fgedu_tenant2;
CREATE DATABASE fgedu_tenant3;

# 创建租户用户
CREATE USER ‘fgedu_tenant1’@’%’ IDENTIFIED BY ‘password’;
CREATE USER ‘fgedu_tenant2’@’%’ IDENTIFIED BY ‘password’;
CREATE USER ‘fgedu_tenant3’@’%’ IDENTIFIED BY ‘password’;

# 授权
GRANT ALL PRIVILEGES ON fgedu_tenant1.* TO ‘fgedu_tenant1’@’%’;
GRANT ALL PRIVILEGES ON fgedu_tenant2.* TO ‘fgedu_tenant2’@’%’;
GRANT ALL PRIVILEGES ON fgedu_tenant3.* TO ‘fgedu_tenant3’@’%’;

### 1.2 Schema隔离
$ mysql -h 192.168.1.100 -P 4000 -u root -p

# 创建共享数据库
CREATE DATABASE fgedu_multi_tenant;

# 创建租户1用户
CREATE USER ‘fgedu_tenant1’@’%’ IDENTIFIED BY ‘password’;

# 授权
GRANT ALL PRIVILEGES ON fgedu_multi_tenant.* TO ‘fgedu_tenant1’@’%’;

$ mysql -h 192.168.1.100 -P 4000 -u fgedu_tenant1 -p

# 创建租户1 schema
CREATE SCHEMA fgedu_tenant1_schema;

# 创建表
CREATE TABLE fgedu_tenant1_schema.fgedu_users (id INT PRIMARY KEY, name VARCHAR(255), email VARCHAR(255));

### 1.3 表隔离
$ mysql -h 192.168.1.100 -P 4000 -u root -p

# 创建共享数据库
CREATE DATABASE fgedu_multi_tenant;

# 创建租户1用户
CREATE USER ‘fgedu_tenant1’@’%’ IDENTIFIED BY ‘password’;

# 授权
GRANT ALL PRIVILEGES ON fgedu_multi_tenant.fgedu_tenant1_* TO ‘fgedu_tenant1’@’%’;

$ mysql -h 192.168.1.100 -P 4000 -u fgedu_tenant1 -p

# 创建表
CREATE TABLE fgedu_multi_tenant.fgedu_tenant1_users (id INT PRIMARY KEY, name VARCHAR(255), email
VARCHAR(255));

## 2. 物理隔离配置

### 2.1 服务器隔离
# 为每个租户分配独立的服务器
# 租户1：192.168.1.101
# 租户2：192.168.1.102
# 租户3：192.168.1.103

### 2.2 存储隔离
# 为每个租户分配独立的存储
# 租户1：/tidb/fgdata/tenant1
# 租户2：/tidb/fgdata/tenant2
# 租户3：/tidb/fgdata/tenant3

### 2.3 网络隔离
# 为每个租户创建独立的VLAN
# 租户1：VLAN 100
# 租户2：VLAN 200
# 租户3：VLAN 300

## 3. 资源隔离配置

### 3.1 CPU隔离
$ tiup cluster edit-config fgedu-tidb-cluster
tidb_servers:
– host: 192.168.1.100
config:
performance.max-procs: 8

### 3.2 内存隔离
$ tiup cluster edit-config fgedu-tidb-cluster
tidb_servers:
– host: 192.168.1.100
config:
memory.limit: “8G”

### 3.3 存储隔离
$ tiup cluster edit-config fgedu-tidb-cluster
tikv_servers:
– host: 192.168.1.101
config:
storage.data-dir: “/tidb/fgdata/tikv1”
– host: 192.168.1.102
config:
storage.data-dir: “/tidb/fgdata/tikv2”
– host: 192.168.1.103
config:
storage.data-dir: “/tidb/fgdata/tikv3”

### 3.4 网络隔离
# 配置网络QoS
$ tc qdisc add dev eth0 root handle 1: htb default 10
$ tc class add dev eth0 parent 1: classid 1:1 htb rate 100mbit
$ tc class add dev eth0 parent 1:1 classid 1:10 htb rate 50mbit ceil 100mbit
$ tc class add dev eth0 parent 1:1 classid 1:20 htb rate 30mbit ceil 60mbit
$ tc class add dev eth0 parent 1:1 classid 1:30 htb rate 20mbit ceil 40mbit

# 配置流量过滤
$ tc filter add dev eth0 protocol ip parent 1:0 prio 1 u32 match ip src 192.168.1.101/32 flowid 1:10
$ tc filter add dev eth0 protocol ip parent 1:0 prio 1 u32 match ip src 192.168.1.102/32 flowid 1:20
$ tc filter add dev eth0 protocol ip parent 1:0 prio 1 u32 match ip src 192.168.1.103/32 flowid 1:30

## 4. 安全隔离配置

### 4.1 身份认证
$ mysql -h 192.168.1.100 -P 4000 -u root -p

# 创建租户用户
CREATE USER ‘fgedu_tenant1’@’%’ IDENTIFIED BY ‘password’;
CREATE USER ‘fgedu_tenant2’@’%’ IDENTIFIED BY ‘password’;
CREATE USER ‘fgedu_tenant3’@’%’ IDENTIFIED BY ‘password’;

### 4.2 授权管理
# 授权租户1只能访问自己的数据库
GRANT ALL PRIVILEGES ON fgedu_tenant1.* TO ‘fgedu_tenant1’@’%’;

# 授权租户2只能访问自己的数据库
GRANT ALL PRIVILEGES ON fgedu_tenant2.* TO ‘fgedu_tenant2’@’%’;

# 授权租户3只能访问自己的数据库
GRANT ALL PRIVILEGES ON fgedu_tenant3.* TO ‘fgedu_tenant3’@’%’;

### 4.3 数据加密
$ tiup cluster edit-config fgedu-tidb-cluster
tikv_servers:
– host: 192.168.1.101
config:
storage.encryption:
enabled: true
key-file: “/tidb/app/tidb-deploy/tikv-20160/conf/encryption.key”

### 4.4 审计日志
$ tiup cluster edit-config fgedu-tidb-cluster
tidb_servers:
– host: 192.168.1.100
config:
audit:
enabled: true
log-file: “/tidb/app/tidb-deploy/tidb-4000/log/audit.log”
format: “json”

## 5. 监控隔离配置

### 5.1 租户级监控
# 配置Prometheus监控租户资源使用
$ cat > prometheus_tenant.yml << EOF global: scrape_interval: 15s scrape_configs: - job_name: 'tidb-tenant1' static_configs: - targets: ['192.168.1.100:9090'] metrics_path: '/metrics' params: tenant: ['tenant1'] - job_name: 'tidb-tenant2' static_configs: - targets: ['192.168.1.100:9090'] metrics_path: '/metrics' params: tenant: ['tenant2'] - job_name: 'tidb-tenant3' static_configs: - targets: ['192.168.1.100:9090'] metrics_path: '/metrics' params: tenant: ['tenant3'] EOF ### 5.2 租户级告警 $ cat> alertmanager_tenant.yml << EOF global: resolve_timeout: 5m route: group_by: ['alertname', 'tenant' ] group_wait: 30s group_interval: 5m repeat_interval: 1h receiver: 'email' receivers: - name: 'email' email_configs: - to: 'admin@fgedu.net.cn' send_resolved: true inhibit_rules: - source_match: severity: 'critical' target_match: severity: 'warning' equal: ['alertname', 'tenant' ] EOF ## 6. 备份隔离配置 ### 6.1 租户级备份 $ cat> tenant_backup.sh << EOF #!/bin/bash # tenant_backup.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 备份租户1 mysqldump -h 192.168.1.100 -P 4000 -u fgedu_tenant1 -ppassword fgedu_tenant1>
/tidb/backup/fgedu_tenant1_$(date +%Y%m%d).sql

# 备份租户2
mysqldump -h 192.168.1.100 -P 4000 -u fgedu_tenant2 -ppassword fgedu_tenant2 >
/tidb/backup/fgedu_tenant2_$(date +%Y%m%d).sql

# 备份租户3
mysqldump -h 192.168.1.100 -P 4000 -u fgedu_tenant3 -ppassword fgedu_tenant3 >
/tidb/backup/fgedu_tenant3_$(date +%Y%m%d).sql
EOF

### 6.2 租户级恢复
$ cat > tenant_restore.sh << EOF #!/bin/bash # tenant_restore.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 恢复租户1 mysql -h 192.168.1.100 -P 4000 -u fgedu_tenant1 -ppassword fgedu_tenant1 < /tidb/backup/fgedu_tenant1_$(date +%Y%m%d).sql # 恢复租户2 mysql -h 192.168.1.100 -P 4000 -u fgedu_tenant2 -ppassword fgedu_tenant2 < /tidb/backup/fgedu_tenant2_$(date +%Y%m%d).sql # 恢复租户3 mysql -h 192.168.1.100 -P 4000 -u fgedu_tenant3 -ppassword fgedu_tenant3 < /tidb/backup/fgedu_tenant3_$(date +%Y%m%d).sql EOF

## 1. 身份认证配置

### 1.1 密码认证
$ mysql -h 192.168.1.100 -P 4000 -u root -p

# 创建租户用户，设置强密码
CREATE USER ‘fgedu_tenant1’@’%’ IDENTIFIED BY ‘StrongPassword123!’;
CREATE USER ‘fgedu_tenant2’@’%’ IDENTIFIED BY ‘StrongPassword456!’;
CREATE USER ‘fgedu_tenant3’@’%’ IDENTIFIED BY ‘StrongPassword789!’;

### 1.2 证书认证
# 生成CA证书
$ openssl genrsa -out ca.key 2048
$ openssl req -new -x509 -days 365 -key ca.key -out ca.crt

# 生成租户1证书
$ openssl genrsa -out fgedu_tenant1.key 2048
$ openssl req -new -key fgedu_tenant1.key -out fgedu_tenant1.csr
$ openssl x509 -req -in fgedu_tenant1.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out
fgedu_tenant1.crt -days 365

# 配置TiDB使用证书认证
$ tiup cluster edit-config fgedu-tidb-cluster
tidb_servers:
– host: 192.168.1.100
config:
security:
ssl-cert: “/tidb/app/tidb-deploy/tidb-4000/conf/tidb.crt”
ssl-key: “/tidb/app/tidb-deploy/tidb-4000/conf/tidb.key”
ssl-ca: “/tidb/app/tidb-deploy/tidb-4000/conf/ca.crt”

### 1.3 LDAP认证
$ tiup cluster edit-config fgedu-tidb-cluster
tidb_servers:
– host: 192.168.1.100
config:
authentication:
ldap:
enabled: true
url: “ldap://ldap.fgedu.net.cn:389”
base-dn: “dc=fgedu,dc=net,dc=cn”
bind-dn: “cn=admin,dc=fgedu,dc=net,dc=cn”
bind-password: “password”
user-attr: “uid”

## 2. 授权管理配置

### 2.1 基于角色的权限管理
$ mysql -h 192.168.1.100 -P 4000 -u root -p

# 创建租户1角色
CREATE ROLE fgedu_tenant1_role;

# 授予角色权限
GRANT SELECT, INSERT, UPDATE, DELETE ON fgedu_tenant1.* TO fgedu_tenant1_role;

# 授予角色给用户
GRANT fgedu_tenant1_role TO ‘fgedu_tenant1’@’%’;

# 激活角色
SET DEFAULT ROLE fgedu_tenant1_role FOR ‘fgedu_tenant1’@’%’;

### 2.2 精细权限控制
# 授予租户1用户只能读取特定表
GRANT SELECT ON fgedu_tenant1.fgedu_users TO ‘fgedu_tenant1’@’%’;

# 授予租户1用户只能插入数据
GRANT INSERT ON fgedu_tenant1.fgedu_users TO ‘fgedu_tenant1’@’%’;

# 授予租户1用户只能更新特定列
GRANT UPDATE (name, email) ON fgedu_tenant1.fgedu_users TO ‘fgedu_tenant1’@’%’;

## 3. 数据加密配置

### 3.1 传输加密
$ tiup cluster edit-config fgedu-tidb-cluster
tidb_servers:
– host: 192.168.1.100
config:
security:
ssl-cert: “/tidb/app/tidb-deploy/tidb-4000/conf/tidb.crt”
ssl-key: “/tidb/app/tidb-deploy/tidb-4000/conf/tidb.key”
ssl-ca: “/tidb/app/tidb-deploy/tidb-4000/conf/ca.crt”

tikv_servers:
– host: 192.168.1.101
config:
security:
ssl-cert: “/tidb/app/tidb-deploy/tikv-20160/conf/tikv.crt”
ssl-key: “/tidb/app/tidb-deploy/tikv-20160/conf/tikv.key”
ssl-ca: “/tidb/app/tidb-deploy/tikv-20160/conf/ca.crt”

pd_servers:
– host: 192.168.1.100
config:
security:
ssl-cert: “/tidb/app/tidb-deploy/pd-2379/conf/pd.crt”
ssl-key: “/tidb/app/tidb-deploy/pd-2379/conf/pd.key”
ssl-ca: “/tidb/app/tidb-deploy/pd-2379/conf/ca.crt”

### 3.2 存储加密
$ tiup cluster edit-config fgedu-tidb-cluster
tikv_servers:
– host: 192.168.1.101
config:
storage:
encryption:
enabled: true
key-file: “/tidb/app/tidb-deploy/tikv-20160/conf/encryption.key”

### 3.3 列级加密
$ mysql -h 192.168.1.100 -P 4000 -u fgedu_tenant1 -p

USE fgedu_tenant1;

# 创建加密函数
CREATE FUNCTION encrypt_data RETURNS STRING SONAME ‘libmysqludf_encrypt.so’;
CREATE FUNCTION decrypt_data RETURNS STRING SONAME ‘libmysqludf_encrypt.so’;

# 创建表，使用加密列
CREATE TABLE fgedu_users (
id INT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255),
password VARCHAR(255) — 存储加密后的密码
);

# 插入加密数据
INSERT INTO fgedu_users VALUES (1, ‘user1’, ‘user1@fgedu.net.cn’, encrypt_data(‘password123’));

# 查询解密数据
SELECT id, name, email, decrypt_data(password) FROM fgedu_users;

## 4. 审计日志配置

### 4.1 启用审计日志
$ tiup cluster edit-config fgedu-tidb-cluster
tidb_servers:
– host: 192.168.1.100
config:
audit:
enabled: true
log-file: “/tidb/app/tidb-deploy/tidb-4000/log/audit.log”
format: “json”
slow-threshold: 1000

### 4.2 配置审计规则
$ tiup cluster edit-config fgedu-tidb-cluster
tidb_servers:
– host: 192.168.1.100
config:
audit:
rules:
– name: “all queries”
sql-types: [“select”, “insert”, “update”, “delete”, “create”, “drop”, “alter”]
users: [“fgedu_tenant1”, “fgedu_tenant2”, “fgedu_tenant3”]
databases: [“fgedu_tenant1”, “fgedu_tenant2”, “fgedu_tenant3”]

### 4.3 分析审计日志
$ cat > analyze_audit_log.py << EOF #!/usr/bin/env python3 # analyze_audit_log.py # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn import json import re # 分析审计日志 def analyze_audit_log(log_file): with open(log_file, 'r' ) as f: for line in f: try: log=json.loads(line) # 提取关键信息 user=log.get('user', 'unknown' ) database=log.get('database', 'unknown' ) sql=log.get('sql', 'unknown' ) time=log.get('time', 'unknown' ) duration=log.get('duration', 0) # 分析异常操作 if re.search(r'drop|delete|truncate', sql, re.IGNORECASE): print(f"WARNING: {time} - User {user} executed potentially dangerous query: {sql}") # 分析慢查询 if duration> 1000:
print(f”SLOW QUERY: {time} – User {user} executed slow query ({duration}ms): {sql}”)

except json.JSONDecodeError:
continue

if __name__ == “__main__”:
analyze_audit_log(“/tidb/app/tidb-deploy/tidb-4000/log/audit.log”)
EOF

$ chmod +x analyze_audit_log.py
$ python3 analyze_audit_log.py

## 5. 网络安全配置

### 5.1 防火墙配置
# 配置防火墙，只允许特定IP访问
$ firewall-cmd –permanent –add-rich-rule=’rule family=”ipv4″ source address=”192.168.1.0/24″
port protocol=”tcp” port=”4000″ accept’
$ firewall-cmd –permanent –add-rich-rule=’rule family=”ipv4″ source address=”192.168.1.0/24″
port protocol=”tcp” port=”2379″ accept’
$ firewall-cmd –permanent –add-rich-rule=’rule family=”ipv4″ source address=”192.168.1.0/24″
port protocol=”tcp” port=”20160″ accept’
$ firewall-cmd –reload

### 5.2 网络加密
# 配置TLS加密
$ tiup cluster edit-config fgedu-tidb-cluster
tidb_servers:
– host: 192.168.1.100
config:
security:
ssl-cert: “/tidb/app/tidb-deploy/tidb-4000/conf/tidb.crt”
ssl-key: “/tidb/app/tidb-deploy/tidb-4000/conf/tidb.key”
ssl-ca: “/tidb/app/tidb-deploy/tidb-4000/conf/ca.crt”

### 5.3 网络隔离
# 创建VLAN
$ vconfig add eth0 100
$ vconfig add eth0 200
$ vconfig add eth0 300

# 配置IP地址
$ ip addr add 192.168.100.100/24 dev eth0.100
$ ip addr add 192.168.200.100/24 dev eth0.200
$ ip addr add 192.168.300.100/24 dev eth0.300

# 启用VLAN接口
$ ip link set eth0.100 up
$ ip link set eth0.200 up
$ ip link set eth0.300 up

## 6. 多租户安全监控

### 6.1 安全监控配置
# 配置Prometheus监控安全指标
$ cat > prometheus_security.yml << EOF global: scrape_interval: 15s scrape_configs: - job_name: 'tidb-security' static_configs: - targets: ['192.168.1.100:9090'] metrics_path: '/metrics' params: type: ['security'] EOF ### 6.2 安全告警配置 $ cat>
alertmanager_security.yml << EOF global: resolve_timeout: 5m route: group_by: ['alertname', 'tenant' ] group_wait: 30s group_interval: 5m repeat_interval: 1h receiver: 'email' receivers: - name: 'email' email_configs: - to: 'admin@fgedu.net.cn' send_resolved: true inhibit_rules: - source_match: severity: 'critical' target_match: severity: 'warning' equal: ['alertname', 'tenant' ] EOF ### 6.3 安全事件响应 $ cat>
security_incident_response.sh << EOF #!/bin/bash # security_incident_response.sh # from:www.itpux.com.qq113257174.wx:itpux-com # web: http://www.fgedu.net.cn # 安全事件响应脚本 # 记录安全事件 log_security_incident() { echo "$(date) - Security incident: $1">>
/tidb/log/security_incidents.log
}

# 隔离受影响的租户
isolate_tenant() {
tenant=$1
log_security_incident “Isolating tenant $tenant due to security incident”
# 实施隔离措施
iptables -A INPUT -s 192.168.1.$tenant -j DROP
}

# 通知管理员
notify_admin() {
incident=$1
log_security_incident “Notifying admin about security incident: $incident”
# 发送通知
echo “Security incident: $incident” | mail -s “Security Incident” admin@fgedu.net.cn
}

# 主函数
main() {
# 检测安全事件
if [ -f “/tmp/security_incident” ]; then
incident=$(cat /tmp/security_incident)
tenant=$(echo $incident | cut -d ‘ ‘ -f 1)
isolate_tenant $tenant
notify_admin $incident
rm /tmp/security_incident
fi
}

main
EOF

$ chmod +x security_incident_response.sh
$ crontab -e
*/5 * * * * /tidb/scripts/security_incident_response.sh