binlog2sql数据恢复利器！

binlog2sql数据恢复利器！特别适合没有备份，只有binlog的情况下进行数据恢复操作，能生成标准解析SQL，亦可生产对应的逆向回滚SQL，而且比之传统的mysqlbinlog更具可读性，本文侧重点针对Python2.6.6、2.7.6两种环境进行数据恢复，侧重使用流程、使用注意点记录说明，以便日后使用查阅

已验证环境如下

Python version

2.6.6、2.7.6

MySQL version

5.6.16-log

使用前MySQL Server必须设置的参数

[mysqld]

server_id = 2 【不一定是2，但必须设置】

log_bin = /home/data/mysql3306/mysql-bin

max_binlog_size = 1G【可不写，默认1G】

binlog_format = row

binlog_row_image = full【可不写，默认full格式】

使用限制说明

MySQL Server必须开启，离线模式下不能解析

解析速度不如mysqlbinlog

支持DML回滚，不支持DDL回滚

回滚所需最小权限用户

mysql> grant select,replication slave,replication client on *.* to flashbackuser@'127.0.0.1' identified by 'flashback@123';

Python 2.6.6环境配置

[root@backup ~]# cd /usr/local/
[root@backup local]#tar xf binlog2sql.tar.gz
[root@backup local]# cd binlog2sql/binlog2sql_dependencies/
[root@backup binlog2sql_dependencies]# tar xf setuptools-0.6c11.tar.gz
[root@backup binlog2sql_dependencies]# cd setuptools-0.6c11
[root@backup setuptools-0.6c11]# python setup.py install
[root@backup setuptools-0.6c11]# cd ..
[root@backup binlog2sql_dependencies]# tar xf pip-9.0.1.tar.gz
[root@backup binlog2sql_dependencies]# cd pip-9.0.1
[root@backup pip-9.0.1]# python setup.py install
[root@backup pip-9.0.1]# cd ..
[root@backup binlog2sql_dependencies]# pip install *.whl mysql-replication-0.9.tar.gz
[root@backup binlog2sql_dependencies]# pip list
 
基本用法：
[root@backup ~]# cd /home/data/mysql3306/
必须在数据目录下
[root@backup mysql3306]# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py --help  
用法说明
 
解析出标准SQL
[root@backup mysql3306]# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py -uflashbackuser -pflashback@123 -h227.0.0.1 -dbailidb -tbl_admin --start-file='mysql-bin.000003' > bl_admin.sql
 
解析出回滚SQL
[root@backup mysql3306]# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py --flashback -uflashbackuser -pflashback@123 -h227.0.0.1 -dbailidb -tbl_admin --start-file='mysql-bin.000003' > fb_bl_admin.sql
 
支持解析标准DDL
[root@backup mysql3306]# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py -uflashbackuser -pflashback@123 -h227.0.0.1 -dbailidb -tbl_admin --start-file='mysql-bin.000003' > bl_admin_ddl.sql
 
不支持回滚DDL
[root@backup mysql3306]# python /usr/local/binlog2sql/binlog2sql/binlog2sql.py --flashback -uflashbackuser -pflashback@123 -h227.0.0.1 -dbailidb -tbl_admin --start-file='mysql-bin.000003' > fb_bl_admin_ddl.sql

Python2.7.6环境配置

[root@backup ~]# cd /usr/local/
[root@backup local]# tar xf Python-2.7.6.tar
[root@backup local]# cd Python-2.7.6/
[root@backup Python-2.7.6]# ./configure --prefix=/usr/local/python2
[root@backup Python-2.7.6]# echo $?
[root@backup Python-2.7.6]# make && make install
[root@backup Python-2.7.6]# cd /usr/local/binlog2sql/binlog2sql_dependencies/
[root@backup binlog2sql_dependencies]# cd setuptools-0.6c11
[root@backup setuptools-0.6c11]# /usr/local/python2/bin/python2 setup.py install
[root@backup setuptools-0.6c11]# cd ../
[root@backup binlog2sql_dependencies]# cd pip-9.0.1
[root@backup pip-9.0.1]# /usr/local/python2/bin/python2 setup.py install
[root@backup pip-9.0.1]# /usr/local/python2/bin/pip --version
[root@backup pip-9.0.1]# cd ../
[root@backup binlog2sql_dependencies]# /usr/local/python2/bin/pip install *.whl mysql-replication-0.9.tar.gz
[root@backup binlog2sql_dependencies]# /usr/local/python2/bin/pip list
[root@backup mysql3306]# vim /root/.bashrc
alias python2='/usr/local/python2/bin/python2'
[root@backup mysql3306]# source /root/.bashrc
 
基本用法：
解析出标准SQL：
[root@backup mysql3306]#python2 /usr/local/binlog2sql/binlog2sql/binlog2sql.py -uflashbackuser -pflashback@123 -h227.0.0.1 -dbailidb -tbl_admin --start-file='mysql-bin.000003' > bl_admin.sql
 
解析出回滚SQL：
[root@backup mysql3306]# python2 /usr/local/binlog2sql/binlog2sql/binlog2sql.py --flashback -uflashbackuser -pflashback@123 -h227.0.0.1 -dbailidb -tbl_admin --start-file='mysql-bin.000003' > fb_bl_admin.sql

参数说明

亿速云「云服务器」，即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘，价格低至29元/月。点击查看>>