Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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 ?
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
                In addition: the sequence is also critical when you pass normal "--defaults-file" parameter to e.g. mysqld
– domi27
                Feb 26, 2014 at 12:03
                On some systems, mysql and mysqldump are set up as aliases such as alias mysql='mysql --skip-secure-auth'. So if you set --defaults-extra-file first and it still does not work, check that no such alias is in place, using eg. which mysql.
– Mo Sander
                Sep 17, 2014 at 9:08
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.
                The accepted answer is correct. As noted, this is for future reference if somebody is also searching for the same keywords from the question, as I did. This is an Docker example where to store credentials correctly and how to read it with --defaults-extra-file. This stuff is so rare and opening a new question just for a small note is not needed.
– Vladimir Vukanac
                Jul 31, 2020 at 19:23
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.
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 "$@"
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).
Another possibility for --defaults-extra-file to be ignored can by a .my.cnf file in your home directory.
The ~/.my.cnf file overrules the values you are trying to set in --defaults-extra-file
                As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
– Albert Logic Einstein
                Aug 20, 2022 at 10:52
Another possibility would be that you invoked MySQL this way:
mysql --defaults-extra-file my.cnf ...
Note that at least on MySQL 5.7 the program doesn't support separating command-line options and their arguments with space characters, equal sign character must be used in order for the program to recognize the option:
mysql --defaults-extra-file=my.cnf ...
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.