要想把mysqlbinlog解析出sql,需要借助工具,这里介绍下binlog2sql
1,安装:百度自己搜下就好,要先安装python
2,如果binlog是从服务器端下载的
1)需要把文件改名,覆盖到本地的binlog
2)本地创建对应的库和所有的表结构
3,代码
python binlog2sql.py -h 127.0.0.1 -u root -p root -d 数据库名 --start-file mysql-bin.001070
注意,binlog文件不能用绝对路径,因为代码会检测bin文件是不是系统的
如果binlog是GTID模式,但是本地测试环境不是GTID模式,就会报如下错误:
[root@testdb1 mysql]# python /root/binlog2sql-master/binlog2sql/binlog2sql.py -h 192.168.119.130 -uroot -pchengce243 --start-file testdb1-bin.000001 > /tmp/binlog2sql-000144.log
Traceback (most recent call last):
File "/root/binlog2sql-master/binlog2sql/binlog2sql.py", line 150, in <module>
binlog2sql.process_binlog()
File "/root/binlog2sql-master/binlog2sql/binlog2sql.py", line 74, in process_binlog
for binlog_event in stream:
File "build/bdist.linux-x86_64/egg/pymysqlreplication/binlogstream.py", line 430, in fetchone
File "build/bdist.linux-x86_64/egg/pymysql/connections.py", line 684, in _read_packet
File "build/bdist.linux-x86_64/egg/pymysql/protocol.py", line 220, in check_error
File "build/bdist.linux-x86_64/egg/pymysql/err.py", line 109, in raise_mysql_exception
pymysql.err.InternalError: (1236, u"Cannot replicate GTID-transaction when @@GLOBAL.GTID_MODE = OFF, at file ./testdb1-bin.000001, position 234.; the first event 'testdb1-bin.000001' at 4, the last event read from './testdb1-bin.000001' at 299, the last byte read from './testdb1-bin.000001' at 299.")
[root@testdb1 mysql]# python /root/binlog2sql-master/binlog2sql/binlog2sql.py -h 192.168.119.130 -uroot -pchengce243 --start-file testdb1-bin.000001 > /tmp/binlog2sql-000144.log
解决办法:
set @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
解析完之后,记得把GTID_MOD 关掉
set @@GLOBAL.GTID_MODE = OFF;
然后你就发现sql都输出到了控制台上,但是这样明显不行,我们需要输出到文件里,此时我是在bat的基础上解决这个问题,在命令后加 > sql.txt。然后就遇到了问题,某条sql编码问题没法写入,造成代码停止运行,只执行了一部分。到这里我有点懵了,sql.txt设置了utf8也不行。
然后去看python源码,大概在111行的位置,找到他打印sql的代码,在后面加上了写入文件的,然后就大功告成了。
我不会python,但是大概还是能看懂,怎么写入文件的代码也是网上搜的,感谢互联网