Configuring .my.cnf in home directory for multiple databases does not work. Works for single database

Question

Here is my .my.cnf file:

 [client]
 user=user1
 password=somePasswd1
 database=someDb
 [client2]
 user=user1
 password=somePassed2
 database=someotherDb

It works if I just have one entry, as in:

 [client]
 user=user1
 password=somePasswd1
 database=someDb

What is the significance of [client]? What should it have? Also, what if I want to restrict these user-passwords for localhost only?

Gerfen

Answer 1

https://dev.mysql.com/doc/refman/8.0/en/option-files.html says:

The [client] option group is read by all client programs provided in MySQL distributions (but not by mysqld).

The [client] group enables you to specify options that apply to all clients. For example, [client] is the appropriate group to use to specify the password for connecting to the server. (But make sure that the option file is accessible only by yourself, so that other people cannot discover your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs that you use.

The MySQL client programs are in the manual: https://dev.mysql.com/doc/refman/8.0/en/programs-client.html

If you use an option group like [client2] this is not used unless you use the --defaults-group-suffix option.

https://dev.mysql.com/doc/refman/8.0/en/option-file-options.html says:

--defaults-group-suffix=str

Read not only the usual option groups, but also groups with the usual names and a suffix of str. For example, the mysql client normally reads the [client] and [mysql] groups. If the --defaults-group-suffix=_other option is given, mysql also reads the [client_other] and [mysql_other] groups.

In your case, you could run:

mysql --defaults-group-suffix=2

That would make the mysql client read options from the [client2] group in your option file.

"Also, what if I want to restrict these user-passwords for localhost only?"

This is handled when you GRANT privileges to your user.

GRANT ... ON *.* TO 'user1'@'localhost';

By specifying the host after user1, it means the grant only works when user1 connects from localhost. If user1 is trying to connect from any other host, the grants won't work. That includes the password credential itself. Read https://dev.mysql.com/doc/refman/8.0/en/grant.html for more information.

Wong

Are programs written using mysql C libraries considered clients? It is not among the list in the link you provided. I will try but just wanted to know if you are aware one way or the other... – Gerfen

No, the MySQL library doesn't automatically look for the .my.cnf file. The official clients each have code to do that. Sometimes I have written similar code for my scripts and programs that I use as a DBA. – Wong

Understood now. I am using Nodejs mysql library and came across this answer which is what you also suggested. Thanks – Gerfen