MySQL, my.cnf and /etc/alternatives

Question

History (the question is in 2020 below)

2018. After the fresh install of 18.04, two years ago, I wanted to use my /etc/mysql/my.cnf setup from 16.04 to get things back to what they were. But the MySQL install sets two symlinks

• /etc/mysql/my.cnf -> /etc/alternatives/my.cnf
• /etc/alternatives/my.cnf -> /etc/mysql/mysql.cnf

thus I replaced the new /etc/mysql/mysql.cnf with my old 16.04 /etc/mysql/my.cnf.
Things went fine until another MySQL update, which, for some reasons, had my mysql.cnf replaced with the new one. Bug? Did I accept the replacement? Don't think so, but, well, that's always possible.

So what I did at the time was to bypass the alternatives system, i.e. replace the /etc/mysql/my.cnf link with my my.cnf file, and zeroed mysql.cnf. Since my.cnf takes precedence over mysql.cnf that worked (I think I tried to symlink mysql.cnf to my my.cnf file, and there were other trouble, don't remember).

(To be honest, didn't like much the alternatives implementation that I found, cumbersome and counter-intuitive)

---

2020. Did an upgrade from 18.04 to 20.04. And naturally, MySQL re-created a clean environment with the alternatives links. Good.

However, to complete the upgrade, this time I'd like to keep things clean, and as much as possible

• keep using the alternatives system or not (depending on your advice)
• ensure that a further MySQL upgrade (unless it's v. 9) won't overwrite my setup (or it asks)

What is the recommended way to keep a clean MySQL install?

Answer 1

Having worked with MySQL since the hairy days of 3.something, I have found that the best place to have environment variables set is in a separate .cnf file. With modern versions of the database (everything from 5.3 up), these can be written to the /etc/mysql/mysql.conf.d/ directory. This allows me to have a directory structure like:

drwxr-xr-x 2 root root 4096 Aug  6 23:08 ./
drwxr-xr-x 4 root root 4096 Aug  6 23:08 ../
-rw-r--r-- 1 root root 1434 Jun 27  2019 mysqld.cnf
-rw-r--r-- 1 root root  155 Jun 27  2019 z-application.cnf
-rw-r--r-- 1 root root 2893 Jun 27  2019 z-innodb.cnf
-rw-r--r-- 1 root root 1464 Jun 27  2019 z-replication.cnf

The configuration files are read alphabetically, which explains the ugly z- prefix, and the purpose of the files are pretty self-explanatory:

File	Contains
application.cnf	Application-specific settings, such as maximum number of connections, log expiration times, etc.
innodb.cnf	InnoDB-specific settings, often tweaked over time for the hardware the server is running on
replication.cnf	Replication-specific settings.

This allows me to have a bunch of .cnf template files with the basics pre-populated and ready to be used/customised for a new MySQL installation.

---

A little background into the reasoning behind this practice ...

MySQL reads configuration files in a very particular order depending on the platform it is installed on. You can ask MySQL for the name and the order of the config files with:

mysqld --help --verbose

This will spit out (for me) 3,000+ lines but, up near the top, you'll see this:

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf 
The following groups are read: mysqld server mysqld-8.0

For me, there is no /etc/my.cnf file. There is, however, a /etc/mysql/my.cnf file. In that file is just two lines that matter:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

With this, MySQL will go searching for all .cnf files in /etc/mysql/conf.d/, then all files in /etc/mysql/mysql.conf.d/, reading them into the engine in alphabetical order. I prefer to have my variables loaded after all of the default MySQL files are read because it reduces the chance of having my values overwritten with a default. So, because /etc/mysql/mysql.conf.d/ comes after /etc/mysql/conf.d/, my files go in there.

They have never been over-written or modified during an update.

Hope this gives you something to explore.

Answer 2

(To be honest, didn't like much the alternatives implementation that I found, cumbersome and counter-intuitive)

The alternatives system isn't specific to MySQL; it's a general system that's used in Debian and Ubuntu across a wide range of packages. I suggest that you learn how it works and how to integrate with it. It is designed to give users full control - so they can override packaging completely if they wish, or integrate with it instead if they want the benefits that packaging provides.

See the update-alternatives(1) manpage for details.

If you have a specific my.cnf that you want to use no matter what, then you can add it somewhere and use update-alternatives to select it at a higher priority than anything provided by packaging. Then it will integrate and never be overridden.

However, if you do this, understand that your my.cnf is likely to fail when you upgrade Ubuntu in the future, since the available configuration directives change with major MySQL releases. You will need to keep your own my.cnf up-to-date as MySQL changes.

If instead you'd like packaging to help, then you can do what @Matigo suggests in their answer and add only overrides using the .d directories. Where feasible, we arrange for packaging to automatically adapt configuration to avoid breaking MySQL during release upgrades. However we generally only do this for files packaging shipped, not for files you've added yourself.

Ultimately, while it's perfectly reasonable to differ from packaging configuration defaults, it is necessary to expect to have to tweak the configuration when you upgrade between Ubuntu releases, since MySQL's expectations about configuration changes between major MySQL releases.