mysqldump prompting password even with credentials saved at /home/user/.my.cnf

Question

My ultimate goal is to create a cron script to automatically dump a selected MySQL database (ngsRunStats_FK) once a day. Please note that the MySQL user ngs_run_stats has all privileges on MySQL.

I was expecting the answer from Franklin here would do the job, though when I run on the terminal

mysqldump --defaults-extra-file=/home/user/.my.cnf  -S /nexus/cliniphenome/mysql.sock -u ngs_run_stats -p ngsRunStats_FK --lock-tables=false > test.sql

I am still required to prompt my password. If I enter the password correctly, the dump will work as expected. Though, as stated previously, this should be done automatically i.e. without prompting for the password.

I am assuming that if I am being asked to prompt my password when I call the command on the terminal my cron (not shown here) script will not work. Or is this assumption wrong?

My /home/user/.my.cnf looks like so:

[mysqldump]
user = ngs_run_stats
password = mypassword

and has permissions 600

can you try without having a space between -p flag and the password? -p[password] — Nasif Imtiaz Ohi, Dec 09 '19 at 16:28

score 0 · Answer 1 · answered Dec 09 '19 at 16:32

0

Try this:

mysqldump --defaults-extra-file=/home/user/.my.cnf  -S /nexus/cliniphenome/mysql.sock -u ngs_run_stats -pngsRunStats_FK --lock-tables=false > test.sql

there should be no space between -p flag and the input password.

However, note that this is an insecure way of connecting to your database as the password will be in plaintext within the command and can be visible through the logs.

answered Dec 09 '19 at 16:32

Nasif Imtiaz Ohi

1,563
5
24
45

That's why I am trying first to store the credentials at `/home/user/.my.cnf`, which is only accessible by me (`600`) – BCArg Dec 09 '19 at 17:51
I am not sure what is your question then. What does ngsRunStats_FK do? – Nasif Imtiaz Ohi Dec 09 '19 at 19:16
that is the database that I am dumping – BCArg Dec 09 '19 at 22:14

score 0 · Answer 2 · answered Dec 10 '19 at 09:46

The mistake is that I am passing the -p flag, which will prompt the password. I misread the information from this website. So I should omit the -p flag and the database is actually specified by the --databases flag

mysqldump --defaults-extra-file=/home/user/.my.cnf  -S /nexus/cliniphenome/mysql.sock -u ngs_run_stats --databases ngsRunStats_FK --lock-tables=false > test4.sql

mysqldump prompting password even with credentials saved at /home/user/.my.cnf

2 Answers2