I recently had to recover some deleted data that was important enough to dig through the binlogs for and fish it out. To make my job easier, I fixed up mysqlbinlog to do a couple of extra tricks:
* show only entries in a given timestamp range * exclude queries containing a certain substring * include only queries matching a certain substring * exclude queries done by a certain thread
Regexp search, and mutliple patterns still remain on the wish list - fortunately ( for me) and unfortunately for others I did not need them. But anyway, here is what I ended up with (patch against 4.0.18), let's hope some find it useful:
----------------------------start---------------------------------- --- ../../tmp/mysql-4.0.18/client/mysqlbinlog.cc Tue Feb 10 11:15:56 2004 +++ mysqlbinlog.cc Thu Mar 18 12:23:27 2004 @@ -27,6 +27,10 @@
#define CLIENT_CAPABILITIES (CLIENT_LONG_PASSWORD | CLIENT_LONG_FLAG | CLIENT_LOCAL_FILES)
+#define FILTER_MATCH 0 +#define FILTER_SAVE_INTVAR 1 +#define FILTER_NO_MATCH -1 + char server_version[SERVER_VERSION_LENGTH]; ulong server_id = 0;
@@ -57,6 +61,12 @@ static short binlog_flags = 0; static MYSQL* mysql = NULL;
+Intvar_log_event* save_insert_ev = 0, *save_last_insert_ev = 0; + +static time_t from_ts = 0, to_ts = 0; +static const char* pattern = 0, *exclude_pattern = 0; +static ulong exclude_thread_id = 0; + static const char* dirname_for_local_load= 0;
static int dump_local_log_entries(const char* logname); @@ -66,6 +76,8 @@ static void die(const char* fmt, ...); static MYSQL* safe_connect();
+static int filter_match(Log_event* ev, char* last_db); +static void clear_saved_events();
class Load_log_processor
{
@@ -300,6 +312,78 @@Load_log_processor load_processor;
+static void clear_saved_events()
+{
+ if (save_insert_ev)
+ {
+ delete save_insert_ev;
+ save_insert_ev = 0;
+ }
+ if (save_last_insert_ev)
+ {
+ delete save_last_insert_ev;
+ save_last_insert_ev = 0;
+ }
+}
+
+static int filter_match(Log_event* ev, char* last_db)
+{
+ if (from_ts && ev->when < from_ts)
+ return FILTER_NO_MATCH;
+ if (to_ts && ev->when > to_ts)
+ return FILTER_NO_MATCH;
+
+ if (exclude_pattern)
+ {
+ if (ev->get_type_code() == QUERY_EVENT)
+ {
+ Query_log_event* qev = (Query_log_event*)ev;
+ if (strstr(qev->query,exclude_pattern))
+ return FILTER_NO_MATCH;
+ }
+ }
+
+ if (pattern)
+ {
+ switch (ev->get_type_code())
+ {
+ case QUERY_EVENT:
+ {
+ Query_log_event* qev = (Query_log_event*)ev;
+ if (!strstr(qev->query,pattern))
+ return FILTER_NO_MATCH;
+ break;
+ }
+ case INTVAR_EVENT:
+ return FILTER_SAVE_INTVAR;
+ default:
+ return FILTER_NO_MATCH;
+ }
+ }
+
+ if (exclude_thread_id)
+ {
+ if (ev->get_type_code() == QUERY_EVENT)
+ {
+ Query_log_event* qev = (Query_log_event*)ev;
+ if (qev->thread_id == exclude_thread_id)
+ return FILTER_NO_MATCH;
+ }
+ }
+ if (save_insert_ev)
+ {
+ save_insert_ev->print(result_file,short_form,last_db);
+ delete save_insert_ev;
+ save_insert_ev = 0;
+ }
+ if (save_last_insert_ev)
+ {
+ save_last_insert_ev->print(result_file,short_form,last_db);
+ delete save_last_insert_ev;
+ save_last_insert_ev = 0;
+ }
+ return FILTER_MATCH;
+} int process_event(ulonglong *rec_count, char *last_db, Log_event *ev,
my_off_t pos, int old_format)
@@ -307,6 +391,37 @@
char ll_buff[21];
if ((*rec_count) >= offset)
{
+ switch (filter_match(ev,last_db))
+ {
+ case FILTER_NO_MATCH:
+ delete ev;
+ clear_saved_events();
+ return 0;
+ case FILTER_SAVE_INTVAR:
+ {
+ Intvar_log_event* iev = (Intvar_log_event*)ev;
+ switch (iev->type)
+ {
+ case INSERT_ID_EVENT:
+ if (save_insert_ev)
+ delete save_insert_ev;
+ save_insert_ev = iev;
+ return 0;
+ case LAST_INSERT_ID_EVENT:
+ if (save_last_insert_ev)
+ delete save_last_insert_ev;
+ save_last_insert_ev = iev;
+ return 0;
+ default:
+ delete ev;
+ return 0;
+ }
+ break;
+ }
+ default:
+ break;
+ }
+
if (!short_form)
fprintf(result_file, "# at %s\n",llstr(pos,ll_buff));@@ -395,6 +510,16 @@ return 0; }
+enum mysqlbinlog_options
+ {
+ OPT_FROM_TS=256,
+ OPT_TO_TS,
+ OPT_EXCLUDE_THREAD,
+ OPT_PATTERN,
+ OPT_EXCLUDE_PATTERN
+ };
+
+static struct my_option my_long_options[] =
{
@@ -439,6 +564,25 @@
{"local-load", 'l', "Prepare files for local load in directory.",
(gptr*) &dirname_for_local_load, (gptr*) &dirname_for_local_load, 0,
GET_STR_ALLOC, OPT_ARG, 0, 0, 0, 0, 0, 0},
+ {"from-ts", OPT_FROM_TS, "Print entries only after this timestamp",
+ (gptr*) &from_ts, (gptr*) &from_ts, 0, GET_ULONG, REQUIRED_ARG, 0, 0, 0, 0,
+ 0, 0},
+ {"to-ts", OPT_TO_TS, "Print entries only prior to this timestamp",
+ (gptr*) &to_ts, (gptr*) &to_ts, 0, GET_ULONG, REQUIRED_ARG, 0, 0, 0, 0,
+ 0, 0},
+ {"exclude-connection-id", OPT_EXCLUDE_THREAD,
+ "Do not print entries created by this connection",
+ (gptr*) &exclude_thread_id, (gptr*) &exclude_thread_id, 0, GET_ULONG,
+ REQUIRED_ARG, 0, 0, 0, 0,
+ 0, 0},
+ {"query-pattern", OPT_PATTERN,
+ "Print entries only containing this pattern in the query",
+ (gptr*) &pattern, (gptr*) &pattern, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0,
+ 0, 0},
+ {"exclude-query-pattern", OPT_EXCLUDE_PATTERN,
+ "Print entries only if they do not contain this pattern in the query",
+ (gptr*) &exclude_pattern, (gptr*) &exclude_pattern, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0,
+ 0, 0},
{"version", 'V', "Print version and exit.", 0, 0, 0, GET_NO_ARG, NO_ARG, 0,
0, 0, 0, 0, 0},
{0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}
-----------------------------end-----------------------------------
A patched Linux binary is also available at http://www.surveyz.com/~sasha/mysqlbinlog
-- Sasha Pachev Create online surveys at http://www.surveyz.com/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
