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

Linux MySQL Backup with Python

A Python 3 script is provided that requires python3 and mysqldump. It defines MySQL connection details, backup directory, retention period (30 days), and a list of databases to ignore. The script enumerates databases, creates timestamped backup folders, runs mysqldump for each database, compresses the output, and removes backups older than the retention period.

#! /usr/bin/python36
# -*- coding: utf-8 -*-
import os
import re
import datetime
import subprocess

# MySQL connection info
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)

# Delete old backups
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
    if time_dif.days >= 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 /usr/bin/mysql_bakup.py. Backups are stored under /backups/mysql/<host>/<timestamp>/ with each database saved as a compressed .sql.gz file.

Windows MySQL Backup with Batch Script

The batch file sets connection parameters, lists databases to back up, builds a timestamped filename, and calls mysqldump for each database, saving the output as .sql files. It also includes a command to delete files older than 30 days.

@echo off

rem Set MySQL connection info
set host=192.168.100.101
set port=3306
set user=root
set pass=ECIDI@hc99

rem Databases to back up
set dbname1=bns_pay
set dbname2=bns_qtnys
set dbname3=fawkes
set dbname4=fawkes_nacos
set dbname5=fawkes_patrol
set dbname6=qt_applet

rem Build timestamp like 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%

rem Set backup file paths
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

rem Run mysqldump for each database
"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%

rem Delete files older than 30 days
forfiles /p D:\mysql_bak /s /m *.sql /d -30 /c "cmd /c del @file /f"

After execution, the backup files are stored in D:\mysql_bak with names that include the database name and timestamp, providing a clear, organized backup set for each scheduled run.