Adding pre/post hooks into MySQL init scripts

On several occasions it may be necessary to do some tasks around the process of starting or stopping a MySQL server such as:

  • Perform a warmup of the database using custom scripts and external information that the init_file configuration may not have access to.
  • Change configuration settings such as innodb_log_file_size and adjust the on disk files that this needs.
  • Change configuration settings such as log_bin (I try to use a relative paths to ensure that different instances use a common setting even if datadir is different, and changing from an absolute to relative path often requires adjusting the binlog index file).
  • Shutting down MySQL may need to be delayed if the SQL thread has open temporary tables or open transactions are still being processed.
  • etc…

You may have others and many of these are likely to be site specific. I could ask Oracle to add lots of configuration options to cover many of these special cases but that is likely to not work as my needs may not match others. However asking them to add hooks to allow me to put in the infrastructure that I need, especially if those hooks are normally disabled and require minimal changes to the current init scripts, might be acceptable. That’s why I’m curious as to what others think about this.

Some may be asking: why do you need this at all? I have certainly seen several issues such as those indicated above which I need to resolve. Also the DBA is not the only person who may have to restart a server. Sometimes this may be done by a system administrator, and on some servers a by developer. These people are not necessarily expected to know about the specifics of one MySQL server compared to another or to know the server’s current state. It is also true that often a server may go for a long time without needing to be restarted, so many configuration changes may be pending. They only need to be done when the server is restarted.

So I was considering the idea of some hooks which would work in a similar way to a system init script, probably composed of the following parts:

  • An /etc/mysql/init.d directory containing any scripts that might need to be run. they can be placed there manually or by system configuration tools
  • An /etc/mysql/rc-start and /etc/mysql/rc-stop directory which would have files named: PRE-nn-scriptname, and POST-nn-scriptname linked to the ../init.d/scriptname file. Again similar to the system init scripts. The nn would, like the number in the system init scripts, define the relative ordering of the execution of any scripts in that directory.
  • A modification to /etc/init.d/mysql so that these scripts would be run (if present) at 4 stages:
    • pre-start: before starting mysqld
    • post-start: after starting mysqld
    • pre-stop: before stopping msyqld
    • post-stop: after stopping mysqld
  • Not absolutely necessary but for completeness a tool which would manage the linking / unlinking of these files.

I can certainly see the use of something like this for my work, but do not see that someone like Oracle will take this unless they see a clear business sense to that, or technical advantage. It would also allow Oracle to provide some “standard” type of scripts if they have seen the need from their customers but also the community could equally share scripts which would be usable for their specific needs but may also be useful to others.

If you have a single mysql instance to manage this may seem like overkill. That’s fine: by default it would not be used. However, as MySQL is gradually being used in larger sites I believe this sort of functionality could be quite helpful as more people may be responsible for managing the servers, and some changes may need to be postponed to a later moment, and the change in the scripts I suggest would make management of the instances simpler.

Have you solved the problem already? If so how? If not does this seem like a reasonable idea?

Tags: , , , , , ,

Leave a Reply