前几天借助AI手搓了一个简单的数据库运维平台，只支持一键安装MySQL数据库。
原文如下:
https://mp.weixin.qq.com/s/Y71e0MXMUBwlP24DIOZ4bA
今天新增了数据库备份功能，计划后面在添加一键巡检的功能。
数据库运维平台:
登录平台
http://192.168.1.5:5000/
添加了备份MySQL的功能
一键安装
通过平台，先一键安装一套新的测试MySQL，耗时不到30秒。
不到30秒安装完成
Mysql root默认密码: MyNewPass!123
[root@cjc-db-03 ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Fri 2025-06-27 17:18:24 CST; 2min 55s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 5776 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 5804 (mysqld)
Status: "Server is operational"
CGroup: /system.slice/mysqld.service
└─5804 /usr/sbin/mysqld
Jun 27 17:18:22 cjc-db-03 systemd[1]: Starting MySQL Server...
Jun 27 17:18:24 cjc-db-03 systemd[1]: Started MySQL Server.
创建测试用户
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.42 |
+-----------+
1 row in set (0.00 sec)
mysql> create user cjc@'%' identified with mysql_native_password by "MyNewPass!123";
Query OK, 0 rows affected (0.00 sec)
mysql> grant all privileges on *.* to cjc@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
创建测试数据
mysql> use cjc;
Database changed
mysql> create table t1 as select * from information_schema.processlist;
Query OK, 2 rows affected, 1 warning (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> create table t2 as select * from mysql.user;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> create table t3 as select * from mysql.plugin;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
通过平台，一键备份
执行备份
备份成功
检查备份文件
[cjc@cjc-db-02 backups]$ ls -lrth
total 24K
-rw-rw-r-- 1 cjc cjc 2.4K Jun 27 17:52 backup_cjc_t1_20250627_175241.sql
-rw-rw-r-- 1 cjc cjc 7.8K Jun 27 17:52 backup_cjc_t2_20250627_175241.sql
-rw-rw-r-- 1 cjc cjc 2.4K Jun 27 18:03 backup_cjc_t1_20250627_180329.sql
-rw-rw-r-- 1 cjc cjc 7.8K Jun 27 18:03 backup_cjc_t2_20250627_180329.sql
[cjc@cjc-db-02 backups]$ more backup_cjc_t1_20250627_180329.sql
……
架构:
~/script_runner/
├── app.py # Flask 主应用
├── tasks.db # SQLite 数据库
├── backups/ # 备份文件存储目录
├── scripts/ # MySQL 安装脚本
│ ├── install_deps.sh
│ ├── download_mysql.sh
│ ├── setup_mysql.sh
│ ├── configure_repl.sh
│ └── start_service.sh
└── templates/ # HTML 模板
├── index.html # 安装页面
└── backup.html # 备份页面
详细代码如下:
app.py
from flask import Flask, render_template, request
import sqlite3
import paramiko
import os
import time
from datetime import datetime
app = Flask(__name__)
# 配置
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
SCRIPTS_DIR = os.path.join(BASE_DIR, "scripts")
DB_PATH = os.path.join(BASE_DIR, "tasks.db")
# 初始化数据库
def init_db():
conn = sqlite3.connect(DB_PATH)
c = conn.cursor()
c.execute('''CREATE TABLE IF NOT EXISTS execution_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
host TEXT NOT NULL,
script_name TEXT NOT NULL,
output TEXT,
start_time TIMESTAMP,
end_time TIMESTAMP,
task_type TEXT)''')
conn.commit()
conn.close()
# 执行远程脚本
def execute_remote_script(host, username, password, script_name):
log_output = ""
start_time = datetime.now()
try:
# 连接SSH
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(host, username=username, password=password, timeout=10)
# 上传脚本
local_script = os.path.join(SCRIPTS_DIR, script_name)
remote_script = f"/tmp/{script_name}"
sftp = ssh.open_sftp()
sftp.put(local_script, remote_script)
sftp.chmod(remote_script, 0o755) # 设置可执行权限
sftp.close()
# 执行脚本
log_output += f"开始执行脚本: {script_name}\n"
command = f"bash {remote_script}"
stdin, stdout, stderr = ssh.exec_command(command)
# 获取输出
stdout_output = stdout.read().decode('utf-8')
stderr_output = stderr.read().decode('utf-8')
log_output += stdout_output
if stderr_output:
log_output += "\n错误输出:\n" + stderr_output
# 清理临时文件
ssh.exec_command(f"rm -f {remote_script}")
log_output += f"\n脚本执行完成: {script_name}\n"
status = "success"
except Exception as e:
log_output += f"\n执行脚本时出错: {str(e)}\n"
status = "failed"
finally:
end_time = datetime.now()
ssh.close() if 'ssh' in locals() else None
# 保存日志到数据库
save_log_to_db(host, script_name, log_output, start_time, end_time)
return log_output
# 保存日志到数据库
def save_log_to_db(host, script_name, output, start_time, end_time):
conn = sqlite3.connect(DB_PATH)
c = conn.cursor()
c.execute('''INSERT INTO execution_logs
(host, script_name, output, start_time, end_time)
VALUES (?, ?, ?, ?, ?)''',
(host, script_name, output, start_time, end_time))
conn.commit()
conn.close()
# app.py 中新增函数
def perform_mysql_backup(ssh_host, ssh_user, ssh_pass,
db_host, db_port, db_name, tables, db_user, db_pass):
"""执行MySQL备份操作"""
log_output = ""
start_time = datetime.now()
try:
# 连接SSH
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect(ssh_host, username=ssh_user, password=ssh_pass, timeout=10)
# 创建备份文件名
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
backup_dir = os.path.join(BASE_DIR, "backups")
os.makedirs(backup_dir, exist_ok=True)
# 处理表名
table_list = [t.strip() for t in tables.split(",")]
backup_files = [] # 存储成功备份的文件名
# 执行备份
for table in table_list:
backup_file = f"backup_{db_name}_{table}_{timestamp}.sql"
local_path = os.path.join(backup_dir, backup_file)
# 构建mysqldump命令
dump_cmd = (
f"mysqldump -h{db_host} -P{db_port} -u{db_user} -p'{db_pass}' "
f"{db_name} {table} > /tmp/{backup_file}"
)
log_output += f"备份表: {table}...\n"
# 执行备份命令
stdin, stdout, stderr = ssh.exec_command(dump_cmd)
# 获取输出
stdout_output = stdout.read().decode('utf-8')
stderr_output = stderr.read().decode('utf-8')
# 检查是否有真正的错误（忽略安全警告）
if stderr_output and "Warning" not in stderr_output:
# 有真正的错误
log_output += f"备份错误: {stderr_output}\n"
raise Exception(f"备份表 {table} 失败")
# 下载备份文件
sftp = ssh.open_sftp()
sftp.get(f"/tmp/{backup_file}", local_path)
sftp.remove(f"/tmp/{backup_file}") # 清理临时文件
sftp.close()
log_output += f"成功备份到: {local_path}\n"
backup_files.append(backup_file)
status = "success"
except Exception as e:
log_output += f"\n备份过程中出错: {str(e)}\n"
status = "failed"
backup_files = []
finally:
end_time = datetime.now()
ssh.close() if 'ssh' in locals() else None
# 保存日志到数据库
save_backup_log(ssh_host, db_name, tables, log_output, start_time, end_time, status)
return log_output, backup_files
def save_backup_log(host, db_name, tables, output, start_time, end_time, status):
"""保存备份日志到数据库"""
conn = sqlite3.connect(DB_PATH)
c = conn.cursor()
c.execute('''INSERT INTO execution_logs
(host, script_name, output, start_time, end_time, task_type)
VALUES (?, ?, ?, ?, ?, ?)''',
(host, f"backup_{db_name}", output, start_time, end_time, "backup"))
conn.commit()
conn.close()
# 路由定义
@app.route('/')
def index():
return render_template('index.html', logs="请填写信息并执行脚本")
# 新增：备份页面路由
@app.route('/backup')
def backup_page():
return render_template('backup.html', logs="请填写信息并执行备份")
@app.route('/execute', methods=['POST'])
def execute_scripts():
host = request.form['host']
username = request.form['username']
password = request.form['password']
scripts = request.form.getlist('scripts')
if not scripts:
return render_template('index.html', logs="错误：未选择任何脚本！")
full_log = f"===== 开始执行MySQL安装任务 =====\n"
full_log += f"目标主机: {host}\n"
full_log += f"执行时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n"
full_log += f"将执行 {len(scripts)} 个脚本\n\n"
for script in scripts:
script_log = execute_remote_script(host, username, password, script)
full_log += script_log
full_log += "\n" + "="*50 + "\n"
full_log += "\n===== 所有脚本执行完成! ====="
return render_template('index.html', logs=full_log)
# 执行备份
@app.route('/do_backup', methods=['POST'])
def execute_backup():
# 获取表单数据
host = request.form['host']
username = request.form['username']
password = request.form['password']
db_host = request.form['db_host']
db_port = request.form['db_port']
db_name = request.form['db_name']
tables = request.form['tables']
db_user = request.form['db_user']
db_pass = request.form['db_pass']
full_log = f"===== 开始MySQL备份任务 =====\n"
full_log += f"目标主机: {host}\n"
full_log += f"数据库: {db_name}\n"
full_log += f"表: {tables}\n"
full_log += f"执行时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n\n"
# 执行备份
backup_log = perform_mysql_backup(
host, username, password,
db_host, db_port, db_name, tables, db_user, db_pass
)
full_log += format(backup_log)
full_log += "\n===== 备份任务完成! ====="
return render_template('backup.html', logs=full_log)
if __name__ == '__main__':
init_db()
app.run(host='0.0.0.0', port=5000, debug=True)
backup.html
<!DOCTYPE html>
<html>
<head>
<title>MySQL备份工具</title>
<style>
body { font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; }
.navbar { background: #333; overflow: hidden; margin-bottom: 20px; }
.navbar a { float: left; color: white; text-align: center; padding: 14px 16px; text-decoration: none; }
.navbar a:hover { background: #ddd; color: black; }
.form-group { margin-bottom: 15px; }
label { display: block; margin-bottom: 5px; font-weight: bold; }
input[type="text"], input[type="password"] {
width: 100%; padding: 8px; border: 1px solid #ddd; border-radius: 4px;
}
button {
background: #2196F3; color: white; border: none; padding: 10px 20px;
border-radius: 4px; cursor: pointer; font-size: 16px;
}
button:hover { background: #0b7dda; }
.logs {
background: #333; color: #fff; padding: 15px; border-radius: 4px;
margin-top: 20px; max-height: 400px; overflow-y: auto;
}
</style>
</head>
<body>
<h1>MySQL数据库备份</h1>
<form action="/do_backup" method="POST">
<div class="navbar">
<a href="/">安装MySQL</a>
<a href="/backup">备份MySQL</a>
</div>
<h3>SSH连接信息：</h3>
<div class="form-group">
<label for="host">目标主机IP:</label>
<input type="text" id="host" name="host" placeholder="例如: 192.168.1.100" required>
</div>
<div class="form-group">
<label for="username">SSH用户名:</label>
<input type="text" id="username" name="username" placeholder="例如: root" required>
</div>
<div class="form-group">
<label for="password">SSH密码:</label>
<input type="password" id="password" name="password" placeholder="输入密码" required>
</div>
<h3>数据库信息：</h3>
<div class="form-group">
<label for="db_host">数据库IP:</label>
<input type="text" id="db_host" name="db_host" value="localhost" required>
</div>
<div class="form-group">
<label for="db_port">数据库端口:</label>
<input type="text" id="db_port" name="db_port" value="3306" required>
</div>
<div class="form-group">
<label for="db_name">数据库名称:</label>
<input type="text" id="db_name" name="db_name" placeholder="例如: mydb" required>
</div>
<div class="form-group">
<label for="tables">表名（多个表用逗号分隔）:</label>
<input type="text" id="tables" name="tables" placeholder="例如: users,products" required>
</div>
<div class="form-group">
<label for="db_user">数据库用户名:</label>
<input type="text" id="db_user" name="db_user" value="root" required>
</div>
<div class="form-group">
<label for="db_pass">数据库密码:</label>
<input type="password" id="db_pass" name="db_pass" placeholder="数据库密码" required>
</div>
<button type="submit">执行备份</button>
</form>
<h3>备份日志:</h3>
<pre class="logs">{{ logs }}</pre>
</body>
</html>
index.html
<!DOCTYPE html>
<html>
<head>
<title>简易MySQL安装平台</title>
<style>
body { font-family: Arial, sans-serif; max-width: 800px; margin: 0 auto; padding: 20px; }
h1 { text-align: center; color: #333; }
form { background: #f9f9f9; padding: 20px; border-radius: 5px; }
.form-group { margin-bottom: 15px; }
label { display: block; margin-bottom: 5px; font-weight: bold; }
input[type="text"], input[type="password"] {
width: 100%; padding: 8px; border: 1px solid #ddd; border-radius: 4px;
}
.script-item { margin: 10px 0; padding: 10px; background: #fff; border-left: 4px solid #4CAF50; }
button {
background: #4CAF50; color: white; border: none; padding: 10px 20px;
border-radius: 4px; cursor: pointer; font-size: 16px;
}
button:hover { background: #45a049; }
.logs {
background: #333; color: #fff; padding: 15px; border-radius: 4px;
margin-top: 20px; max-height: 400px; overflow-y: auto;
}
</style>
</head>
<body>
<h1>智库DBA:MySQL自动化安装平台</h1>
<form action="/execute" method="POST">
<div class="navbar">
<a href="/">安装MySQL</a>
<a href="/backup">备份MySQL</a>
</div>
<div class="form-group">
<label for="host">目标主机IP:</label>
<input type="text" id="host" name="host" placeholder="例如: 192.168.1.100" required>
</div>
<div class="form-group">
<label for="username">SSH用户名:</label>
<input type="text" id="username" name="username" placeholder="例如: root" required>
</div>
<div class="form-group">
<label for="password">SSH密码:</label>
<input type="password" id="password" name="password" placeholder="输入密码" required>
</div>
<h3>选择要执行的MySQL安装步骤:</h3>
<div class="script-item">
<input type="checkbox" id="script1" name="scripts" value="install_deps.sh" checked>
<label for="script1">1. 安装系统依赖</label>
</div>
<div class="script-item">
<input type="checkbox" id="script2" name="scripts" value="download_mysql.sh" checked>
<label for="script2">2. 下载并安装MySQL</label>
</div>
<div class="script-item">
<input type="checkbox" id="script3" name="scripts" value="setup_mysql.sh" checked>
<label for="script3">3. 初始化MySQL配置</label>
</div>
<div class="script-item">
<input type="checkbox" id="script4" name="scripts" value="configure_repl.sh">
<label for="script4">4. 配置复制（可选）</label>
</div>
<div class="script-item">
<input type="checkbox" id="script5" name="scripts" value="start_service.sh" checked>
<label for="script5">5. 启动MySQL服务</label>
</div>
<button type="submit">执行安装</button>
</form>
<h3>执行日志:</h3>
<pre class="logs">{{ logs }}</pre>
</body>
</html>
欢迎关注我的公众号《IT小Chen》
近期和多位专家联合创建了两个知识星球，限时优惠100元，感兴趣的可以看看，支持72小时内无理由退款。
最后修改时间：2025-06-27 15:17:36
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
