
第一步数据库查询创建数据库和数据库账号密码

create database toughradius DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
GRANT ALL ON toughradius.* TO raduser@'127.0.0.1' IDENTIFIED BY '19821230a' WITH GRANT OPTION;FLUSH PRIVILEGES;

核心数据表创建

USE toughradius;

-- BRAS设备表（网络接入设备配置）
CREATE TABLE IF NOT EXISTS tr_bras (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(128) NULL,
    name VARCHAR(64) NOT NULL,
    ipaddr VARCHAR(32) NULL,
    vendor_id VARCHAR(32) NOT NULL,
    portal_vendor VARCHAR(32) NOT NULL,
    secret VARCHAR(64) NOT NULL,
    coa_port INT NOT NULL,
    ac_port INT NOT NULL,
    auth_limit INT NULL,
    acct_limit INT NULL,
    status ENUM('enabled', 'disabled') NULL,
    remark VARCHAR(512) NULL,
    create_time DATETIME NOT NULL,
    
    INDEX ix_tr_bras_identifier (identifier),
    INDEX ix_tr_bras_ipaddr (ipaddr)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 系统配置表
CREATE TABLE IF NOT EXISTS tr_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(32) NOT NULL,
    name VARCHAR(128) NOT NULL,
    value VARCHAR(255) NULL,
    remark VARCHAR(255) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 用户订阅表
CREATE TABLE IF NOT EXISTS tr_subscribe (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    node_id BIGINT DEFAULT 0 NOT NULL,
    subscriber VARCHAR(32) NULL,
    realname VARCHAR(32) NULL,
    password VARCHAR(128) NOT NULL,
    domain VARCHAR(128) NULL,
    addr_pool VARCHAR(128) NULL,
    policy VARCHAR(512) NULL,
    is_online INT NULL,
    active_num INT NULL,
    bind_mac TINYINT(1) NULL,
    bind_vlan TINYINT(1) NULL,
    ip_addr VARCHAR(32) NULL,
    mac_addr VARCHAR(32) NULL,
    in_vlan INT NULL,
    out_vlan INT NULL,
    up_rate BIGINT NULL,
    down_rate BIGINT NULL,
    up_peak_rate BIGINT NULL,
    down_peak_rate BIGINT NULL,
    up_rate_code VARCHAR(32) NULL,
    down_rate_code VARCHAR(32) NULL,
    status ENUM('enabled', 'disabled') NULL,
    remark VARCHAR(512) NULL,
    begin_time DATETIME NOT NULL,
    expire_time DATETIME NOT NULL,
    create_time DATETIME NOT NULL,
    update_time DATETIME NULL,
    
    INDEX ix_tr_subscribe_create_time (create_time),
    INDEX ix_tr_subscribe_expire_time (expire_time),
    INDEX ix_tr_subscribe_status (status),
    INDEX ix_tr_subscribe_subscriber (subscriber),
    INDEX ix_tr_subscribe_update_time (update_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 在线用户表（用于记录当前在线用户）
CREATE TABLE IF NOT EXISTS tr_online (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    subscriber VARCHAR(32) NOT NULL,
    nas_id VARCHAR(64) NOT NULL,
    nas_addr VARCHAR(32) NOT NULL,
    nas_port VARCHAR(32) NULL,
    mac_addr VARCHAR(32) NULL,
    ip_addr VARCHAR(32) NULL,
    start_time DATETIME NOT NULL,
    accept_time DATETIME NULL,
    framed_ipaddr VARCHAR(32) NULL,
    framed_netmask VARCHAR(32) NULL,
    nas_class VARCHAR(64) NULL,
    nas_port_id VARCHAR(64) NULL,
    nas_port_type INT NULL,
    nas_real_port VARCHAR(32) NULL,
    service_type VARCHAR(32) NULL,
    acct_session_id VARCHAR(64) NULL,
    acct_session_time BIGINT NULL,
    acct_input_octets BIGINT NULL,
    acct_output_octets BIGINT NULL,
    acct_input_packets BIGINT NULL,
    acct_output_packets BIGINT NULL,
    acct_terminate_cause VARCHAR(32) NULL,
    UNIQUE KEY uk_tr_online_session (subscriber, nas_addr, acct_session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

测试数据插入

-- 插入测试BRAS设备（Windows环境下IP建议使用实际IP或127.0.0.1）
INSERT INTO tr_bras (identifier, name, ipaddr, vendor_id, portal_vendor, secret, coa_port, ac_port, auth_limit, acct_limit, status, remark, create_time)
VALUES 
('bras-windows-01', 'Windows测试BRAS', '127.0.0.1', '14988', 'cmccv1', 'secret', 3799, 2000, 1000, 1000, 'enabled', 'Windows环境测试设备', NOW()),
('bras-windows-02', 'Windows华为设备', '192.168.1.1', '2011', 'huawei', 'huawei123', 3799, 2000, 2000, 2000, 'enabled', '华为设备测试', NOW());

-- 插入测试用户
INSERT INTO tr_subscribe (node_id, subscriber, realname, password, domain, addr_pool, policy, is_online, active_num, bind_mac, bind_vlan, ip_addr, mac_addr, in_vlan, out_vlan, up_rate, down_rate, up_peak_rate, down_peak_rate, up_rate_code, down_rate_code, status, remark, begin_time, expire_time, create_time, update_time)
VALUES 
(0, 'test01', '测试用户01', '888888', NULL, NULL, NULL, 0, 10, 0, 0, '', '', 0, 0, 10000000, 10000000, 20000000, 20000000, '10M', '10M', 'enabled', 'Windows测试用户', NOW(), DATE_ADD(NOW(), INTERVAL 365 DAY), NOW(), NOW()),
(0, 'test02', '测试用户02', '888888', NULL, 'pool1', 'policy1', 0, 5, 1, 0, '192.168.10.100', '00:11:22:33:44:55', 100, 200, 20000000, 20000000, 50000000, 50000000, '20M', '20M', 'enabled', '绑定MAC测试用户', NOW(), DATE_ADD(NOW(), INTERVAL 180 DAY), NOW(), NOW());

-- 插入基础系统配置
INSERT INTO tr_config (type, name, value, remark) VALUES
('system', 'admin_password', 'admin123', '管理员默认密码'),
('radius', 'auth_port', '1812', 'RADIUS认证端口'),
('radius', 'acct_port', '1813', 'RADIUS计费端口'),
('system', 'session_timeout', '3600', '会话超时时间(秒)');

