Is it possible to have passwords configured per database or per host in .my.cnf

Question

I have the following in my ~/.my.cnf

[client]
password="somepass"

but this is not the password I use for every user@host/database I connect to. Is there some way to specify in the config different passwords for different things so I don't have to type them in?

User @DTest answered a question like this in May of this year — RolandoMySQLDBA, Jul 15 '11 at 19:32

score 83 · Accepted Answer · edited Apr 13 '17 at 12:42

83

As I answered here, you can add a section for each user/host/db you connect to using the syntax in your ~/.my.cnf:

[clienthost1]   # Note: client + host1
user=myuser
password=mypass
database=dbname
host=server.location.com

Once this is in your user's .my.cnf, you can utilize it by doing this on a command line:

$ mysql --defaults-group-suffix=host1

edited Apr 13 '17 at 12:42

Community

1

answered Aug 23 '11 at 13:30

Derek Downey

23,440
11
78
104

As promised, +1 !!! – RolandoMySQLDBA Aug 23 '11 at 15:42
Could you explain this --defaults-group-suffix? – Otheus Oct 08 '15 at 14:09
Nice. It took me a while to figure the rest out from here but for an mysql Cron backup, I can now use
mysqldump --defaults-group-suffix=host1 -P 3306 -h 111.0.0.xxx --ssl -u db_usr db_name > /home/myaccount/backups/db_name_$(echo $(date '+%Y%m%d').sql.gz)

to create a gzipped backup. Thank you!
– user1324409 Dec 30 '15 at 18:48
Note: if you put this in a global my.cnf, such as /etc/mysql/my.cnf, but have a user-defined .my.cnf with [client] defined therein, the latter will override settings in the global file! boo. – Otheus Apr 05 '16 at 08:24
Note that MariaDB’s manpage falsely states that a suffix of x will read the section [client_x] but in reality you need the suffix _x for that – Daniel Böhmer Oct 31 '19 at 21:40
@DanielBöhmer which man page does state that? I'm genuinely curious because there's little info about it aside from one sentence for --defaults-group-suffix option. – woky Apr 26 '20 at 17:55
@woky: I can't recall right now where I found this statement. But it was when I used MariaDB from its official .deb repositories under Debian 9 or 10. – Daniel Böhmer Apr 27 '20 at 21:59

the · Answer 2 · 2016-08-20T11:14:29.290

11

The other answer is correct. Unfortunately mysqladmin doesn't support --defaults-group-suffix (at least not the version I'm using).

Hence I resorted to using --defaults-file=HOST.cnf instead, which works for mysql, mysqladmin and mysqldump.

edited Aug 20 '16 at 11:14

answered Jun 18 '15 at 12:43

the

343
1
3
12

2

Adding this to my .bash_profile made it even easier: alias my-host='mysql --defaults-file=HOST.cnf' – spyle Oct 20 '15 at 13:59

score 10 · Answer 3 · answered Jun 18 '15 at 13:50

10

Put clear passwords in text files is not recommended since mysql 5.6.6.

You can use mysql_config_editor to save passwords encrypted also to provide different passwords for different connections https://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html

answered Jun 18 '15 at 13:50

PerroVerd

246
2
4

3

Security by obscurity... – Federico Razzoli Jun 18 '15 at 14:04
+1 for mentioning mysql_config_editor – RolandoMySQLDBA Jun 18 '15 at 16:26
2

Until this program is (1) backported to previous versions, (2) allows automated password deployment (ie, not forcing passwords to be read on TTY), I do not recommend this method. – Otheus Apr 05 '16 at 08:16
Does MariaDB have comparable support or feature to match mysql_config_editor? – Jeremy Hajek Jul 28 '17 at 15:54
@JeremyHajek No, it does not, and likely never will because the MariaDB devs point out that only provides a false sense of improved security: MySQL 5.6: Security through Complacency? – Bloodgain Jun 29 '18 at 21:26

Is it possible to have passwords configured per database or per host in .my.cnf

3 Answers3