100 lines
3.4 KiB
SQL
100 lines
3.4 KiB
SQL
-- ProxyAuto Pro MySQL 初始化脚本
|
|
-- 执行方式: mysql -u root -p < init_database.sql
|
|
|
|
-- 创建数据库
|
|
CREATE DATABASE IF NOT EXISTS proxyauto DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- 创建用户并授权
|
|
CREATE USER IF NOT EXISTS 'proxyauto'@'localhost' IDENTIFIED BY 'proxyauto';
|
|
GRANT ALL PRIVILEGES ON proxyauto.* TO 'proxyauto'@'localhost';
|
|
FLUSH PRIVILEGES;
|
|
|
|
-- 使用数据库
|
|
USE proxyauto;
|
|
|
|
-- 用户表
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
username VARCHAR(64) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(128) NOT NULL,
|
|
is_admin BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 代理机器表
|
|
CREATE TABLE IF NOT EXISTS proxy_machines (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(128) NOT NULL UNIQUE,
|
|
aws_service VARCHAR(32) NOT NULL DEFAULT 'ec2',
|
|
aws_region VARCHAR(64) NOT NULL DEFAULT 'us-east-1',
|
|
aws_instance_id VARCHAR(64) NOT NULL,
|
|
note VARCHAR(255) NULL,
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
|
|
-- 域名配置
|
|
cf_zone_id VARCHAR(64) NULL,
|
|
cf_record_name VARCHAR(255) NULL,
|
|
cf_record_id VARCHAR(64) NULL,
|
|
cf_proxied BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
-- 更换时间配置
|
|
change_interval_seconds INT NOT NULL DEFAULT 3600,
|
|
auto_enabled BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
-- 当前状态
|
|
current_ip VARCHAR(64) NULL,
|
|
last_run_at DATETIME(6) NULL,
|
|
last_success BOOLEAN NULL,
|
|
last_message TEXT NULL,
|
|
|
|
-- 流量预警配置
|
|
traffic_alert_enabled BOOLEAN NOT NULL DEFAULT FALSE,
|
|
traffic_alert_limit_gb FLOAT NULL,
|
|
traffic_alert_triggered BOOLEAN NOT NULL DEFAULT FALSE,
|
|
traffic_last_check_at DATETIME(6) NULL,
|
|
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 全局配置表
|
|
CREATE TABLE IF NOT EXISTS configs (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
|
-- AWS 凭证
|
|
aws_access_key VARCHAR(255) NULL,
|
|
aws_secret_key VARCHAR(255) NULL,
|
|
|
|
-- Cloudflare 凭证
|
|
cloudflare_auth_type VARCHAR(32) NOT NULL DEFAULT 'api_token',
|
|
cf_api_token VARCHAR(255) NULL,
|
|
cf_email VARCHAR(255) NULL,
|
|
cf_api_key VARCHAR(255) NULL,
|
|
|
|
-- 全局设置
|
|
release_old_eip BOOLEAN NOT NULL DEFAULT TRUE,
|
|
|
|
-- 邮件通知配置
|
|
smtp_host VARCHAR(255) NULL,
|
|
smtp_port INT NULL DEFAULT 587,
|
|
smtp_user VARCHAR(255) NULL,
|
|
smtp_password VARCHAR(255) NULL,
|
|
smtp_use_tls BOOLEAN NOT NULL DEFAULT TRUE,
|
|
alert_email VARCHAR(255) NULL,
|
|
|
|
created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
|
|
updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- 插入默认管理员用户 (密码: 80012029Lz@)
|
|
INSERT INTO users (username, password_hash, is_admin) VALUES
|
|
('admin', 'cd9989255736652cd0161653ab79fed5f7cc3dc9308b61045106e78e073202ae', TRUE)
|
|
ON DUPLICATE KEY UPDATE username=username;
|
|
|
|
-- 插入默认配置
|
|
INSERT INTO configs (cloudflare_auth_type, release_old_eip) VALUES ('api_token', TRUE)
|
|
ON DUPLICATE KEY UPDATE id=id;
|
|
|
|
SELECT 'ProxyAuto 数据库初始化完成!' AS message;
|