MySQL defaults-file argument not recognized

Question

I am trying to log in to mysql with --defaults-file option on the command line:

$ mysql --defaults-file ~/mycnf.cnf

But I get the following error:

mysql: unknown option '--defaults-file'

But this option is listed in the help:

$ mysql --help
...
Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
The following groups are read: mysql client
The following options may be given as the first argument:
--print-defaults        Print the program argument list and exit.
--no-defaults           Don't read default options from any option file.
--defaults-file=#       Only read default options from the given file #.
--defaults-extra-file=# Read this file after the global files are read.
--defaults-file=#       Only read default options from the given file #.

Whats going on here? Here is the output of mysql --version

$ mysql --version
mysql  Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1

Answer 1

I got the same error below:

[ERROR] unknown option '--defaults-extra-file'.

When I tried to log in with --defaults-extra-file as shown below:

mysql --defaults-extra-file 'C:\ProgramData\MySQL\MySQL Server 8.0\my.ini'

So, I put = just after --defaults-extra-file as shown below, then I could log in:

                           ↓
mysql --defaults-extra-file='C:\ProgramData\MySQL\MySQL Server 8.0\my.ini'

Answer 2

It should be:

mysql --defaults-file=~/mycnf.cnf

You were missing the =.

Also note that the options used to specify option files must precede any other options. See the documentation for specific details.

Answer 3

In my case the issue was misplacement of the parameters.

the --console parameter should have been last...

My original take:

mysqld --console --defaults-file="path-to-ini/my.ini"

While should have run this:

mysqld --defaults-file="path-to-ini/my.ini" --console

Answer 4

To solve the same issue using bash/sh script instead of directly on the cli with OSX, I had to use the full path of the mysql.client app.

$which mysql
/usr/local/zend/mysql/bin/mysql

MySql was installed by Zend Server as shown by the path below and wrapped the mysql.client version:

$mysql --version
/usr/local/zend/mysql/bin/mysql.client  Ver 14.14 Distrib 5.5.27, for osx10.6 (i386) using readline 5.1

The error continues to show until I change how my bash script was calling mysql.

From:

MYSQL=`which mysql`
...
${MYSQL} --defaults-file=/dev/stdin -e "SHOW DATABASES"

output

$ ./import_dbs.sh
/usr/local/zend/mysql/bin/mysql.client: unknown variable 'defaults-file=/dev/stdin'

Change to:

MYSQL=/usr/local/zend/mysql/bin/mysql.client
...
${MYSQL} --defaults-file=/dev/stdin -e "SHOW DATABASES"

outputs

$ ./import_dbs.sh
Database
information_schema
mysql
performance_schema
test

Edit: I could also use the following in my script:

MYSQL=`which mysql.client`

$ which mysql.client
/usr/local/zend/mysql/bin/mysql.client

Evidently there is a slight difference between calling mysql and mysql.client that affects being able to pass --defaults-file or other variables mentioned in the OP as the first parameter, within a script. I haven't tested via the shell

Answer 5

I would like to add another answer to this problem to a similar situation I faced.

In my case, the command was similar to this:

/path/to/mysqld_safe start --defaults-file=/path/to/my.cnf --other-options-here

and mysql would complain

unknown variable '--defaults-file=/path/to/my.cnf'

In order to solve this, I had to "drop" the start command, making the line read like this:

/path/to/mysqld_safe --defaults-file=/path/to/my.cnf --other-options-here

And then mysql would finally start using the specified configuration file.