Search code examples
mysqlmy.cnf

How to make mysql client prompt reflect the server you're connected to, not local one

I have 2 servers - serverA and serverB, both have mysql server and mysql client. I have a reverse SSH tunnel set up from serverB to serverA so that I don't have to open ports up on server B to the internet. I access serverB from serverA by doing mysql --host 127.0.0.1 --port 50003

If I am logged into serverA and do mysql --host 127.0.0.1 --port 3306 I want the command line prompt to be me@serverA [dbname].
If I am logged into serverA and do mysql --host 127.0.0.1 --port 50003 I want the command line prompt to be me@serverB [dbname].

For these examples, I am always logged in to serverA and am connecting the mysql client to serverA or serverB

Using the prompt directive in /etc/my.cnf on serverA and serverB, if I do 

[mysql]
prompt = \u@\h [\d]

then I get [email protected] [dbname] on both of them.

if I do prompt = \u@serverA [\d] on ServerA and prompt = \u@serverB [\d] on serverB, then I get me@serverA [dbname] whether I'm trying to connect to serverA or serverB

So it looks like the command prompt is picking up the details for the server on serverA regardless of what I'm actually connecting to.

Is there any way I can make the prompt reflect what I'm actually connected to ?

Solution

  • The prompt setting is a client option. For connections from serverA, the only config file that will matter is the one on serverA. The reason you see 127.0.0.1 in your prompt for both connections is that, from the client's perspective, both connections connect to 127.0.0.1.

    The MySQL client provides a mechanism for specifying option groups (command line argument --default-groups-suffix), and you can use this to approximate what you are looking for. As an example for your needs, you can add the following to your ~/.my.cnf file:

    [clientserverA]
prompt = \u@serverA [\d]>
host = 127.0.0.1

[clientserverB]
prompt = \u@serverB [\d]>
host = 127.0.0.1
port = 50003

    Using this, you can connect to MySQL using mysql --defaults-group-suffix=serverA or mysql --defaults-group-suffix=serverB. The client will use the appropriate host, port, and prompt for your connection based on the suffix you provide.

    You can create a shell function that takes a suffix and creates the appropriate command line if you don't want to type that all out.