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日志 数据备份的效率不会有太大的影响 而且还原时也可以最大程度还原到数据丢失前的状态,减少数据丢失.
