mysql data目录 清空_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将新增的记录****，回滚到数据库中！

搞定

作者：陈彦斌