A more general approach would be to just run your happy path tests while wrecking the environment (e.g. randomly killing instances, adding latency, dropping packets, whatever).
I’ve found that the latter often uncovers problems that you can use the former to solve.
Testing these sort of things with unit tests can work, but I’m more confident in tests that run on a ‘real’ networking stack, instead of e.g. mocking a socket timeout exception.
Don't overthink everything. KISS
I would like to write a test to verify that my code properly handles the case where one of the MySQL instances has experienced a hardware failure. The point is that I can't do this as a strict black-box test where I merely issue calls to my service's public API.
[edit] And of course "testing the externalities" doesn't help here. I can test the MySQL instances and verify that they are all running, but that doesn't remove the need for my code to handle the possibility that at some point one of them goes down.
Second. You've done this/someone else has done this and now you need to maintain it (we've all been there!). In this case my original post holds. Your test suite mocks the databases for unit tests anyway right? So write some test(s) checking that when the various databases are down appropriate responses are given by your service.