79 lines
3.0 KiB
Python
79 lines
3.0 KiB
Python
"""审计字段数据库迁移脚本"""
|
|
import pymysql
|
|
|
|
conn = pymysql.connect(
|
|
host='10.1.1.122',
|
|
user='nginxserver',
|
|
password='mokee.',
|
|
database='nginxserver',
|
|
charset='utf8mb4',
|
|
autocommit=True
|
|
)
|
|
cur = conn.cursor()
|
|
|
|
tables = ['forwarding_rules', 'dns_zone_config', 'certificates']
|
|
columns = [
|
|
('created_by', 'VARCHAR(100) DEFAULT NULL', '创建人'),
|
|
('updated_by', 'VARCHAR(100) DEFAULT NULL', '更新人'),
|
|
]
|
|
|
|
for table in tables:
|
|
for col_name, col_def, col_comment in columns:
|
|
cur.execute(f"""SELECT COLUMN_NAME FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA='nginxserver' AND TABLE_NAME='{table}' AND COLUMN_NAME='{col_name}'""")
|
|
if not cur.fetchone():
|
|
cur.execute(f"ALTER TABLE {table} ADD COLUMN {col_name} {col_def} COMMENT '{col_comment}'")
|
|
print(f'{table}.{col_name} 已添加')
|
|
else:
|
|
print(f'{table}.{col_name} 已存在，跳过')
|
|
|
|
# 新建 dns_records 表
|
|
cur.execute("""SELECT TABLE_NAME FROM information_schema.TABLES
|
|
WHERE TABLE_SCHEMA='nginxserver' AND TABLE_NAME='dns_records'""")
|
|
if not cur.fetchone():
|
|
cur.execute("""
|
|
CREATE TABLE dns_records (
|
|
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
|
|
record_id VARCHAR(128) NOT NULL COMMENT '阿里云解析记录ID',
|
|
domain_zone VARCHAR(255) NOT NULL COMMENT '主域名',
|
|
rr VARCHAR(255) NOT NULL COMMENT '主机记录',
|
|
record_type VARCHAR(20) NOT NULL COMMENT '记录类型: A/CNAME/TXT',
|
|
record_value VARCHAR(500) NOT NULL COMMENT '记录值',
|
|
ttl INT DEFAULT 600 COMMENT 'TTL(秒)',
|
|
status TINYINT DEFAULT 1 COMMENT '0-已删除, 1-正常',
|
|
created_by VARCHAR(100) DEFAULT NULL COMMENT '创建人',
|
|
updated_by VARCHAR(100) DEFAULT NULL COMMENT '更新人',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|
UNIQUE KEY uk_record_id (record_id),
|
|
INDEX idx_domain_zone (domain_zone),
|
|
INDEX idx_status (status)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|
COMMENT='DNS解析记录本地审计表'
|
|
""")
|
|
print('dns_records 表已创建')
|
|
else:
|
|
print('dns_records 表已存在，跳过')
|
|
|
|
# 验证
|
|
print()
|
|
print('=== 验证结果 ===')
|
|
cur.execute("""SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLUMN_COMMENT
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA='nginxserver'
|
|
AND TABLE_NAME IN ('forwarding_rules', 'dns_zone_config', 'certificates')
|
|
AND COLUMN_NAME IN ('created_by', 'updated_by')
|
|
ORDER BY TABLE_NAME, COLUMN_NAME""")
|
|
for row in cur.fetchall():
|
|
print(row)
|
|
|
|
print()
|
|
cur.execute('SHOW CREATE TABLE dns_records')
|
|
row = cur.fetchone()
|
|
if row:
|
|
print('dns_records 表已就绪')
|
|
|
|
cur.close()
|
|
conn.close()
|
|
print('\n数据库迁移完成！')
|