数据库操作手册

SQLite

sqlite3 /path/to/db.sqlite

# 元数据
.tables                                    # 列出所有表
.schema <table>                            # 查看建表语句
.indexes <table>                           # 列出索引
.mode column                               # 列对齐输出 (比默认分隔符可读)
.headers on

# 查询
SELECT * FROM <table> LIMIT 10;
SELECT name, kind FROM resource ORDER BY kind;
SELECT COUNT(*) FROM resource WHERE kind='dashboard';
.exit

# 备份 (在线, 不阻塞读写)
sqlite3 db.sqlite ".backup backup.sqlite"
# 或: sqlite3 db.sqlite ".dump" > dump.sql   # SQL 文本导出
# 恢复: sqlite3 new.sqlite < dump.sql

# 优化
sqlite3 db.sqlite "PRAGMA integrity_check"    # 完整性检查
sqlite3 db.sqlite "VACUUM"                    # 回收已删除空间 (会重建 DB, 需要额外磁盘空间)
sqlite3 db.sqlite "PRAGMA wal_checkpoint(TRUNCATE)"  # WAL → 主文件

Grafana DB 维护 (SQLite, unified storage)

systemctl stop grafana
cp /var/lib/grafana/grafana.db grafana.db.bak    # 一定要先备份

sqlite3 /var/lib/grafana/grafana.db <<SQL
-- 查看所有 dashboard/folder (名称 + 类型)
SELECT name, kind FROM resource WHERE kind IN ('dashboard', 'folder') ORDER BY kind;

-- 删除 UI 建的 dashboard (provisioned 的归文件管, 不在这里)
DELETE FROM resource WHERE name = 'old-dashboard' AND kind = 'dashboard';

-- resource_history 记录了所有版本变更, 可能很大, 可安全清理
SELECT COUNT(*) FROM resource_history;
DELETE FROM resource_history;

-- 回收空间
VACUUM;
SQL

systemctl start grafana

PostgreSQL

psql -U postgres -d dbname

# 元数据
\l                                          # 列出数据库
\dt                                         # 列出表
\d <table>                                  # 表结构 (列 + 索引 + 约束)
\du                                         # 用户/角色
\dx                                         # 已安装扩展

# 备份 (pg_dump, 不在 psql 内)
pg_dump -U postgres dbname > backup.sql     # 单个库
pg_dumpall -U postgres > all.sql            # 所有库 + 角色
pg_dump -U postgres -Fc dbname > backup.dump  # 自定义格式 (可并行恢复)

# 恢复
psql -U postgres dbname < backup.sql
pg_restore -U postgres -d dbname backup.dump

# 连接数
SELECT count(*) FROM pg_stat_activity;
SELECT pid, state, query FROM pg_stat_activity WHERE state != 'idle';

MySQL/MariaDB

mysql -u root -p

# 元数据
SHOW DATABASES;
SHOW TABLES;
DESCRIBE <table>;

# 备份
mysqldump -u root -p dbname > backup.sql
mysqldump -u root -p --all-databases > all.sql
mysqldump -u root -p --no-data dbname > schema.sql   # 只导出结构

# 恢复
mysql -u root -p dbname < backup.sql

备份自动化 (systemd timer)

cat > /etc/systemd/system/db-backup.service <<'EOF'
[Service]
Type=oneshot
ExecStart=/usr/local/bin/db-backup.sh
User=root
EOF

cat > /etc/systemd/system/db-backup.timer <<'EOF'
[Timer]
OnCalendar=daily
Persistent=true
[Install]
WantedBy=timers.target
EOF

cat > /usr/local/bin/db-backup.sh <<'EOF'
#!/bin/bash
set -e
BACKUP_DIR=/backup/db
mkdir -p "$BACKUP_DIR"
DATE=$(date +%Y%m%d)
sqlite3 /var/lib/grafana/grafana.db ".backup $BACKUP_DIR/grafana-$DATE.db"
find "$BACKUP_DIR" -name 'grafana-*.db' -mtime +30 -delete
EOF
chmod +x /usr/local/bin/db-backup.sh
systemctl enable --now db-backup.timer