Connecting to remote MySQL servers so they appear as though they are local

Simple! Just create an SSH tunnel. Or a couple of them.

Create SSH tunnel to MySQL server running on port 3306 on 192.168.213.207 so that server appears to be running locally on port 3307:

• ssh -L 3307:127.0.0.1:3306 root@192.168.213.207 -NnT

Create SSH tunnel to MySQL server running on port 3306 on machine2 so that server appears to be running locally on port 3308:

• ssh -L 3308:127.0.0.1:3306 root@machine2 -NnT

Compare the fancyDatabaseName database on each machine using the mysqldbcompare tool (part of the mysql-utilities package):

• /usr/local/bin/mysqldbcompare --server1=root@127.0.0.1:3308 --server2=root@127.0.0.1:3307 fancyDatabaseName

Explanation of flags to ‘ssh‘:

-L [bind_address:]port:host:hostport
Specifies that the given port on the local (client) host is to be forwarded to the given host and port on the remote side. This works by allocating a socket to listen to port on the local side, optionally bound to the specified bind_address. Whenever a connection is made to this port, the connection is forwarded over the secure channel, and a connection is made to host port hostport from the remote machine. Port forwardings can also be specified in the configuration file. IPv6 addresses can be specified with an alternative syntax:
[bind_address/]port/host/hostport or by enclosing the address in square brackets. Only the superuser can forward privileged ports. By default, the local port is bound in accordance with the GatewayPorts setting. However, an explicit bind_address may be used to bind the connection to a specific address. The bind_address of ”localhost” indicates that the listening port be bound for local use only, while an empty address or ‘*’ indicates that the port should be available from all interfaces.

-N will disable the ability to execute a remote command.

-n will prevent reading from stdin.

-T will disable the pseudo-terminal allocation.