Pythian Group

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.