python编写Mysql自动备份脚本

时间:2024-10-26 23:37:32

1、先写一个python脚本

vim /opt/mysql_dump.py

#!/usr/bin/env python
# -*- coding:utf-8 -*-
import os
import time

databases = '--all-database'
Mysql_username = 'root'
Mysql_password = '123456'
Mysql_dir = '/opt/mysql'
databases_name = 'database'

def Mysql_dump_file():
  mkdir_dir = '/opt/'+time.strftime('%Y%M')+'/'
  if not os.path.exists(mkdir_dir):
    os.mkdir(mkdir_dir)
  os.chdir(mkdir_dir)
  today_sql=mkdir_dir+databases_name+'_'+time.strftime('%Y%m%d')+'.sql'
  sql_comm = "mysqldump -u %s -p'%s' -R --events --quick --single-transaction %s > %s"%(Mysql_username,Mysql_password,databases,today_sql)
  os.system(sql_comm)

if __name__ == '__main__':
  Mysql_dump_file()

2、写一个计划任务

crontab -e进入计划任务编写模式(这里是每天凌晨3点自动备份数据库)

0 3 * * *  /usr/bin/python /opt/mysql_dump.py