1

I have a MySQL 8 server on a virtual machine. I'd like to start it up when another server wants to communicate with it, and shut it down some time after there hasn't been any connections or queries.

Right now I'm polling the VM to get it somewhat working, but there's gotta be a better way.

I read the MySQL docs on handling Unix signals, but there isn't a "startup" type signal.

I also read about MySQL events, but those seem to be limited to operations within the database like SQL queries.

Does MySQL have a way to push events to a remote server on startup, shutdown, error, etc.?

It's probably possible to achieve in a script on the OS, and then send a curl request to the remote server with appropriate info, but MySQL's a big software and I'm curious if this functionality is hidden somewhere in it.

3
  • 2
    Does MySQL have a way to push events to a remote server on startup, shutdown, error, etc.? No. The only visible solution (except custom UDF, of course) is to register some remote table as fedefated and try to access it (polling). But this is a crutch..
    – Akina
    Commented Nov 24, 2020 at 6:50
  • Just leave MySQL running.
    – Rick James
    Commented Nov 28, 2020 at 0:06
  • @RickJames - Can't afford to run the VM on the cloud 24/7. It may be unused for days or even weeks.
    – slanden
    Commented Nov 28, 2020 at 17:00

1 Answer 1

0

Such a feature cannot be in mysqld in the cloud.

In order not to pay for a cloud VM, it must be totally down. That means that communication with it must be preceded by restarting your image. Only then is mysqld awake. At that point, it is ready to receive connections. (And you must "connect" before doing "insert" or whatever you need to do.)

So, your "it's probably possible ..." paragraph is the approach to take.

To add to the complexity, there is probably no easy way to "automatically shut down" the VM after an idle period. Consider having your "restart" script wait a few minutes, then shutdown. (Where "a few minutes" is tuned to accommodate your typical activity time.)

For security reasons, MySQL deliberately has no built-in way to communicate with the rest of the world. (A common request is to send an email based on some data change.)

Another thing to look into -- a proxy between your clients and the VM-MySQL server. The proxy would live on the client machine(s). It might be able to orchestrate the startup. (There are many Proxy products out there; they are aimed at other things -- HA, sharding, routing, load balancing, etc.)

4
  • "Such a feature cannot be in mysqld in the cloud." - Why?
    – slanden
    Commented Nov 30, 2020 at 16:25
  • @slanden - For mysqld to be awake enough to do something, the cloud provider will charge you money. (Yeah, it may be "possible" to be a tadpole that wakes up after the bog thaws next spring. But Cloud serves are not likely to do that for free.)
    – Rick James
    Commented Nov 30, 2020 at 17:04
  • 1
    My mention of cloud costs was the motivation for the original question, but I thought there was a technical reason for that statement in relation to MySQL's features (or lack of) for communicating signals, MySQL lifecycle events or something. Still a little fuzzy on the technical why not, but the MySQL specific parts of your answer + your suggestion about a proxy is enough to answer the question
    – slanden
    Commented Nov 30, 2020 at 18:42
  • @slanden - MySQL designers made a deliberate choice not to provide any way to, for example, send email. This is touted as a "security feature".
    – Rick James
    Commented Nov 30, 2020 at 21:31

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.