mysql binlog 备份_Mysql数据库的增量备份与还原

一、备份的目的

做灾难恢复：对损坏的数据进行恢复和还原。

二、备份需要考虑的问题

可以接受丢失多少数据；

恢复数据的时间要多久；

恢复数据时是否持续提供服务；

恢复的对象，是一个库，多个表，单个表，还是整个数据库。

三、备份的类型

1，完全备份 每次对数据库的数据进行完全备份，备份整个数据库。所以完全备份耗时较长。

2，增量备份 在上次备份的基础上，备份更新的数据。

四、如何完全备份

在数据库较小，要备份的库小于(50G)时可以使用mysql 自带的备份工具mysqldump进行数据库或者表的完全备份。

Mysqldump 工具备份

优点：

备份的粒度灵活，既可以备份整个mysql服务也可以备份单个的库或者表。

缺点：

1，当数据浮点数时会出现精度丢失。

2，Mysqldump 备份的过程是串行化的，不会并行的进行备份，当备份的数据量较大时，耗时太长效率太低。

Mysqdump 工具对innodb 的储存引擎支持热备，即在不影响服务使用的情况下备份。

使用语法；

在命令行下 mysqldump -uroot -p‘password’ 数据库名称 > 备份文件的名称以 . sql 结尾.

-B 选项 导出指定的某个/某几个库 用空格 隔开 包含 create database 的语句.

五、如何增量备份

二进制日志，binary log

记录着数据库的所有增、删、改、等操作日志。可以使用 mysqlbinlog 在命令行下查看日志。可利用binlog日志文件进行数据的备份和还原。

查看数据binlog功能是否开启

Show variables like ‘log_bin%’;

要开启binlog功能可以修改 my.cnf文件

在文件尾部添加 log-bin=/data/mysql/log/mysql_bin

server-id=1

重启mysql服务使其生效即可.

Mysqlbinlog 指定binlog 文件 | mysql -uroot -p 密码 即可恢复指定日志文件中所记录的所有操作.

六、备份和恢复数据库实验。

准备实验环境

1，进入mysql到中 ，创建实验数据库 create database shiyan；

Use shiyan； 切换当前数据库为 shiyan

Create table kdata1(id int，name char(200))； 创建表一

2, 修改 my.cnf mysql配置文件. 在最后一行插入

Log-bin=/data/mysql/log/mysql_bin

Server-id=1

重启mysql服务使修改的配置项生效 也就是开启 binlog服务

3,

先对实验数据库做完全备份

Mysqldump -uroot -p密码 --single-transaction --flush-logs -B shiyan > shiyan_data_$(date +%Y%m%d%H%M%S).sql

–single-transaction 是热备份 只有在存储引擎为 innodb 时有效

–flush-logs 是生成新的binlog 文件 刷新二级制日志.

4,

在实验库 的kdata1表中插入数据 insert into shiyan.kdata1 values(1,’zhangsan’);

查看数据是否插入成功 select * from shiyan.kdata1;

可以看到数据成功插入了.

Mysqlbinlog -vv /data/mysql/log/mysql_bin.000001

可以看到刚才插入数据的操作已经记录在日志文件中了.

5 ,

使用flush logs; 生成一个新的日志文件

在实验库中再创建一个新的表二

Create table kdata2(u_id int,b_name char(20));

插入一条数据

Insert into shiyan.kdata2 values(2,’wangwu’);

查看数据

Set sql_log_bin=0 临时关闭binlog功能

6, 模拟一个表的数据丢失

Drop table kdata2; 删除表二

7, 使用binlog 日志来恢复删除的表二.

Mysqlbinlog /data/mysql/log/mysql_bin.000002 | mysql -uroot -p密码 再命令行下

进入到mysql中 use shiyan; 切换到实验库

Show tables; 查看当前库中的表

可以看到表二已经恢复了

查看一下表二的记录

表二中的数据也找回来了.

8,模拟实验数据库丢失了

Drop database shiyan; 删除实验数据库

然后恢复数据库

1, 先利用 完全备份的文件进行恢复

Mysql -uroot -p密码 < /root/shiyan_data_20200112131142.sql 数据还原

然后查看实验库中的表

Show tables;

发现表一恢复回来了但是没有表二

这时想要最大程度的恢复数据需要利用binlog文件尽可能的去恢复数据库丢失前的数据.

Mysqlbinlog /data/mysql/log/mysql_bin.00000.2 | mysql -uroot -p密码

这时再去查看实验库中的表就会看到表二也恢复了

实验总结:

单独的使用完全备份 无疑有很大缺点,当数据越来越多时备份的效率也越来越低. 但是我们使用 完全备份加 增量备份 binlog日志 数据备份的效率不会有太大的影响 而且还原时也可以最大程度还原到数据丢失前的状态,减少数据丢失.