windows下mysql增量备份和全量备份

文章目录

前言

Windows下mysql的增量备份和全量备份，并利用java定时任务执行bat脚本。

一、增量备份

1. 准备工作

首先在进行增量备份之前需要查看一下配置文件，查看log_bin是否开启。首先，进入到 mysql 命令行，输入如下命令：

show variables like '%log_bin%';

如下命令所示，则为未开启

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

修改配置文件my.ini

[mysqld]
#开启binlog日志
#指定备份文件目录
log-bin="E:/mysql-5.7.32-winx64/backup/logbin.log"
#服务唯一标识
server_id = 1

修改之后，重启 mysql 服务，输入：

show variables like '%log_bin%';

如下证明已开启

mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------------------+
| Variable_name                   | Value                                      |
+---------------------------------+--------------------------------------------+
| log_bin                         | ON                                         |
| log_bin_basename                | E:\mysql-5.7.32-winx64\backup\logbin       |
| log_bin_index                   | E:\mysql-5.7.32-winx64\backup\logbin.index |
| log_bin_trust_function_creators | OFF                                        |
| log_bin_use_v1_row_events       | OFF                                        |
| sql_log_bin                     | ON                                         |
+---------------------------------+--------------------------------------------+

2. 增量备份

查看当前使用的 logbin.000*** 日志文件，输入：

show master status;

状态如下：

+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| logbin.000011 |      154 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.09 sec)

当前正在记录日志的文件名为 logbin.000011 。

增量备份的关键命令：通过执行如下命令，使用新的日志文件：

mysqladmin -uroot -123456 flush-logs

如此，已经完成增量备份，下面用脚本实现。

3. 脚本

Incremental_backup.bat

@echo off

::服务器数据库 用户名 密码
set suser=root
set suserpwd=123456
::mysql 二进制日志文件存放的位置
set filePath=E:\mysql-5.7.32-winx64\backup


::当前系统日期 20210804
set now=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%

::备份文件夹
set backup=E:\mysql-5.7.32-winx64\backup
if not exist %backup% md %backup%

::以日期命名的文件夹
set nowfile=%backup%\%now%-Increase
if not exist %nowfile% md %nowfile%

::前一次日志文件名
set logbin=%nowfile%\logbin.txt

mysql -u%suser% -p%suserpwd% -e "show master status"|findstr -B logbin.>%logbin%

::处理日志文件名  复制到目标文件夹
FOR /F "delims=	" %%i in (%logbin%) do  copy %filePath%\%%i %nowfile%

::刷新日志产生新的日志
mysqladmin -u%suser% -p%suserpwd% flush-logs

exit@echo off

::服务器数据库ip 用户名 密码
set suser=root
set suserpwd=123456
::mysql 二进制日志文件存放的位置
set filePath=E:\mysql-5.7.32-winx64\backup


::当前系统日期 20210804
set now=%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%

::备份文件夹
set backup=E:\mysql-5.7.32-winx64\backup
if not exist %backup% md %backup%

::以日期命名的文件夹
set nowfile=%backup%\%now%-Increase
if not exist %nowfile% md %nowfile%

::前一次日志文件名
set logbin=%nowfile%\logbin.txt

mysql -u%suser% -p%suserpwd% -e "show master status"|findstr -B logbin.>%logbin%

::处理日志文件名  复制到目标文件夹
FOR /F "delims=	" %%i in (%logbin%) do  copy %filePath%\%%i %nowfile%

::刷新日志产生新的日志
mysqladmin -u%suser% -p%suserpwd% flush-logs

exit

4. java后端

com.xx.xx.task.DatabaseBackupTask

@Component
public class DatabaseBackupTask {

    // 增量备份 周一至周六凌晨3点触发
    @Scheduled(cron = "0 0 3 ? * MON-SAT")
    public void IncrementalBackupTask() {
        DatabaseBackupUtils.Incremental_backup();
    }
}

com.xx.xx.utils.DatabaseBackupUtils;

public class DatabaseBackupUtils {
    
    // 增量备份
    public static void Incremental_backup() {
        String batPath = "E:/mysql-5.7.32-winx64/Incremental_backup.bat"; // 把你的bat脚本路径写在这里
        File batFile = new File(batPath);
        boolean batFileExist = batFile.exists();
        System.out.println("batFileExist:" + batFileExist);
        if (batFileExist) {
            callCmd(batPath);
        }
    }

    // 执行bat脚本
    private static void callCmd(String locationCmd) {
        StringBuilder sb = new StringBuilder();
        try {
            Process child = Runtime.getRuntime().exec(locationCmd);
            InputStream in = child.getInputStream();
            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(in));
            String line;
            while ((line = bufferedReader.readLine()) != null) {
                sb.append(line).append("\n");
            }
            in.close();
            try {
                child.waitFor();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            System.out.println("callCmd execute finished");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

二、全量备份

1. 脚本

::******Backup MySQL Start******
@echo off
::设置时间变量
set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%"

::本机安装的mysql的bin目录路径
set mysqlBinPath=E:\mysql-5.7.32-winx64\bin\

::备份文件存储的目标路径
set backupPath=E:\mysql-5.7.32-winx64\backup

::备份文件的名字前半部分-后半部分使用的是上面的时间参数
set backupFileName=Full_backup_

::备份文件的保存天数1
set days=1

::要备份的数据库服务器的ip
set host=127.0.0.1

::要备份的数据库服务器的端口
set port=3306

::要备份的数据库登录名
set user=root

::要备份的数据库登录密码
set password=123456

::要备份的数据库名
set dbName=test

::默认的字符集
set charSet=utf8

::创建存储的文件夹
if not exist %backupPath% md %backupPath%

::根据上面设置的days参数删除以前的备份数据
forfiles /p "%backupPath%" /m %backupFileName%*.sql /d -%days% /c "cmd /c del @path"

::进入mysql安装目录的bin目录下
cd %mysqlBinPath%
 
::执行备份操作
mysqldump --opt --single-transaction=TRUE --user=%user% --password=%password% --host=%host% --protocol=tcp --port=%port% --default-character-set=%charSet% --routines --events %dbName% > %backupPath%\%backupFileName%%Ymd%.sql
echo %backupPath%\%backupFileName%%Ymd%.sql
 
@echo on
::******Backup MySQL End******

2. java后端

com.xx.xx.task.DatabaseBackupTask

@Component
public class DatabaseBackupTask {
   
    // 全量备份 周一至周六凌晨3点触发
    @Scheduled(cron = "0 0 3 ? * MON-SAT")
    public void FullBackupTask() {
        DatabaseBackupUtils.Full_backup();
    }

}

com.xx.xx.utils.DatabaseBackupUtils;

public class DatabaseBackupUtils {
    
      // 全量备份
    public static void Full_backup() {
        String batPath = "E:/mysql-5.7.32-winx64/Full_backup.bat"; // 把你的bat脚本路径写在这里
        File batFile = new File(batPath);
        boolean batFileExist = batFile.exists();
        System.out.println("batFileExist:" + batFileExist);
        if (batFileExist) {
            callCmd(batPath);
        }
    }
}

参考：

原文链接：https://blog.csdn.net/weixin_50255400/article/details/119424775