Problem with mysqldump: "--defaults-extra-file" option is not working as expected

Question

I run the following command from the Windows command line to backup my database:

...\right_path\mysqldump --add-drop-database --databases my_database_name
                         --defaults-extra-file=d:\1.cnf

where d:\1.cnf contains the following:

[client]
user="my_user"
password="my_password"

Unfortunately, I got the following error message:

mysqldump: unknown variable 'defaults-extra-file=d:\1.cnf'

If I do:

...\right_path\mysqldump --add-drop-database --databases my_database_name
                         --user="my_user" --password="my_password"

it works as expected.

What am I doing wrong ?

Answer 1

I found the answer: --defaults-extra-file must be the first option. This works as expected:

...\right_path\mysqldump --defaults-extra-file=d:\1.cnf
                         --add-drop-database --databases my_database_name

Answer 2

Also meet this problem. Found there's another situation that would cause --defaults-extra-file option not recognized.

When you changed IFS in script, it's possible to hit this problem. The solution is to reset IFS before execute mysql statement.

For reference.

Answer 3

For future reference:

The accepted answer is correct and it is required to place --defaults-extra-file option at the first position.

To try it with containers one option is:

$ docker network create dbnet
$ docker run --network dbnet -it --rm \
    --name db -e MYSQL_ROOT_PASSWORD=example \
    mysql echo "[client]\npassword=example\n">.mydbcreds.cnf  
$ docker run --network dbnet -it --rm \
    --name db-client -v "$PWD:/app/" mysql \
    mysql --defaults-extra-file=/app/.mydbcreds.cnf \
        --host db --user root \
        -e "SHOW SCHEMAS;"

Not to forget to keep credentials safe and out of VCS:

echo ".mydbcreds.cnf">>.gitignore

The file can contain all connection parameters (host, user, etc.).

Unfortunately, this file can not contain database name, which would be awesome to have a different file per environment. Now we have all for connection in file and DB name set in the command.

Answer 4

I realize this is Linux-specific, but my searches brought me here.

I'm using bitnami's LAMP stack, and found out that their "mysqldump" is actually a script:

LD_LIBRARY_PATH=/opt/lampstack-5.5.3-0/mysql/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
case "$@" in
  *--no-defaults*)
    exec $0.bin "$@"
    exit
esac
exec $0.bin --defaults-file=/opt/lampstack-5.5.3-0/mysql/my.cnf "$@"

which doesn't appear to allow --defaults-file to be passed in... and work (as per the accepted answer here).