A user has deleted the wrong row in a table and you are preparing a point-in-time recovery
skipping the DELETE event.
The server is configured with:
You have identified that the DELETE statement to skip has the Global Transaction Identifier
(GTID) ‘dbbe7da-fe25-11e2-b6c7-0800274aa49e:5 and you replay the binary log with:
Mysqlbinlog – exclude-gtides=’ dbbe07da-fe25-11e2-b6c7-0800274aa49e:5’ binlog.00000.2 |
mysql
However all events were skipped instead of just the one deleting the wrong row.
What is the reason for this?
A.
Mysqlbinlog ignores arguments to – exclude-gtids-it means ignore all events with GTIDs.
B.
The server keeps track of which GTIDs have already been executed and skips those.
C.
Enforce_gtid_consistency is set to ON.
D.
Gtid_mode must be set to AUTO during point in time recoveries.
Explanation:
This is a tricky one.
According to the question, both parameters are turned off.
This means the server is not using GTIDs at all.
The server tracks the last binlog and position and starts from there.
While not exactly saying this, B seems the closest answer.
Wait.. I have that the people entering these did SO little proofreading.
Firstly, the GTIDs don’t even match, as typed. The SECOND gtid (mysqlbinlog) has a 07, where the first GTID has just a 7 — but ignoring that…
And the graphic was hard to actually read, since it’s TWO variables GTID_MODE and Enforce_GTID_Consistency.
NO is not a legitimate value for those variables — it can be ON or OFF. ON is the option that I think they meant, since that’s what answer C actually claims.
A is wrong, mysqlbinlog does heed the ignore-gtid parameters.
D is wrong — since that’s not a valid value (AUTO).
C is correct, BUT it isn’t the correct answer.
B IS the answer, but here’s why… Doing a point-in-time of the ENTIRE server would be fine. Doing JUST the single database OR just the table will NOT clear the rows in the mysql.gtid_executed table. That table has the list of all GTIDs. So that restore from that excluded GTID onward will fail.
B
I think A
mysqlbinlog has –skip-gtids option and should not check the server gtid-mode
https://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_skip-gtids
https://dev.mysql.com/doc/mysql-backup-excerpt/5.6/en/point-in-time-recovery.html