一、linux備份

使用python腳本，要求有python3和mysqldump

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

import os
import re
import datetime
import subprocess
##  需要備份數(shù)據(jù)庫的登錄信息
mysql_host = {
    'test': '10.10.3.207'
}
db_user = 'root'
db_passwd = 'PASSWD'
db_port = '3306'
##  備份存放的目錄地址，沒有回自動生成
back_dir = '/backups/mysql'

#  備份文件保存時間，單位：天，超過就刪除
backup_keep_time = 30

# 此列表中的數(shù)據(jù)庫將不會備份，下面是默認的基礎數(shù)據(jù)庫。
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))

執(zhí)行命令

python3 vim /usr/bin/mysql_bakup.py

會在/home/mysql下自動生成一個mysql_bak的文件夾，文件夾是以當時時間的文件夾，文件下就是數(shù)據(jù)庫的備份

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

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

二、windows服務器備份mysql腳本

@echo off

echo 設置MySql數(shù)據(jù)庫的連接信息
set host=192.168.100.101
set port=3306
set user=root
set pass=ECIDI@hc99

echo 設置要備份MySql數(shù)據(jù)庫名稱
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-winx64inmysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname1% > %backupfile1%
"D:mysql-8.0.30-winx64inmysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname2% > %backupfile2%
"D:mysql-8.0.30-winx64inmysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname3% > %backupfile3%
"D:mysql-8.0.30-winx64inmysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname4% > %backupfile4%
"D:mysql-8.0.30-winx64inmysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname5% > %backupfile5%
"D:mysql-8.0.30-winx64inmysqldump" -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"

鏈接：https://www.cnblogs.com/wangyuanguang/p/16893146.html