Default password in .my.cnf for a specific user

Question

We've got a backup script which takes a mysql dump every night, and to authenticate we've got a .my.cnf file with

[mysqldump]
password=somepass

However, every mysql user that doesn't pass in a password seems to inherit this password.

Is it possible to lock this into a combination? e.g.

[mysqldump@backupuser]
password=somepass

so it only gets used by that specific user?

score 0 · Answer 1 · answered Feb 19 '21 at 11:41

0

Sure, just use the following:

[mysqldump]
user=backup
password=password

answered Feb 19 '21 at 11:41

mestia

1

From our testing, that just sets the default user AND password to those, it didn't do any logical checks e.g. "if user = backup, then password = password" it purely provides both to any mysqldump command – owenmelbz Feb 19 '21 at 12:56
Every user is using his own account and thus has (or has not) his own ~/.my.cnf – mestia Feb 19 '21 at 13:55
1

SSH user yes, but not mysql user :( – owenmelbz Feb 22 '21 at 21:46

score 0 · Answer 2 · answered Feb 19 '21 at 15:47

I have solved this issue by using MySQL socket connection and authentication with the socket user.

First you need to make sure you have auth_socket plugin installed.

Then the user needs to be created with:

CREATE USER 'user'@'localhost' IDENTIFIED WITH auth_socket;

After this, when you log in as this user and connect to MySQL using UNIX socket, there is no password prompt.

2 Answers2