mysql 记录binlog过程_记录一次mysqlbinlog恢复过程

最近老友线上一个不太重要的数据库被自己误删了,于是找到小猪本猪我恢复数据,早已把"经验大于一切"思想贯彻全身的小猪肯定是不会放过这次机会滴,滴,滴,滴,滴.

开始枯燥的恢复环节

恢复方案选择

老友平时备份都是手动备份,这样子好像也只能选择旧备份数据+binlog来恢复了.

保护现场关闭服务器,禁止数据继续写入

因为我要使用的是binlog方法恢复,所以要在数据库执行一段flush logs

表示开启新的一段binlog

本地数据库版本

老友服务器上的mysql版本是5.6

我本地5.7重新安装数据库太麻烦了,docker安装也麻烦.然后开始找有5.6版本的一键环境...PhpStudy没

WampServer太粉(通过截图看好像也没有5.6)

APMServ没

phpenv有

....找啊找啊终于找到一个说的清清楚楚支持mysql5.6一键环境,然后就是下载安装巴拉巴拉的5.6环境搭建好了.

本地mysql版本开启binlog

可以看到我本地未开启binlog, no:开启 off:未开启打开my.ini# mysqld配置下增加以下内容

# binlog文件前缀,配置好这个就表示开启了bin

log-bin = mysql-bin

或者

log_bin=ON

log_bin_basename=/var/lib/mysql/mysql-bin

log_bin_index=/var/lib/mysql/mysql-bin.index

### 以下配置看你需要,不过只恢复数据的话下面不用管

# 前缀

log_bin_basename

# 设置此参数是指定二进制索引文件的路径与名称

log_bin_index

# 此参数表示只记录指定数据库的二进制日志

# 这个参数的使用方法有点坑.不是通过,分隔的哦具体百度一下

binlog_do_db

# 此参数表示不记录指定的数据库的二进制日志.不是通过,分隔的哦具体百度一下

binlog_ignore_db

# 此参数表示binlog使用的内存最大的尺寸

max_binlog_cache_size

# 此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。

binlog_cache_size

# 使用二进制日志缓存的事务数量

binlog_cache_use

# 使用二进制日志缓存但超过

binlog_cache_disk_use

# 值并使用临时文件来保存事务中的语句的事务数量

binlog_cache_size

# 最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束

max_binlog_sizeBinlog

使用mysqlbinlog.exe恢复#目录切换到mysql/bin下面

# 执行

.\mysqlbinlog.exe --database test(要恢复的数据库) -s ../data/mysql-bin.000707 > sqls-x\000707-all.txt

# 以上命令的意思是

# 提取mysql-bin.000707文件中的test数据库的语句(只显示日志中包含的语句，不显示其它信息。)

恢复了几个文件之后发现太大啦！！！

于是想着先恢复几个较为重要的表数据,发现mysql提供的工具并不支持恢复到某个表或语句,然后各种搜索引擎发现了几个开源项目binlog2sql、mysqlbinlog_flashback

使用binlog2sql恢复

binlog2sql

这二款工具里就它star最多,毫无疑问就是它了.

安装# 克隆项目

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql

# 安装依赖

pip install -r requirements.txt

环境安装和mysql配置安装python2.7,3.4+ python的安装教程网上有非常多

修改本地mysql配置server_id = 1

# 这个的值不一定非要一样

log_bin = /var/log/mysql/mysql-bin.log

max_binlog_size = 1G

binlog_format = row

binlog_row_image = full

设置mysql权限GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'root'@'127.0.0.1';

使用# 切换到项目目录

1. cd 项目目录/binlog2sql/

2. 执行命令 python binlog2sql.py -h127.0.0.1 -P3306 -uroot -proot -dtest -t wp_userinfo wp_balance --sql-type INSERT DELETE --start-file=mysql-bin.000715 --stop-file=mysql-bin.000726 > C:\phpEnv\server\mysql\mysql-5.6\bin\sqls-x\all.sql

3. 等待执行完毕即可

4. 将这些语句导入到数据库就ok啦

-h127.0.0.1(数据库链接)

-P3306(数据库端口)

-uroot(数据库账号)

-proot(数据库密码)

-dtest(要恢复的数据库)

-t wp_userinfo wp_balance(导出的指定表)

--sql-type INSERT DELETE(要导出的指定语句)\

--start-file=mysql-bin.000715 --stop-file=mysql-bin.000726(开始binlog到结束binlog)

C:\phpEnv\server\mysql\mysql-5.6\bin\sqls-x\all.sql(将打印内容输出大指定文件)

成果

使用过程中遇到的问题编码问题

出现编码问题的地方会有很多,具体还是要看报错.导致的问题报错直接停止恢复

解决方法忽略该编码(self.schema_length - 1).decode("utf-8", "ignore")

建议

1.线上一定要记着开启binlog

2.再不济也要配置一下任务计划备份哦

3.有条件的伙伴可以使用XtraBackup进行备份