MySQL Forums
Forum List  »  Data Recovery

Obtaining binlog coordinates after crash recovery
Posted by: Rob Wagner
Date: February 03, 2021 07:54AM

Hey all - in MySQL < 8.0, following crash recovery, the binlog coordinates to which InnoDB recovered were displayed in the .err log. For example:

2018-05-24 14:05:31 16098 [Note] Plugin 'FEDERATED' is disabled.
2018-05-24 14:05:31 16098 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-05-24 14:05:31 16098 [Note] InnoDB: The InnoDB memory heap is disabled
2018-05-24 14:05:31 16098 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2018-05-24 14:05:31 16098 [Note] InnoDB: Memory barrier is not used
2018-05-24 14:05:31 16098 [Note] InnoDB: Compressed tables use zlib 1.2.3
2018-05-24 14:05:31 16098 [Note] InnoDB: Using Linux native AIO
2018-05-24 14:05:31 16098 [Note] InnoDB: Using CPU crc32 instructions
2018-05-24 14:05:31 16098 [Note] InnoDB: Initializing buffer pool, size = 1.0G
2018-05-24 14:05:31 16098 [Note] InnoDB: Completed initialization of buffer pool
2018-05-24 14:05:31 16098 [Note] InnoDB: Highest supported file format is Barracuda.
2018-05-24 14:05:31 16098 [Note] InnoDB: The log sequence numbers 2812209 and 2812209 in ibdata files do not match the log sequence number 48593147 in the ib_logfiles!
2018-05-24 14:05:31 16098 [Note] InnoDB: Database was not shutdown normally!
2018-05-24 14:05:31 16098 [Note] InnoDB: Starting crash recovery.
2018-05-24 14:05:31 16098 [Note] InnoDB: Reading tablespace information from the .ibd files...
2018-05-24 14:05:31 16098 [Note] InnoDB: Restoring possible half-written data pages
2018-05-24 14:05:31 16098 [Note] InnoDB: from the doublewrite buffer...
InnoDB: Last MySQL binlog file position 0 23195778, file name mysql-bin.000005 <--- ** THIS LINE **
2018-05-24 14:05:31 16098 [Note] InnoDB: 128 rollback segment(s) are active.
2018-05-24 14:05:31 16098 [Note] InnoDB: Waiting for purge to start
2018-05-24 14:05:31 16098 [Note] InnoDB: 5.6.32 started; log sequence number 48593147


Note the line labeled "THIS LINE". In MySQL 8.0, we no longer see this information:

2021-02-02T19:48:51.378475Z 0 [System] [MY-010116] [Server] /sso/sfw/mysql/bin/mysqld (mysqld 8.0.23) starting as process 23900
2021-02-02T19:48:51.448273Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-02-02T19:48:53.616044Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-02-02T19:48:54.529151Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2021-02-02T19:48:54.571252Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
2021-02-02T19:48:54.579919Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
2021-02-02T19:48:54.667175Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-02-02T19:48:54.667460Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-02-02T19:48:54.711383Z 0 [System] [MY-010931] [Server] /sso/sfw/mysql/bin/mysqld: ready for connections. Version: '8.0.23' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server - GPL.

In 8.0, how can I determine the binlog coordinates following crash recovery? Without it, there is no way to perform additional PIT recovery using binlogs (i.e., I don't know at what coordinates to start applying binlogs).

Thanks
Rob

Options: ReplyQuote


Subject
Views
Written By
Posted
Obtaining binlog coordinates after crash recovery
97
February 03, 2021 07:54AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.