📊 CmdCode 访客统计数据库管理

香港站 MySQL 访客统计数据库的连接、诊断、数据查询及常见问题排查

连接信息

项目	香港站 MySQL	说明
`主机`	`my4320439746.xincache1.cn`	实际数据存储位置
`端口`	`3306`	标准 MySQL 端口
`数据库`	`my4320439746`	访客统计库
`用户`	`my4320439746`	读写权限
`密码`	`Xusu8800033`	仅内部使用
`全球站 MySQL`	`my3816429080.xincache1.cn`	未使用全球站无 PHP，数据走跨域 API 写入香港站

mysql -h my4320439746.xincache1.cn -P 3306 -u my4320439746 -p'Xusu8800033' my4320439746

表结构

visit_logs — 香港站原始访问日志

CREATE TABLE visit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip VARCHAR(45) NOT NULL,
    domain VARCHAR(100) DEFAULT '',
    is_mainland TINYINT(1) DEFAULT 0,
    visit_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    user_agent VARCHAR(500) DEFAULT '',
    page VARCHAR(200) DEFAULT '',
    referer VARCHAR(200) DEFAULT '',
    INDEX idx_visit_time (visit_time),
    INDEX idx_domain (domain),
    INDEX idx_ip (ip)
);

global_visit_logs — 全球站跨域写入日志

CREATE TABLE global_visit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip VARCHAR(45) NOT NULL,
    domain VARCHAR(100) DEFAULT '',
    is_mainland TINYINT(1) DEFAULT 0,
    visit_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    user_agent VARCHAR(500) DEFAULT '',
    page VARCHAR(200) DEFAULT '',
    referer VARCHAR(200) DEFAULT '',
    INDEX idx_visit_time (visit_time),
    INDEX idx_domain (domain),
    INDEX idx_ip (ip)
);

daily_stats / global_daily_stats — 每日汇总表

CREATE TABLE daily_stats (
    stat_date DATE PRIMARY KEY,
    total_visits INT DEFAULT 0,
    mainland_visits INT DEFAULT 0,
    overseas_visits INT DEFAULT 0,
    unique_ips INT DEFAULT 0
);

常用诊断查询

1. 查看今日 / 本月汇总

-- 今日总访问
SELECT COUNT(*) AS today_visits,
       COUNT(DISTINCT ip) AS today_unique_ips,
       SUM(is_mainland) AS mainland,
       SUM(NOT is_mainland) AS overseas
FROM visit_logs
WHERE DATE(visit_time) = CURDATE();

-- 本月总访问
SELECT COUNT(*) AS month_visits,
       COUNT(DISTINCT ip) AS month_unique_ips
FROM visit_logs
WHERE DATE_FORMAT(visit_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m');

-- 全球站今日
SELECT COUNT(*) AS today_visits,
       COUNT(DISTINCT ip) AS unique_ips
FROM global_visit_logs
WHERE DATE(visit_time) = CURDATE();

2. 按域名查询今日 / 本月

-- 今日各域名访问量（香港站）
SELECT domain,
       COUNT(*) AS visits,
       COUNT(DISTINCT ip) AS unique_ips,
       SUM(is_mainland) AS cn,
       SUM(NOT is_mainland) AS os
FROM visit_logs
WHERE DATE(visit_time) = CURDATE()
GROUP BY domain
ORDER BY visits DESC;

-- 本月各域名
SELECT domain,
       COUNT(*) AS visits,
       COUNT(DISTINCT ip) AS unique_ips
FROM visit_logs
WHERE DATE_FORMAT(visit_time, '%Y-%m') = DATE_FORMAT(NOW(), '%Y-%m')
GROUP BY domain
ORDER BY visits DESC;

-- 全球站按域名
SELECT domain,
       COUNT(*) AS visits,
       COUNT(DISTINCT ip) AS unique_ips
FROM global_visit_logs
WHERE DATE(visit_time) = CURDATE()
GROUP BY domain
ORDER BY visits DESC;

3. 最近访客记录

-- 香港站最近 20 条
SELECT id, domain, ip, is_mainland, visit_time, page, user_agent
FROM visit_logs
ORDER BY visit_time DESC
LIMIT 20;

-- 全球站最近 20 条
SELECT id, domain, ip, is_mainland, visit_time, page, user_agent
FROM global_visit_logs
ORDER BY visit_time DESC
LIMIT 20;

-- 两站合并最近 20 条
(SELECT '香港' AS source, domain, ip, is_mainland, visit_time, page
 FROM visit_logs ORDER BY visit_time DESC LIMIT 10)
UNION ALL
(SELECT '全球' AS source, domain, ip, is_mainland, visit_time, page
 FROM global_visit_logs ORDER BY visit_time DESC LIMIT 10)
ORDER BY visit_time DESC
LIMIT 20;

4. 近 7 天趋势

SELECT stat_date,
       total_visits,
       mainland_visits,
       overseas_visits,
       unique_ips
FROM daily_stats
WHERE stat_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
ORDER BY stat_date DESC;

-- 全球站趋势
SELECT stat_date,
       total_visits,
       mainland_visits,
       overseas_visits,
       unique_ips
FROM global_daily_stats
WHERE stat_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
ORDER BY stat_date DESC;

5. Tracker 故障排查

-- 检查 tracker 是否有数据写入（实时监控）
SELECT COUNT(*) AS total_records,
       MIN(visit_time) AS oldest,
       MAX(visit_time) AS newest
FROM visit_logs;

SELECT COUNT(*) AS total_records,
       MIN(visit_time) AS oldest,
       MAX(visit_time) AS newest
FROM global_visit_logs;

-- 检查某域名是否被记录（如 qqcmd.cn）
SELECT COUNT(*) AS visits, COUNT(DISTINCT ip) AS ips
FROM visit_logs
WHERE domain = 'qqcmd.cn' AND DATE(visit_time) = CURDATE();

SELECT COUNT(*) AS visits, COUNT(DISTINCT ip) AS ips
FROM global_visit_logs
WHERE domain = 'qqcmd.cn' AND DATE(visit_time) = CURDATE();

-- 检查是否有异常大量重复 IP（刷流量检测）
SELECT ip, COUNT(*) AS cnt, domain
FROM visit_logs
WHERE DATE(visit_time) = CURDATE()
GROUP BY ip
HAVING cnt > 50
ORDER BY cnt DESC
LIMIT 20;

-- 检查某时间段是否有写入
SELECT COUNT(*) FROM visit_logs
WHERE visit_time >= NOW() - INTERVAL 30 MINUTE;

SELECT COUNT(*) FROM global_visit_logs
WHERE visit_time >= NOW() - INTERVAL 30 MINUTE;

数据验证（与页面展示对比）

API 端点：https://cmdcode.cn/stats_combined_api.php

curl -sL https://cmdcode.cn/stats_combined_api.php | python3 -m json.tool

验证项：

merged.today.total_visits ≈ 香港站 + 全球站今日 sum
merged.today.unique_ips = 两站 IP 合并去重数
hk_domains 数量应 ≈ 19（香港站域名数）
global_domains 数量应 ≈ 16（全球站域名数）

常见问题

问题	可能原因	排查方法
统计页面数据为 0	Tracker 未执行 / API 报错	检查浏览器控制台 Network 中 `tracker.php` 或 `tracker_global.js` 请求是否 200
全球站数据全为 0	JS Tracker 未加载 / CORS 被拒	检查 `tracker_global.js` 是否存在，检查浏览器控制台 CORS 报错
某个域名数据缺失	该域名未在 Tracker 中注册	在 `visit_logs/domain` 查询该域名是否有记录
数据异常多/少	爬虫/刷流量或 Tracker 失效	按 IP 分组看是否有单个 IP 异常大量请求
汇总数据不准	两站 IP 去重逻辑问题	确认 `merged.unique_ips` = 两站 IP UNION 后去重数