Get position of mysqlbinlog error. I have the error's line number

Question

If I use mysqlbinlog to load a binlog into my database like this:

mysqlbinlog --defaults-file=/path/mysqld.cnf bin.000011| mysql -u root

and get an error like this:

ERROR 2006 at line 290: MySQL server has gone away

How do I convert line 290 into a binlog position that I can use to rerun the log, starting at the failed line?

 mysqlbinlog --defaults-file=/path/mysqld.cnf --start-position=<WANT THIS> bin.000011| mysql -u root

I'm using binlog-format=ROW

Or, how can I log the binlog position of the error, and not just the line number?

It seems that there must be a way to recover from an error while updating a database with mysqlbinlog, otherwise the whole database is corrupted.

• danblack: gone away error causes.

Answer 1

You can follow this steps/workaround :

1. Take converted binary logs in separate file.

mysqlbinlog --base64-output=DECODE-ROWS -v hostname_binlog.00XXX > Binary_log.sql

2. You directly Binary_log.sql file on MySQL server or you can use mysqlbinlog command ( if you have sufficient space)

mysqlbinlog --base64-output=DECODE-ROWS -v hostname_binlog.00XXX | mysql -v -u root

Here using -v ( verbose ), logs will printed on terminal & you can get details of each statement.