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

• Rick James: Is the my.cnf on the same machine as mysqldump?

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.

• Yasien Dwieb: port 3306 is the default port you can safely delete the field -P
• Sabbir Ahmed: Thank you very much! I got it working now without the prompt for password =D You area legend!

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