I'm new to the idea of MySQL Replication (mainly because I've never really had data important enough to have redundancy until recently). Today I had a random crash of my MySQL 8 database with the following in the error log. Apparently (after some research) this type of error is often hardware related.... hard drive, RAM, power surge, random shut power cut, etc.

2023-03-01T19:09:47.492740Z 0 [ERROR] [MY-012833] [InnoDB] tried to purge non-delete-marked record in index `title_year_idx` of table `newdb`.`movie`: tuple: TUPLE (info_bits=0, 5 n_cmp=5, fields): {[13]The Strangers(0x54686520537472616e67657273),[4]    (0x800007d8),NULL,[4]    (0x80000001),[4]    (0x800efdbb)}, record: COMPACT RECORD(info_bits=0, 5 fields): {[d]The Strangers(0x54686520537472616e67657273),[4]    (0x800007d8),NULL,[4]    (0x80000001),[4]    (0x800efdbb)}
2023-03-01T19:09:47.499479Z 0 [ERROR] [MY-012833] [InnoDB] tried to purge non-delete-marked record in index `en_title` of table `newdb`.`movie`: tuple: TUPLE (info_bits=0, 2 n_cmp=2, fields): {[13]The Strangers(0x54686520537472616e67657273),[4]    (0x800efdbb)}, record: COMPACT RECORD(info_bits=0, 2 fields): {[d]The Strangers(0x54686520537472616e67657273),[4]    (0x800efdbb)}
2023-03-01T19:09:49.123533Z 66 [ERROR] [MY-013183] [InnoDB] Assertion failure: fut0lst.ic:81:addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA thread 2320
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
2023-03-01T19:09:49Z UTC - mysqld got exception 0x16 ;
Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.
Thread pointer: 0x25d15281520
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
7ff6dfa78408    mysqld.exe!?my_print_stacktrace@@YAXPEBEK@Z()
7ff6debf6c01    mysqld.exe!?print_fatal_signal@@YAXH@Z()
7ff6debf6943    mysqld.exe!?my_server_abort@@YAXXZ()
7ff6dfa5a75a    mysqld.exe!?my_abort@@YAXXZ()
... etc

Upon trying to restart the server, it said my data was corrupt. I could only get the server running with InnoDB Recovery set to 5 or 6, and so I had to restore from a backup made 24 hours ago to get my server up and running again.

So here are my questions as a newbie:

  • If I had this DB as the master and another server DB set as a slave, would the crash on the slave never happened? Would it have been up-to-date up to the last good transaction before the crash?

  • Would the failover to the slave have been automatic?

  • If I wanted to take what was on the slave and then restore it back to the master to get things working again, how would that have worked? Would I have had to do a complete import of my data? Or just been able to copy over the ..\MySQL\Data folder? What's the quickest recovery here to get things back up and running?

1 Answer 1

  • Assuming the crash was hardware-related, it is unlikely that the Replica would encounter the same failure.

  • The Replica would be up-to-date or very close to it.

  • "Semi-sync" would probably say that the Replica would be fully up to date.

  • Failover is not automatic

  • The usual technique is to change the Replica to be no longer "readonly" and have the clients access it instead of the old Primary. That is, dumping the Replica and copying the contents to the Primary is not necessary.

  • Be sure to read and understand the rest of the details about replication, if you decide to go that way.

  • There are clustering solutions (Innodb Cluster; Galera) that provide automatic fileover. But that requires 3 machines, and they 'should' be geographically separate.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.