Is there a good way to detect MySQL is "ready?"

Question

I am not a MySQL expert.

I have a script that installs MySQL, starts mysqld, and then uses mysql to do some initialization.

Currently, in order to have this work, I enter into a loop that (apologize for the pseudocode mixing multiple languages):

mysqld_safe /* ... */ & /* ampersand to start in background so we can continue */
while(fileDoesNotExist("/tmp/mysql.sock")) {
    sleepFor100ms();
}
mysql -u root /* and so forth */ initialize.sql

This seems to work (!) but has multiple problems:

• polling smells funny,
• I am not smart enough about MySQL to know whether looking at that hard-coded pathname /tmp/mysql.sock is smart at all.

And yet it's a lot easier than trying to (for example) consume and parse the stdout (or is it stderr?) of mysqld_safe to figure out whether the server has started.

My narrow question is whether there's a way to issue a blocking start of mysqld: can I issue any command that blocks until the database has started, and then exits (and detaches, maybe leaving a PID file), and has a companion stop command? (Or maybe allows me to read the PID file and issue my own SIGTERM?)

My broader question is, am I on the right track, or is there some totally different and easier (to be "easier" for me it would have to be lightweight; I'm not that interested in installing a bunch of tools like Puppet or DbMaintain/Liquibase or whatever) approach to solving the problem I articulated? That is, starting with a .gz file containing MySQL, install a userland MySQL and initialize a database?

Answer 1

Check out the init shell script for mysqld. They do polling, in a function called wait_for_pid().

That function checks for the existence of the pid file, and if it doesn't exist yet, sleeps for 1 whole second, then tries again. There's a timeout that defaults to 900 seconds, at which point it gives up waiting and concludes that it's not going to start (and outputs a totally unhelpful message "The server quit without updating PID file").

You don't have to guess where the pid file is. If you're starting mysqld_safe, you should tell it where it should create the pid file, using the --pid-file option.

One tricky part is that the pid file isn't created until mysqld initializes. This can take a while if it has to perform crash recovery using the InnoDB log files, and the log files are large. So it could happen that 900 seconds of timeout isn't long enough, and you get a spurious error, even though mysqld successfully starts a moment after the timeout.

You can also read the error log or the console output of mysqld. It should eventually output a line that says "ready for connections."

To read until you get this line, and then terminate the read, you could use:

tail -f | sed -e '/ready for connections/q'