# 授予权限
mysql> GRANT ALL PRIVILEGES ON fgedudb.* TO ‘admin’;
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON fgedudb.* TO ‘developer’;
mysql> GRANT SELECT ON fgedudb.* TO ‘readonly’;

# 创建用户
mysql> CREATE USER ‘fgedu_admin’@’%’ IDENTIFIED BY ‘password’;
mysql> CREATE USER ‘fgedu_dev’@’%’ IDENTIFIED BY ‘password’;
mysql> CREATE USER ‘fgedu_read’@’%’ IDENTIFIED BY ‘password’;

# 分配角色
mysql> GRANT ‘admin’ TO ‘fgedu_admin’@’%’;
mysql> GRANT ‘developer’ TO ‘fgedu_dev’@’%’;
mysql> GRANT ‘readonly’ TO ‘fgedu_read’@’%’;

# 激活角色
mysql> SET DEFAULT ROLE ALL TO ‘fgedu_admin’@’%’;
mysql> SET DEFAULT ROLE ALL TO ‘fgedu_dev’@’%’;
mysql> SET DEFAULT ROLE ALL TO ‘fgedu_read’@’%’;

# 查看角色
mysql> SHOW ROLES;

# 查看用户角色
mysql> SHOW GRANTS FOR ‘fgedu_admin’@’%’;

# 安装MySQL Enterprise Firewall
# 风哥教程参考MySQL官方文档

# 配置防火墙规则
mysql> CALL mysql.sp_set_firewall_group(‘admin’, ‘SELECT.*fgedudb.*’);
mysql> CALL mysql.sp_set_firewall_group(‘developer’, ‘SELECT.*fgedudb.*, INSERT.*fgedudb.*, UPDATE.*fgedudb.*, DELETE.*fgedudb.*’);
mysql> CALL mysql.sp_set_firewall_group(‘readonly’, ‘SELECT.*fgedudb.*’);

# 分配用户到防火墙组
mysql> CALL mysql.sp_set_firewall_group_member(‘admin’, ‘fgedu_admin’@’%’);
mysql> CALL mysql.sp_set_firewall_group_member(‘developer’, ‘fgedu_dev’@’%’);
mysql> CALL mysql.sp_set_firewall_group_member(‘readonly’, ‘fgedu_read’@’%’);

# 启用防火墙
mysql> SET GLOBAL mysql_firewall_mode = ‘ON’;

# 配置TDE
mysql> SET GLOBAL innodb_encrypt_tables = ON;
mysql> SET GLOBAL innodb_encrypt_log = ON;
mysql> SET GLOBAL innodb_encryption_threads = 4;

# 创建加密表
mysql> CREATE TABLE fgedudb.fgedu_user (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50),
-> age INT,
-> email VARCHAR(100),
-> sensitive_data VARCHAR(255)
-> ) ENGINE=InnoDB ENCRYPTION=’Y’;

# 加密现有表
mysql> ALTER TABLE fgedudb.fgedu_order ENCRYPTION=’Y’;

# 查看加密状态
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_SCHEMA = ‘fgedudb’;

# 生成SSL证书
$ openssl req -x509 -nodes -days 365 -newkey rsa:2048 -keyout /polardb/app/mysql/data/server-key.pem -out /polardb/app/mysql/data/server-cert.pem

# 配置MySQL
$ cat /polardb/app/mysql/my.cnf
[mysqld]
ssl-ca=/polardb/app/mysql/data/ca-cert.pem
ssl-cert=/polardb/app/mysql/data/server-cert.pem
ssl-key=/polardb/app/mysql/data/server-key.pem

# 重启MySQL
$ systemctl restart mysqld

# 创建SSL用户
mysql> CREATE USER ‘fgedu_ssl’@’%’ IDENTIFIED BY ‘password’ REQUIRE SSL;

# 测试SSL连接
$ mysql -u fgedu_ssl -p -h pc-12345678.mysql.polardb.rds.aliyuncs.com -P 3306 –ssl

# 查看SSL状态
mysql> SHOW SESSION STATUS LIKE ‘Ssl_cipher’;

# 配置审计日志
$ cat /polardb/app/mysql/my.cnf
[mysqld]
audit_log=ON
audit_log_file=/polardb/app/mysql/data/audit.log
audit_log_format=JSON
audit_log_policy=ALL

# 重启MySQL
$ systemctl restart mysqld

# 查看审计日志
$ cat /polardb/app/mysql/data/audit.log

# 使用MySQL Enterprise Audit
# 安装MySQL Enterprise Audit
# 风哥教程参考MySQL官方文档

# 配置审计策略
mysql> CREATE AUDIT POLICY ‘all_actions’ ACTIONS ALL;
mysql> ATTACH AUDIT POLICY ‘all_actions’ TO ‘fgedu’@’%’;

# 安装Prometheus
$ yum install prometheus

# 安装Grafana
$ yum install grafana

# 配置Prometheus
$ cat /etc/prometheus/prometheus.yml
global:
scrape_interval: 15s

scrape_configs:
– job_name: ‘mysql’
static_configs:
– targets: [‘localhost:9104’]

# 安装MySQL Exporter
$ wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz
$ tar -xzf mysqld_exporter-0.14.0.linux-amd64.tar.gz
$ cd mysqld_exporter-0.14.0.linux-amd64
$ ./mysqld_exporter –config.my-cnf=/etc/my.cnf

# 启动Prometheus和Grafana
$ systemctl start prometheus
$ systemctl start grafana

# 访问Grafana
# http://localhost:3000