windows环境MySQL开启二进制进行全量、增量备份恢复

一、MySQL开启二进制日志

1.开启MySQL服务

打开 服务–>启动mysql服务

2.添加MySQL日志配置

进入mysql的安装目录，找到 my.ini 配置文件，添加以下：

#开启二进制日志
log-bin=mysql-bin
#指定写入二进制的事件格式
binlog-format=MIXED
#给mysql服务器分配id
server-id=1001

上面的id可以随便写，主要是给服务器分配一个id，有些情况下需要用到唯一标识id。

3.重启MySQL服务

进入服务–>重启MySQL服务

• 重启MySQL服务之后，会在MySQL的安装目录下会生成data文件，里面存放的就是日志，这个日志就是用来数据进行备份和恢复的重要。
  

4.开启二进制日志

---

• 这里需要进入到mysql安装目录下的bin目录里面。

使用黑窗口登录mysql：

mysql -u root -p

查看是否开启了二进制日志：

show variables like 'log_bin';

显示No则表示已经开启了，显示OFF则表示还没有开启二进制日志。

---

二、创建数据库

1.解决数据库不能远程连接

我这里远程连接的时候出现了一些问题，是因为我的数据库用户不支持远程连接，下免是我的解决方法：

• 进入mysql安装目录的bin目录下面，登录mysql。

mysql -u root -p

• 查看root用户是否支持远程连接：

use mysql;

select host from user where user=‘root’;

这里显示localhost，说明只能本地连接，现在我们要改成全部都能连接。

 update user set host = ‘%’ where user ='root’

执行完再查看一下host的权限，现在就显示是%了，这样就可以远程连接了。

然后再执行以下这个，让配置立即生效。

flush privileges;

回到主题，继续创建数据库。

2.创建数据库

1.用mysql或者Navicat创建一个db_ab的数据库，数据编码是UTF-8的，我这里用的是SQLyog。

create database if not exists db_blbl default charset utf8 collate utf8_general_ci;

在mysql安装目录下的data文件里面会看到我们创建好的数据库

2.切换到我们创建的数据库，也可以自己手动切换

use db_ab

三、创建数据表

1.创建一个数据表

create table t_student
(
   sid                  int not null comment '学号',
   sname                varchar(60) not null comment '姓名',
   sex                  tinyint not null default 1 comment '性别：1男， 2女',
   age                  tinyint not null comment ' 年龄',
   icard                varchar(18) not null comment '身份证，唯一约束',
   primary key (sid),
   unique key AK_Key_2 (icard)
) comment '学生信息表';

2.插入测试数据

insert into t_student values(1,'张学友',1,36,'1234567891011');
INSERT INTO t_student VALUES(2,'张小妹',1,36,'1114567891011');

然后查看，就能看到我们插入的数据。

四、使用全量数据库恢复备份

1.进行全量备份

进入到mysql安装目录下的bin目录，可以手动进入，也可以自己使用命令，一步步的cd 进去。

执行以下命令，让刚刚创建的数据库进行全量备份。

mysqldump --opt --host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE -u root --password=123456 db_blbl t_student > "C:\beifen.sql"

这里是提示你密码直接写在里面不安全，只是提示。

注意：备份的位置要和mysql安装目录在同一个盘符，否则会访问不了

1.host=127.0.0.1就是你要备份的数据库IP地址
2.port=3306数据库端口号
3.-u root --password=123456 数据库用户名和密码
4. "C:\beifen.sql"将db_ab数据库全量备份到C盘根目录下，取名叫beifen.sql
5.db_blbl t_student，就是你要备份的数据库名和表名，可以对db_blbl进行全库备份，也可以单独对t_student表进行全量备份

---

2.单独不进行数据全量备份

这一步不用操作
这里也就是说你全量备份的数据库里面，有写表是不需要进行备份的，就可以忽略不不备份。
– 示例，一下操作只是演示，不进行。

mysqldump --opt --host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE -u root --password=123456 db_ab --ignore-table=db_ab._t_student> "C:\beifen.sql"

ignore-table=db_ab._t_student
这里前面接的是mysql.com db_ab,也就是说这个库里面除了db_ab数据库里面的t_student数据表不进行备份，其他都要备份。

---

这里就是我们进行全量备份的那个数据库。

3.创建bat批处理文件

现在我们要把这些封装到一个bat文件里面，创建文件的时候，后缀名写bat。
rem Auther By Anker
rem date:202435
rem ******Backup MySQL Start******
@echo off
::设置时间变量
set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%"
 
::创建存储的文件夹
if not exist "C:\mysql_backup" md "C:\mysql_backup"
 
::执行备份操作
"C:\Program Files\mysql-5.7.23-winx64\mysql-5.7.23-winx64\bin\mysqldump" --opt --user=root --password=123456 --host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events "db_ab" >C:\beifen_%Ymd%.sql
 
::删除30天前的备份数据
forfiles /p "C:\mysql_backup" /m backup_*.sql -d -30 /c "cmd /c del /f @path"
 
@echo on
rem ******Backup MySQL End******

这里就已经备份完了，下面开始进行测试。

4.测试全量备份恢复数据

1.删除数据库

drop database db_ab;

2.创建数据库表结构

• 这里用的是drop命令，将数据库数据和结构全部删除了，需要重新创建数据库才能进行恢复操作。

create database if not exists db_ab default charset utf8 collate utf8_general_ci;

这里切换到mysql的bin目录下进行操作：

3.cmd登录mysql切换数据库

use db_ab

4.执行命令恢复数据

source C:\beifen.sql

再查询，就发现删除的数据恢复了

五、增量备份

1.插入数据

INSERT INTO t_student VALUES(3,'嘻嘻',1,36,'2224567891011');
INSERT INTO t_student VALUES(4,'哈哈',1,36,'3334567891011');

加上之前的两条数据，就是四条 测试数据了

2.删除数据

truncate t_student;

• 这里不执行drop命令，否则又是整个表结构都删除了，那样只能使用全量进行恢复。

这里再查询student表就已经显示没有数据了。

3.封装bat批处理文件脚本

创建一个后缀名bat的批处理文件，写入下面的脚本，然后双击运行：

rem Auther By Anker
rem date:202436
rem ******Backup MySQL Start******
@echo off
::设置时间变量
set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%"
 
::执行增量备份操作
mysqladmin -u root -p 123456 flush-logs
 
@echo on
rem ******Backup MySQL End******

记得修改用户ing和用户密码

4.增量恢复数据库

1.执行增量恢复之前，咱先全量还原一下最新的某个数据时段：

source C:\beifen.sql

2.再去数据库查询的时候就发现，最先增加的两条数据已经恢复在数据了，但是后面加的两条数据并没有恢复过来。要想把后面两条新增的数据页恢复过来，就需要用二进制的方法来恢复，后面就是自己数据库生成日志的名称，每台数据库名称不一样，看自己生成的日志文件：

先看一下日志记录，在数据库软件里面运行该下代码：

handlebars show binlog events in 'mysql-bin.000001';

3.在mysql安装路径下的data里面用cmd执行以下命令：

mysqlbinlog --no-defaults mysql-bin.000001 --start-position=4157--stop-position=4034> C:\zengliang.sql

mysql-bin.000001 ：日志文件名称
start-position=4157 ：起始位置
stop-position=4034 ：结束位置
C:\zengliang.sql ：转换文件的保存位置

执行完成后所保存的位置会多出一个sql文件，那就是我们转换过来的日志文件

4.登录mysql，执行还原命令：

source C://zengliang.sql;

!](https://img-blog.csdnimg.cn/direct/be5d842d55904d8883d40e016717ff74.png)

再查询的时候你就发现后面新增的两条数据也恢复到数据库里面了。

六、开启定时备份

1.打开控制面板–>管理工具-计划任务–>创建基本任务 --》新建名称 --》根据自己情况设定备份时间 --》启动程序 --》选择脚本位置（全量或增量的那个bat脚本）—》完成！









OK，到这里定时备份就已经完全设置好了。