mysql数据库的备份

linux备份
使用python脚本，要求有python3和mysqldump
脚本中 保留时间有点问题，不会自动删除，后期再看

vim /usr/bin/mysql_bakup.py

#! /usr/bin/python36
# -*- coding: utf-8 -*-

import os
import re
import datetime
import subprocess
#mysql_bak表示备份的文件夹名称，IP地址表示需要备份的数据库地址
mysql_host = {
#    'test': '192.168.0.3',
#    'pre': '192.168.0.2',
    'mysql_bak': '127.0.0.1'
}
db_user = 'root'
db_passwd = 'mypasswd'
##备份文件夹存放地址
back_dir = '/home/mysql/'

#  备份文件保存时间，单位：天
backup_keep_time = 10

# 此列表中的数据库将不会备份，下面是默认的基础数据库。
ignore_database = ['Database','information_schema','mysql','sys','performance_schema']

today = datetime.datetime.today().strftime('%Y%m%d%H%M')
for env in mysql_host:
    cmd = '/usr/bin/mysql -h {} -u{} -p{} -e "show databases"'.format(mysql_host.get(env),db_user,db_passwd)
    result = subprocess.check_output(cmd,shell=True,universal_newlines=True).split('\n')
    databases = [i for i in result if i and i not in ignore_database]
    env_backup_dir = os.path.join(back_dir,env,today)
    os.system('mkdir -p {}'.format(env_backup_dir))

    for database in databases:
        back_path = os.path.join(env_backup_dir,'%s_%s.sql.gz' %(database,today))
        command = '/usr/bin/mysqldump -h {} -u{} -p{} {} --force |gzip > {}'.format(
            mysql_host.get(env),db_user,db_passwd,database,back_path
        )
        os.system(command)

    for dirname in os.listdir(env_backup_dir):
        t1 = re.findall(r'_([\d]*?).sql.*',dirname)[0]
        time1 = datetime.datetime.strptime(t1,'%Y%m%d%H%M')
        time_dif = datetime.datetime.today()-time1
        times = time_dif.days
        if times >= backup_keep_time:
            os.remove(os.path.join(env_backup_dir,dirname))

执行命令

python3 vim /usr/bin/mysql_bakup.py

会在/home/mysql下自动生成一个mysql_bak的文件夹，文件夹是以当时时间的文件夹，文件下就是数据库的备份

[root@localhost ~]# ls /home/mysql/mysql_bak/
202211151637

[root@localhost ~]# ls /home/mysql/mysql_bak/202211151637/
mysql_bak_202211151637.sql.gz