This howto concerns the case when, for whatever reason (power failure, nasty bug, maintenance, etc.), all your MySQL/Galera nodes went down. As in “mysqld process is no more”. The logic there is rather simple and obvious:
For that you need to find the node state ID - which is the GTID of the last transaction committed there. For that you need to check the
grastate.dat file, which by default is stored in MySQL
# GALERA saved state version: 2.1 uuid: 5ee99582-bb8d-11e2-b8e3-23de375c1d30 seqno: 8204503945773 cert_index:
look no further: node state ID is
5ee99582-bb8d-11e2-b8e3-23de375c1d30:8204503945773. You get this lucky when you shut down the node gracefully.
# GALERA saved state version: 2.1 uuid: 5ee99582-bb8d-11e2-b8e3-23de375c1d30 seqno: -1 cert_index:
the node crashed. To find the sequence number of the last committed transaction run
–wsrep-recover option. This will recover InnoDB table space to the consistent state, print the corresponding GTID into the error log and exit. In the error log you should see something like
130514 18:39:13 [Note] WSREP: Recovered position: 5ee99582-bb8d-11e2-b8e3-23de375c1d30:8204503945771
. This is the state ID. You need to edit
grastate.dat to update seqno field or let
mysqld_safe to automatically recover it and pass it to
mysqld next time you start it.
# GALERA saved state version: 2.1 uuid: 00000000-0000-0000-0000-000000000000 seqno: -1 cert_index:
the node either crashed during execution of a non-transactional operation (like ALTER TABLE) or aborted due to DB inconsistency. You still can recover the ID of the last committed transaction from InnoDB as described above, however it is rather meaningless, as the node state is very likely corrupted and may not be even in working condition. In case when there are no other nodes with a well defined state, a thorough database recovery procedure (similar to that on a standalone MySQL server, which is out of scope of this article) must be performed on one of the nodes and it should be used as a seed node for new cluster. Preservation of state ID in this case does not make much sense.