Windows下mysql数据库备份策略

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

一、备份要求

序号	备份类型	备份频次	备份时间
1	增量备份	每周一-每周六各一次	18:00:00
2	全量备份	每周日一次	18:00:00

二、备份方法

2.1增量备份

2.1.1准备工作

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

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

修改配置文件my.ini

修改之后，重启 mysql 服务，输入：show variables like '%log_bin%';

如下证明已开启

2.1.2增量备份

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

状态如下：

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

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

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

2.1.3创建脚本

• Incremental_backup.bat

@echo off

::服务器数据库 用户名 密码

set suser=root

set suserpwd=yourpassword（需要根据实际数据库登录密码填写）

::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

2.1.4执行schtasks定时计划

1.创建定时计划，在cmd中执行以下命令：

schtasks /create /tn "Incremental Backup" /tr "D:\code\Incremental_backup.bat" /sc WEEKLY /mo 1 /d MON,TUE,WED,THU,FRI,SAT /st 18:00:00 /ed 2100/12/31

2.查看已创建的定时计划，在cmd中执行以下命令查询：

schtasks /query

2.2全量备份

2.2.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=yourpassword（需要根据实际数据库登录密码填写）

::要备份的数据库名

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.2.2执行schtasks定时计划

 1.创建定时计划，在cmd中执行以下命令：

schtasks /create /tn "Full Backup" /tr "D:\code\Full_backup.bat" /sc WEEKLY /mo 1 /d SUN /st 18:00:00 /ed 2100/12/31

 2.查看已创建的定时计划，在cmd中执行以下命令查询：

schtasks /query

三、备份文件查看

1. 确定index文件和日志文件。index文件打开可以看到日志文件的列表；000001文件才是增量日志，是二进制的，直接打开无法查看，需要用mysqlbinlog命令打开，打开后可以看到日志信息。

2. 执行命令 mysqlbinlog D:\mysql-5.7.32-winx64\backup\logbin.000001

四、备份文件查看

4.1按时间点恢复

1. 确定需要恢复的时间点，如2022-04-22 16:32:59
2. 执行按时间点恢复命令: mysqlbinlog --no-defaults --stop-datetime="2022-04-22 16:32:59" D:\mysql-5.7.32-winx64\backup\logbin.000001|mysql -uroot -proot
3. 查看数据库中数据是否已经被恢复
4. 补充指定恢复的参数

--start-datetime="2020-04-30 15:29:05"(从这个时间点开始，包含这个时间点)

--stop-datetime="2020-04-30 15:29:05"(这个时间点之前，不包含这个时间点)

--start-position="591" (从这个点开始，包含这个位置点)

--stop-position="591" (这个位置点之前，不包含这个位置点)

4.2按位置点恢复

1.确定需要恢复的位置点(位置指的是增量日志文件中 at 后面的数字)

2.执行按位置点恢复的命令行:mysqlbinlog --no-defaults --stop-position="959" D:\mysql-5.7.32-winx64\backup\logbin.000001|mysql -uroot -proot

3.查看数据库中数据是否已经被恢复