Automate MySQL Backups on Linux and Windows with Python and Batch Scripts

Linux MySQL Backup with Python

Use a Python script; requires Python 3 and mysqldump.

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

import os
import re
import datetime
import subprocess
##  需要备份数据库的登录信息
mysql_host = {
    'test': '10.10.3.207'
}
db_user = 'root'
db_passwd = 'PASSWD'
db_port = '3306'
##  备份存放的目录地址，没有回自动生成
back_dir = '/backups/mysql'

#  备份文件保存时间，单位：天，超过就删除
backup_keep_time = 30

# 此列表中的数据库将不会备份，下面是默认的基础数据库。
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/local/mysql/bin/mysql -h {} -u{} -p{} -P{} -e "show databases"'.format(mysql_host.get(env),db_user,db_passwd,db_port)
    result = subprocess.check_output(cmd,shell=True,universal_newlines=True).split('
')
    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/local/mysql/bin/mysqldump -h {} -u{} -p{} -P{} {} --force |gzip > {}'.format(
            mysql_host.get(env),db_user,db_passwd,db_port,database,back_path
        )
        os.system(command)

    tmp = os.path.join(back_dir,env)
    for dirname in os.listdir(tmp):
        time1 = datetime.datetime.strptime(dirname,'%Y%m%d%H%M')
        time_dif = datetime.datetime.today()-time1
        times = time_dif.days
        if times >= backup_keep_time:
            for file in os.listdir(os.path.join(tmp,dirname)):
                os.remove(os.path.join(tmp,dirname,file))
            os.rmdir(os.path.join(tmp,dirname))

Run the script with: python3 vim /usr/bin/mysql_bakup.py The script creates a directory under /home/mysql named with the current timestamp, and stores compressed .sql.gz files for each database.

[root@localhost ~]# ls /backups/mysql/test/
202211151637

[root@localhost ~]# ls /backups/mysql/test/202211151637/
nacos_202211151637.sql.gz

Windows Server MySQL Backup Batch Script

@echo off

echo 设置MySql数据库的连接信息
set host=192.168.100.101
set port=3306
set user=root
set pass=ECIDI@hc99

echo 设置要备份MySql数据库名称
set dbname1=bns_pay
set dbname2=bns_qtnys
set dbname3=fawkes
set dbname4=fawkes_nacos
set dbname5=fawkes_patrol
set dbname6=qt_applet

echo 获取当天的日期格式，例如：20200902231300
set hour=%time:~0,2%
if "%time:~0,1%"==" " set hour=0%time:~1,1%

set backup_date=%Date:~0,4%%Date:~5,2%%Date:~8,2%%hour%%Time:~3,2%%Time:~6,2%

echo 设置备份文件的路径
set backupfile1=D:\mysql_bak\%dbname1%-%backup_date%.sql
set backupfile2=D:\mysql_bak\%dbname2%-%backup_date%.sql
set backupfile3=D:\mysql_bak\%dbname3%-%backup_date%.sql
set backupfile4=D:\mysql_bak\%dbname4%-%backup_date%.sql
set backupfile5=D:\mysql_bak\%dbname5%-%backup_date%.sql
set backupfile6=D:\mysql_bak\%dbname6%-%backup_date%.sql


echo 使用mysqldump对指定的MySql进行备份
echo 注意路径中有空格的要加上双引号

"D:\mysql-8.0.30-winx64\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname1% > %backupfile1%
"D:\mysql-8.0.30-winx64\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname2% > %backupfile2%
"D:\mysql-8.0.30-winx64\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname3% > %backupfile3%
"D:\mysql-8.0.30-winx64\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname4% > %backupfile4%
"D:\mysql-8.0.30-winx64\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname5% > %backupfile5%
"D:\mysql-8.0.30-winx64\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname6% > %backupfile6%



echo 删除过期文件，这里是超过30天就删除

forfiles /p D:\mysql_bak /s /m *.sql /d -30 /c "cmd /c del @file /f"

Reference: https://www.cnblogs.com/wangyuanguang/p/16893146.html (© original author).