58

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?

1
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
7
  • As promised, +1 !!! Aug 23 '11 at 15:42
  • Could you explain this --defaults-group-suffix?
    – Otheus
    Oct 8 '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! 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 5 '16 at 8: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 Oct 31 '19 at 21:40
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

5
  • 3
    Security by obscurity... Jun 18 '15 at 14:04
  • +1 for mentioning mysql_config_editor 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 5 '16 at 8:16
  • Does MariaDB have comparable support or feature to match mysql_config_editor? 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
10

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.

1
  • 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

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

Not the answer you're looking for? Browse other questions tagged .