MySQL数据恢复

前段时间因为要保证数据恢复的质量，写了一个进行回滚的脚本。

binglog2sql工具介绍

binglog2sql是一款用于解析binlog的工具，纯Python开发，安装需要有Python环境；

安装

git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sqlpip install -r requirements.txt

MySQL配置

[mysqld]server_id = 1log_bin = /var/log/mysql/mysql-bin.logmax_binlog_size = 1Gbinlog_format = rowbinlog_row_image = full

脚本内容

#!/bin/bashuser="root"password="hao_123"host="xxxx"port="3306"binglog=`mysql -u${user} -p${password} -h${host} -e "show master logs;"|grep -v ^Log`echo -e "最新的的binglog:\n${binglog}"cd /root/binlog2sql/binlog2sqlread -p "库名:" dbread -p "表名:" tableread -p "binglog文件:" binecho -e "格式:年-月-日 时:分:秒"read -p "删除的大概时间:" timeread -p "结束的大概时间:" datatimepython ./binlog2sql.py -h${host} -u${user} -p${password} -P${port} -d ${db} -t ${table} --start-file="${bin}" --start-datetime="${time}" --stop-datetime="${datatime}" > /tmp/rollback.sqlecho -e "这个时间点执行的SQL是:"cat /tmp/rollback.sqlread -p "开始位置:" spread -p "结束位置:" epread -p "输入Yes生成回滚文件:" yes if [[ "${yes}" == "yes" ]];then python ./binlog2sql.py -h${host} -u${user} -p${password} -P${port} -d ${db} -t ${table} --start-file="${bin}" --start-position="${sp}" --stop-position="${ep}" -B > /tmp/rollback.sqlfiif [ $? -eq 0 ];then echo "rollback file success!"fiecho -e "回滚的SQL语句是:"cat /tmp/rollback.sqlread -p "输入Yes开始回滚到数据库:" yes if [[ "${yes}" == "yes" ]];then mysql -u${user} -p${password} -h${host} < /tmp/rollback.sql fiif [ $? -eq 0 ];then echo "rollback MySQL success!"fi

这时候对于一些误操作，可以通过过滤大概的时间，和指定的SQL语句进行回闪，保证了数据的安全性。