45

陈彦斌 博客园 首页归档 新随笔 联系 订阅 管理
Mysql binlog备份数据及恢复数据，学会这个，我在也不怕删库跑路啦~
导读#
　　我一直都主张，技多不压身(没有学不会的技术，只有不学习的人)，多学一项技能，未来就少求人一次。网上经常听到xxx删库跑路，万一真的遇到了，相信通过今天的学习，也能将数据再恢复回来~~~

当然啦，备份数据/还原数据也是挺重要的，可以看我另一篇：点我直达

如果感觉这样还不安全，可以考虑授予用户权限：点我直达

介绍#
　　记录着mysql数据库中的一些增删改操作(没有查询)

功能#

数据复制(主从复制)
数据恢复
注意事项#

开启二进制日志会有性能的消耗！！！

查看二进制日志是否开启#

查看是否开启：show variables like ‘log_bin%’;

开启二进制日志#

修改my.cnf文件

[mysqld]
datadir=/usr/local/mysql/data
port = 3306
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0
max_connections=400
innodb_file_per_table=1
#表名大小写不明感，敏感为
lower_case_table_names=1

skip-grant-tables

log-bin=/usr/local/mysql/log_bin_data/mysql-bin
server-id=1
切换：cd /usr/local/mysql/
创建目录(用于存放二进制日志)：mkdir log_bin_data

注意：
1、log_bin_data必须要有mysql权限
2、修改权限：chown -R mysql:mysql /usr/local/mysql/log_bin_data

修改my.cnf： vim /usr/local/mysql/my.cnf

在[mysqld]下添加
log-bin=/usr/local/mysql/log_bin_data/mysql-bin
server-id=1

修改完之后，一定要重启mysql服务：service mysql restart

此时继续查看二进制日志是否开启，现在可以看到已经开启

查看binlog日志列表#

show master logs;

刷新二进制日志#

刷新/重启mysql服务，都会在加一个binlog日志文件

flush logs;

重置(清空)日志文件#

有时候感觉日志文件没啥用，还占用空间，可以使用下面命令重置日志文件

登录mysql：mysql -uroot -proot
删除binlog：reset master

二进制备份#
　　备份好二进制日志文件之后，往表中插入一条记录，然后将数据库给删掉，等会使用二进制日志恢复数据库

描述：备份本地db2库，并压缩存放到/mysql_data_back下

切换：cd /usr/local/mysql/bin

语法：./mysqldump -u用户 -p密码 -F --databases 数据库 | gzip > 路径+文件名

备份：./mysqldump -uroot -proot -F --databases db2 | gzip > /mysql_data_back/db2.sql.gz

新增一条记录：
INSERT INTO db2.nba_player(id, countryEn, teamName, birthDay, country, teamCityEn, code, displayAffiliation, displayName, schoolType, teamConference, teamConferenceEn, weight, teamCity, playYear, jerseyNo, teamNameEn, draft, displayNameEn, birthDayStr, heightValue, position, age, playerId) VALUES (999999, ‘Croatia’, ‘快船’, 858661200000, ‘克罗地亚’, ‘LA’, ‘ivica_zubac’, ‘Croatia’, ‘伊维察 祖巴茨’, ‘’, ‘西部’, ‘Western’, ‘108.9 公斤’, ‘洛杉矶’, 3, ‘40’, ‘Clippers’, 2016, ‘Ivica Zubac’, ‘1997-03-18’, 2.16, ‘中锋’, 22, ‘1627826’);

刚才的所有操作，都会记录在binlog日志文件中！

binlog恢复数据#
　　刚才我们备份数据库之前，新增一条记录，id=9999，恢复回去之后，如果有这条记录，说明我们恢复成功啦

查看binlog里的日志#

语法：./mysqlbinlog binlog日志文件

切换：/usr/local/mysql/bin

查看binlog日志 :./mysqlbinlog /usr/local/mysql/log_bin_data/mysql-bin.000002

如果查看binlog日志报错，解决方法

添加：–no-defaults参数
./mysqlbinlog --no-defaults /usr/local/mysql/log_bin_data/mysql-bin.000002
将binlog日志转换普通文件#

语法：./mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v binlog日志文件 > 导出文件路径

./mysqlbinlog --no-defaults --base64-output=DECODE-ROWS -v /usr/local/mysql/log_bin_data/mysql-bin.000002 > /mysql_data_back/bin-log.sql

恢复数据方式#

根据binlog日志的时间#

根据binlog的位置#

找到删除库的行#

./mysqlbinlog --no-defaults …/log_bin_data/mysql-bin.000002 | cat -n |grep -iw ‘drop’

./mysqlbinlog --no-defaults …/log_bin_data/mysql-bin.000002 | cat -n | sed -n ‘行数1,行数2p’

./mysqlbinlog --no-defaults …/log_bin_data/mysql-bin.000002 | cat -n | sed -n ‘59,70p’

开始恢复数据#

先恢复数据库#

先将原先备份的数据库给备份回来

切换： cd /mysql_data_back

解压： gunzip -d db2.sql.gz

恢复数据：mysql -uroot -proot -h 127.0.0.1 < /mysql_data_back/db2.sql

此时数据库已经恢复回来，但是新增的那条记录没有恢复回来(id=9999)

恢复binlog#

语法：./mysqlbinlog --no-defaults --set-charset=utf8 --stop-position=“停止位置” binlog日志 | mysql -u用户 -p密码
./mysqlbinlog --no-defaults --set-charset=utf8 --stop-position=“761” /usr/local/mysql/log_bin_data/mysql-bin.000002 | mysql -uroot -proot

恢复某一段记录
./mysqlbinlog --no-defaults --set-charset=utf8 --start-position=“开始坐标” --stop-position=“结束坐标” | mysql -uroot -proot

备注#

本次案例，我先备份一次数据库，然后新增一条记录，再把数据库给删了，我先恢复数据库(恢复的数据库中是没新增的这条记录)，通过binlog将新增的记录，回滚到数据库中！

搞定#
作者：陈彦斌
出处：https://www.cnblogs.com/chenyanbin/
关注： 点我哟(＾Ｕ＾)ノ~ＹＯ
个性签名：没有学不会的技术，只有不学习的人！
联系方式：543210188(WeChat/QQ)，「推荐WeChat」
分类: SQL
联系方式
0
« 上一篇： Mysql数据安全备份
posted @ 2020-11-23 09:34 陈彦斌 阅读(123) 评论(0) 编辑 收藏
登录后才能发表评论，立即 登录 或 注册， 访问 网站首页
博客园派送云上免费午餐，AWS注册立享12个月免费套餐
Copyright © 2020 陈彦斌
Powered by .NET 5.0.0 on Kubernetes & Theme Silence v3.0.0
00:00 / 01:12

作词 : 无
作曲 : 无
李瑨瑶-Count On You
Written by ：Bruno Mars
吉他：@李瑨瑶瑶瑶瑶
You can count on me like 123
I’ll be there
'Cause I know when I need it
I can count on you like 432
And you’ll be there
'Cause that’s what friends are supposed to do oh yeah
Oh yeah
You can count on me like 123
I’ll be there
And I know when I need it
I can count on you like 432
And you’ll be there
'Cause that’s what friends are supposed to do oh yeah