Bug #88479 Unable to start mysqld using a single config file (and avoiding reading defaults
Submitted: 14 Nov 2017 12:08 Modified: 15 Dec 2017 15:35
Reporter: Simon Mudd (OCA) Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Options Severity:S3 (Non-critical)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: configuration, single config file, startup

[14 Nov 2017 12:08] Simon Mudd
Description:
My desire is to startup mysqld with a single configuration file located at /path/to/my.cnf.

Even though the installed binary may have a list of locations where it may search for a my.cnf file I don't want that to happen.

The usual way of starting mysqld is to do something like mysqld --defaults-file=/etc/my.cnf ...<other options>...

I've tried several ways to do this yet never seen it work.
If you want to run multiple instances of mysqld on the same host you must pay special care to not provide or leave a /etc/my.cnf file about on the filesystem as this may get read.

This means that inadvertently placing a file in the wrong location may trigger mysqld to not startup correctly and this may not be obvious if you have specified an explicit path to read the configuration from.

How to repeat:

Try using:
$ mysqld --defaults-file=/path/to/my.cnf ...    # this will read from /etc/my.cnf or search path
Try using
$ mysqld --no-defaults --defaults-file=/path/to/my.cnf ... # this may give an error but it still doesn't do wnat's wanted and the startup will fail.

I can provide different scenarios where this doesn't work but just using the pre-built binaries from Oracle will trigger /etc/my.cnf being read and if it does not exist and you do --no-defaults --defaults-file=/some/other/my.cnf then it's likely to generate errors.

Here's a sample example. 
* install CentOS 7
* install mysql-community rpms (5.7)
* start up server and set root password
* move /var/lib/mysql to /home/someuser/mysql
* move /etc/my.cnf to /home/someuser/my.cnf
* adjust paths to point under user's /home/someuser directory
* adjust permissions: ownership to someuser

I adjust my.cnf to /home/someuser/my.cnf

[mysqld]
datadir=/home/someuser/mysql
socket=/home/someuser/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

###log-error=/var/log/mysqld.log
pid-file=/home/some/user/mysqld/mysqld.pid

Starting mysqld gives this error:

[someuser@centos7 ~]$ mysqld --no-defaults --defaults-file=/home/someuser/my.cnf
mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 2 - No such file or directory)
2017-11-14T11:58:57.223894Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-11-14T11:58:57.223993Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2017-11-14T11:58:57.383788Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-11-14T11:58:57.383861Z 0 [Warning] Can't create test file /var/lib/mysql/centos7.lower-test
2017-11-14T11:58:57.383899Z 0 [Note] mysqld (mysqld 5.7.20) starting as process 1504 ...
2017-11-14T11:58:57.386622Z 0 [Warning] Can't create test file /var/lib/mysql/centos7.lower-test
2017-11-14T11:58:57.386642Z 0 [Warning] Can't create test file /var/lib/mysql/centos7.lower-test
2017-11-14T11:58:57.386728Z 0 [ERROR] failed to set datadir to /var/lib/mysql/
2017-11-14T11:58:57.386741Z 0 [ERROR] Aborting

2017-11-14T11:58:57.386752Z 0 [Note] Binlog end
2017-11-14T11:58:57.386805Z 0 [Note] mysqld: Shutdown complete

So it looks to me like variable setup etc does not do the right thing.
If I setup datadir=/some/path then I expect mysqld to recognise that and adjust settings appropriately.

Suggested fix:
Provide (and document) clearly a way in which the user can provide one my.cnf file which store the server configuration ([mysqld] section) and the server will ONLY use this explicitly provided file.

The exact cause to this failing is not 100% clear to me but the desire to be able to startup one or more instances without having them interfere with each other should mean that providing a single configuration file should be enough.

Speaking to various people it seems this "issue" is known. People have worked around it for some time (by ensuring the search locations are empty, or by providing an empty configuration file) and then you forget but it only comes to bite you again.

Providing and documenting a way to do this clearly would be most appreciated.
[14 Nov 2017 12:40] Simon Mudd
Note: my example was not using any .my.cnf or .mylogin.cnf files anywhere.
[14 Nov 2017 16:46] MySQL Verification Team
AFAIK, you must do this without --no-defaults:
mysqld --defaults-file=/home/someuser/my.cnf

If you do this instead:
mysqld --no-defaults --defaults-file=/home/someuser/my.cnf

The --defaults-file will be ignored since it is not the first option.
--no-defaults and --defaults-file must always be the *first* option
[15 Nov 2017 15:35] MySQL Verification Team
Hi Simon,

--defaults-file must be a first argument in startup of the MySQL server. Can you please, try omitting --no-defaults and see if it works for you.

It works for me, since I have so many versions installed, each with its separate config file. I have a global one, as well, but none of my instances uses it. I will remove it after we close this bug.

Let us know if it works for you. Thank you in advance.

Many thanks in advance.
[16 Dec 2017 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".