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?

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.