MySQL BinLog语句检索
我有七个 1G MySQL binlog 文件，我必须使用它们来检索一些“丢失”的信息。我只需要从日志中获取某些 INSERT 语句（例如，该语句以“INSERT INTO table SET field1=”开头）。如果我只运行 mysqlbinlog（即使每个数据库并使用 --short-form），我会得到一个数百兆字节的文本文件，这使得几乎不可能用任何其他程序进行解析。
有没有办法从日志中检索某些sql语句？我不需要任何辅助信息（时间戳、自动增量#等）。我只需要一个与某个字符串匹配的 sql 语句列表。理想情况下，我希望有一个仅列出这些 sql 语句的文本文件，例如：
INSERT INTO table SET field1='a';
INSERT INTO table SET field1='tommy';
INSERT INTO table SET field1='2';
我可以通过将 mysqlbinlog 运行到文本文件，然后根据字符串解析结果来获得该文本文件，但文本文件太大了。它只会使我运行的任何脚本超时，甚至使其无法在文本编辑器中打开。
提前感谢您的帮助。
I have seven 1G MySQL binlog files that I have to use to retrieve some "lost" information. I only need to get certain INSERT statements from the log (ex. where the statement starts with "INSERT INTO table SET field1="). If I just run a mysqlbinlog (even if per database and with using --short-form), I get a text file that is several hundred megabytes, which makes it almost impossible to then parse with any other program.
Is there a way to just retrieve certain sql statements from the log? I don't need any of the ancillary information (timestamps, autoincrement #s, etc.). I just need a list of sql statements that match a certain string. Ideally, I would like to have a text file that just lists those sql statements, such as:
INSERT INTO table SET field1='a';
INSERT INTO table SET field1='tommy';
INSERT INTO table SET field1='2';
I could get that by running mysqlbinlog to a text file and then parsing the results based upon a string, but the text file is way too big. It just times out any script I run and even makes it impossible to open in a text editor.
Thanks for your help in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我从未收到过答复，但我会告诉你我是如何度过的。
1. 将 mysqlbinlog 运行到文本文件
2. 创建一个 PHP 脚本，使用 fgets 读取日志的每一行
3. 在循环每一行时，脚本使用 stristr 函数对其进行解析
4. 如果该行与我要查找的字符串匹配，则会将该行记录到文件中。
运行 mysqlbinlog 和 PHP 脚本需要一段时间，但不会再超时。我最初在 PHP 中使用 fread，但这会将整个文件读入内存并导致脚本在大型 (1G) 日志文件上崩溃。现在，运行需要几分钟（我还将 max_execution_time 变量设置得更大），但它的工作方式就像一个魅力。 fgets 一次获取一行，因此它不会占用那么多内存。
I never received an answer, but I will tell you what I did to get by.
1. Ran mysqlbinlog to a textfile
2. Created a PHP script that uses fgets to read each line of the log
3. While looping through each line, the script parses it using the stristr function
4. If the line matches the string I am looking for, it logs the line to a file
It takes a while to run mysqlbinlog and the PHP script, but it no longer times out. I originally used fread in PHP, but that reads the entire file into memory and caused the script to crash on large (1G) log files. Now, it takes several minutes to run (I also set the max_execution_time variable to be larger), but it works like a charm. fgets gets one line at a time, so it doesn't take up nearly as much memory.