mysq备份脚本

运维 , , 评论

Linux debian

  • 7z压缩安装

    1
    sudo apt-get install p7zip p7zip-full p7zip-rar

  • 备份mysql所有数据库,7z打包,保留7天文件

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
        #!/bin/bash

    # MySQL配置
    MYSQL_IP="127.0.0.1"
    MYSQL_PORT="3306"
    MYSQL_USER="root"
    MYSQL_PASSWORD="111111"

    # 备份配置
    BACKUP_DIR="/home/dbback"  # 修改为实际备份目录
    DATE_STAMP=$(date +"%Y%m%d%H%M%S")
    TODAY=$(date +"%Y%m%d")

    # 创建备份目录
    mkdir -p $BACKUP_DIR

    # 获取所有数据库名称
    DATABASES=$(mysql -h $MYSQL_IP -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD -e 'SHOW DATABASES;' | grep -vE '^Database|^information_schema|^performance_schema|^mysql|^sys')

    # 遍历每个数据库进行备份
    for db in $DATABASES; do
        mysqldump -h $MYSQL_IP -P $MYSQL_PORT -u $MYSQL_USER -p$MYSQL_PASSWORD $db > "$BACKUP_DIR/$db-$DATE_STAMP.sql"
    done

    # 打包成7z文件
    7z a "$BACKUP_DIR/dbbackup-$DATE_STAMP.7z" "$BACKUP_DIR"/*.sql

    # 清理旧备份
    find $BACKUP_DIR -name "*.7z" -type f -mtime +7 -exec rm {} \;
    find $BACKUP_DIR -name "*.sql" -type f -mtime +7 -exec rm {} \;

    echo "Backup completed and old backups cleaned."

Window

mysql 单个数据库备份，并使用rar打包，保留7天数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@echo off
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
set WINRAR=C:\Program Files\WinRAR\WinRAR.exe
set MYSQLDUMP_PATH=C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe
set BACKUP_PATH=c:\dbback\
set DB_NAME=test
set DBPASSWORD=111111

:: 备份
"%MYSQLDUMP_PATH%" --opt -u root --password=%DBPASSWORD% %DB_NAME% > %BACKUP_PATH%%DB_NAME%_%Ymd%.sql
"%WINRAR%"  a -afrar -ep1 -df  "%BACKUP_PATH%%DB_NAME%_%Ymd%.zip"  "%BACKUP_PATH%%DB_NAME%_%Ymd%.sql" && del %BACKUP_PATH%%DB_NAME%_%Ymd%.sql

:: 删除大于7天的
forfiles /p %BACKUP_PATH% /s /d -7 /c "cmd /c del/f/s/q @path"


@echo on

完结

holdenJava & Linux

聊聊？