目录
一、二进制文件增量备份
1、意义:
(1)二进制日志保存了所有更新或者可能更新数据库的操作
(2)二进制日志在启动MySQL服务器后开始记录,并在文件达到 max_binlog_size所设置的大小或者接收到flush logs命令后重新 创建新的日志文件
(3)只需定时执行flush logs方法重新创建新的日志,生成二进制文 件序列,并及时把这些日志保存到安全的地方就完成了一个时间 段的增量备份。
2、备份方法步骤:
2.1、在进行增量备份前加以配置
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin #开启二进制日志。如果使用相对路径,则保存在/usr/local/mysql/data/目录下
binlog_format = MIXED #可选,指定二进制日志(binlog)的记录格式为MIXED
server-id = 1
systemctl restart mysqld
ls -l /usr/local/mysql/data/mysql-bin.*
(1) log-bin=mysql-bin 指定日志存放的位置;
(2)binlog_format = MIXED 日志记录方式为混合;
(3)server-id = 1 指定服务id。
2.2、刷新出新的二进制文件
执行新增/删除/修改操作前,刷新以生成新的日志文件
mysqladmin -u root -p flush-logs #刷新出新的二进制文件
2.3、执行变更SQL
执行业务新增/删除/修改SQL
2.4、保存新产生的二进制文件
即完成备份
#创建一个新的文件夹 作为二进制备份文件保存的目录
mkdir -p /opt/sql_bak
#将新产生的二进制备份文件移动至新的目录(更保险的方式:复制),并且以时间命名的方式修改其名称
mv /usr/local/mysql/data/mysql-bin.000003 /opt/sql_bak/wife_sql_`date +%Y%m%d`
二、二进制文件进行恢复
1、基于位置点恢复
#从某一个位置点开始恢复,一直到日志结尾
mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p
#从日志开头,一直恢复到某一个位置点前结束
mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p
#从指定的点开始 到指定的点结束
mysqlbinlog --no-defaults --start-position='xxx'--stop-position='位置点' 文件名 | mysql -u root -p
如
#从开始点到结尾恢复
mysqlbinlog --no-defaults --start-position=4 mysql-bin.000005 |mysql -u root -p
#从指定点开始到指定结尾结束
mysqlbinlog --no-defaults --start-position=1513 --stop-position=1800 mysql-bin.000003 |mysql -u root -p
2、基于时间点的恢复方式
#从某一个时间点开始恢复,一直到日志结尾
mysqlbinlog --no-defaults --start-datetime='时间点' 文件名 | mysql -u root -p
#从日志开头,一直恢复到某一个时间点前结束
mysqlbinlog --no-defaults --stop-datetime='时间点' 文件名 | mysql -u root -p
#从某一个时间点开始恢复,一直到某一个位置点前结束
mysqlbinlog --no-defaults --start-datetime='起始时间点' --stop-datetime'结束时间点' 文件名 | mysql -u root -p
3、操作demo
3.1、恢复变更的数据
如恢复update修改的数据,现数据库只有一张t_user单表
CREATE TABLE `t_user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_user` (`user_name`, `age`) VALUES ('张三', '1');
INSERT INTO `t_user` (`user_name`, `age`) VALUES ('李四', '2');
INSERT INTO `t_user` (`user_name`, `age`) VALUES ('王五', '3');
执行update
update t_user set age = 0;
现在希望恢复数据
(1)通过binlog找到pos节点,
开始和结束pos分别为439和1231
(2)将t_user表清空
(3)执行mysqlbinlog --start-position=439 --stop-position=1231 mysql-bin.000001 | mysql -uroot -p,输入密码
回车后查看数据库,恢复成功:
3.2、恢复删除的数据表:
在上面的基础上,再新增t_school表
CREATE TABLE `t_school` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`school_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t_school` (`school_id`) VALUES ('1111');
INSERT INTO `t_school` (`school_id`) VALUES ('2222');
误执行了删表语句
drop TABLE t_user;
恢复方法:找到create table语句到drop之前的pos,并过滤其他无关的表,执行binlog。
3.3、恢复删除的数据库:
执行整个binlog文件即可
以上恢复方法建立在只有一个binlog日志文件的前提下,通过binlog恢复数据,一是大批量的SQL恢复会较慢;二是如果业务复杂,需要耗费时间查找和过滤binlog日志。所以一般采用定时全备份+binlog日志恢复增量数据部分的方式来恢复数据。如每日凌晨进行数据库全库备份,某日需要恢复,则使用当日凌晨的备份脚本进行恢复,再执行凌晨到最后一次日志之间的pos即可。