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.
