Remote connections without leaving the mysql shell

Posted in: Technical Track

You probably know that mysql -h host_or_ip can connect you to a remote host.

But did you know that you can change the host you are connected to from within mysql?

The undocumented (as far as I can tell, in the MySQL manual and in the “help” on the mysql command line) CONNECT statement can help.

With just one argument, CONNECT will reconnect to the current server and change the database. For example:

shell> mysql -h localhost -u user -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2457702
Server version: 5.0.51a-3ubuntu5-log (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT @@global.server_id;
+--------------------+
| @@global.server_id |
+--------------------+
|                127 | 
+--------------------+
1 row in set (0.01 sec)

mysql> CONNECT test;
Connection id:    2457704
Current database: test

mysql> CONNECT test;
Connection id:    2457705
Current database: test

Note how every time I used CONNECT I reconnected and got a new connection id.

With two arguments, though, mysql will try to connect to the database (first argument) on the server (second argument). For example:

mysql> CONNECT test db2;
Connection id:    624937
Current database: test

mysql> SELECT @@global.server_id;
+--------------------+
| @@global.server_id |
+--------------------+
|                 16 | 
+--------------------+
1 row in set (0.00 sec)

The interesting part is that now the host is set to db2 as if I had connected with mysql -h db2. For example:

mysql> CONNECT test;
Connection id:    624939
Current database: test

The connection id clearly shows I’m still on db2.

Can I connect back?

mysql> CONNECT test localhost;
Connection id:    2457866
Current database: test

Yes I can! (note: I’d tried CONNECT test db1; first but that acted like mysql -h db1 and user@ip_for_db1 does not have permission to connect — but user@localhost does!)

It seems that any arguments after the second argument are ignored. The username and password of the current connection are reused, and I have not been able to figure out a way to specify a different username and password.

Still, this is a pretty neat function, and it is very nice to be able to “hop” from one server to another without leaving the mysql command line.

Interested in working with Sheeri? Schedule a tech call.

7 Comments. Leave new

Hi!

hey, this is cool stuff! Never realized it was there.

Reply

Is this any different than using “use”.

> use test;

Reply

AppBeacon — yes, with “use” you cannot specify a remote host. You are correct that “connect” with one argument is not different from “use” — however the focus of the post was intended to be on the fact that you could connect to different hosts once you are already in the mysql command line shell.

Reply

Connect is documented in the MySQL Manual:

connect [db_name host_name]], r [db_name host_name]]

Reconnects to the server. The optional database name and host name arguments may be given to specify the default database or the host where the server is running. If omitted, the current values are used.

http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html

Unfortunately this doesn’t show up when typing “help connect” in the command-line interface, but that’s due to the fact that the “help” command brings up help for the server only, and “connect” is a client-side command.

Reply
MySQL: Using CONNECT to Quickly Verify Replication Health
January 27, 2009 6:44 pm

[…] very helpful use of the technique Sheeri described in Remote connections without leaving the mysql shell is making sure that replication is working […]

Reply

Excellent stuff Sheeri. Is there any option to change the user inside the mysql prompt ?

Reply

Suresh — The MySQL manual link that Stephan showed earlier in the comments,

http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html

shows that the db and host are the only arguments that can be used. So the user cannot be changed, unfortunately.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *