The second step was to restore the data from the time of the backup (which was about 10 hours ago) up to the point of the crash. The binary log was already spread across two files at that time. So I had to extract all the data manipulating statements for the database holding the crashed table from those two binlog files to a text file.
mysqlbinlog --database=db_name --start-position=102655866 mysql1-bin.000312 > restore.sql
mysqlbinlog --database=db_name mysql1-bin.000313 >> restore.sql
The start-position is of course the position of the binlog at the time of the backup. Now I could do a search for all statements affecting the crashed table and feed them to mysql again.
grep -B3 table_name restore.sql | egrep -v '^--$' > restore_table.sql
emacs restore_table.sql
mysql db_name < restore_table.sql
As I knew that all those statements didn't contain any newlines I used a simple approach with grep (the -B3 giving me the lines with the meta information just before the actual statement), quickly checked the resulting file in a text editor (where I deleted the ALTER TABLE statement, too, to not have the crash happen again) and ran the queries.
That's it. The table was now in exactly the same state as it was before the crash.
阅读(367) | 评论(0) | 转发(0) |
