一、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,到这里定时备份就已经完全设置好了。