通过解析binlog,实现MySQL的flashback功能【python实现】
来源:互联网 发布:mac电脑还原出厂设置 编辑:程序博客网 时间:2024/05/22 06:58
工作中经常会遇到需要确定MySQL库中在某个时间点修改了什么数据的需求,通过mysqlbinlog命令可以将binlog解析成文本,不过字段会显示成字段位置,如@1,可读性很差。
本脚本通过调用mysqlbinlog可执行文件,先将binlog解析成文本,然后通过python去解析文本,并替换成可读性较强的SQL语句,最后按每事务对应一条记录,存入数据库中,以方便筛选。回滚时,只需要过滤掉不相关的表、时间点,然后按log position倒序排序,即可得到正确的回滚语句。
经测试,文本解析速度为10276行/秒,每个事务大约对应60行文件(取决于你表的宽度),对应171.5事务/秒。
这种将二进制日志解析成文本再将文本解析成最终结果的方式效率不高,最好的方式应该是直接解析二进制日志。不过近期工作较忙,等有空再去学习二进制日志的格式吧,先凑和着用一段时间。
使用方法:
./flashback_mysql mysql-binlog-path -S='2016-05-16T18:00:00' -E='2016-05-16T19:00:00' -h=192.168.88.11 -P=3306 -u=root -p=xxx
下图是解析入库之后的结果:
以下是表结构:
CREATE TABLE `t_binlog_event` (`auto_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,`binlog_name` VARCHAR(100) NOT NULL,`dml_start_time` DATETIME NOT NULL,`dml_end_time` DATETIME NOT NULL,`start_log_pos` BIGINT(20) NOT NULL,`end_log_pos` BIGINT(20) NOT NULL,`db_name` VARCHAR(100) NOT NULL,`table_name` VARCHAR(200) NOT NULL,`dml_sql` LONGTEXT NULL,`undo_sql` LONGTEXT NULL,PRIMARY KEY (`auto_id`),INDEX `dml_start_time` (`dml_start_time`),INDEX `dml_end_time` (`dml_end_time`),INDEX `start_log_pos` (`start_log_pos`),INDEX `end_log_pos` (`end_log_pos`),INDEX `db_name` (`db_name`),INDEX `table_name` (`table_name`))COLLATE='utf8_general_ci'ENGINE=InnoDB
以下是源码:
#!/usr/bin/env python# -*- coding: utf-8 -*-#参考代码:http://blog.csdn.net/selectdb/article/details/16861063#解析出来的每个事务对应的DB名、表名、事务开始时间、结束时间、开始log pos、结束log pos保存在参数指定的MySQL实例的test.t_binlog_event中import MySQLdbimport reimport osimport sysimport datetimeusage = """\nflashback_mysql 1.0 for python 2.6+Usage: ./flashback_mysql mysql-binlog-path -S='2016-05-16T18:00:00' -E='2016-05-16T19:00:00' -h=192.168.88.11 -P=3306 -u=root -p=xxx Argv1 is mandatory, and must be set as mysql-binlog-path, The other argvs are optional, '-S' indicates '--start-datetime', '-E' indicates '--stop-datetime' tag: 1. need MySQLdb module 2. need your mysql server desc table privileges 3. results will be stored in test.t_binlog_event on the MySQL instance you provided."""class ClassFlashback: def __init_db(self): self.mysql_db = MySQLdb.connect(host=self.host, user=self.user, passwd=self.password, port=self.port, charset='utf8') self.mysql_db.autocommit(True) self.mysql_cur = self.mysql_db.cursor() def __init__(self): self.host = '' self.user = '' self.password = '' self.port = '' self.db = '' self.start_datetime = '' self.stop_datetime = '' self.tmp_binlog_file = 'mysqlbin000125.txt' self.field = [] self.db_name = '' self.tb_name = '' self.patt = re.compile("/* .* */") self._get_argv() self.__init_db() self.begin_time = '' self.end_time = '' self.start_pos = '' self.end_pos = '' self.dml_sql = '' self.undo_sql = '' def _get_argv(self): if len(sys.argv) == 1: print usage sys.exit(1) elif sys.argv[1] == '--help' or sys.argv[1] == '-h': print usage sys.exit() elif len(sys.argv) > 2: for i in sys.argv[2:]: _argv = i.split('=') if _argv[0] == '-S': self.start_datetime = _argv[1].replace('T', ' ') elif _argv[0] == '-E': self.stop_datetime = _argv[1].replace('T', ' ') # elif _argv[0] == '-d': self.db = _argv[1] elif _argv[0] == '-h': self.host = '%s' % _argv[1] elif _argv[0] == '-P': self.port = int('%s' % _argv[1]) elif _argv[0] == '-u': self.user = '%s' % _argv[1] elif _argv[0] == '-p': if len(_argv) == 2: self.password = '%s' % _argv[1] elif len(_argv) == 1: self.password = raw_input('Please enter your mysql passwd: ') else: print usage; sys.exit(1) self.input_binlog_file = sys.argv[1] if self.port == '': self.port = 3306 elif self.password == '': self.password = raw_input('Please enter your mysql passwd: ') elif self.start_datetime != '' and self.stop_datetime != '': self.start_datetime = "--start-datetime='" + self.start_datetime + "'" self.stop_datetime = "--stop-datetime='" + self.stop_datetime + "'" elif self.start_datetime != '': self.start_datetime = "--start-datetime='" + self.start_datetime + "'" elif self.stop_datetime != '': self.stop_datetime = "--stop-datetime='" + self.stop_datetime + "'" def _create_tab(self): create_tb_sql = """ CREATE TABLE IF NOT EXISTS test.t_binlog_event ( auto_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, binlog_name VARCHAR(100) NOT NULL , dml_start_time DATETIME NOT NULL, dml_end_time DATETIME NOT NULL, start_log_pos BIGINT NOT NULL, end_log_pos BIGINT NOT NULL, db_name VARCHAR(100) NOT NULL , table_name VARCHAR(200) NOT NULL , dml_sql LONGTEXT NULL , undo_sql LONGTEXT NULL , PRIMARY KEY (auto_id), INDEX dml_start_time (dml_start_time), INDEX dml_end_time (dml_end_time), INDEX start_log_pos (start_log_pos), INDEX end_log_pos (end_log_pos), INDEX db_name (db_name), INDEX table_name (table_name) ) COLLATE='utf8_general_ci' ENGINE=InnoDB; """ self.mysql_cur.execute(create_tb_sql) def _release_db(self): self.mysql_cur.close() self.mysql_db.close() def _get_table_name(self,line): try: if line.find('Table_map:') != -1: l = line.index('server') m = line.index('end_log_pos') n = line.index('Table_map') begin_time = line[:l:].rstrip(' ').replace('#', '20') self.begin_time = begin_time[0:4] + '-' + begin_time[4:6] + '-' + begin_time[6:] self.start_pos = int(line[m::].split(' ')[1]) self.db_name = line[n::].split(' ')[1].replace('`', '').split('.')[0] self.tb_name = line[n::].split(' ')[1].replace('`', '').split('.')[1] except Exception, ex: print ex def _get_end_time(self,line): try: if line.find('Xid =') != -1: l = line.index('server') m = line.index('end_log_pos') end_time = line[:l:].rstrip(' ').replace('#', '20') self.end_time = end_time[0:4] + '-' + end_time[4:6] + '-' + end_time[6:] self.end_pos = int(line[m::].split(' ')[1]) self.dml_sql = self.dml_sql.replace("'", "''''") + ';' if self.dml_sql.find('INSERT INTO ') != -1: self.undo_sql = self.dml_sql.replace('INSERT INTO', 'DELETE FROM').replace('SET', 'WHERE') elif self.dml_sql.find('UPDATE ') != -1: self.undo_sql = self.dml_sql.replace('WHERE', 'WHERETOxxx').replace('SET', 'WHERE').replace('WHERETOxxx', 'SET') elif self.dml_sql.find('DELETE ') != -1: self.undo_sql = self.dml_sql.replace('DELETE FROM', 'INSERT INTO').replace('WHERE', 'SET') # print self.begin_time, self.end_time, self.start_pos, self.end_pos, self.db_name, self.tb_name, self.dml_sql # print self.undo_sql + '\n\n\n' insert_sql = "insert into test.t_binlog_event values (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')" % (self.input_binlog_file, self.begin_time, self.end_time, self.start_pos, self.end_pos, self.db_name, self.tb_name, self.dml_sql, self.undo_sql) # print insert_sql self.mysql_cur.execute(insert_sql) self.dml_sql = '' self.undo_sql = '' #此处一个事务结束, 将self.dml_sql 和self.undo_sql置空, 用于存放下一条解析出来的DML语句 except Exception, ex: print ex def _get_table_structure(self, db_name, tb_name): desc_sql = 'desc %s.%s' % (db_name, tb_name) self.field = [] self.mysql_cur.execute(desc_sql) res = self.mysql_cur.fetchall() for j in res: self.field.append(j[0]) def _do(self): '''先把mysql二进制的binlog解析成可识别文件,再从里面提取需要的数据''' starttime = datetime.datetime.now() print ("\nConverting binlog to text file...") # os.popen('mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS %s %s %s > %s' %(self.start_datetime, self.stop_datetime, self.input_binlog_file,self.tmp_binlog_file)) print "mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS %s %s %s > %s" %(self.start_datetime, self.stop_datetime, self.input_binlog_file,self.tmp_binlog_file) print ("File converting complete.") endtime = datetime.datetime.now() timeinterval = endtime - starttime print("Converting elapsed :" + str(timeinterval.seconds) + '.' + str(timeinterval.microseconds) + " seconds") self._create_tab() print ("\nParsing file...") starttime = datetime.datetime.now() with open(self.tmp_binlog_file, "r") as infile: for line in infile.readlines(): if line.find('Table_map:') != -1: #匹配到表名,解析开始时间、开始log pos、DB名、表名,并获取表结构 self._get_table_name(line) self._get_table_structure(self.db_name, self.tb_name) line = '' # 将这一行内容清空,否则line的内容将会传入到dml_sql中去 elif line.find('### @') != -1: #匹配到字段内容, 将字段位置代码替换成字段名,并去除字段值后面的字段类型等内容 i = line.replace('### @', '').split('=')[0] line = unicode(line, "utf-8") line = line.replace('### @'+str(i), self.field[int(i)-1]) # if(int(i) == len(self.field)): # line = self.patt.sub(' ', line) # else: line = self.patt.sub(',', line) elif line.find('###') != -1: #匹配到关键字,如UPDATE, WHERE, SET等等 line = line.replace('###', '') elif line.find('Xid =') != -1: #匹配到提交时间,到这里整个事务解析完毕 self._get_end_time(line) line = '' # 将这一行内容清空,否则line的内容将会传入到dml_sql中去 else: line = '' # 丢弃其他信息 if line.rstrip('\n') != '': #到此处只会是SQL语句 self.dml_sql = self.dml_sql + line + ' ' print ("\nParsing completed.") endtime = datetime.datetime.now() timeinterval = endtime - starttime print("Parsing elapsed :" + str(timeinterval.seconds) + '.' + str(timeinterval.microseconds) + " seconds")def main(): p = ClassFlashback() p._do() p._release_db()if __name__ == "__main__": main()
0 0
- 通过解析binlog,实现MySQL的flashback功能【python实现】
- 通过init-connect + binlog 实现MySQL审计功能
- Python:通过摄像头实现的监控功能
- Python:通过摄像头实现的监控功能
- MySQL工具推荐 | 基于MySQL binlog的flashback工具
- mysql的binlog解析(一)
- MySQL Binlog预分配的实现和性能
- 玩转binlog实现灵活的MySQL数据恢复
- python-mysql实现简单功能
- [MySQL binlog]彻底解析Mixed日志格式的binlog
- [MySQL binlog]彻底解析Mixed日志格式的binlog
- MySQL通过Binlog恢复删除的表
- mysql 5.6 binlog组提交实现原理
- mysql 5.6 binlog组提交实现原理
- mysql binlog解析
- MySQL binlog格式解析
- MySQL Binlog解析
- MySQL下实现闪回的设计思路 (MySQL Flashback Feature)
- Pascal's Triangle
- JSP中的EL表达式(2)
- 正则表达式使用方法
- android设置系统时间
- php getimagesize()函数获取图片宽度高度
- 通过解析binlog,实现MySQL的flashback功能【python实现】
- 1003
- android 基础 线程sleep,join,yield
- 有关ios健康记步的算法
- 职业生涯规划案例
- 线程同步的方式和机制
- html引入css文件和js文件方式
- JAVA 笔记(二)
- 【bzoj 1616】: [Usaco2008 Mar]Cow Travelling游荡的奶牛 dp