資料庫備份恢復

import datetime
import os
import subprocess
# 資料庫備份目錄
BACKUP_DIR = '/path/to/backup'
# 備份檔案保留週期（天）
RETENTION_PERIOD = 7
# 備份資料庫
def backup_database():
current_time = datetime.datetime.now()
backup_file = f"backup_{current_time.strftime('%Y%m%d%H%M%S')}.sql"
backup_path = os.path.join(BACKUP_DIR, backup_file)

# 使用 subprocess 模組執行資料庫備份命令
backup_command = [
'mysqldump',
'-u',
'username',
'-p',
'password',
'--all-databases'
]
with open(backup_path, 'w') as backup_file:
subprocess.run(backup_command, stdout=backup_file)
print(f"資料庫備份已完成，備份檔案儲存為: {backup_path}")
# 清理過期備份檔案
def cleanup_backup():
current_time = datetime.datetime.now()
cutoff_time = current_time - datetime.timedelta(days=RETENTION_PERIOD)

for file in os.listdir(BACKUP_DIR):
file_path = os.path.join(BACKUP_DIR, file)
if os.path.isfile(file_path):
file_time = datetime.datetime.fromtimestamp(os.path.getmtime(file_path))
if file_time < cutoff_time:
os.remove(file_path)
print(f"過期備份檔案已刪除: {file_path}")
# 恢復資料庫
def restore_database(backup_file, restore_time):
backup_path = os.path.join(BACKUP_DIR, backup_file)

# 使用 subprocess 模組執行資料庫恢復命令
restore_command = [
'mysql',
'-u',
'username',
'-p',
'password'
]
with open(backup_path, 'r') as backup_file:
subprocess.run(restore_command, stdin=backup_file)

print(f"資料庫已成功恢復到時間點: {restore_time}")
# 主函式
def main():
# 執行資料庫備份
backup_database()

# 清理過期備份檔案
cleanup_backup()
# 恢復資料庫到指定時間點
restore_database('backup_20220101120000.sql', '2022-01-01 12:00:00')
if __name__ == '__main__': main()