0

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

1
  • Is the my.cnf on the same machine as mysqldump?
    – Rick James
    Oct 15 '18 at 19:20
0

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.

2
  • port 3306 is the default port you can safely delete the field -P
    – Yasien Dwieb
    Oct 3 '18 at 13:00
  • Thank you very much! I got it working now without the prompt for password =D You area legend!
    – Sabbir Ahmed
    Oct 3 '18 at 13:25
1

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

This site is temporarily in read only mode and not accepting new answers.