MySQL – How Binary Log Files Rotate When Number Extension Reaches Maximum

i re-ran the sql statements in the binary log files on the same DB instance (which gave me a older version of my wordpress tables)

Ouch. How much hope there was for recovery depended on whether your tables were using the MyISAM or the InnoDB storage engine. If MyISAM, then... "none," and if InnoDB, then "some."

MyISAM, the older engine, stores its data in files named after the table, with an MYD extension, while InnoDB stores its data either in files named after the table, with an ibd extension, or in the ibdata1 file, which houses the system tablespace, depending on the setting of innodb_file_per_table in configuration. The definition of each the table (column names, data types, index definitions), for either engine, is stored in files named after the tables, with an frm extension.

The large size of your ibdata1 file suggested that maybe you didn't have innodb_file_per_table enabled, and that some variation of the following steps might have worked, if you were indeed using InnoDB without innodb_file_per_table:

1. Make a backup copy of everything.
2. Set up a separate, new MySQL server.
3. Apply the binlogs you have to the new server, in the interest of recreating the .frm files for the table definitions, and then manually altering any tables to make any changes you recall that aren't accounted for in the older binlogs.
4. Stop the new server.
5. Copy directories inside your data directory and their .frm files to the appropriate places on the old server
6. Start the old server, using innodb_force_recovery if needed, to see how well InnoDB would manage to pair up the .frm files with the table data in ibdata1, which it should, if the table definitions were the same.

Since you have already created new tables, presumably with the same names, on the existing server, any hope for that plan to have succeeded are likely to be eliminated, or significantly diminished to the point that it is unlikely to be worth trying, since your best hope if you tried it would be to see what you are seeing now -- old data that you inserted into tables you created from the binary logs.

So, what's plan B? To borrow a fitting phrase from the source I'll cite:

"It is a tedious process that requires an understanding of InnoDB's internal data storage format, C programming, and quite a bit of intuition and experience."

http://www.percona.com/docs/wiki/innodb-data-recovery-tool:start

The Percona InnoDB Data Recovery Tool is the only meaningful approach I can think of that might get you where you need to be... or any comparable tools, assuming there are comparable tools. I don't see any simple path... you're going to have to go "low level" if you want to rescue your data.

You have deleted important files, and in an effort to recover from the first problem, you have altered the one remaining file that might have been salvageable, and I'm not entirely sure how InnoDB will have handled those abandoned tables in its internal structures. The data may still be hiding.

There is one reason for optimism, though, and that is in the strings utility. The link I've provided is not to the genuine GNU "strings" utility, it's Microsoft's analogous semi-equivalent, but the point of this tool is to display strings embedded in files that the internal algorithm thinks might be human-meaningful strings.

There are two uses for this: one, you can try using it on your ibdata1 file (it only reads, shouldn't modify) to see what your eyeballs tell you about what you find in there. It offers essentially a brute-force window into the raw content living in the file, and if you find text in there that is newer than anything you put in using the binlog files, that's a good sign that time spent with the Percona tools could be worthwhile because the old data is actually in the file. If you see nothing at all that you recognize, there may be a problem with the method (though I've verified it works on Linux with genuine GNU "strings"), but if you see old content and not new, then the new content is likely gone.

Of course, the other potential use of "strings" if you do see the content and if it's primarily content that is the most valuable thing you've lost -- not configuration and customization -- then you could just use "strings" to extract the raw content, redirect it into another file, and proceed with a new wordpress installation where you could use that raw text to recreate things.

If the data is "that" important, Percona and SkySQL (I'm not affiliated with either company, but those seem to be the two big players in MySQL space) and other companies offer paid recovery services.

If the binlog file number reaches its maximum value, it appears the server will throw an error.

However, the maximum value is not 999999 as it would appear ... it's 2,147,483,647.

From the source file sql/log.h (MySQL 5.6 source tree):

#define MAX_LOG_UNIQUE_FN_EXT 0x7FFFFFFF

Other relevant files are sql/binlog.cc and sql/log.cc. The code that generates log file names zero-pads values to 6 digits after incrementing the current largest number by 1, but sprintf(ext_buf, "%06lu", next) will not truncate larger numbers, it just won't pad them... so mysql-bin.999999 would be followed by mysql-bin.1000000.

And, according to the Google Machine, somebody apparently tried it just to make sure.