mysql 数据快速恢复之 mysqlbinlog AND binlog2sql
mysql 数据库还原方案测试
第一种方案: 使用mysqlbinlog 还原:
a. 适用时增量恢复, 使用 mysqlbinlog mysql-bin.000001 > xx.sql
b. 适用于 truncate 误操作, 此操作不记录还原日志, 只能增量还原
c. 这种方式对于一个delete 误操作似乎比较复杂,
第二种方案: 使用binlog2sql
a. 导致的sql可读性更好
b. 参考文档
https://github.com/danfengcao/binlog2sql
c. mysql server必须开启,离线模式下不能解析
一. 准备工作
Centos 6.* 更新python2.6 为 python2.7
yum -y install gcc zlib zlib-devel python-devel libffi-devel openssl openssl-devel wget https://www.python.org/ftp/python/2.7.14/Python-2.7.14.tgz tar -xvf Python-2.7.14.tgz cd Python-2.7.14 ./configure --prefix=/opt/python2.7 --with-ssl make && make install mv /usr/bin/python /usr/bin/python.2.6 rm -f /usr/bin/python-config ln -s /opt/python2.7/bin/python /usr/bin/python ln -s /opt/python2.7/bin/python-config /usr/bin/python-config ln -s /opt/python2.7/include/python2.7/ /usr/include/python2.7 wget https://bootstrap.pypa.io/get-pip.py python get-pip.py ln -s /opt/python2.7/bin/pip2.7 /usr/bin/pip2.7 sed -i "s/bin\/python/bin\/python2.6/g" /usr/bin/yum
修改: 不然使用yum 时可能会报错
vim /usr/bin/yum
#!/usr/bin/python2.6
二. 安装binlog2sql:
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql pip2.7 install -r requirements.txt pip2.7 install PyMySQL pip2.7 install mysql-replication