Remote MySQL Dump not working with cnf file

Question

I have created a user 'backup'@'%' with Grant all privileges access to .

I have added the user backup to my.cnf file

When I do the following in CMD:

mysqldump -p3306 -h<dns/ip> db-name table-name > D:\table-name.sql

I get an error 1045: Access denied for the user 'backup'@'' using password: YES when trying to connect

However, when I do the below with user and password credentials it work

mysqldump -p3306 -h<dns/ip> --user=backup --password=<secret> db-name table-name > D:\table-name.sql

I cant get my head around this can someone please help

Answer 1

You need to supply username used to login while logging try this

mysqldump -u backup -P3306 --user=backup -p db-name table-name > D:\table-name.sql

You need to note that from mysql documentation

-P port (capital P)

-ppassword (no cap, no space) also equal to --password=password; -p without anything immediately after it prompts for the password.

Answer 2

Or you can put the credentials in a .cnf file in the correct section. Mysql commands will search that file and use those. This is typically how servers do local backups and maint scripts and such things. I.e.

In /etc/my.cnf

# bottom of my.cnf file
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Then in some file such as /etc/my.cnf.d/credentials.cnf

#
[client]
user                  =  admin
password              =  ';\m^P[9MVJ'
#
[mysqldump]
user                  =  root
password              =  ';\m^P[9MVJ'
#

Make only root has rights to read them. I.e.

-rw-------. 1 root root  657 Dec 1 14:22 /etc/my.cnf.d/client.cnf