python3.6脚本备份mysql数据库

环境cnetos7.3+python3.6+mysql5.7

安装python3.6环境

1.安装IUS软件源

安装EPEL依赖

sudo yum install epel-release

安装IUS软件源

sudo yum install https://centos7.iuscommunity.org/ius-release.rpm

2.安装python3.6

sudo yum install python36u

安装Python3完成后的shell命令为python3.6，为了使用方便，创建一个到python3的符号链接

sudo ln -s /bin/python3.6 /bin/python3

3.安装pip3

sudo yum install python36u-pip

安装pip完成后的shell命令为pip3.6，为了使用方便，创建一个到pip3的符号链接

sudo ln -s /bin/pip3.6 /bin/pip3

控制台输入python3,如下图所示代表安装成功

脚本文件

# Import required python libraries
import os
import time
import datetime
import glob
import shutil
# MySQL database details to which backup to be done. Make sure below user having enough privileges to take databases backup.
# To take multiple databases backup, create any file like /backup/dbnames.txt and put databses names one on each line and assignd to DB_NAME variable.
#这里填你的公网ip
DB_HOST = ' '
#这里填数据库用户名
DB_USER = ' '
#这里填数据库密码
DB_USER_PASSWORD = ' '
#DB_NAME = '/backup/dbnames.txt'
#这里填要备份的数据库
DB_NAME = ' '
#这里填需要备份到的路径
BACKUP_PATH = ' '

# Getting current datetime to create seprate backup folder like "12012013-071334".
DATETIME = time.strftime('%Y%m%d')

TODAYBACKUPPATH = BACKUP_PATH + DATETIME

# Checking if backup folder already exists or not. If not exists will create it.

print ('del folder three days ago')
folders = glob.glob('这里填前面填的需要备份到的路径/*')
#删除前三条备份的文件目录
today = datetime.datetime.now()
for item in folders:
    try:
        foldername = os.path.split(item)[1]
        day = datetime.datetime.strptime(foldername, "%Y%m%d")
        diff = today - day
        if diff.days >= 3:
            shutil.rmtree(item)
    except:
        pass

print ("creating backup folder")
if not os.path.exists(TODAYBACKUPPATH):
    os.makedirs(TODAYBACKUPPATH)

# Code for checking if you want to take single database backup or assinged multiple backups in DB_NAME.
print ("checking for databases names file.")
if os.path.exists(DB_NAME):
    file1 = open(DB_NAME)
    multi = 1
    print ("Databases file found...")
    print ("Starting backup of all dbs listed in file " + DB_NAME)
else:
    print ("Databases file not found...")
    print ("Starting backup of database " + DB_NAME)
    multi = 0

# Starting actual database backup process.
if multi:
    in_file = open(DB_NAME,"r")
    flength = len(in_file.readlines())
    in_file.close()
    p = 1
    dbfile = open(DB_NAME,"r")

    while p <= flength:
        db = dbfile.readline()   # reading database name from file
        db = db[:-1]         # deletes extra line
        dumpcmd = "mysqldump -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + TODAYBACKUPPATH + "/" + db + ".sql"
        os.system(dumpcmd)
        p = p + 1
    dbfile.close()
else:
    db = DB_NAME
    dumpcmd = "mysqldump -u " + DB_USER + " -p" + DB_USER_PASSWORD + " " + db + " > " + TODAYBACKUPPATH + "/" + db + ".sql"
    os.system(dumpcmd)

print ("Backup script completed")
print ("Your backups has been created in '" + TODAYBACKUPPATH + "' directory")
           

新建一个sh启动脚本

vi auto.sh

添加内容

#!/usr/bin/env bash
#/test/db 是我前面设置的需要备份到的路径
#db_backup.py
cd /test/db是我的python脚本文件名
setsid python3 db_backup.py
           

设置定时器执行间隔时间(我设置的是两分钟执行一次，具体的可根据个人需要设置)

vi /etc/crontab

在文件末尾添加如下格式

*/2 * * * * root /test/db/auto.sh

当我们编辑好定时任务后记得保存：wq

得装载一遍才生效哦crontab /etc/crontab

crontab -l 查看任务

crontab -e 便捷定时编辑任务

定时器失效解决参考方案(设置目录可写权限)：

chmod -R 777 /test/db

参考：

https://www.yuzhi100.com/tutorial/centos/centos-anzhuang-python36

http://www.zhangdongshengtech.com/article-detials/33