自动化数据库备份和恢复
脚本如下
import os
import subprocess
import datetime
import oss2
# 数据库配置
DB_HOST = 'localhost'
DB_USER = 'your_username'
DB_PASSWORD = 'your_password'
DB_NAME = 'your_database'
# 备份配置
BACKUP_DIR = '/path/to/backup/dir'
BACKUP_RETENTION = 7 # 保留最近7天的备份
# 阿里云OSS配置
OSS_ENDPOINT = 'http://oss-cn-hangzhou.aliyuncs.com' # 根据你的地域修改
OSS_BUCKET = 'your-oss-bucket'
OSS_ACCESS_KEY = 'your-oss-access-key'
OSS_SECRET_KEY = 'your-oss-secret-key'
def create_backup():
"""创建数据库备份"""
timestamp = datetime.datetime.now().strftime('%Y%m%d_%H%M%S')
backup_file = f"{BACKUP_DIR}/backup_{DB_NAME}_{timestamp}.sql"
command = f"mysqldump -h {DB_HOST} -u {DB_USER} -p{DB_PASSWORD} {DB_NAME} > {backup_file}"
try:
subprocess.run(command, shell=True, check=True)
print(f"备份成功:{backup_file}")
return backup_file
except subprocess.CalledProcessError as e:
print(f"备份失败:{e}")
return None
def upload_to_oss(file_path):
"""上传备份文件到阿里云OSS"""
auth = oss2.Auth(OSS_ACCESS_KEY, OSS_SECRET_KEY)
bucket = oss2.Bucket(auth, OSS_ENDPOINT, OSS_BUCKET)
file_name = os.path.basename(file_path)
try:
bucket.put_object_from_file(file_name, file_path)
print(f"文件上传到OSS成功:{file_name}")
except oss2.exceptions.OssError as e:
print(f"文件上传到OSS失败:{e}")
def cleanup_old_backups():
"""清理旧的备份文件"""
now = datetime.datetime.now()
for file in os.listdir(BACKUP_DIR):
if file.startswith(f"backup_{DB_NAME}_") and file.endswith('.sql'):
file_path = os.path.join(BACKUP_DIR, file)
file_time = datetime.datetime.strptime(file.split('_')[2].split('.')[0], '%Y%m%d')
if (now - file_time).days > BACKUP_RETENTION:
os.remove(file_path)
print(f"已删除旧备份:{file}")
def restore_backup(backup_file):
"""从备份文件恢复数据库"""
command = f"mysql -h {DB_HOST} -u {DB_USER} -p{DB_PASSWORD} {DB_NAME} < {backup_file}"
try:
subprocess.run(command, shell=True, check=True)
print(f"数据库恢复成功:{backup_file}")
except subprocess.CalledProcessError as e:
print(f"数据库恢复失败:{e}")
def main():
# 创建备份
backup_file = create_backup()
if backup_file:
# 上传到阿里云OSS
upload_to_oss(backup_file)
# 清理旧备份
cleanup_old_backups()
# 如果需要恢复数据库,取消下面的注释并指定要恢复的备份文件
# restore_backup('/path/to/backup/file.sql')
if __name__ == '__main__':
main()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
![微信](http://pic.zzppjj.top/LightPicture/2023/02/cebf13bbcea9264d.jpg)
![支付宝](http://pic.zzppjj.top/LightPicture/2023/02/d98d15501306c7c4.jpg)
上次更新: 2024/07/16, 14:12:47